Tutto .NET

Conserving Resources When Writing BLOB Values to SQL Server and Streaming BLOB Values back to the Client

alessandro — Mon, 22 Sep 2008 23:22:00 GMT

Reading and writing blob data to/from your database can be a resource hog, because it normally involves holding the entire stream in memory. Holding 10kb, 20, 100kb in memory might not be an issue, however as you start allowing larger file uploads, your application starts to feel the stress and easily starts to run out of resources.

Particularly, say you had a relatively small file (2mb) your allowing users to upload from your pages. User A uploads, it's only 2mb, you have not exceeded your memory allocation yet. However, as more users try to upload simultaneously, now your allocating more memory slots at a time, eg. if they were 5 simultaneous users, that's 2*5 = 10mb, 100 users, then it's 2*100.

This is all too stressful on your webserver, or atleast was until ASP.NET 2.0. Since, things have changed and now asp.net supports setting a diskThreshold that allows you to specify the amount of data buffered in server memory for the request. So, say you wanted to keep the default diskThreshold of 256 bytes, when the file is larger than 256bytes, asp.net begins to write the filestream to disk Versus keeping it in memory. This is just great because i remember back in the day, this was quite annoying. Glad it's fixed. This means we do nothing as far as the upload is concerned from the client to your webserver.

Still, if we are storing the stream in SQL Server, now we have a whole new scenario, and the question remains, how do we stream this data to sql server while conserving memory ? Should we stream the entire data as is in one lump ? Wont that allocate memory on our database server before writing to the database field ? This is what i want to address. Basically, what we want to do is save our file stream to sqlserver in smaller chunks, that way our database server will allocate only enough memory for that little chunk before writing it to the database field, in this way conserving memory at the cost of write speed(no free lunch, i'm afraid).

However, the price to pay in latency for streaming chunks of data to sql server might be well worth, because the alternative is to tax your database server when memory is available and to crash it (I assume it may crash when it runs out of memory. I have not tried personally. You may try that yourself if your feeling curious. Do not forget to share your findings if you do :P)

Take, SQL SERVER Express edition as an example which has the imposed limit of 1gb memory. That's not a whole lot is it ? Streaming the data to sql server in chunks is actually not as taxing as it may seem. True, the write performance is degraded but you can regulate it by increasing/decreasing the size of your chunk to find a compromise.

So, how do we do this ? SQL Server 2005 has a new feature that enables it to update a field with .WRITE ; as you stream your chunks it just appends it to the existing field. You can read about it here : http://msdn.microsoft.com/en-us/library/ms177523.aspx

To Quote the documentation from the above link :
Because the .WRITE clause cannot be used to modify a NULL column, the column is first populated with temporary data. This data is then replaced with the correct data by using the .WRITE clause

Now, that's not cool! We can work around that nicely though. Ineffect, it does not even feel like were working around anything. I've basically followed microsofts own documentation located at : http://msdn.microsoft.com/en-us/library/aa719771.aspx, which had the proper code, however, instead of .Write, it uses an older method that is not compatible with the newer varbinary,varchar(max),nvarchar(max) datatypes supported by SQL Server 2005 as opposed to the older not recommeded types -> image, text, and ntext.

Following is an extract of the code i use to stream chunks of data to sql server using the .Write method. The purpose of the sample code is to display how you can go about writing your own ofcourse. I have just ripped the piece of code out of my DAL with a few minor adjustments, so just running a copy/paste of the code below will or may not yield much depending on your level of expertise. I am just way too lazy to write a completely contained example so bear with me. As it is, this article has gotten longer than I want it to be.

public void InsertFile(string applicationName, Stream data,
            string mimeType, string fileName)
{
    int fileId = -1;
    using (SqlConnection myConnection = GetSqlConnection())
    {
        myConnection.Open();
        SqlCommand cmd = new SqlCommand(DbOwner + 
    ".DataFile_Insert", myConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@applicationName", 
SqlDbType.NVarChar, 256).Value = applicationName;
        cmd.Parameters.Add("@mimeType", 
    SqlDbType.NVarChar, 50).Value = mimeType;
        cmd.Parameters.Add("@length", 
    SqlDbType.Int, 4).Value = data.Length;
        cmd.Parameters.Add("@fileName", 
    SqlDbType.NVarChar, 256).Value = fileName;
        SqlParameter fileIdParam = 
cmd.Parameters.Add("@fileId", SqlDbType.Int, 4);
        fileIdParam.Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        // now insert in chunks
        fileId = (int)fileIdParam.Value;
    }
    if (fileId > -1)
    {
        //The size of the "chunks" 
// 128 bytes, regulate at will
        InsertFileByChunks(fileId, data, 128);
    }
}

note how we are calling InsertFileByChunks from our insert method above ?

public void InsertFileByChunks(int fileId, Stream data, int bufferLen)
{
    using (SqlConnection myConnection = GetSqlConnection())
    {
        myConnection.Open();
        SqlCommand cmd = new SqlCommand(DbOwner + 
".DataFile_InsertChunk", myConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@fileId", 
SqlDbType.Int, 4).Value = fileId;
        SqlParameter paramData = cmd.Parameters.Add("@data", 
    SqlDbType.VarBinary, 128);
        SqlParameter paramOffset = cmd.Parameters.Add("@offset", 
                SqlDbType.BigInt);
        cmd.Parameters.Add("@length", 
    SqlDbType.Int, 4).Value = bufferLen;
        using (BinaryReader br = new BinaryReader(data))
        {
            byte[] buffer = br.ReadBytes(bufferLen);
            int offset = 0;
            while (buffer.Length > 0)
            {
                paramData.Value = buffer;
                paramOffset.Value = offset;
                cmd.ExecuteNonQuery();
                offset += bufferLen;
                buffer = br.ReadBytes(bufferLen);
            }
        }
        data.Close();
    }
}

As you can see the above method just writes chunks of data at a time(the accompanying stored procedure DataFile_InsertChunk uses .Write to update the field with the new chunks as they come in).

And now the accompanying stored procedures DataFile_Insert and DataFile_InsertChunk :

CREATE PROCEDURE [dbo].[DataFile_Insert]
    @applicationName nvarchar(256),
    @mimeType nvarchar(50), 
    @length int, 
    @fileName nvarchar(256),
    @fileId int output
AS
BEGIN
    DECLARE @applicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
        INSERT INTO 
             dataFile (applicationId, data, mimeType, [length],  
            [fileName], dateCreated, lastDateModified)
        VALUES (@applicationId, 0x0, @mimeType, @length, @fileName,  
        GETDATE(), GETDATE())
        SET @fileId = SCOPE_IDENTITY()    
END

Note above how we introduce the value 0x0 (null) as per the article on MSDN into the data field ? "data" is the name of the field that will be holding our data stream and is a varbinary type. The reason we introduce the value 0x0 is because .Write cannot write into a NULL field. This is the workaround i was talking about earlier and it does not effect the appended chunks in anyway.

CREATE PROCEDURE [dbo].[DataFile_InsertChunk]
    @data varbinary(MAX), 
    @length int, 
    @fileId int output,
    @offset bigint
AS
BEGIN
    UPDATE dataFile SET data.WRITE(@data, @offset, 
@length) WHERE fileId = @fileId    
END

As you can see, it all boils down to this. Quite simple indeed. the .WRITE method takes as argument your chunk, the offset and the length. Not much for me to add here. Oh one thing you may also want to note is that .WRITE is called via the field to which it needs to write to. so the syntax takes the form of -> fieldName.WRITE(...) ; in my case the field name is "data" as you can note from the code in the stored proc above.

We have now successfully stored our file stream in chunks. Next we want to see how to retrieve our filestream in a similar manner, that way we do not hog resources on our webserver when binary content is requested, requiring us to stream it out from the database server to the webserver where our code streams it out again to the client. Firstly, I hope you are already familiar with an HttpHandler, if not you can look it up on google.

An HttpHandler/Module is general knowledge so i'm not going to get into it. I've mapped somefile.ashx to my HttpHandler, that way, every request that comes in will be handled by my custom handler, where i request the file stream from the database and then stream it out again in chunks to the client. My HttpHandler looks like this :

public class BinaryDataHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
    int fileId;
    bool success = int.TryParse(
        context.Request.QueryString["fileId"], 
            out fileId);
    if (success)
    {
        IDataReader r = FileManager.GetFileById(fileId);
        StreamData(r, context);
    }
}
void StreamData(IDataReader r, HttpContext context)
{
    if (r.Read())
    {
        DataFileItem dfi = new DataFileItem();
        dfi.Length = (r["length"] is DBNull) ? 
                    -1 : (int)r["length"];
        dfi.MimeType = (r["mimeType"] is DBNull) ? 
        string.Empty : (string)r["mimeType"];
        if (dfi.MimeType != string.Empty)
            context.Response.ContentType = dfi.MimeType;
        dfi.FileName = (r["fileName"] is DBNull) ? 
        string.Empty : (string)r["fileName"];
        dfi.Extention = (r["extention"] is DBNull) ? 
        string.Empty : (string)r["extention"];
        context.Response.AddHeader("Content-Disposition", 
    string.Format("inline;filename={0}{1}", 
        dfi.FileName, dfi.Extention));
        context.Response.AddHeader("Content-Length", 
        dfi.Length.ToString());
        int dataOrdinal = r.GetOrdinal("data");
        if (!(r[dataOrdinal] is DBNull))
            StreamByteArrayInChunks(r, dataOrdinal, context);
    }
}
public void StreamByteArrayInChunks(IDataReader r, int ordinal, 
        HttpContext context)
{
//102400 is 100kb at a time buffer
    byte[] buffer = new byte[102400];
    int index = 0;
    while (true)
    {
        long count = r.GetBytes(ordinal,
           index, buffer, 0, buffer.Length);
        if (count == 0)
        {
            break;
        }
        else
        {
            index = index + (int)count;
            context.Response.BinaryWrite(buffer);
            context.Response.Flush();
        }
    }
    //closes datareader + underlying db connection
    r.Close();
}
// Override the IsReusable property.
public bool IsReusable
{
    get { return false; }
}
}

