Frank Wang's inspirations on .NET

Shorten your conditional statements with the ?? operator

2008-07-16T15:14:37-07:00:00

I enjoy the new C# language features very much. Recently I started using the new ?? operator in my current projects. The ?? operator defines the default value to be returned when a nullable type is assigned to a non-nullable type. It is specially handy when you need to return a default value from your custom methods. A good example would be you wanted to capture the query string values in a web page but you needed to avoid the null reference exception in case the query string doesn't exist in the request. Consider the following code.

private string GetQueryStringValue(string name, string defaultValue)
{
   return Request[name] ?? defaultValue;
}

If the query string doesn't exist, the string defaultValue will be returned.

Execute Transact-SQL statement asynchronously

2008-07-05T15:33:13-07:00:00

Processing a large amount of data from SQL server in .NET applications can be challenging some times. A common concern that people have is it just takes too much time to get the data from SQL. Waiting for the entire result set to return and then handling it require a great amount of RAM usage. That's not the worst thing yet. Imagine how frustrated your users can get when they have to wait for minutes after they click a button. A better designed application should utilize the asynchronous pattern to the data retrieval and process the data as soon as it becomes available in the callback function.

.NET developers used to write their own asynchronous code to execute T-SQL statements back in the .NET 1.0 & 1.1 days. The System.Data.SqlClient namespace in .NET 2.0 shipped with a number of out-of-box asynchronous methods for T-SQL execution, including ExecuteReader, ExecuteNonQuery, ExecuteXmlReader etc. These methods strictly follow the standard MS naming conventions for asynchronous methods. Take ExecuteReader as an example, BeginExecuteReader and EndExecuteReader are provided. The only caveat, if any, is that the asynchronous processing of T-SQL requires SQL Server 2005 or higher versions. If you haven't paid attention yet, the Management Studio for SQL 2005 or 2008 execute "select" queries asynchronously, as you can see in the screen shot below. I am selecting 4.5 million records from a table. The query comes back in less than 2 seconds with the first set of records. At the same time, the Management Studio keeps rendering the rest of the records behind the scene.

To demonstrate how the asynchronous operation works with large result sets, we are going to create a similar UI to the query window you just saw above.

Let's start off with creating a new Windows Form project named AsyncReader. Go ahead and add the controls to the Windows Form as shown below.

The end goal of the example is: when I click on the Execute button, the application connects to the Northwind database, executes the T-SQL statement entered in the text box, and displays the result set in the ListBox at the bottom asynchronously.

Double click on the Execute button to bring up the code editor. We are going to implement the OnClick event of this button.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection("Network Address=(local), Initial Catalog=Northwind; Integrated Security=true; Asynchronous Processing=true"))
    {
        using (SqlCommand cmd = new SqlCommand(tbQuery.Text, conn))
        {
            try
            {
                conn.Open();
                cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                conn.Close();
            }
        }
    }
}

The code above follows the standard procedure for reading data from a SQL database in a .NET application, except two things:

1. There's an additional name-value pair in the connection string. "Asynchronous Processing=true" tells the SQL Server (2005 or above) that this application will be executing T-SQL statements asynchronously. This name-value pair MUST be included in the connection string to enable asynchronous operations. Otherwise, an InvalidOperationException will be thrown when the BeginExecuteReader is called.

2. We used BeginExecuteReader method instead of ExecuteReader. This is the key. We are not going to wait until the entire result set returns. A callback delegate "QueryCallback" was passed as the first parameter. This means we are going to process the result set in "QueryCallback". The second parameter is called "stateObject" and it is of the System.Object type. It really can be boxed from any type of object holding the user state that is passed into the callback procedure. In our example, we pass the SqlCommand as the state object because we will need it to get the actual reference of the SqlDataReader.

The execution may take 2 seconds, 2 minutes, or probably 2 hours, but we have just solved two problems here by doing things asynchronously. First of all, the UI will NOT freeze because the BeginExecuteReader fires the query off and returns to the UI thread IMMEDIATELY. The actual T-SQL execution happens in a separate work thread. We will demonstrate this in a moment. Secondly, the query may take hours to complete but we can start processing the result set as soon as the data comes back in our callback method. Let's take a look at what's going to happen in the callback method.

