Zack's Fiasco

LINQ and Code Generation Slides

2009-01-14T12:26:00.001-05:00

On Monday I did a presentation with Chad Brooks and Dave Cameron at our local .NET User Group: MaconDotNet.

My sections were on LINQ and Code Generation. Other than me running a little long, the presentation went great. Here are my slides and examples but head over to the user group page to grab Chad and Dave's slides and examples when they get posted.

If you find any errors or typos, let me know.

(Note: I did a section on LINQ and a section on Code Generation. Not "LINQ and Code Generation")

LINQ and Code Generation Slides (Zack)
LINQ and Code Generation Examples (Zack)

ASP.NET eCommerce Solutions?

2009-01-05T12:41:00.001-05:00

I had a long break after Christmas that I used to work on some projects. Sometimes it is hard to get any free time and it was a well enjoyed break.

I didn't get as much done on my XNA project as I would like but I've been studying the book pretty thoroughly.

My main project that I worked on was to build a web page for my dad's business. He has an old brink-and-mortar style business that was started by my grandfather. My dad is pretty tech savvy but he never got around to building a web page for his business so I thought it would be a good project.

I got a lot done on that and I'm talking to my sister and my wife to help me finish writing content for the site.

For my dad's web page I decided to investigate what it would take to add an eCommerce section and see if he was interested in selling a few items over the 'net as well as in person. So I started looking for a good ASP.NET based free eCommerce solution preferably that had built in support for PayPal's free (mostly) services. I figured there had to be lots of them out there but I didn't know one off the top of my head so I decided to look.

Turns out I was wrong, or at least I couldn't find as many as I thought. osCommerce seems to dominate the PHP world with several other strong competitors, but I didn't find as many stand-out ASP.NET kits.

However I did find 2 that seemed to be the best.

Microsoft used to have an eCommerce starter kit that you could download for ASP.NET. I don't know the details of how this happened but the kit has now been taken over by someone else and is called dashCommerce. The code is published under a free open source license and a commercial license with the main difference being a support agreement and the requirement that the free version must always display a message at the bottom of the page that reads "Powered by dashCommerce".

The other good product I found, which is also listed on Microsoft's ASP.NET web page, is DotShoppingCart. This product has as far as I can tell the same general options as dashCommerce, that you can use a free open source version as long as you leave the "Powered by DotShoppingCart" message at the bottom of every page or you can buy a commercial license with a support agreement.

I downloaded both products and tried them out using PayPal's sandbox. (btw, PayPal's sandbox is a pain to use.) I liked both. They are both similar in the features that they offer. They both have basic CMS tools and product catalogs. Both allow products to have configurable attributes like Size or Color or whatever you need. dashCommerce gives each product a different SKU per attribute while DotShoppingCart seems to give a product one SKU no matter the attributes. Both require SQL Server 2005 Express (or greater) with Advanced Services. I believe the main reason for this is that both use the Full Text searching. I don't know if either uses any of the other Advanced Features. Both have source code. dashCommerce uses SubSonic and log4net while DotShoppingCart uses the MS Enterprise Library. Both appear to have IPN support but I didn't test it.

I would also like to point out that ComponentOne also offers a free control library for dealing with PayPal. I haven't downloaded it or looked at it but it might be worth checking out and kudos to ComponentOne for offering it for free.

What do most people use? Either of the items I've listed or do you roll your own? Or is there another free or low cost kit or product that I have missed?

Zune bug revealed

2009-01-05T12:31:00.001-05:00

A little update on the Zune issue I mentioned in my last post. Aeroxperience gives a good explanation of the bug and shows some source code here. The problem ended up being a bad way to calculate years and the date combined with a while() loop with a case that can loop forever. Check it out.

Zune 30 GB crash came and went

2009-01-01T16:16:00.000-05:00

This may have came and went without you even noticing.

On 31 Dec 2008 all 30 GB Zunes locked up and stopped working. If you think that MS doesn't make a 30 GB Zune, your confusion is understandable. the 30 GB models were the original Zunes which had the circle style navigator instead of the touch based squarish navigators all the new ones have.