A few things i will explain quickly from the above code is how we retrieve an IDataReader from our DAL instead of retrieving a stream. The reason is i want to retrieve the filestream in chunks from the DataReader and every chunk i read from the database, i want to write it out to the client, as i receive the chunks without storing them in memory on my webserver where my application resides.

Another thing you may want to notice is the usage of the Http Header Content-Disposition and Content-Length ; basically, it was important for me to flush out the file with the original filename when the user choses to save the file and Content-Disposition helped there. Whereas Content-Length helped ensure the filesize on the receiving end (the client) since without it i was getting errors unzipping a file for example where winzip complained that the file was corrupt :-)

So that's pretty much it. And oh, the method in my DAL that returns the IDataReader follows :

public override IDataReader GetFileById(int fileId)
{
    SqlConnection myConnection = GetSqlConnection();
    myConnection.Open();
    SqlCommand cmd = new SqlCommand(DbOwner + 
            ".DataFile_GetFileById", myConnection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@fileId", 
            SqlDbType.Int, 4).Value = fileId;
    SqlDataReader r = cmd.ExecuteReader(
                    CommandBehavior.CloseConnection);
    return r;
}

With that i think we have succeeded in conserving resources on both our database server and our Web server. One last thing, when you got this all working, experiment by changing the buffer size and regulate it to the write speed that is acceptable as per your standards. Basically from my tests on my dev machine, i was getting a download speed of 500KB - 517KB/sec with a buffer size of 100kb (102400 bytes). While setting the same buffer size to 10kb (10240bytes), i was getting a download speed of 50KB - 60KB/sec ;