private void QueryCallback(IAsyncResult result)
{
    try
    {
        SqlCommand cmd = (SqlCommand)result.AsyncState;
        SqlDataReader reader = cmd.EndExecuteReader(result);
        while (reader.Read())
        {
             Invoke(new AddDataToResultDelegate(AddDataToResult), reader.GetString(0));
        }
        if (cmd.Connection.State.Equals(ConnectionState.Open))
        {
            cmd.Connection.Close();
        }
    }
    catch (Exception ex)
    {
        Invoke(new AddDataToResultDelegate(AddDataToResult), "Error: " + ex.Message);
    }
}

The first thing we do is un-box the AsynState back to the SqlCommand, which was supplied as the state object from BeginExecuteReader. Then we finish the asynchronous reading by calling the EndExecuteReader, which returns the requested SqlDataReader. Remember that calling EndExecuteReader is NOT optional. When you call BeginExecuteReader to execute a T-SQL statement, you MUST call EndExecuteReader in order to complete the operation. If the process of executing the command has not yet finished, this method blocks until the operation is complete. Now that we've got hold of the reference to the SqlDataReader, we can do anything we want with the data it brings back. In this case, we are going to display the data in the ListBox control named lbResult we placed on the form earlier.

private delegate void AddDataToResultDelegate(string text);
 
private void AddDataToResult(string Text)
{
    lbResult.Items.Add(Text);
}
 
private void DisplayInfo(SqlDataReader reader)
{
    Invoke(new AddDataToResultDelegate(AddDataToResult), reader.GetString(0));
}

We may not interact with the form and its contents from a different thread, and this callback procedure is all but guaranteed to be running from a different thread than the form. Therefore you cannot simply call code that update the ListBox control, like this: AddDataToResult(reader.GetString(0)); Instead, we must call the procedure from the form's thread. One simple way to accomplish this is to call the Invoke method of the form, which calls the delegate AddDataToResultDelegatae supplied from the form's thread.

Up to this point, we have done all we need to do for this quick example. Compile it to make sure there're no errors, and then hit F5 to run the application. Enter "select * from customers" in the text box and click the Execute button. So what did you get? Nothing!!! None of the records was added to the ListBox. We know there're 91 customers in the Northwind database. So what happened? Stop the application. Place a break point at the beginning of the QueryCallback method and run the application again. The program doesn't even stop at the breakpoint?!

Welcome to the asynchronous world, where nothing is what it seems. We know the problem already. The callback function expects the state object supplied by BeginExecuteReader, but it's not there any more. Take a closer look at the Execute button's OnClick event.

private void button1_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection("Network Address=(local), Initial Catalog=Northwind; Integrated Security=true; Asynchronous Processing=true"))
    {
        using (SqlCommand cmd = new SqlCommand(tbQuery.Text, conn))
        {
            try
            {
                conn.Open();
                cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                conn.Close();
            }
        }
    }
}

