Working with SqlDataSource Control in ASP.NET 3.5

I have been writing articles about all data sources and now it is time for the SqlDataSource control.

As you may guess, the SquDataSource control in ASP.NET 3.5 is designed to work with a SQL Server database. It uses SQL Server .NET data provider internally to connect to the database.

SQL Server .NET data provider classes are defined in the System.Data.SqlClient namespace.The SqlDataSource data source control represents data in an SQL relational database to data-bound controls. You can use the SqlDataSource control in conjunction with a data-bound control to retrieve data from a relational database and to display, edit, and sort data on a Web page with little or no code. SqlDataSource control inherited from DataSourceControl class, which provides common functionality for all of these data source controls.

The SqlDataSource class provides a FilterExpression property that can be used to filter the results of calling the SqlDataSource class' Select method.

To connect to a database, you must set the ConnectionString property to a valid connection string. The SqlDataSource can support any SQL relational database that can be connected to using an ADO.NET provider, such as the SqlClient, OleDb, Odbc, or OracleClient.

This example shows how to Select, Insert, Update data from database using SqlDataSource control without writing a single line of code. How cool is that?

This is for select data from a database.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>"  SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor]">
</asp:SqlDataSource>

This is for update data into a database:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>"

SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor]"

UpdateCommand="UPDATE Vendor SET VendorFName = @VendorFName, VendorLName = @VendorLName, VendorCity = @VendorCity, VendorState = @VendorState, VendorCountry = @VendorCountry, VendorDescription = @VendorDescription WHERE VendorId = @VendorId">          

            <UpdateParameters>
            <asp:FormParameter Name="VendorId" FormField="VendorId" />
            <asp:FormParameter Name="VendorFName" FormField="VendorFName" />
            <asp:FormParameter Name="VendorLName" FormField="VendorLName" />
            <asp:FormParameter Name="VendorCity" FormField="VendorCity" />
            <asp:FormParameter Name="VendorState" FormField="VendorState" />
           <asp:FormParameter Name="VendorCountry" FormField="VendorCountry" />
            <asp:FormParameter Name="VendorDescription" FormField="VendorFName" />            
            </UpdateParameters>          
        </asp:SqlDataSource>    

This is for insert data into a database:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:VendorConnectionString %>"           

SelectCommand="SELECT [VendorId], [VendorFName], [VendorLName], [VendorCity], [VendorState], [VendorCountry], [PostedDate], [VendorDescription] FROM [Vendor

InsertCommand="INSERT INTO Vendor(VendorFName, VendorLName, VendorCIty, VendorState, VendorCountry, VendorDescription) VALUES (@VendorFName, @VendorLName, @VendorCity, @VendorState, @VendorCountry, @VendorDescription)"         

            <InsertParameters>
            <asp:FormParameter Name="VendorFName" FormField="VendorFName" />
            <asp:FormParameter Name="VendorLName" FormField="VendorLName" />
            <asp:FormParameter Name="VendorCity" FormField="VendorCity" />
            <asp:FormParameter Name="VendorState" FormField="VendorState" />
            <asp:FormParameter Name="VendorCountry" FormField="VendorCountry" />
            <asp:FormParameter Name="VendorDescription" FormField="VendorFName" />           
            </InsertParameters>           
        </asp:SqlDataSource>    
 
After setting up the data source, now I will display data in a GridView control. In GridView control, you just set the DataSourceID property of the GridView control to the data source control we created above. 

Here is the code to do so.

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
            AutoGenerateColumns="False" BackColor="White" BorderColor="#E7E7FF"
            BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="VendorId"
            DataSourceID="SqlDataSource1" GridLines="Horizontal">
            <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
            <Columns>
                <asp:BoundField DataField="VendorId" HeaderText="VendorId"
                    InsertVisible="False" ReadOnly="True" SortExpression="VendorId" />
                <asp:BoundField DataField="VendorFName" HeaderText="VendorFName"
                    SortExpression="VendorFName" />
                <asp:BoundField DataField="VendorLName" HeaderText="VendorLName"
                    SortExpression="VendorLName" />
                <asp:BoundField DataField="VendorCity" HeaderText="VendorCity"
                    SortExpression="VendorCity" />
                <asp:BoundField DataField="VendorState" HeaderText="VendorState"
                    SortExpression="VendorState" />
                <asp:BoundField DataField="VendorCountry" HeaderText="VendorCountry"
                    SortExpression="VendorCountry" />
                <asp:BoundField DataField="PostedDate" HeaderText="PostedDate"
                    SortExpression="PostedDate" />
                <asp:BoundField DataField="VendorDescription" HeaderText="VendorDescription"
                    SortExpression="VendorDescription" />
            </Columns>
            <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
            <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
            <AlternatingRowStyle BackColor="#F7F7F7" />
        </asp:GridView>

Now, let's do this steo by step using Visual Studio 2008.

First of all, we need to create a database connection. Use the Add Connection wizard as shown in the following figure 1. 

Figure 1.

Now configure a data source. You can use this by using Configure Data Source option available on the GridView control. The Configure Data Source wizard looks like Figure 2.

Figure 2.

In the end, this stop will save the connection string in the web.config file if you have the default options selected on Figure 3.

Figure 3.

Next step, you will be asked the database table, SP or SQL statement details. As you can see from Figure 4, I select the Vendor table.

 

Figure 4.

On the next step, you may see the test query and the data from the database table as you can see in Figure 5.

Figure 5.

After setting up a data source, you simply needs to set the DataSourceID property of a data bound control. In my case, I use the GridView control.

Now if you run the application, you will see results like Figure 6 where you can even edit and delete a record.

Figure 6.

If you want to use my database, download the attached project. You can use any database just add the database to your app_Data folder or select a database during your data connection wizard.