These tests are ofcourse based on what i see as i request a file handled through my custom httphandler above. These values are more or less the same i reckon when streaming the same data to sql server in chunks or a good approximation, so use that as a starting point to regulate the desired speed/latency required by your app based on how much resources your webserver/database server has. Normally, uploads are usually restricted to a certain amount by most ISP's so it means you can set a much smaller chunk size when streaming from your application to sql server. And increase the chunk size when streaming from your webserver to the client (since your requesting clients download speeds are significantly high). It will also depend on the bandwidth availability of your webserver and other factors ofcourse, but that's off topic.

Update 23 september 2008 :
One last thing i forgot to mention but my friend filip duyck brought up is that now since we are sending the data in chunks to sql server (whilst reusing the same connection), it's still additional hits to our database server. So consider that too while you evaluate how big you want your chunks to be.

Automatically reload reCAPTCHA when postingback via a partial refresh (UpdatePanel)

alessandro — Tue, 09 Sep 2008 13:03:00 GMT

So, today, i wanted to use reCAPTCHA. Since this is an online service I was a bit skeptical at first with regard to downtime. However their faq states the following :

reCAPTCHA has distributed locations and multiple servers.

With that, we know that the service should be available pretty much all the time. And honestly, if it's good enough to service facebook's massive user base, it's good enough for me. Fact is, many people are already familiar with the service and know what to expect and how to proceed. However, the deciding factor was the noble cause of this project. Basically for every captcha that is deciphered, you are actually helping decode a word in a book that was digitized for the public domain by volunteers using OCR(optical character reconigition). Sometimes there are a few words that an OCR cannot read and these are the words presented in the catpcha. So while fighting spam, you are actually helping digitize books. Brilliant! I was sold :P:P