The two "using" statements are the first thing that came into our attention. We are supplying the SqlCommand instance as the object state to the callback function but it gets disposed by the "using" immediately after the asynchronous call is fired. If you scream "oh I should've thought of that", you are not alone. This is a common mistake that developers make from time to time in asynchronous programming. The state object are accessed by two threads (the form's thread and a work thread that executes the asynchronous T-SQL), so its reference needs to exist through the entire application context. This is why sometimes the state objects are also called "user context". The solution is obvious now - remove the "using" statements to make sure the SqlCommand lives the entire application's life cycle.

private void button1_Click(object sender, EventArgs e)
{
  SqlConnection conn = new SqlConnection("Network Address=localhost;Initial Catalog=Northwind;Integrated Security=true;Asynchronous Processing=true");
  SqlCommand cmd = new SqlCommand( tbQuery.Text, conn);
  try
  {
      conn.Open();
      cmd.BeginExecuteReader(new AsyncCallback(QueryCallback), cmd);
  }
  catch (SqlException se)
  {
      AddDataToResult(se.Message);
  }
  catch (Exception ex)
  {
      AddDataToResult(ex.Message);
  }
}

Run the program again. We got everything together this time. The form's UI is still responsive to the user interactions as the query executes in the background. The ListBox is filled up with all the 91 customer names after a slight delay.

The demo code is all yours. You can download it here and play with it a little bit more. If you want to feel the true power of asynchronous processing, I suggest you connect this application to a database where you have millions of records.

Experience SQL Server 2008 RC0

2008-06-19T23:06:39-07:00:00

Microsoft last week rolled out the first Release Candidate of SQL Server 2008. The RC0 bits are available for public download here. I have been playing with SQL 2008 RC0 for a few days already, and I would like to share my experience in this quick blog post.

First of all, I have to warn you that my RC0 installation experience was not pleasant at all. If you are an early adopter of new releases like me, you will very likely run into the same troubles I had.

Previous CTP releases of SQL Server 2008 "Katmai" must be uninstalled first. The annoying part was the installation requirement check process doesn't warn you about the previous installations. Instead, it reports a fatal error as soon as the installation has started, because the system database files are being overwritten.

SQL Server 2005 Express can NOT exist on the machine where you are installing RC0. The installation wizard will tell you the check on "SQL Server 2005 Express Tools" fails and it must be uninstalled. I had a hard time looking for "Express tools" so I ended up uninstalling the entire SQL Server 2005 Express. Remember to reboot your machine before you attempt to install RC0 again :-)

I am not a SQL guy so I have played with the management studio for the most part. First thing I noticed was the splash screen is prettier than the SQL 2005 :-)

IntelliSense, introduced in the Feb 2008 CTP of SQL Server 2008, is still available in RC0. Please refer to my previous blog post if you want to know more about the IntelliSense in SQL 2008.

Real-time syntax check is a feature I missed in my previous blog. But yeah it's also very cool.

An out-of-the-box Activity Monitor is provided to DBAs for real-time monitoring the health of the database server.

The most stunning new feature in RC0, I have to say, is the query debugging in the management studio. If you have paid attention to the previous screen shots, you should've noticed the new "Debug" button in the toolbar already.

I have two queries ready to go in the window above. Now let's hit the green debug button. What are you expecting?

Very nice! Our favorite yellow debug arrow is showing up in the SQL management studio now. Press F10 to step over the first query. We have got the first record set already in the result panel, and the second query is waiting to be executed.

The debugging can be really helpful when you need to investigate some logics in the database. Here's a better example.

How many times have you copied a result set into Excel and manually added the headers to the sheet? And for how long have you been doing this? SQL 2000, SP1, 2,3,4, SQL 2005, SP1, SP2, SQL 2008 "Katmai', July 2007 CTP, Feb 2008 CTP...

Well, the folks at Redmond could've made our life easy a long time back. I don't know why we've waited so long for such a small feature. "Copy with Headers" is finally here, in RC0 of SQL 2008.

I have the feeling that I probably will have to publish another blog post about the new features in SQL 2008 RC0. There're just too many things to cover. I am certainly looking forward to your comments as well, if you happen to discover other cool stuff before I do.

Clean up KB944899 before installing Visual Studio 2008 SP1 Beta

2008-06-16T01:06:17-07:00:00

Visual Studio 2008 SP1 Beta was released a few weeks ago. If the installation on your computer failed with a "fatal error" message in the summary dialog, you may want to check to see if the KB944899 for VS 2008 was already installed. This patch was released by Microsoft as a hotfix roll-up to solve a number of slow response issues with VS 2008 and VWD 2008.

The error message is not displayed immediately after you start the installation. Instead, it is displayed at the end of the 20-minute installation process. If you view the installation log file, you will see reason being the KB944899 needs to be removed.

Fortunately, the remove utility for KB944899 was provided on Code Gallery, and it can be downloaded here. Running this tool will clean up your previous hotfix roll-up. After that, install VS 2008 SP1 beta again. It should go through fine this time.

Saving table changes is safer in SQL Server 2008

