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.