If your looking to using this captcha service, then, after signing up to the service on their site ReCAPTCHA , you shall be provided with a public/private key to access the service. And this is all you need, since they already have a custom web control that you can insert into your asp.net pages to communicate with their service and consume the captcha. You can inform yourself more about how to consume it from your webform pages here : reCAPTCHA AND ASP.NET

Now, while this is all working nicely, getting it to play nice with the UpdatePanel might be difficult for some. I say that because i have read a few articles online with workarounds, when there was no need. So the goal here is to have the captcha automatically refresh itself, if the captcha words submitted were wrong. So lets see how to do just that :

First, what you want to do is make sure that the UpdateMode for the updatepanel is set to conditional. This means, you are responsible for calling update on the updatepanel manually. We want to make this manual because if the captcha is invalid, then you do not want the updatepanel containing the captcha to refresh(this will only make recaptcha dissappear). Instead, we add a second updatepanel. Code speaks a thousand words, so lets take a look at functional code :

 <form runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" ChildrenAsTriggers="false" 
UpdateMode="Conditional"
        runat="server">
        <ContentTemplate>
            <asp:TextBox ID="TextBoxComment" TextMode="MultiLine" Rows="5"
 Columns="35" runat="server"></asp:TextBox>
            <recaptcha:RecaptchaControl ID="recaptcha1" runat="server" 
Theme="red" PublicKey="6LcBAAAAAAAAAKtzVYRsIgOAAvCFge3iiMtf6hI9"
                PrivateKey="6LcBAAAAAAAAACQnFb_BI5tX7OxqC-C5RtROzx-S" />
            <asp:UpdatePanel ID="UpdatePanel2" 
ChildrenAsTriggers="false" UpdateMode="Conditional"
                runat="server">
                <ContentTemplate>
                    <asp:Label ID="labelError" 
runat="server" EnableViewState="false" />
                </ContentTemplate>
        </asp:UpdatePanel>
        <br />
        <asp:Button ID="btnSubmit" ValidationGroup="GroupName" 
runat="server" Text="Submit" OnClick="btnSubmit_Click" />
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>

As you can note from the code above, we have two updatepanels. The second updatepanel contains a label to tell the user that the text they submitted is wrong. This is the only updatepanel we want to refresh when the captcha submitted is invalid.

Now the minimum c# code to do our bidding :

void btnSubmit_Click(object sender, EventArgs args)
    {
        if (!recaptcha1.IsValid)
        {
            labelError.Text = "Incorrect, try again!";
            labelError.ForeColor = System.Drawing.Color.Red;
            //Reload recaptcha
            ScriptManager.RegisterClientScriptBlock(this.Page, 
        this.Page.GetType(),  "whatever1", 
            "Recaptcha.reload();", true);
            UpdatePanel2.Update();
        }
    }

As you can note from above, the main code of interest here is the code we are registering via the ScriptManager, specifically -> "Recaptcha.reload(); what this will do is reload the captcha on the clientside when the second updatepanel refreshes whilst display an error message to our clients telling them to retry the captcha. I would love to make this article longer than it already is, however that's about it :-)

Screen shots of what it looks like before we submit and what it looks like after below :

After : note how the captcha is successfully reloaded via a partial postback.

Update 26-september-2008 : The full source to the example code i have used throughout this posting :