2008-05-29T15:36:00-07:00:00

I have been playing with SQL Server 2008 "Katmai" for quite a while. Although the new management studio designer looks almost the same as that of SQL 2005, there're actually many new features under the hood, and some of them are quite thoughtful. I have just been discovering these goodies here and there everyday.

What I found today is also pretty interesting. By default, SQL Server 2008's designer no longer allows saving changes that require table re-creation. You will need to turn off this safety check manually if you enjoy the flexibility of saving table changes directly from the designer. This feature may be a little annoying for your day to day development work, but it definitely makes your data safer if you are working with tables with a huge amount of data.

To demonstrate this, we will try to add a new column in the Northwind.Suppliers table, and then attempt to save the change, as shown in the screen shot below.

You get immediate warning message saying the saving changes is not permitted. That makes sense. Adding a new column to a table obviously requires table re-creation. Now go to Tools --> Options --> Designers --> Table and Database designer. You should see a new option "Prevent saving changes that require table re-creation" is checked.

Simply uncheck this option if you still want to be able to save table changes from the designer.

Forms Authentication with Active Directory in ASP.NET 2.0

2008-05-18T00:59:00-07:00:00

The Membership API is new to ASP.NET 2.0. It provides you with a full-fledged infrastructure for managing and authenticating users of your applications. ASP.NET 2.0 shipped two Membership providers, SQL Server and Active Directory. While plenty of articles and blog posts have been published on how to use the SQL Server Membership provider, very few have been done for the Active Directory Membership provider. I was recently leading an enterprise web site project that required the Active Directory authentication. I just thought it might be interesting to share with you a few bullet points of using Active Directory Membership provider in ASP.NET 2.0.

In this blog post, we will implement the AD authentication in an ASP.NET web site by completing the following four steps.

Create a web app with a login page
Configure the web app to use forms authentication
Add the ActiveDirectoryMemebershipProvider into the web app
Manager users with ActiveDirectoryMemebershipProvider

Create a web app with a login page

Open Visual Studio 2008, create a new Web Site named FormsAuthAD. After the web site is created, add a new web form named "Login.aspx", and then place a Login control onto the form.

<asp:Login ID="Login1" runat="server" BackColor="#F7F6F3" BorderColor="#E6E2D8" 
            BorderPadding="4" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" 
            Font-Size="Small" ForeColor="#333333" Height="130px" 
            onloginerror="Login1_LoginError" Width="303px">
          <TextBoxStyle Font-Size="Small" />
          <LoginButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid" 
              BorderWidth="1px" Font-Names="Verdana" Font-Size="Small" ForeColor="#284775" />
          <InstructionTextStyle Font-Italic="True" ForeColor="Black" />
          <TitleTextStyle BackColor="#5D7B9D" Font-Bold="True" Font-Size="Small" 
              ForeColor="White" />
        </asp:Login>

We don't need to configure anything for the Login control.

Configure the web app to use forms authentication

If the web.config file was not created, go ahead and add it to the project. Locate the <authentication> element in the web.config file, and then change the mode attribute to Forms. Add the <forms> element as the child of the <authentication> element, set the loginUrl, defaultUrl, name and the timeout attributes as shown in the following example.

<authentication mode="Forms">
            <forms name=".ADAuthCookie" timeout="10" 
                    loginUrl="Login.aspx" defaultUrl="Default.aspx">
            </forms>
        </authentication>

The <authorization> element is also required to make the forms authentication work. Add the following <authorization> element beneath the <authentication> element in the web.config file.

<authorization>
   <deny users="?"/>
   <allow users="*"/>
</authorization>

What's happening here is we are allowing only authenticated users to access the app. The "?" indicates unauthenticated users and the "*" indicates all users.

Add the ActiveDirectoryMemebershipProvider

The ActiveDirectoryMemebershipProvider can be configured by specifying memebership settings in the web.config file. First of all, we need to add a connection string that points to the Active Directory user container. The domain name of my home lab is called dotnetinspirations.com, so my connection string looks like this:

