Ryan Smith

Data Access - The SqlDataSource Part 1

Welcome back!

If you are following along from the last post, you'll notice that two controls were automatically created on our page when we dropped the Customer table onto the page. The first control is called a GridView.  The GridView control is used to display data in much the same way that the SQL manager shows you results when you have run a query directly out of it (that is row by row).  The GridView is very powerful and flexible; we'll go into the GridView control a little deeper in later posts but for now let's focus on how we are getting data from the database to the page. 

The SqlDataSource serves as the plumbing that connects the web page to the database.  Let's take a look at how Visual Studio created our control and talk a little bit about the parameters it set up for us. 

Here's the code it created for us:

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString1 %>"
            DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @CustomerID"
            InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle],

[Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID,

@CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)"
            ProviderName="<%$ ConnectionStrings:NORTHWNDConnectionString1.ProviderName %>"
            SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region],

[PostalCode], [Country], [Phone], [Fax] FROM [Customers]"
            UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName,

[ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region,

[PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @CustomerID">
            <DeleteParameters>
                <asp:Parameter Name="CustomerID" Type="String" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="CustomerID" Type="String" />
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="ContactName" Type="String" />
                <asp:Parameter Name="ContactTitle" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="Region" Type="String" />
                <asp:Parameter Name="PostalCode" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Phone" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="ContactName" Type="String" />
                <asp:Parameter Name="ContactTitle" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="Region" Type="String" />
                <asp:Parameter Name="PostalCode" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Phone" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
                <asp:Parameter Name="CustomerID" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>

There's a lot in there right?  Imagine if you had to type all that out!

So what you've got there is a control that will handle all of your CRUD (Create, Retrieve, Update and Delete) operations for the Customer table. Let's go through the properties and parameters a little. 

They're a little out of order but that's okay.

The first one is pretty self-explanatory, that's the ID.  It's just a unique name to identify the control.  We can use this ID in our code behind page if we need to make any programatical changes to our control.

The next one is really important: it's the connection string that resides in our Web.Config and was put there by Visual Studio when we dropped it on the page.  Notice that it's surrounded by the ASP markers, the reason for this is that the ConnectionStrings: object is a little bit of design time magic.  It has a reference to the ConnectionsStrings XML node in the Web.Config file.  There's a related one called ProviderName, this one dictates the type of database to be used, according to our Web.Config file.  Our sample will be using System.Data.SqlClient meaning it will be using Sql Server or Sql Server Express.   However you could conceivably use any database if there was an available provider.

Now we'll move on to the commands.  Visual Studio was nice enough to create T-Sql statements for each of the commands.  The commands created are pretty basic as you can see as they are just CRUD statements.

Take a look at the SelectCommand, it's very a standard Sql select statement, and Visual Studio was even so considerate to surround the fields with []'s in case your field's had spaces etc in them. Just because Visual Studio created T-Sql doesn't mean we're stuck with T-Sql, we can easily use Stored Procedures here, we'll show those off in the next post.

For each of the other Command statements, parameters have be created and integrated into the T-Sql. Let's take a look at them a little deeper.

Check out the DeleteCommand.  When you are deleting a record, you need a way to identify the record you want to delete.  By default Visual Studio uses the table's primary key, in this case it's CustomerID. This is almost enough to get the job done, however since these are Sql Parameters we need to know a little more about the parameter we are passing.  This is where the DeleteParameters section comes in.  In this section we define what type of data the parameter is.  In this case it's a string.

The rest of commands and parameters are more of the same, standard Sql statements and parameters, so I won't bore you by going through each of the others.

When a data control is bound to this SqlDataSource, these commands will be executed when requested by the data control.

If you take a few moments to examine the properties of the SqlDataSource control in Visual Studio, you'll find some interesting stuff.  We'll go into those in a future post and then finish with a look back to highlight the pros and cons of the SqlDataSource.  But now I'm off to get ready for the next post where we'll convert these T-sql statements into stored procedures.

Further Reading:

W3Schools Sql Tutorial

SqlDataSource Web Server Control Overview - Microsoft