<%@ Page Language="C#" %>
<%@ Register TagPrefix="recaptcha" 
Namespace="Recaptcha" Assembly="Recaptcha" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
void btnSubmit_Click(object sender, EventArgs args)
{
    if (!recaptcha1.IsValid)
    {
        labelError.Text = "Incorrect, try again!";
        labelError.ForeColor = System.Drawing.Color.Red;
        //Reload recaptcha
        ScriptManager.RegisterClientScriptBlock(this.Page,
    this.Page.GetType(), "whatever1",
        "Recaptcha.reload();", true);
        UpdatePanel2.Update();
    }
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="Form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" ChildrenAsTriggers="false" 
UpdateMode="Conditional"
    runat="server">
    <ContentTemplate>
        <asp:TextBox ID="TextBoxComment" TextMode="MultiLine" Rows="5"
Columns="35" runat="server"></asp:TextBox>
        <recaptcha:RecaptchaControl ID="recaptcha1" runat="server" 
Theme="red" PublicKey="6LcBAAAAAAAAAKtzVYRsIgOAAvCFge3iiMtf6hI9"
            PrivateKey="6LcBAAAAAAAAACQnFb_BI5tX7OxqC-C5RtROzx-S" />
        <asp:UpdatePanel ID="UpdatePanel2" 
ChildrenAsTriggers="false" UpdateMode="Conditional"
            runat="server">
            <ContentTemplate>
                <asp:Label ID="labelError" 
runat="server" EnableViewState="false" />
            </ContentTemplate>
    </asp:UpdatePanel>
    <br />
    <asp:Button ID="btnSubmit" ValidationGroup="GroupName" 
runat="server" Text="Submit" OnClick="btnSubmit_Click" />
    </ContentTemplate>
</asp:UpdatePanel>
</form>
</body>
</html>

Part 2 : Building and binding hierarchical data from the database to the ASP.NET Navigation Controls

alessandro — Sat, 01 Mar 2008 16:14:00 GMT

Around last week, I wrote about how we can make use of, some of the databinding capabilities of controls, that can bind to hierarchical data like the TreeView. While I covered pretty much everything, the data i was binding to was not deeply nested. Instead the data we were consuming was just 3 levels deep and that was that.

Today, we will be binding to a hierarchical datasource whose nesting structure is not known and can go as deep as it wants. Here, also SQL servers XML capabilities seem to come short since it does not support recursions. so if our data had this kind of deep nesting, SQL Servers FOR XML queries are not helping out much and we'd need to combine XSLT to our FOR XML Query.

Because of that, and since we end up using XSL for the transformation anyway, i'm going to be using the Dataset/XSLT approach I discussed in my previous post.

First, lets create a "pages" table in Sql server with the following schema as you can see in the screen shot below :

As you can note from above, we have a parentId field which is a self related field, related to the pageId in the same table. I personally prefer to move this field out to a junction table but each approach has it's pro's and con's, and this is simpler and serves our purpose :-)

Now, let's fill it with sample data, as you can note in the screenshot below :

ohh perfect. Now it's time to put together some c# code :

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            XmlDataSource1.Data = GetHierarchicalData();
    }
    string GetHierarchicalData()
    {
        string queryString =
        "SELECT pageId, pageDisplayName, parentId FROM pages;";
        DataSet ds = new DataSet("Pages");
        string connectionString = ConfigurationManager.
        ConnectionStrings["LocalSqlServer"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(
                   connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(
                queryString, connection);
            adapter.Fill(ds);

            ds.Tables[0].TableName = "PageItem";
            // relate our tables
            DataRelation dr = new DataRelation("FK_pageId_parentId", 
            ds.Tables["PageItem"].Columns["pageId"],
                        ds.Tables["PageItem"].Columns["parentId"]);
            // we'd like the page items nested within 
        // each node for every child page.
            dr.Nested = true;
            ds.Relations.Add(dr);
        }
        return ds.GetXml();
    }

The xml returned by our dataset looks like this : Dataset generated XML

Notice how every pageId that had a corresponding parentId was nested within it's parent, forming a hiearchical set of data. This is just perfect, all thanks to the dataset's DataRelation capabilities and our setting Nested=true on the DataRelation. This kind of recusive nesting didn't seem supported by SQL Servers FOR XML queries, and if it was, then it's probably trival. I did find a few implicit hints in the documentation that recursive nesting was not supported on SQL Server FOR XML Queries, however i could be wrong (so please do your homework).

Now our XSL file that does the recursion, but it's easier to make the transformation now, because the data is already structured out nicely with the proper hierarchical structure. Only thing missing and why we need to perform transformation is as i explained in the previous post, we need the fields as attributes and not as xml elements. So here is our recursive xslt that does just that :

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"/>
  <xsl:template name="Menu" match="/">
    <xsl:element name="Pages">
      <xsl:for-each select="Pages/PageItem">
        <xsl:variable name="parentId" select="parentId/text()"/>

        <xsl:element name="PageItem">
          <xsl:attribute name="pageId">
            <xsl:value-of select="pageId/text()" />
          </xsl:attribute>
          <xsl:attribute name="pageDisplayName">
            <xsl:value-of select="pageDisplayName/text()" />
          </xsl:attribute>

          <xsl:call-template name="processChildren" />
  
        </xsl:element>
      </xsl:for-each>
   </xsl:element>
  </xsl:template>
<!-- recursive template -->
  <xsl:template name="processChildren">
    <xsl:for-each select="PageItem">
        <xsl:element name="PageItem">
          <xsl:attribute name="pageId">
            <xsl:value-of select="pageId/text()" />
          </xsl:attribute>
          <xsl:attribute name="pageDisplayName">
            <xsl:value-of select="pageDisplayName/text()" />
          </xsl:attribute>
          <xsl:call-template name="processChildren" />
        </xsl:element>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

And here is the output of this miraculous transformation : XSL Transformed XML

Ohhh what beauty :-) and to complete, here is the declarative code to bind our asp.net menu to the XmlDataSource control that consumes this data :