<connectionStrings>
    <add name="ADConnectionString" connectionString="LDAP://dotnetinspirations.com/CN=Users,DC=dotnetinspirations,DC=com"/>
</connectionStrings>

Now we need to add the ActiveDirectoryMemebershipProvider to use the connection string defined above. Add a <membership> element after the <authorization> element as shown below.

<membership defaultProvider="DomainLoginMembershipProvider">
   <providers>
     <add name="DomainLoginMembershipProvider" 
          type="System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=2.0.0.0,Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
          connectionStringName="ADConnectionString" 
          connectionUsername="dotnetinspiration.com\administrator" connectionPassword="youradminpassword"/>
   </providers>
 </membership>

Note the connectionStringName is set to the name "ADConnectionString" we specified earlier. An interesting point here is we overwrote the defaultProvider attribute to "DomainLoginMemebershipProvider", which is defined in the <providers> element. We have to overwrite this attribute because the machine-level default MembershipProvider points to SQLMembershipProvider, using the localhost\SQLExpress instance, and that's the default provider used by ASP.NET.

In this example, I have all the control over my own dotnetinspiration.com domain and I logged into the Active Directory as the administrator. If you are running this application in a less flexible environment, you need to obtain an domain account that has sufficient permissions in Active Directory. If you do not specify account credentials (connectionStringName and connectionPassword), Active Directory uses your ASP.NET web app's process account, which typically has fairly low priviledges, and you may not be able to test all the features of the application.

Up to this point, we are ready to test the Active Directory authentication. We will just add a quick line of code in the default.aspx page to display the authenticated user's identity. This should be done in the Page_Load event handler of the default.aspx page.

protected void Page_Load(object sender, EventArgs e)
{
  Response.Write("Hello, " + Server.HtmlEncode(User.Identity.Name));
}

Run the web site and log in using any existing account on your domain. If the the login is successful, you will be redirected to the default.aspx page, which displays the name of the logged in user. Otherwise, the login control will automatically display login failure message to you.

Manager users with ActiveDirectoryMemebershipProvider

The ActiveDirectoryMemebershipProvider not only provides you with you the capability of authenticating users without writing any code but also allows you to conveniently manage users in ASP.NET as if you were working on the Active Directory. We will demonstrate this by creating a new web form for adding new users. From the Solution Explorer, add a new page called CreateNewUser.aspx, add a CreateUserWizard control to the form once it is generated.

<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#F7F6F3" 
    BorderColor="#E6E2D8" BorderStyle="Solid" BorderWidth="1px" 
    Font-Names="Verdana" Font-Size="Small">
    <SideBarStyle BackColor="#5D7B9D" BorderWidth="0px" Font-Size="0.9em" 
        VerticalAlign="Top" />
    <SideBarButtonStyle BorderWidth="0px" Font-Names="Verdana" ForeColor="White" />
    <ContinueButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" 
        BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" 
        ForeColor="#284775" />
    <NavigationButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" 
        BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" 
        ForeColor="#284775" />
    <HeaderStyle BackColor="#5D7B9D" BorderStyle="Solid" Font-Bold="True" 
        Font-Size="0.9em" ForeColor="White" HorizontalAlign="Center" />
    <CreateUserButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" 
        BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" 
        ForeColor="#284775" />
    <TitleTextStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <StepStyle BorderWidth="0px" />
   <WizardSteps>
       <asp:CreateUserWizardStep ID="CreateUserWizardStep1" runat="server">
       </asp:CreateUserWizardStep>
       <asp:CompleteWizardStep ID="CompleteWizardStep1" runat="server">
       </asp:CompleteWizardStep>
   </WizardSteps>
</asp:CreateUserWizard>

Just like the Login control, nothing needs to be configured for the CreateUserWizard control to work. The ASP.NET reads the web.config file during runtime and it becomes aware of the underlying data source for managing users.

Run the web site again, log into the site this time with a privileged account (we need to be able to create new users). In the browser's address bar, replace default.aspx with CreateNewUser.aspx. Follow the wizard and create a brand new user. Log into the web site one more time with this new account.

The source code for the example can be downloaded here.