Introduction
SqlDataSource control is a data source control provided in ASP.NET to connect to database providers such as SQL, OLEDB, ODBC, and Oracle. This control only establishes a connection with the data source; it does not display data on the web page. The data is displayed by binding the SqlDataSource control to a data-bound control such as a GridView or DataList. These data-bound controls, in turn, display the data on the web page. The SqlDataSource control also supports editing, updating, deleting, and sorting of the records retrieved from the database. This support to manipulate the data in the data source can be implemented in data-bound controls without writing any code. In other words, a SqlDataSource control allows you to access databases without creating a Connection, Command, or a Data Reader object.
Adding SqlDataSource Control
A SqlDataSource control is added to the webform using markup or by dragging it from the Toolbox and dropping it on the web form. The code below shows the markup to create a SqlDataSource control.
<asp:SqlDataSource ID=”sqldsSuppliers” runat=”server”>
</asp:SqlDataSource>
The markup in this code creates a SqlDataSource control named sqldsSuppliers. This control will be used to connect to the NWSuppliers table in the Northwind database.
Configuring SqlDataSource Control
The basic configuration of the SqlDataSource control involves setting two attributes, namely ConnectionString and SelectCommand. The ConnectionString attribute specifies the connection string to connect to a particular database. The SelectCommand attribute specifies the select statement to retrieve the records from a database table. Source code uses these attributes to connect to the Northwind database located on the SQL Server instance, SQLEXPRESS, installed on the system 10.2.1.51.
<asp:SqlDataSource ID=”sqldsSuppliers” runat=”server” ConnectionString=”Data Source=10.2.1.51\SQLEXPRESS;Initial Catalog=Northwind; Integrated Security=True” SelectCommand=”select * from NWSupplierss;”>
</asp:SqlDataSource>
The SqlDataSource control, sqldsSuppliers, will retrieve all the records from the NWSuppliers table.
The attribute ConnectionString and SelectCommand are available only in the Source view of the web form in design view, the selectCommand attribute is available as a SelectQuery property.
Binding to a Data Bound Control
After the SqlDataSource control is configured to retrieve data from the database, it is bound to a data-bound control. The code below shows the code to bind an SQqlDataSource control named sqldsSuppliers to a DataList control named dlstSuppliers using the DataSourceID property.
<asp:DataList ID=”dlstSuppliers” runat=”server” backcolor=”LightGoldenrodYellow” BorderColor=”Tan” BorderWidth=”1px” CellPadding=”2” DataSourceID=”sqldsSuppliers” Font-Name=”verdana” Font-Size=”Smaller” ForeColor=”Black”>
</asp:Datalist>
The same SqlDataSource control can be bound to another data-bound control such as the GridView control by setting the DataSourceID property to sqldsSuppliers. Code shows the ItemTemplate code to display the data in a DataList control named dlstSuppliers.
<ItemTemplate>
<strong>Supplier ID </strong>
<asp:Label ID=”lblSupplierID” runat=”server” Text=’<%#Eval(“SupplierID”)%>’ width=”45px” Font-size=”x-small”>
</asp:Label> <strong> Company Name:</strong>
<asp:Label ID=”lblCompanyName” runat=”server” Test=’<%#Eval(“Companyname”)%>’ width=”197px” Font-size=”x-small”>
</asp:Label>
<br />
<br />
<strong> Contact Name:</strong>
<asp:Label ID=”lblContactName” runat=”server” Test=’<%#Eval(“ContactName”)%>’ width=”127px” Font-size=”x-small” Font-Bold=”False”>
</asp:Label>
<br />
<br />
<strong> Phone:</strong>
<asp:Label ID=”lblPhone” runat=”server” Test=’<%#Eval(“Phone”)%>’ width=”111px” Font-size=”xx-small” Font-Bold=”False”>
</asp:Label>
<br />
<br />
</ItemTemplate>
<AlternatingItemStyle BackColor=”PaleGoldenrod” />
The ItemTemplate in the source code comprises four times. It displays four textual labels namely Supplier ID, Company name, Contacts Name, and Phone. The ItemTemplate also has four corresponding Label controls namely lblSupplierID, lblCompanyName, lblContactName, and lblPhone respectively. These Label controls are bound to the columns SupplierID, CompanyName, ContactName, and Phone respectively of the NWSuppliers table.
Commands in SqlDataSource Control
The SqlDataSource control uses the SelectCommand property to retrieve the records from a database table. Similarly, SqlDataSource control is provided with DeleteCommand, UpdateCommand, and InsertCommand properties to delete, update and add a new record into a database table.
DeleteCommand Property
The DeleteCommand property is used to specify the DELETE statement to delete a record. DeleteCommand is available as an attribute of the SQLDataSource tag in source view. In the Design view, the DELETE statement is specified as a value of the property, DeleteQuery. Source code shows in bold how to set the DeleteCommand attribute of the SqlDataSource tag.
<asp:SqlDataSource ID=”sqldssuppliers” runat=”server” ConnectionString=”Data Source=10.2.1.51\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=”True” SelectCommand=”Select * from NWSuppliers ;”DeleteCommand=”delete from NWSuppliers where SupplierID=@SupplierID” >
<DeleteParameters>
<asp:Parameter Type=”Int32” Name=”SupplierID” />
</DeleteParameters>
</asp:SqlDataSource>
The DeleteCommand in code contains a placeholder, @SupplierID, for SupplierID value. The details about this placeholder are specified by the <DeleteParameters> element. This data source control can now be used with a data-bound control such as a DataList control.
If the SqlDataControl is used with a GridView control, you can provide delete functionality by setting two properties. The DataSource property is set to sqldsSuppliers and the DataKeyNames property is set to SupplierID.
The steps to be followed in order to implement delete functionality in the DataList control, dlstSuppliers are listed below:
In the Properties window of the dlstSuppliers control, set the DatakeyField property to the primary key column of the database table, NWSuppliers. In this case, you set it to SupplierID. The DataKeyField attribute specifies the name of the primary key column, which is used to delete or update a record in the database table.
In the ItemTemplate of dlstSuppliers control, add a LinkButton Web Server control with the CommandName property set to delete and Text property set to Delete. This will render the DataList control with a Delete link to delete an item.
Add a DeleteCommand event handler to dlstSuppliers control a shown in code below,
protected void dlstSuppliers DeleteCommand(object source, DataListCommandEventArgs e) {
int id = (int) dlstSuppliers.DataKeys[e.Item.ItemIndex];
sqldsSuppliers.DeleteParameters[“SupplierID”].DefaultValue = id.ToString();
sqldsSuppliers.Delete();
}
The DataKeys collection of dlstSuppliers contains all the keys values of the items listed in the control. Therefore, the SupplierID value of the item clicked is retrieved by indexing the Datakeys collection with the index of the item clicked. The ItemIndex property returns the index of the item clicked. The SupplierID value retrieved is then assigned to the delete parameter of the SqlDataSource control named sqldsSuppliers. Finally, the delete() method is invoked.
Summary
The SqlDataSource control is used to connect to databases such as Access, SQLServer, and Oracle. The SqlDataSource and XMLDataSource controls are associated with a data-bound control using the DataSourceID attribute. The SelectCommand attribute of the SqlDataSource tag is used to specify a SELECT statement while the DeleteCommand attribute is used to specify a DELETE statement. Parameters to DELETE statement in a DeleteCommand are described using the <DeleteParameters> element.