<form id="form1" runat="server">
    <div>
        <asp:Menu ID="Menu1" MaximumDynamicDisplayLevels="1000" 
        Orientation="Horizontal"
         DataSourceID="XmlDataSource1" runat="server">
        <DataBindings>
            <asp:MenuItemBinding DataMember="PageItem" 
            TextField="pageDisplayName" ValueField="pageId" />
        </DataBindings>
        </asp:Menu>
        <asp:XmlDataSource ID="XmlDataSource1" XPath="Pages/PageItem"
         TransformFile="~/MenuTransform.xsl" runat="server"></asp:XmlDataSource>
    </div>
    </form>

One gotcha you want to watch out for is the root node showing in the menu. Since we do not want to show the starting node, we explain this to the XmlDataSource nicely by setting an XPath expression : XPath="Pages/PageItem".

And below is our menu, when previewed in the browser. Time to congratulate ourselves on a job well done :-)

Update 21 May 2008

A note i forgot to mention is that the XmlDataSource control has caching turned on by default. So in case you made a change in your xslt file and didn't see the change occuring, then you know it's using a cached copy. So make sure you disable caching during development.

Building and binding hierarchical data from the database to the ASP.NET Navigation Controls

alessandro — Wed, 20 Feb 2008 21:30:00 GMT

If we need to bind our navigations controls to hierarchical data we define manually ourselves in an xml file, this is easy as pie. However, things can get rather complicated or not so obvious when we need to generate this data from a database. First off, what can we use that is already provided to us for binding hierarchical data to our navigation controls in ASP.NET ?

The already out of the box approach and ideal solution is to use the XmlDataSource control. This is quite a flexible datasource control since it not only enables us to define the path to our xml file containing the structure we need but also it allows us to define xml data to it via it's "Data" property. As you may have already guessed, because our data is going to be retrieved from our database, this is the property we shall be using :-)

But first let's look at a sample data structure we may have in our database. I'm using the classic Northwind database. Let's imagine we want to display products grouped by category. So in short, for every category node, we want to show products under it. Following is a screenshot of the categories and products table and how they relate :

The most common thing i see done is to manually loop through the records returned, create TreeNodes again manually and keep adding till you've build the TreeView or Menu, etc. Nothing wrong with this approach, since it works, however it is quite lengthy in code and time consuming too.

But that's not the main reason why I'm writing this article. The main reason is that all these navigation controls in ASP.NET know how to consume hierarchical data. Once they have this, they know how to render themselves without you needing to do anything special. This is indeed some powerful databinding support that we miss out on when we go the manual approach. Here i am going to list two different approaches :

1) DataSet and XSL Transformations, which while being clean and gives a more declarative model to work with (XSLT) versus the manual c# code approach.

2)The second approach uses SqlServer's XML generating capabilities which allows us to skip XSLT and the dataset all together :-)

DataSet and XSL Transformation approach:

---------------------------------------------------
Note below how our select statements retrieves all categories, while the second statement retrieves all products. We then relate both these tables using a key field they have in common "CategoryID". We also use a dataset since it's providing us a lot of functionality like relating the tables after data retrieval and representing the data in xml.

