My site has two roles: Admin, SecureUser, and three types of content:Admin, SecureUser, Public. I found that the code in the last post from Jeff Prosise wasn't managing roles in the SiteMap. For a visitor to the site that has not logged on (anon), the site map improperly showed Admin and Public pages. I checked the web.config and the SQL statement so it had to be either the database caching or the sql provider for sitemaps.

I google'd and found several posts in 2006 about this but none since so I knew it had to have been resolved. I found this blog post by Ishai Hachlili that had the code to deal with roles correctly. I added the code and the problem went away.

I'm not sure the code is robust enough to handle a large site but it fixed the problem for now.

As an aside, several of the 2006 posts said to just fix the web.config by making sure the <location> tags were correct. This meant any user could see links for the site that were restricted but would be denied access once they actually clicked on the page.  The location tags are only one solution to the problem. You need to have the sitemap, roles and the <location> tag to solve the problem. I go one step further and have the master page (1 for each area of the site) check the user and deny access.

I stared working on my sitemap tonight. Never done the SiteMap stuff before. Opened my Pro ASP.NET 2.0 in C# 2005 that I use as a jump off point for intro topics I haven't touched yet. The chapter is great but I like my data in the database which gets backed up every night. I understood enough and even got a test page up using nested site map files. Hurray!

Now on to the SQL provider. I knew there had to be one so I googled and found an article with code by Jeff Prosise. The article covers the .cs file that is the SQL provider, as well as the web.config changes, the T-SQL, and the aspnet_regsql commands.

After you get the provider up and working, don't expect the SQL provider to show up in the Data Source Configuration Wizard:

You'll have the change the SiteMapDataSource to specify the Provider that you added to the web.config for SQL:

<asp:SiteMapDataSource ID="TotalSiteMap" runat="server" SiteMapProvider="AspNetSqlSiteMapProvider" />

 

But now my data is in the database and I changed the proc to be NetTiers-compliant so I can use their Admin pages for my data management.

The Problem

I want to show a page with all the user-settable properties. Any properties that the user hasn't set should be shown but the value should be empty or null or some nice text indicating such. However, what is the BEST way to design this?

The Tables

I have a [Properties] table. I have a [UserProperties] table that has a FK back to [Properties]. There is only a row in the [UserProperties] table is the user has set one. If not set, no row. So back to what I want to show: in the user's account area, a page of all properties where any properties that have been set show that value, otherwise some text indicating the property has not been set. I'm going to go through every option to go from DB design to .Net code for the page that Wayne and I discussed on this fine rainy, sunny day.

All SQL

Most obvious from a database perspective: T-SQL join (1 db call) that includes all properties so depending on how it is written, right or left join. The problems with this are that the NetTiers layer is table-based. So I'm returning the properties for the user, but if the [UserProperties] table doesn't have the record, then NetTiers can't make an Entity object for that record because it requires the [UserProperties] PK for creation.

All .Net

Most obvious from a .aspx perspective: N+1 db calls. First page is a list of all properties (1 DB call), with each row using the userproperties entity for that property (1 call for each property). Any property that doesn't have a valid object in the userproperties shows text saying the property is not set. I hate this approach. This means both SQL and .Net have to chug through unnecessary work. Yuk. Yuk. Yuk. However, I can write it quickly. Hmm, write it quickly but spend eternity hating what I've done. Or scratch my head some more.

Other Answers

Less obvious: Work with NetTiers to have a custom proc and custom c# code to get this working. This should be easy. I know the proc code and I know where to tie into NetTiers. However, I still have an entity without a PK. Hmm, I'll have to think some more.

Less obvious: I'm using NetTiers as a stand-alone Library so how about sub/super class (depending on your perspective). Add a custom User class to my library that does what I need to do with a Properties object and a UserProperties object. I don't see how this reduces the db calls.

So far, I don't like any of these but I have a low-traffic site so I'm going to opt for the All .Net answer until I come up with a much better answer. Perhaps the table design is the problem.

Any thoughts? Suggestions?

I love it when I'm just cleaning up some code after some major changes and I discover code that has been sitting there a while that would never have worked. I don't even have to run/test it. I can read it and know it's broken. So is that a win for finding a bug and fixing it before it showed up or is that WTF? for having such an obvious bug in the first place.

The days when I don't have to deal with web.config are great. The days I have to go in there are bad, bad, bad. Why can't web.config be easy? Why can't it tell me more, and complain less. Why can't it be easily organized in some sort of logical pattern instead of loosey goosey? The registry looks great compared to web.config.

Is this fixed in the latest VS or .Net?

Previously, a few of us held a discussion about the best way to concatinate a number of string array elements into a single string. You can find details here. Last week, I needed something a bit more general purpose. Came up with an extension method on IEnumerable that will accomplish this for any type.

public static string Fuse<TSource>(this IEnumerable<TSource> source, string separator)
{
    if (source is string[])
    {
        return string.Join(separator, source as string[]);
    }

    return source.Aggregate(new StringBuilder(),
        (sb, n) => sb.Length == 0 ? sb.Append(n) : sb.Append(separator).Append(n),
        sb => sb.ToString());
}

The method is optimized to use the string.Join method when the underlying type is a string. Join uses unsafe code and is very quick. Here are a few example uses:

string[] names = new string[] { "Andy", "Wayne", "Dina", };
string allNames = names.Fuse(", ");  // "Andy, Wayne, Dina"

int[] numbers = new int[] { 1, 2, 3, 4, 5, };
string allNumbers = numbers.Fuse(":"); // "1:2:3:4:5"

object[] objects = new object[] { 1, "ten", 5.5, true, false, };
string allObjects = objects.Fuse("/"); // "1/ten/5.5/True/False"

And finally Wayne's Codesmith version for all procs prefaced with 'aspnet_':

<%@ CodeTemplate Language="C#" TargetLanguage="C#"  %>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="System.Design" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
USE [<%=SourceDatabase.Name%>]

GO

<%

foreach(CommandSchema command in SourceDatabase.Commands)
{
 %>
 
 <%
 if ((command.Name.Length>7) && (command.Name.Substring(0,7) == "aspnet_"))
 {
%>
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<%=command.Name%>]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[<%=command.Name%>]

GO

<%
 }
}
%>

After generating all CRUD procs with several different naming prefixes, my database was looking messy. So I knew what I wanted to produce but I needed to get rid of all these other procs. So how to quickly drop all the procs. Wayne said he had a codesmith template for it but I couldn't wait. I google'd and found another person that had the same problem after a heavy CodeSmith NetTiers session. His method was not too different from my post from yesterday. I did my final gen of procs via NetTiers but instead of going into the database, sent them to a file. I opened the file to find the following code which should have been both obvious in that NetTiers would need it and where I should look for it:

BEGIN

DECLARE @procedureName SYSNAME

DECLARE c CURSOR FOR

SELECT name FROM sysobjects WHERE type = 'P' AND objectproperty(id, 'IsMSShipped') = 0

AND name LIKE 'NetTiers_%' AND name NOT LIKE 'WW_{0}_%'

OPEN c

FETCH NEXT FROM c INTO @procedureName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC('DROP PROC ' + @procedureName)

FETCH NEXT FROM c INTO @procedureName

END

CLOSE c

DEALLOCATE c

END

In Sql server query window, execute query that selects all stored procedures with the prefix you want (such as procedures that start with 'cust'):

SELECT 'DROP PROCEDURE ' + Name FROM sys.objects

WHERE type in (N'P', N'PC')

and name like 'cust_%'