(quick side note: I like the new Zunes. I got my Mom a 4 GB Zune for Christmas and I hadn't realized until I played with it that the little square is touch sensitive. I thought it was just a square version of the circle joystick that mine had but its actually very nice upgrade. It was new to me even if this feature has been out a while. Now back to my main point...)

I use my Zune quite a bit. In fact I had just used it on Tuesday when I went running. But I hadn't used it yet on Wednesday when I saw an article on Slashdot about Zunes mass crashing. I said to myself, "That's weird." and turned mine on to see that it too was locked up.

Turns out there was a bug in the original Zune in how it dealt with leap years. 2008 was a leap year and the Zune got confused because it was one day longer than it expected.

I was wondering how long it would take for a fix to come out but it was pretty quick as it turns out. In fact I didn't have to do anything except let the battery run down. See here for the details from MS, but all you had to do was let the battery die then wait until after the new year started in GMT and re-synch your Zune and the leap year problem went away.

So I guess it was mostly a much ado about nothing unless you were using your Zune for your New Years party music.

(another side note: Over my Christmas break I've been reading about XNA Game Studio 3.0. Thats MS tools to write games for Windows, XBox 360, and your Zune. I mention this here because I remember reading that if you want your game to work correctly on the old 30 GB Zunes like mine that you had to be careful how you used the joystick/navigator because it only has up, down, left, right, and click while the new ones have a much more precise control which can detect a greater degree of movement in every direction. Kind'of interesting, to me at least.

Here is the XNA book I'm reading now. It doesn't get into super complicated aspects but it covers all of the basics so I would recommend it as a good getting started book.

Here are some other XNA 3.0 books that are coming out in 2009 that I'm interested in. When I bought the above book there were only a few other 3.0 books slated to come out soon. Now it looks like there are quite a few so I'm only listing a few of the most interesting looking ones:

)

Getting SQL Express Studio upgraded when upgrading SQL Server Express 2008

2009-01-01T15:47:00.001-05:00

I've been running SQL Server Express 2005 and the Express Management Studio for all my DB development. Ever since SQL Server 2008 came out I've been thinking about upgrading and today I decided to. However, after I upgraded I noticed that my SQL Management Studio didn't upgrade. After a few minutes I couldn't figure it out so I did a web search and found that Luis Rocha had already figured out the solution to the problem.

You can read his solution here. Some of the screen shots weren't working when I read it and I think he mis-named one of the items to click on Step #4 but much thanks to him for figuring it out.

Essentially, the solution is to close the installation program after upgrading SQL Server. Then, re-run the installation program and select Installation->New SQL Server stand-alone installation or add features to an existing installation. When you are prompted for a new install or add features then select your current instance. Finally, on the feature tree you can select the features to add including the Management Studio.

IE Modal Dialog - Debug Y/N Rant

2008-11-20T14:24:00.001-05:00

I first wrote this up as a post on the ASP.NET Client Side Web Development Forum when I realized that my blog is badly neglected and I ought to be writing stuff there. You can find my forum post here to see if anybody responded over there.

Yesterday I was reading the Visual Web Developer Blog. They have a really good article on JavaScript Intellisence in Visual Studio. http://blogs.msdn.com/webdevtools/archive/2008/11/18/jscript-intellisense-faq.aspx

In the article and comments the author was really encouraging users to post their issues with the technology because they really did use it to improve the products.

That request got me to thinking about the different little issues that I run into as a developer and I was reminded of something that I deal with countless times every day. I started writing it up as a comment in that blog post but I stopped because it was a little off topic and I didn't want to pollute the blog comments with off topic stuff.

So I decided to write up my little rant here .

I don't know how many other people this affects but the following issue affects me every day. In order to be able to debug JavaScript in web pages, I leave my browser in "debug" mode all the time because it is a pain to switch it on and off. This allows me to quickly jump into some JavaScript if I find a problem. However, the consequence is that now every page I browse to with IE pops up that little dialog asking me if I want to debug a script error on the page. It is amazing to me how many pages have so many script errors (at least in IE). I also think this causes a lot of my IE crashes especially when I get 2 or more dialogs in deadlock with each other on different tabs. Does anyone else get this?

Every time I go to cnn.com I know before I even type in the address that I'm going to get at least 2 Debug popups.

Something that compounds the problem for me is that I'm a "Right Click -> Open in New Tab" browser. As I read, if I encounter a link that I'm interested in I open it in a new tab and keep reading. After I finish the page I'm on, I go to the next tab and start reading. But this causes conflicts and lockups when multiple tabs popup Modal Debug Dialogs and I think that leads to a lot of the crashes that I get.

But it is annoying even if you don't browse that way. This problem is at its worst if you even encounter a page that causes a LOT of JavaScript errors or if you browse to a page that triggers an error when your mouse hovers over an element that you have to cross in order to get your mouse out of the browser window so you can close it. Every time you try to get the mouse out you trigger a modal dialog. After you close it, you try to get out again and re-trigger the modal dialog.

So how do other people deal with this? Is there a trick that I don't know that would make this more bearable?

How about the IE team? Could that change how this works so that it wouldn't cause so many problems and halts when I'm working? I would love to see a way to turn debugging on and off easier. I would also love to see the modal dialog removed and replaced with something else like a little error icon or something. The work on IE 8 has been awesome so far. the new debug and dev tools are pretty neat. How about making it easier for web developers to browse the web normally when not debugging?

MS Office 2007 "Document Information Panel" Problems

2008-10-15T11:09:00.001-04:00

I've gotten behind on my posts here but this is worth recording. I don't know how many other people were having this problem but when I first searched for a solution I couldn't find one.

The problem I was having was that when I would try to view a document's Properties in Word or Excel 2007 I would get the error "Document information panel was unable to load".

It turns out that the problem is a missing DLL in one of Office's folders.

Thanks to Roman Hnatiuk for finding the solution. You can can read his fix here.

I also want to thank William Boyer for pointing me in the right direction when I couldn't find the answer.

Digital Picture Frames infected with a computer virus

2008-01-23T11:43:00.001-05:00

This is great! For Christmas, my wife and I bought digital picture frames for my parents and her parents. We looked at all the different ones and picked one from Insignia because it was a good trade-off between price, features, and picture size.

Well, wouldn't you know it I found out today that a few of the very model that we bought were infected with a computer virus.

You can read Insignia's announcement here.

The article states that the virus was an older virus that is easily detected and deleted by current anti-virus software. At Christmas, I loaded some photos onto each of the frames before we wrapped them up and I didn't detect a virus when the frame was connected our computer, so I feel pretty safe that the ones we bought were ok. But it really sucks that I now have to tell my dad and my wife's parents to watch our for their "nice" new Christmas gifts.

The picture frames were purchased at Best Buy. Its not Best Buy's fault that they were defective, however I do think that Best Buy should have moved quickly to alert their customers. I would have respected that. I have read that there is an announcement from Best Buy but I have yet to see or hear it and so I am disappointed in Best Buy.

I first heard about this when my wife saw an article appear on The Red Tape Chronicles on MSNBC.com. You can read that article here.

Insignia is the one who should really be ashamed. They are the ones who manufactured this product. I am glad to see that they have this article up on the front of their web page, but how many people saw that? I don't make a habit of visiting Insignia's web page. They should have done more to prevent this and done more to alert their customers. Is Insignia going to be liable to any of their customers who lost data or had their lives disrupted by having to deal with a computer disaster like loosing data or worse spreading across a network or hurting someone's reputation by sending out spam emails?

New Address and Missing Images

2008-01-22T12:20:00.001-05:00

Good News, Zack's Fiasco now has a new URL: http://Blog.ZacksFiasco.com/ !

If you have anything that links to the old address http://ZacksFiasco.blogspot.com/ it should continue to work. However it might be a good idea to go ahead and update your links. If I ever move off of Blogspot then the old URL will stop working.

Now the bad news. Because of the URL change, Blogspot has stopped serving up some of my screenshots that I have in my articles. I am currently researching a solution and will fix it ASAP. In the meantime, you should still be able to read the articles and I apologize for the problem with the screenshots.

Screenshots are working again. I just had to upload them to a new location and repost all my old articles.

Build a complete Stored Procedure based Data Access Layer using Code Generation - Part 2

2008-01-20T03:08:00.000-05:00

All code published in this article is published under the Microsoft Public License. See source code download for a copy of the license.

Code can be downloaded from here.

During the course of writing the next part in this series, I made some changes to the scripts. I fixed some bugs in how computed columns were handled, cleaned up some inefficient code, added a lot more comments, and I changed the Insert and Update procedures to make better use of the SQL Server 2005 OUTPUT clause in returning computed and identity columns. This leads into a good discussion of the structure of MyGeneration scripts, how to change them, and how to version them.

Before I begin discussion of the code there are some things you need to know about MyGeneration scripts. Each script is identified by a Unique ID which is a GUID. If you edit a script that you did not create, then it is a good idea to create a new id for that script. Otherwise it could be overwritten or confused with the original script. It is also a good idea to update the title to reflect that it is a modified version of the original.

This also requires you to consider what to do when updating your own script. What do you do if you make a change or fix a bug in your own script? Should you create a new id or keep the old one? One option would be to create a new id every time you edit a script, but that is tedious and would also clutter the namespace with many versions of the same script.

Recommended Reading

For my scripts, if the change is small and does not cause a breaking change in the interface then I keep the same id and I add a version to the Title and update the minor version number. If the change is major, then I create a new id and update the Title of the script to include a version number and update the major or minor version number.

In this example, I'm going to modify the SQL that the stored procedures produce, so I'm only going to update the minor version number and keep the same id.

As you can see from the script properties, the CRUD script is written in JScript. This script has the following basic format.

var fkProcList = new Array();

for (var i = 0; i < tablenames.Count; i++)
{
    // Loop through all the columns of the table 
    for (var j = 0; j < tableMeta.Columns.Count; j++) 
    {

    }

%>
Stored procedures
<%

    for(var x = 0; x < tableMeta.ForeignKeys.Count; x++)
    {
%>
SelectByFK procedures
<%
    }

    // Save this set of procedures to disk
    output.save(filename, false);
    buffer += output.text;
    output.clear();
}

output.write(buffer);

There is a lot left out of this skeleton, but essentially the script loops through each table and processes each column. The script then creates the Select, Select All, Insert, Update, and Delete procedures. Then the script examines the foreign keys and creates the SelectBy Foreign key procedures. Then all of the TSQL is written to a file and the process starts over with the next table.

The CRUD script v1.0 produces the following TSQL for INSERT and UPDATE.

-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    

    INSERT INTO [dbo].[Table1]
    (
        [Table1Id],
        [Col1],
        [Col2]
    )
    VALUES
    (
        @Table1Id,
        @Col1,
        @Col2
    )

    SELECT         @rowversion = [rowversion]
    FROM [dbo].[Table1]
    WHERE         [Table1Id] = @Table1Id;

    RETURN @@Error
END
GO

-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @t TABLE(x int);    

    
    UPDATE [dbo].[Table1]
    SET
        [Table1Id] = @Table1Id,
        [Col1] = @Col1,
        [Col2] = @Col2
    OUTPUT 1 into @t(x)
    WHERE 
        [Table1Id] = @Table1Id AND
        [rowversion] = @rowversion

    IF(SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR('Concurrency Error',16,1)
    END

    SELECT         @rowversion = [rowversion]
    FROM [dbo].[Table1]
    WHERE         [Table1Id] = @Table1Id;


    RETURN @@Error
END
GO

These procedures add and update records to the table and then run a second query to retrieve the rowversion which is returned as a OUTPUT parameter. The purpose of this is to allow an application to continue to use the data they have and if necessary perform an update. If the rowversion wasn't returned, then the application would have to query the entire record again in order to perform an update.

I would like to modify the code generation script so that it produces INSERT procedures that don't need to run a separate query to return the rowversion.

Using a text editor, I edited the above TSQL until it looked how I think I want it. After testing the new procedures, we end up with the following:

-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE @t TABLE
    (
        [rowversion] binary(8)
    );

    INSERT INTO [dbo].[Table1]
    (
        [Table1Id],
        [Col1],
        [Col2]
    )
    OUTPUT 
        rowversion
        INTO @t
        (
            [rowversion]
        )
    VALUES
    (
        @Table1Id,
        @Col1,
        @Col2
    )

    SELECT
        @rowversion = [rowversion]
    FROM @t

    RETURN @@Error
END
GO

-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @t TABLE
    (
        [rowversion] binary(8)
    );
    
    UPDATE [dbo].[Table1]
    SET
        [Table1Id] = @Table1Id,
        [Col1] = @Col1,
        [Col2] = @Col2
    OUTPUT
        [rowversion]
        into @t
        (
            [rowversion]
        )
    WHERE 
        [Table1Id] = @Table1Id AND
        [rowversion] = @rowversion

    IF(SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR('Concurrency Error',16,1)
    END

    SELECT
        @rowversion = [rowversion]
    FROM @t
    WHERE
        [Table1Id] = @Table1Id;

    RETURN @@Error
END
GO

The updated procedures use the new OUTPUT clause to return the rowversion to a table variable. We can then query the table variable for the rowversion and return it in the OUTPUT parameter. The query against the table variable should be much faster than a query against the main table since the table variable has fewer records and is in memory.

In order to produce this new TSQL we need to modify our code generation script.

When the script loops over each column, it builds the pieces it needs to build the the procedures. The variable insertParams contains the string of comma seperated parameters of the Insert procedure. The variable insertFields is the list of columns to be inserted by the insert statement. The variable insertValues maps the insert parameters to the columns.

The section of code that produces the INSERT procedure looks like the following:

-- Proc: <%= insertProcName %>
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="<%=tablename%>" commandType="Insert"/>
CREATE PROCEDURE [<%=mySchema%>].[<%= insertProcName %>]
(
<%= insertParams %>
)
AS
BEGIN
    SET NOCOUNT ON
    <% if(updatedOn == true) { %>
    set @UpdatedOn = getdate();
    <%}%>

    INSERT INTO <%= tablenameFull %>
    (
<%= insertFields %>
    )
    VALUES
    (
<%= insertValues %>
    )
<%= (insertAutoKeyCode == "" ? "" : "\r\n" + insertAutoKeyCode) %><%

if (hasComputedFields) 
{
    insertComputedCode += "\r\n\tFROM " + tablenameFull + "\r\n";
    insertComputedCode += "\tWHERE " + deleteWhere + ";\r\n";
}

%>
<%=insertComputedCode%>
    RETURN @@Error
END
GO

The deleteWhere variable may stand out as being out of place. This variable contains the code for the delete WHERE clause, but some of the other procedures require exactly the same code so it gets reused in several places.

Recommended Reading

One of the things to notice is the computed fields section. In the current script, this is where the timestamp field value is retrieved since technically a timestamp is computed automatically. So when we change the code that returns the timestamp columns, this also needs to work for other computed columns.

In order to retrieve the computed columns we need to create our table variable, specify our OUTPUT clause, copy the values from the table variable into the OUTPUT parameters. This is accomplished by using these three variables.

var insertComputedTableVar = "";    // generated TSQL to hold computed values
var insertComputedCode = "";    // generated TSQL to retrieve Computed values
var insertComputedReturn = "";    // code to return values into output parameters

You can see the first variable used in the script above, but we are going to modify the values it holds. The second two variables are new. In addition, we are getting rid of insertAutoKeyCode. AutoKeys are what MyGeneration calls like identity columns and in our modification they will be handled by the computed column code.

The code below is executed inside a loop over each table's columns and populates our three variables. It uses a simple pattern to append a comma and new line if there is more than one computed value.

// generate code to retrieve computed values on insert
if (column.IsComputed || column.IsAutoKey)
{
    hasComputedFields = true;
        
    // build the list of values to go in the
    // OUTPUT clause
    if (insertComputedCode != "") 
    {
        insertComputedCode += ", \r\n";
    }
    
    insertComputedCode += "\t\tINSERTED.[" + column.Name + "]";
    
    // build the list of columns for the table variable
    // to hold the OUTPUT values
    if(insertComputedTableVar != "")
    {
        insertComputedTableVar += ", \r\n";
    }
    
    if(column.DataTypeName == "timestamp")
    {
        insertComputedTableVar += "\t\t[" + column.Name + "] binary(8)";
    }
    else
    {
        insertComputedTableVar += "\t\t[" + column.Name + "] " + column.DataTypeNameComplete;
    }
    
    // build a list of select columns to return 
    // output parameters
    if(insertComputedReturn != "")
    {
        insertComputedReturn += ", \r\n";
    }
    
    insertComputedReturn += "\t\t@" + paramName + " = [" + column.Name + "]";
}

Take note that this code contains a special case for timestamp in the table variable code that it generates. The table variable is used to store values that get spit out by the insert statement when we use an OUTPUT clause. So we declare a column in the table variable for each value we want to output and we create each column in the temp table to be the same data type as they are in the real table. The only problem is if we declare a timestamp column on our temp table, it wants to behave the way timestamp columns always behave and it will forbid you form trying to insert a value into it. The solution is to use a binary(8) column to store the timestamp and all is well.

With this new code we can update our procedure generation script to the following.

-- Proc: <%= insertProcName %>
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="<%=tablename%>" commandType="Insert"/>
CREATE PROCEDURE [<%=mySchema%>].[<%= insertProcName %>]
(
<%= insertParams %>
)
AS
BEGIN
    SET NOCOUNT ON
    <% if(hasComputedFields) { %>
    declare @t table
    (
<%=insertComputedTableVar%>
    )
    <% 
    }
    if(updatedOn == true) { %>
    set @UpdatedOn = getdate();
    <%
    }%>

    INSERT INTO <%= tablenameFull %>
    (
<%= insertFields %>
    )<% 
    if(hasComputedFields) { %>
    OUTPUT
<%=insertComputedCode%>
    INTO @t<%
    }%>
    VALUES
    (
<%= insertValues %>
    )
    <%if(hasComputedFields) { %>
    SELECT
<%=insertComputedReturn%>    
    FROM @t
    <%}%>
    RETURN @@Error
END
GO

The same process is repeated for update and then all we have to do is regenerate our stored procedures.

The final TSQL looks like this.

-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    
    declare @t table
    (
        [rowversion] binary(8)
    )
    

    INSERT INTO [dbo].[Table1]
    (
        [Table1Id],
        [Col1],
        [Col2]
    )
    OUTPUT
        INSERTED.[rowversion]
    INTO @t
    VALUES
    (
        @Table1Id,
        @Col1,
        @Col2
    )
    
    SELECT
        @rowversion = [rowversion]    
    FROM @t
    
    RETURN @@Error
END
GO

-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int,
    @rowversion timestamp = NULL OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    
    declare @t table
    (
        [rowversion] binary(8)
    )
    
    UPDATE [dbo].[Table1]
    SET
        [Table1Id] = @Table1Id,
        [Col1] = @Col1,
        [Col2] = @Col2
    OUTPUT
        INSERTED.[rowversion]
    INTO @t
    WHERE 
        [Table1Id] = @Table1Id AND
        [rowversion] = @rowversion

    IF(SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR('Concurrency Error',16,1)
    END

    SELECT
        @rowversion = [rowversion]    
    FROM @t
    RETURN @@Error
END
GO

The updated code generation scripts are posted as Release 1.2 on CodePlex.

Give this a try in your own databases.

Bill Gates' Last Day Video

2008-01-16T15:44:00.001-05:00

I just saw this and it was hilarious.

DAL scripts were updated

2007-12-10T00:28:00.001-05:00

svnbridge keeps crashing so I wasn't able to updated the code on Codeplex Source Code section hasn't been updated, but I posted a new release which is the templates in source code form.

DAL Code generation scripts are currently written for MyGeneration v1.2

2007-12-07T17:47:00.001-05:00

As chance would have it, MyGeneration released v1.3 the same day I published this project. The scripts "Data Access Layer (C#, SQL Server) v1.0" and "Data Access Layer (C#, SQL Server) v2.0" were written to take into account certain bugs in MyGeneration v1.2. Those bugs have been fixed in MyGeneration v1.3 so if you are seeing weird results like SQL Server datatypes being used as C# data types then this is why.

An update will be released soon that fixes this problem and I will post an update here and on codeplex when this issue has been resolved.

Build a complete Stored Procedure based Data Access Layer using Code Generation - Part 1

2007-12-07T02:33:00.001-05:00

All code published in this article is published under the Microsoft Public License. See source code download for a copy of the license.

Code can be downloaded from here.

Intro

Data Access Layers are one of those problems that people keep coming up with solutions for. There are many reasons for this, but basically it boils down to two things. People are different and different people solve problems differently. Data is different and people need to access different kinds of data differently.

I wanted to build something that was simple, easy to use, easy to extend, and compatible with data binding. I wanted to be able to automatically generate well written stored procedures for all the standard CRUD (Create Read Update Delete) and also allow me to add my own custom written stored procedures. I wanted to be able to call all of my stored procedures from .NET code without having to manually write all of the ADO.NET and I wanted the compiler to check my method names and my parameters like a regular .NET method. I wanted to be able to use data binding with objects instead of DataSets. And last, the data binding had to be two way and not just for display.

Code Generation

Code Generation is the act of having one computer program write another computer program and it is a great way to build components that repeat a pattern like stored procedures and ADO.NET code.

There are different ways of performing code generation. One way is text output. A computer program can easily spit out a file containing C# or VB.NET or any language you want. That is how MyGeneration and CodeSmith work or you could write a program yourself that builds a code file. In .NET you could also use the Code DOM to write code using a structured framework.

I use MyGeneration: http://www.mygenerationsoftware.com/

Recomended Reading

MyGeneration is a free open source code code generation tool. Codge Generation scripts are writing like classic ASP. You use a set of tags to escape in and out of script text and script code. Usually these are the same tags as used in classic ASP <% and %> but they are configurable. My Generation supports writing scripts in VB Script, JScript, C#, and VB.NET. In addition to the code generation code, each script also supports a user interface section where you can prompt the user for code generation parameters. MyGeneration also supports saving the script parameters in a file so that you can run the script over and over without having to go through the UI each time so long as you don't need to change the parameters.

Stored Procedures

The first step in building a stored procedure based DAL is to generate your stored procedures. I started with an excellent script titled "Script Insert/Update/Delete Procedures for SQL Server" written by Justin Greenwood. Justin wrote his script in February of 2004 for SQL Server 2000 and I wanted my script to work with SQL Server 2005 so I needed to make some changes. In addition, I had some additional features that I wanted to add.

I would like to extend my thanks to Justin for writing his very useful script and for giving me permission to redistribute my modified version.

Justin's original script included a lot of features like its handling of primary keys in where clauses, treatment of computed and identity columns, and its use of timestamp columns in update procedures.

I made a lot of changes, so I am sure that any mistakes are more likely mine.

Lets take a look at how the script handles a simple sample database.

One point I would like to make as we begin looking at generated code is that none of the generated code has a date emended in it. Its common for programmers to want to put a generated on date in code that is output so that they can tell when it was produced, but there is a very good reason not to. If you use source control, every time you check in a file your source control system checks to see if the file changed. If not, then it doesn't do anything, but if it did then it creates a new revision and checks in your changes. Now if you add a generated on date to your output, then every time you regenerate you create a new generated on date even if the actual code that was output didn't change at all. This means that your source control system has to create a new revision and it becomes harder for you to track real changes when you are having to sort through a bunch of revisions where only the dates changed. Let your source control system track when things changed. Leave dates and other unnecessary artifacts out of your generated code.

To begin, run the script "Basic CRUD Plus SelectBy FK". This script is under the MyGeneration namespace "ZacksFiasco.DataAccessLayer.SQLServer2005". You should see a UI screen that looks like the following:

The first parameter is the path on your computer to write the stored procedure files to. For testing, you could leave it to the default. However, if you use this for a project then I suggest you point it to a folder in your project where you would like to store your stored procedure scripts.

The second parameter is the SQL Server schema to store your procedures under. The UI defaults this to 'api'. You could put your procedures under 'dbo' but I suggest that you don't. Lots of things can put procedures into 'dbo'. In a later part of this series we will look at another code generation script that will take our stored procedures and build a .NET DAL and it will use the schema to filter only the stored procedures that we want. (Note: if you use 'api' or another schema that doesn't exist, the be sure to create that schema in your database before trying to load your stored procedures.)

The next parameter is the database to read.

The last parameter is the set of tables to generate procedures for. One file will be generated for each table, so for our test two files while be generated: sql_procs_Table1.sql and sql_procs_Table2.sql.

Generated TSQL

This example generates 381 lines of TSQL so I won't be able to go over the entire output. Instead I will try to give a quick synopsis of the key points.

The insert statements are pretty basic.

-- Proc: Table1Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Insert"/>
CREATE PROCEDURE [api].[Table1Insert]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    

    INSERT INTO [dbo].[Table1]
    (
        [Table1Id],
        [Col1],
        [Col2]
    )
    VALUES
    (
        @Table1Id,
        @Col1,
        @Col2
    )

    SELECT         @rowversion = [rowversion]
    FROM [dbo].[Table1]
    WHERE         [Table1Id] = @Table1Id;

    RETURN @@Error
END
GO

The stored procedure does not use newid() to generate the key. My personal preference is to generate the key in the application. There are many times when it is beneficial to know the key in the application before inserting the record. In this example we are using a uniqueidentifier which in .NET is a System.Guid but if we were using an int there are other ways of generating a unique value.

Recomended Reading

If you are concerned about SQL Server clustering of uniqueidentifiers I suggest that you use custom code to generate your Guids.

If this table did use a auto-key, this script is smart enough return the generated value as an OUTPUT parameter.

Also note that the rowversion column is returned as an OUTPUT parameter.

Take a look at the last line of the comment section. It is an XML block. For now I will defer the discussion of this, but we will use this in our DAL code.

The update procedure attempts to update the row in the database. Rowversion is an OUTPUT parameter which is in/out. First rowversion is used for optimistic concurrency. If the update succeeds, the the new rowversion is returned in the same parameter. If the update fails then an error is raised.

-- Proc: Table1Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Update"/>
CREATE PROCEDURE [api].[Table1Update]
(
    @Table1Id uniqueidentifier,
    @Col1 varchar(50),
    @Col2 int = NULL,
    @rowversion timestamp OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @t TABLE(x int);    

    
    UPDATE [dbo].[Table1]
    SET
        [Table1Id] = @Table1Id,
        [Col1] = @Col1,
        [Col2] = @Col2
    OUTPUT 1 into @t(x)
    WHERE 
        [Table1Id] = @Table1Id AND
        [rowversion] = @rowversion

    IF(SELECT COUNT(*) FROM @t) = 0
    BEGIN
        RAISERROR('Concurrency Error',16,1)
    END

    SELECT         @rowversion = [rowversion]
    FROM [dbo].[Table1]
    WHERE         [Table1Id] = @Table1Id;


    RETURN @@Error
END
GO

The delete procedure is pretty simple. It just deletes by the primary key.

-- Proc: Table1Delete
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Delete"/>
CREATE PROCEDURE [api].[Table1Delete]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON

    DELETE 
    FROM [dbo].[Table1]
    WHERE  
        [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

The select procedure is also pretty basic. There is also a SelectAll procedure which is identical except it doesn't have a where clause.

-- Proc: Table1Select
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table1" commandType="Select"><!--
--><Keys><!--
--><Key>Table1Id</Key><!--
--></Keys><!--
--></MetaData>
CREATE PROCEDURE [api].[Table1Select]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON

    select
        [Table1Id],
        [Col1],
        [Col2],
        [rowversion]
    FROM [dbo].[Table1]
    WHERE  
        [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

One of the cool enhancements of my updated script is that it also generates SelectBy<ForeignKey> procedures. In this example we generated a procedure that selects records from Table2 where it matches the foreign key from Table1.

-- Proc: Table2SelectByTable1
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table2" commandType="Select"><!--
--><Keys><!--
--><Key>Table2Id</Key><!--
--></Keys><!--
--></MetaData>
CREATE PROCEDURE [api].[Table2SelectByTable1]
(
    @Table1Id uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON

    select
        [Table2Id],
        [Col1],
        [Table1Id],
        [UpdatedOn],
        [UpdatedBy]
    FROM [dbo].[Table2]
    WHERE  
        [Table1Id] = @Table1Id

    RETURN @@Error
END
GO

This script looks for certain column names that it treats differently. If a column is named 'UpdatedOn' then the script generates code to mark this parameter as an OUTPUT parameter in insert and update procedures and assigns the value from getdate(). A future enhancement might make these columns configurable instead of hard-coding the column names in the script.

-- Proc: Table2Insert
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table2" commandType="Insert"/>
CREATE PROCEDURE [api].[Table2Insert]
(
    @Table2Id uniqueidentifier,
    @Col1 varchar(50) = NULL,
    @Table1Id uniqueidentifier,
    @UpdatedOn datetime OUTPUT,
    @UpdatedBy uniqueidentifier = NULL
)
AS
BEGIN
    SET NOCOUNT ON
    
    set @UpdatedOn = getdate();
    

    INSERT INTO [dbo].[Table2]
    (
        [Table2Id],
        [Col1],
        [Table1Id],
        [UpdatedOn],
        [UpdatedBy]
    )
    VALUES
    (
        @Table2Id,
        @Col1,
        @Table1Id,
        @UpdatedOn,
        @UpdatedBy
    )


    RETURN @@Error
END
GO

-- Proc: Table2Update
-- This proc was created by script. You can edit it, but if you do
-- then DO NOT regenerate it from the script or you will loose your edits.
-- <MetaData entityName="Table2" commandType="Update"/>
CREATE PROCEDURE [api].[Table2Update]
(
    @Table2Id uniqueidentifier,
    @Col1 varchar(50) = NULL,
    @Table1Id uniqueidentifier,
    @UpdatedOn datetime OUTPUT,
    @UpdatedBy uniqueidentifier = NULL
)
AS
BEGIN
    SET NOCOUNT ON
    
    set @UpdatedOn = getdate();
    
    UPDATE [dbo].[Table2]
    SET
        [Table2Id] = @Table2Id,
        [Col1] = @Col1,
        [Table1Id] = @Table1Id,
        [UpdatedOn] = @UpdatedOn,
        [UpdatedBy] = @UpdatedBy

    WHERE 
        [Table2Id] = @Table2Id



    RETURN @@Error
END
GO

I've talked mostly in this article about what the code generation script outputs and not how it generates code. The key to generating a good code generation script is to first decide what it should output for each scenario. The second key is a good meta-data engine. MyGeneration has an excellent meta-data engine and this is how I can easily gather information on the tables and their keys.

Download the source code and take a look at how the scripts work.

This concludes Part 1. We have seen how you can generate basic CRUD stored procedures for our database. The script generates good TSQL and includes features for rowversion/timestamp columns, UpdatedOn columns, identities, keys, and foreign keys.

Part 2 will talk about taking these stored procedures, writing custom stored procedures and generating a basic Data Access Layer where we can call each of these stored procedures from .NET applications.

Make programmatic navigation easier in ASP.NET 2.0

2007-11-26T21:52:00.001-05:00

All code published in this article is published under the Microsoft Public License. See source code download for a copy of the license.

This is a small project I built earlier this year that I thought I would share.
ASP.NET web pages usually consist of several pages and users transition from page to page through a few different mechanisms. The simplest method is through clicking a link. Links can appear on menus or could be placed anywhere on a page. This is an example of a self guided transition.
Users can also be transitioned to different pages programmatically. Usually, this happens when a users performs an action like clicking a command button. When this happens, the page posts back to the server and the command button event handler runs. The event handler runs some code, perhaps making a decision, and then calls Response.Redirect() or perhaps Server.Transfer().
Most ASP.NET applications probably contain this same sequence many times. The code to perform the redirect should look something like this:

string url = string.Format("~/Output.aspx?ID={0}&StartDT={1}", 
    Server.UrlEncode(id), Server.UrlEncode(StartDate.ToShortDateString())); 
Response.Redirect(this.ResolveUrl(url));

There are several problems with this code. While this is short at only 2 lines it is overly complex and repetitive. Second, the developer has to remember to URL Encode each query string parameter. The developer must also correctly type in the URL. Lastly, if a resource moves then every page that does a redirect to that resource must be edited and retested.
That's a lot for just a couple of lines of code. Wouldn't it be nicer if we could wrap some of the repetitive parts of this together and only have one place to change the URL if the page or resource moved?
As it turns out, ASP.NET 2.0 has already given us the base on which to build something to do just that. The ASP.NET Site Navigation system is very nice and it is now standard on most of the new web projects that I start. This system allows you to define the navigation of your web site and bind that information to either a standard databinding control or a specialized navigation control. You can define hierarchy and even limit which user roles can see which navigation nodes. This is a powerful framework, and we can extend it a little further.
What we want, is a system that will automatically perform URL Encoding for us, and which will allow us to redirect or transfer users to different pages without having to put the URL in each page.

Recomended Reading

The ASP.NET Site Navigation by default uses an XML file, usually with the file extension ".sitemap", to define the page locations and hierarchy. The Site Navigation system uses the provider model, so there are other providers that retrieve navigation information from other sources such as a database, however in this article we are focusing on the XmlSiteMapProvider.
Here is an example sitemap file:

<?xml version="1.0" encoding="utf-8" ?>
<siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0" >
    <siteMapNode url="~/Default.aspx" title="Home">
        <siteMapNode url="~/Details.aspx" title="Details" />
        <siteMapNode url="~/ContactUs.aspx" title="Contact Us"/>
    </siteMapNode>
</siteMap>

This sitemap defines a Default page as the root of a hierarchy containing a ‘Details’ and ‘Contact Us’ page below it. The sitemap defines a title for each page and the URL for each page. This is a minimum set of information, but each node can have more information associated with it depending on your needs.

When a menu or other control binds to this sitemap, the nodes for the correct level will be displayed. That means that you typically wouldn’t put nodes in the sitemap that you wouldn’t want displayed on a menu. Consequently, there could be many pages not represented in the sitemap, but that you may wish to be able to navigate to through code.

In order to create a solution for this, I am going to add two new attributes to the siteMapNode. The first new attribute I am going to add is a ‘visible’ attribute which can be either true or false. This will be used to tell the site map provider whether or not this node should be shown on a menu.

In order to be able to direct our navigation to a particular node, we need to be able to uniquely identify a particular node. The site map provider assigns a key for each node already, but the XmlSiteMapProvider uses the URL as the key. This doesn’t help us, so I am going to define a new attribute which I will call ‘navigatorId’. This can be any string which will be easy for you and your developers to use as an identifier for a page or resource. I could have used the already existing ‘title’, but there may be a scenario where you need more than one node to have the same title.

If you are observant, you may have realized that siteMap defines a namespace and adding new attributes to siteMapNode would violate that schema. Luckily for use, the XmlSiteMapProvider doesn’t use a validating XML Reader. However if it did, we could attempt to define a different schema which extended the default schema.

Our new site sitemap file looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0" >
    <siteMapNode url="~/Default.aspx" title="Home" navigatorId="Default">
        <siteMapNode url="~/Details.aspx" title="Details" navigatorId="Details"/>
        <siteMapNode url="~/ContactUs.aspx" title="Contact Us" navigatorId="ContactUs"/>
        <siteMapNode url="~/ContactUsEmailSent.aspx" title="Contact Us" navigatorId="ContactUsEmailSent" visible="false"/>
    </siteMapNode>
</siteMap>

The first change is adding a ‘navigatorId’ attribute to each node. Notice that I also added a new node which has its ‘visible’ attribute set to false.

Just adding these attributes isn’t enough. In order to take advantage of this new data, we need to extend the XmlSiteMapProvider and configure our new provider in the web.config file.

In order to accomplish what we want, I could write our own site map provider and add all the features we want. But with only a few new features, we can get by simply extending the existing provider and this saves us the trouble of having to re-implement all the parts that we want to keep.

Site map providers implement a method named IsAccessibleToUser() to determine if a node should be shown. This is typically used with security trimming, but we can extend this method along with our ‘visible’ attribute to hide nodes that we don’t want to show up on a menu. Take a look at the code below.

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;

namespace ZacksFiasco.Web.Navigation
{
    public class NavigatorSiteMapProvider : XmlSiteMapProvider
    {
        public override bool IsAccessibleToUser(HttpContext context, SiteMapNode node)
        {
            bool isVisible = true;
            bool rc = false;

            return rc;
        }        
    }
}

IsAccessibleToUser() takes two parameters: the current HttpContext and the SiteMapNode to test. SiteMapNode has an indexer that enumerates all of the attributes of the node, including the custom attributes that we added. If you check the 'visible' attribute, you can determine if the node should be visible to the user or not. You should be sure to test if the node that was passed to you is not null, that the attribute exists, and that the attribute value can be parsed into a boolean. For this implementation because this is a new attribute, if the attribute does not exists, then we are going to assume that the node is visible.

if (node != null && node["visible"] != null && bool.TryParse(node["visible"], out isVisible))
{

If the visible attribute exists and is true, you should not return true. For a correct implementation you should call down to the base class and return the base class's result. This allows the base class to have the final say and apply any logic that the base class implements. If the attribute does not exist, you should call down to the base class. The only time you should return a value different than the base class is if the attribute exists, but is false. In that case, we definitely do not want to show the node and so we want to return false.

The provider code ends up looking like this

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;

namespace ZacksFiasco.Web.Navigation
{
    public class NavigatorSiteMapProvider : XmlSiteMapProvider
    {
        public override bool IsAccessibleToUser(HttpContext context, SiteMapNode node)
        {
            bool isVisible = true;
            bool rc = false;

            if (node != null && node["visible"] != null && bool.TryParse(node["visible"], out isVisible))
            {
                if (isVisible)
                {
                    rc = base.IsAccessibleToUser(context, node);
                }
                else
                {
                    rc = false;
                }
            }
            else
            {
                rc = base.IsAccessibleToUser(context, node);
            }

            return rc;
        }        
    }
}

This is all we need to complete our site map provider. Just add this to your web application by inserting the following into your web.config. Just keep in mind that the value of type is "<namespace>.<type name>, <assembly>". In this example, I also turned on security trimming. This is a good example of why we call down to the base class in our custom site map provider. If we returned true without calling the base class, then the base class would not have a chance to apply security trimming and possibly limit visibility to this node.

<siteMap defaultProvider="NavSiteMapProvider" enabled="true">
    <providers>
        <add name="NavSiteMapProvider"
             description="Custom SiteMap provider."
             type="MyNavigation.MySiteMapProvider, MyNavigation"
             siteMapFile="Web.sitemap"
             securityTrimmingEnabled="true"/>
    </providers>
</siteMap>

Now that our site map provider is in place, we want to take advantage of this new functionality. We can now navigate and perform Redirects by navigatorId by searching the site map provider for the correct node and building our URL. This is very useful, but still repetitive. We can improve this by removing manual string manipulation and wrapping the code that performs the URL Encoding.

To begin, create a new class. I named my class Navigator. First, I gave Navigator a single private static method.

static string GetUrlFromKey(string navigatorId)

This method takes as a parameter a navigatorId string. Using this id, we can search all nodes in the site map provider until we find a node that matches and return that node's URL. We can search the nodes using a foreach loop like the following:

foreach (SiteMapNode node in SiteMap.Providers["AspNetXmlSiteMapProvider"].RootNode.GetAllNodes())

This loop statement uses the AspNetXmlSiteMapProvider. In the XML I provider for the web.conig I did not clear the provider list before adding our custom provider. There are reasons why we are not searching our own custom site map provider. First,since the name of the site map provider is specified in the web.config, I can't know at compile time what that name is. But I do know what the default name of the XmlSiteMapProvider is. Secondly, I want to be able to call IsAccessibleToUser() to check whether we should return this URL. If I call our custom provider, it will check the 'visible' flag which we don't want, since we want to be able to navigate to nodes that aren't visible on a menu. The solution is to use a provider that doesn't check the 'visible' flag when it evaluates accessibility. So in your loop, if the navigatorId matches and the node is accessible, then retrieve the URL from the node and return it.

We can improve this by adding to our configuration file a list of the site map providers that we wish to search. This give us the flexibility to search any and as many providers as we wish and only the providers that we wish.

static string GetUrlFromKey(string navigatorId)
{
    SiteMapNode rcNode = null;
    string rc = string.Empty;

    NavigatorSection ns = ConfigurationManager.GetSection("ZacksFiasco.Web.Navigation") as NavigatorSection;

    if (ns != null)
    {
        foreach (ProviderElement pe in ns.SiteMapProvidersToSearch)
        {
            foreach (SiteMapNode node in SiteMap.Providers[pe.SiteMapProviderName].RootNode.GetAllNodes())
            {
                if (node.IsAccessibleToUser(HttpContext.Current) && node["navigatorId"] == navigatorId)
                {
                    rcNode = node;
                    break;
                }
            }

            if (rcNode != null)
            {
                break;
            }
        }
    }
    else
    {
        foreach (SiteMapNode node in SiteMap.Providers["AspNetXmlSiteMapProvider"].RootNode.GetAllNodes())
        {
            if (node.IsAccessibleToUser(HttpContext.Current) && node["navigatorId"] == navigatorId)
            {
                rcNode = node;
                break;
            }
        }
    }

    if (rcNode != null)
    {
        rc = rcNode.Url;
    }

    return rc;
}

Implementing this configuration is outside the scope of this article, but the resulting web.config ends up looking like this.

<configuration>
    <configSections>
        <section name="ZacksFiasco.Web.Navigation"
                 type="ZacksFiasco.Web.Navigation.Configuration.NavigatorSection, ZacksFiasco.Web.Navigation"/>
    </configSections>
    <ZacksFiasco.Web.Navigation>
        <siteMapProvidersToSearch> 
            <add siteMapProviderName="AspNetXmlSiteMapProvider" />          
        </siteMapProvidersToSearch>       
    </ZacksFiasco.Web.Navigation>
    <system.web>
        <siteMap defaultProvider="NavSiteMapProvider" enabled="true">
            <providers>
                <add name="NavSiteMapProvider"
                     description="Custom SiteMap provider."
                     type="ZacksFiasco.Web.Navigation.NavigatorSiteMapProvider, ZacksFiasco.Web.Navigation"
                     siteMapFile="Web.sitemap"
                     securityTrimmingEnabled="true"/>
            </providers>
        </siteMap>
    </system.web>
</configuration>

Next, add to your class a member dictionary that takes a string as the key and a string as the value. Add a property get that wraps this dictionary. I called my property Parameters.

Now add a string member named navigatorId and add a property with a get and set that wraps this member.

string navigatorId;

public string NavigatorId
{
    get { return navigatorId; }
    set { navigatorId = value; }
}

Dictionary<string, string> parameters;

public Dictionary<string, string> Parameters
{
    get { return parameters; }
}

Override the ToString() member. In ToString(), perform the string concatenation necessary to build the URL. To retrieve the base URL, call the static method GetUrlFromKey() and pass the navigatorId member. To add the querystring parameters, perform a loop on the parameters dictionary and URL Encode each parameter.

Now add two final methods. Redirect() and Transfer(). Redirect() performs a Response Redirect() and Transfer performs a server-side Transfer. To implement each of these, just get the current HttpContext and call to the appropriate context object. Use the ToString() method to build your URL and that is all you need.

public void Transfer()
{
    HttpContext.Current.Server.Transfer(ToString());
}

public void Redirect()
{
    HttpContext.Current.Response.Redirect(ToString());
}

To use what you have written, the code to perform a programmatic navigation looks like the following:

protected void btHidden1_Click(object sender, EventArgs e)
{
    Navigator n = new Navigator("HiddenOne");
    n.Parameters["text"] = txParam.Text;
    n.Parameters["time"] = DateTime.Now.ToLongTimeString();

    n.Redirect();
}

Visit the ZacksFiasco.Web.Navigation Codeplex site to download the source code or download the assembly and begin using it in your projects right away.

First post

2007-08-16T10:58:00.000-04:00

Hi, this is Zack. I thought I would give this blogging thing a try. "Why?" you ask. Aren't there enough blogs already. You are probably correct, but I thought I would add my voice to the cacophony. So who am I and why would you care about what I have to say? I live in Middle Georgia with my wife. I am a software developer so I plan to post some articles on technology that I'm interested in (ASP.NET, C#) and can share with other people. I also participate in a few outdoor activities that I might write about. I love running. I do on and off road foot races every now and again. My favorite is the Dances With Dirt. I mountain bike some also, but I kindof suck at it. I did my first adventure race this year at the Ocmulgee Adventure race and that was a lot of fun. Thats a good start. If any of this sounds interesting check back or keep reading.