string GetHierarchicalData()
    {
        string queryString =
        "SELECT CategoryID, CategoryName, Description FROM Categories AS Category;";
        queryString +=
            "SELECT ProductID, CategoryID, ProductName FROM Products AS Product";
        DataSet ds = new DataSet("TreeView");
        string connectionString = ConfigurationManager
.ConnectionStrings["LocalSqlServer"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(
                   connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(
                queryString, connection);
            adapter.Fill(ds);

            ds.Tables[0].TableName = "Category";
            ds.Tables[1].TableName = "Product";
            // relate our tables
            DataRelation dr = new DataRelation("FK_Products_Categories",
ds.Tables["Category"].Columns["categoryId"],
                                ds.Tables["Product"].Columns["categoryId"]);
            // we'd like the products nested within 
            // each Category Node. Thank you :-)
            dr.Nested = true;
            ds.Relations.Add(dr);
        }
        return ds.GetXml();
    }

A small sample output of the generated xml we get by calling dataset's GetXml() method is here -> DataSet Generated XML

As you can see, we have our rootnode "TreeView", then a childNode "Category", which in turn will contain every product node within it that belongs to this category. This nesting was established when we created a relationship btw the Categories table and the Products table using a DataRelation, where we set Nested = true; on it. Enabling the Nested property did just what the name says. It nested all our products within it's specific Category node.

While this is great, it does not help much with the TreeView control. Notice how every field our select statement returned is now an xml node and the data for the field is contained as inner text in our node. The TreeNodeBinding instead expects the fields to be contained as attributes, and the data as attribute values. Following screenshot is what we get when we run our TreeView bound to XmlDatasource

Definately, not what we are after. This is because the datasets GetXml method returned :

<Category>
    <CategoryID>1</CategoryID>
    <CategoryName>Beverages</CategoryName>
    <Description>Soft drinks, coffees, teas, beers, and ales</Description>

while, what we need instead for the TreeNodeBinding to work is :

<Category CategoryID="1" CategoryName="Beverages" Description="Soft drinks, coffees, teas, beers, and ales">

Had we the above xml structure with the fields defined as attributes we could easily create a TreeNodeBinding like this :

<asp:TreeNodeBinding Depth="2" DataMember="Category" TextField="CategoryName" ValueField="CategoryID" ToolTipField="Description" />

So, our next task is to transform our xml to represent fields as attributes and their data as attribute values. We can accomplish this easily using XSL Transformations. By defining some instructions in our XSL file, we can read our XML(what our dataset GetXml method output) and transform it into what the Navigation Controls expect, outputting a totally new XML document.

<?xml version="1.0" encoding="utf-8"?>
<xsl:transform version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"/>
  <xsl:template match="/">
    <xsl:element name="TreeView">
      <xsl:for-each select="TreeView/Category">
        <xsl:element name="Category">
          <xsl:attribute name="id">
            <xsl:value-of select="CategoryID/text()" />
          </xsl:attribute>
          <xsl:attribute name="name">
            <xsl:value-of select="CategoryName/text()" />
          </xsl:attribute>
          <xsl:attribute name="description">
            <xsl:value-of select="Description/text()" />
          </xsl:attribute>
          <xsl:for-each select="Product">
            <xsl:element name="Product">
              <xsl:attribute name="id">
                <xsl:value-of select="ProductID/text()" />
              </xsl:attribute>
              <xsl:attribute name="name">
                <xsl:value-of select="ProductName/text()" />
              </xsl:attribute>
            </xsl:element>
          </xsl:for-each>
        </xsl:element>
      </xsl:for-each>
    </xsl:element>
  </xsl:template>
</xsl:transform>

As you can see from the above XSL, we have defined two for-each loops, one that loops through category nodes and the other that loops through product nodes. Very simple but powerful stuff indeed :-)

The output xml after this transformation is XSL Transformed Xml output

<TreeView>
<Category>
  <CategoryName name="" id="" description="" />
  <Product name="" id="" />
   ...
</Category>
...
</TreeView>

ohh this is perfect. It was fun using XSL for the transmformation. Now, now, while it was fun, it can be a big pain in the behind if I'd have to do this all over again and again (Fortunately, i don't. Atleast now right now) :P

So, can we have made this job easier ? Surely. SQL Server and FOR XML Queries to the rescue :-)

SQL Server and FOR XML Queries approach :

---------------------------------------------------

Now, what if, instead of having to do all this manual labor, we could get SQL Server to do all the xml generation for us, the way we wanted it ? Very much possible indeed. In effect this does not even need any explainations. Code speaks a thousand words, so here it is, the same xml output but this time we didn't use XSLT, nor did we use a dataset and the code is even more minimized.

string GetHierarchicalDataFromSqlServer()
    {
        string xml = string.Empty;
        string queryString = @"
                    SELECT Category.categoryName as [name], Category.categoryId as id, 
                                Category.description as description, 
                    Product.productName as name, Product.productId as id
                    FROM categories AS Category 
                    INNER JOIN products AS Product
                    ON Category.categoryId = Product.categoryId
                    ORDER BY Category.categoryId                    
                    FOR XML Auto, ROOT('TreeView')";

        string connectionString = ConfigurationManager.
ConnectionStrings["LocalSqlServer"].ConnectionString;

        using (SqlConnection connection = new SqlConnection(
                   connectionString))
        {
            SqlCommand SelectCommand = new SqlCommand(
                queryString, connection);
            connection.Open();
            XmlReader xr = SelectCommand.ExecuteXmlReader();
            xr.MoveToContent();// move to the root node
            xml = xr.ReadOuterXml();
        }
        return xml;
    }

The c# code to pass the XML data to our XmlDataSource bound to a treeView :

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            XmlDataSource1.Data = GetHierarchicalDataFromSqlServer();
    }

That's it. No fuss, SQL Server's xml generation capabilities are just outstanding. And here is the declarative code i used to bind this hiearchical data to a treeview :

