SqlDataSource Control in ASP.NET 2.0 for Rapid Application Development (RAD)

The SqlDataSource Control in ASP.NET 2.0 is all about Rapid Application Development (RAD).  Although it will probably be snubbed by n-layer purist who won't entertain the thought of placing data layer components on the UI, you have to appreciate the simplicity associated with dropping the SqlDataSource Control on a webform, hooking it up to a GridView, and without writing any code having the ability to page and sort through records returned by query.

As you might expect the SqlDataSource Control supports two DataSourceModes, which is exposed by the DataSourceMode property on the class:

• DataReader
• DataSet

The DataReader is faster, but it doesn't allow you to page, sort, and cache the data.  In fact, if you set your GridView so that AllowPaging=”True” and AllowSorting=”True” but have set the DataSourceMode = DataReader for your SqlDataSource Control, you will get an error stating you must set the mode to DataSet.  By default, SqlDataSource uses a DataSet.

Here is a snippet of connecting a GridView to a SqlDataSourceControl:

<asp:GridView
    ID="GridView1"
    runat="server"
    DataSourceID="SqlDataSource1"
    AllowPaging = "True"
    AllowSorting = "True"
    
    ...
    
asp:GridView>


<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString =
     "<%$ ConnectionStrings:ConnectionString" %>"
    SelectCommand = "Select * from Product"
asp:SqlDataSource>

The database ConnectionString is specified in the web.config file

<connectionStrings>
    <add name="ConnectionString" connectionString="..."
        providerName="System.Data.SqlClient"/>
connectionStrings>

Using the ConnectionString above, the SqlDataSource Control will query all the products from the Product Table using the SelectCommand = “Select * from Product”.

If you want to use a Stored Procedure instead of a normal text query, just set SelectCommandType=”StoredProcedure” and change the SelectCommand to the name of your stored procedure, “GetProducts”:

<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString =
      "<%$ ConnectionStrings:ConnectionString" %>"<%$ ConnectionStrings:ConnectionString" %>
    SelectCommand = "GetProducts"
    SelectCommandType = "StoredProcedure"
asp:SqlDataSource>

Perhaps you have a querystring variable, CategoryID, that is passed to the page and you only want to display products within that particular category.  No problem.  Just tell the SqlDataSource Control to look for that querystring variable and substitute @CategoryID with the value passed in the querystring:

<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    ConnectionString =
     "<%$ ConnectionStrings:ConnectionString" %>"<%$ ConnectionStrings:ConnectionString" %>
    SelectCommand = "Select * from Product
                    Where (CategoryID = @CategoryID)"
    
    <SelectParameters>
        <asp:QueryStringParameter
            Name="CategoryID"
            QueryStringField="CategoryID"
            Type="Int32" />
    SelectParameters>
asp:SqlDataSource>

Note that I have not written a single line of code.  Not one.  This functionality is all built-in to the SqlDataSource Control.

Just to be complete, the SqlDataSource Control also supports the other CRUD methods and you can specify commands and/or stored procedures for

• InsertCommand
• UpdateCommand
• DeleteCommand

It also fires off events prior to and right after it executes each commands:

• Selecting
• Selected
• Inserting
• Inserted
• Updating
• Updated
• Deleting
• Deleted

And, you can specify other types of parameters, other than the QueryStringParameter, to be used with the commands:

• Control Parameter
• QueryString Parameter
• Form Parameter
• Session Parameter
• Cookie Parameter
• Profile Parameter

I will talk about the different commands, events, and parameters in future posts.  It is truly amazing how easy Microsoft has made all of this for us.

posted on Monday, May 23, 2005 1:33 PM