The SqlDataSource Control in ASP.NET 2.0

In earlier version of ASP.NET 1.x we have to write data access code to generate the rowset of data that we want to use in our pages. However with the induction of data source control this task of fetching data is much easier.

Thus through simple adding a control to the page and setting the properties we can display the data we want. The control such as GridView and DropDownList renders the data by using the data source control to fetch the data and expose it for data binding.

The data source controls uses the System.Web.UI.WebControls namespace. And implements IDataSource and IHierarchicalDataSoruce interface.

There are few data source controls which are listed below:

1. SqlDataSource Control.
2. AccessDataSource Control.
3. XmlDataSource Control.
4. DataSetDataSource Control.
5. ObjectDataSource Control.

Here we are going to see about the very first in the list i.e. SqlDataSource Control.

The SqlDataSource Control is used to access data from all relational databases. Developer does not have to rewrite code or page changes, but simply has to change the connection string to use any of the databases for which provider is available.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="Data Source=.;Initial Catalog=Employee;User ID=sa"

SelectCommand="SELECT [FirstName], [LastName], [JoinDate], [UserDesignation] FROM [employee_master]"

InsertCommand="" DeleteCommand="" UpdateCommand="">

<SelectParameters></SelectParameters>

<DeleteParameters></DeleteParameters>

<FilterParameters></FilterParameters>

<InsertParameters></InsertParameters>

<UpdateParameters></UpdateParameters>

</asp:SqlDataSource>

 

Attribute	Description
ID	ID for the SqlDataSource Control. Here it is ID="SqlDataSource1".
Runat	runat="server".
ConnectionString	The Connection String. Here it is: ConnectionString="Data Source=.;Initial Catalog=Employee;User ID=sa" Else in most cases it is saved in the web.config and access it like: ConnectionString="<%$ ConnectionStrings:EmployeeConnectionString %>"
ProviderName	Used when SQL Server is not used. Values can be System.Data.Odbc, System.Data.Oledb ect. Default is System.Data.SqlClient.
SelectCommand	Specifies the SQl statement or stored procedure name to extract the data from the the database.Parameters can be passed by using a <SelectParameters>  element.

Step-by-Step Process:

Simply drag and drop the SqlDataSource control from the Data tab of the Toolbox onto the form to get started. Choose Configure Data Source from the control's smart tag menu to get the following screen:

Figure 1: Choose your data connection

In this wizard, the first thing we have to do is to create a connection to the data source or can select the existing one. For creating the new connection, click the "New Connection" button to get the screen shown in the following figure:

 
Figure 2: Create a new connection.

In the "Add Connection" dialog box we can select the name of the server to access using the login credentials and also select the database to access.

Setting the connection and clicking Ok will show this screen:

Figure 3: Database connection created.

Clicking the "Next" button will fetch this screen:

Figure 4: Saving connection string to web.config.

The above screen will store the connection string in web.config file if you want. Although we can hard-code the string into our pages, it's much better to store connection stings in the web.config file. If any changes are required during the development process, then we don't have to change connection string in every pages. We only have to make changes in the web.config file for any of the database or server changes.

The connection string that is stored in web.config file is shown below:

<connectionStrings>

        <add name="EmployeeConnectionString" connectionString="Data Source=.;Initial Catalog=Employee;User ID=sa"

            providerName="System.Data.SqlClient" />

</connectionStrings>

The page code will look like this:

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

</asp:SqlDataSource>

Now, clicking the "Next" button will fetch the screen for configuring the Select statement.

Figure 5: Configuring Select statement

In the above screen, we can sort the records by clicking "ORDER BY" button. We can also specify the selection criteria by clicking the "WHERE" button.

Figure 6: Specifing where clause.

Define custom SQL statement by checking the radio button and clicking the "Next" button in figure 5.

Figure 7: Custom statements.

Here one can define any type of query.

When we define WHERE clause in fugure 6, the wizard creates one or more select parameters that provide the values used by the where clause.

The below table shows how the parameter value can be obtained from different sources.

Element	Description
ControlParameter	Defines a parameter whose value is obtained from control on the page.
QueryStringParameter	Defines a parameter whose value is obtained from query string in the URL.
FormParameter	Defines a parameter whose value is obtained from HTML form field.
SessionParameter	Defines a parameter whose value is obtained from an item in session state.
ProfileParameter	Defines a parameter whose value is obtained from a property of the user's profile.
CookieParameter	Defines a parameter whose value is obtained from a cookie.

That sums up the SqlDataSource control. In the coming articles, I will be explaining about other data source control.

Until then... Happy .NETing!