<form id="form1" runat="server">
        <div>
            <asp:TreeView ID="TreeView1" DataSourceID="XmlDataSource1" runat="server">
                <DataBindings>
                   <asp:TreeNodeBinding Depth="1" DataMember="Category" TextField="name" ValueField="id" ToolTipField="Description" />
                   <asp:TreeNodeBinding Depth="2" DataMember="Product" TextField="name" ValueField="id" />
                </DataBindings>
            </asp:TreeView>
        </div>
        <asp:XmlDataSource ID="XmlDataSource1" runat="server"></asp:XmlDataSource>
    </form>

And here below is a screenshot of the treeview rendering itself. Fantastic :-)

Update 21 May 2008

Customizing the ChangePassword control and removing the required CurrentPassword field

alessandro — Fri, 15 Feb 2008 15:47:00 GMT

It's very rare that what is already provided in asp.net under the Login controls fits my requirements out of the box without some tweaking. Not that any of these controls offer anything specialized, but certainly they are a big time saver if we can re-utilize their functionality.

First some background as to why i personally want to customize the ChangePassword control to suit my needs :

Password recovery is what i was after today, however i have hashed passwords, and recovery is impossible. If the user lost their password, then there is no way for me to know what their password is and send it back in clear text.

The ideal solution is to reset the password, however the autogenerated password is quite ugly and quite hard to remember. What I've decided to do is send the email during password recovery, but as part of the email, instead of telling the user their old password(which i can't) , I'm instead going to ask them to click on a tokenized link that will guarantee to me that they are indeed the ones that requested the password, send them to the page where they can provide a new password, in the background i'd be autogenerating a password first ofcourse, then updating the password with their new password because the MembershipUser.ChangePassword(oldPassword, newPassword) method requires Old password as one of it's two parameters.

This password change step, i'd like to be done using the ChangePassword control, however to my big surprise CurrentPassword Field is a required field that i cannot remove. This is also a field that I do not want asked for during the password change request(since my user has forgotten their password and are now going to provide their new pasword).

There is ofcourse no property or method in this control that removes the CurrentPassword field requirement, below is a screenshot of the ChangePassword control in designview, as you can note, the highlighted field is the CurrentPassword field i do not want.

I've done a quick look on google and in the asp.net/forums and didn't find anybody providing any proper solutions either, mostly vague replies : http://forums.asp.net/p/1189347/2038354.aspx

As you can read from the posts there, the issue seems to be two things which were also my same issues :
1) Remove the current password label/TextBox
2) Pass the new resetpassword to CurrentPassword Property which by the way is a getter only and not settable (SAD SAD)

Both of these things are not supported in this control. So let's quickly fix requirement 1 and there are a couple of ways to fix this :
a) You have to define a custom <ChangePasswordTemplate>. This can be easily done by taking your ChangePassword control into DesignView in Visual studio, right click on the control and select "Convert to template". You can then switch to HtmlView and set the visibility of CurrentPasswordLabel, CurrentPassword and CurrentPasswordRequired controls.

b) If you prefer to do this in code, then you can find the Label and TextBox for CurrentPassword and set its visiblity to false. Since a is a nobrainer, i'm including a sample code of method (b) :

Label l = (Label)changePassword1.ChangePasswordTemplateContainer.
    FindControl("CurrentPasswordLabel");
if (l != null)
    l.Visible = false;

TextBox tb = (TextBox)changePassword1.ChangePasswordTemplateContainer.
FindControl("CurrentPassword");
if (tb != null)
    tb.Visible = false;

RequiredFieldValidator rfv = (RequiredFieldValidator)changePassword1.
ChangePasswordTemplateContainer.FindControl("CurrentPasswordRequired");
if (rfv != null)
    rfv.Visible = false;

Now that we have the fields we want disabled, let's head onto fix issue 2 :
We can't pass the Autogenerated password to the CurrentPassword Property because its a getter only, however this getter returns the value from our CurrentPassword TextBox, and this job is done immidiately after ChangingPassword event fires. This is good news for us, so we can resolve issue 2 like this :

void changePassword1_ChangingPassword(object sender, 
    LoginCancelEventArgs e)
{
    changePassword1.UserName = user.UserName;
        TextBox currentPassword = (TextBox)changePassword1.
         ChangePasswordTemplateContainer.FindControl("CurrentPassword");
        if (currentPassword != null)
            currentPassword.Text = user.ResetPassword();
}

Note that in the above code, user is a reference to a field of type MembershipUser. Ok, that's it. Now we have what were after, look at the screenshot below :