ASP.NET 2.0 SqlDataSource Control in VB.NET

This article features the SqlDataSource control in VB.NET
  • 3622

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>

 

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:

SqlDatasource1.gif
 

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:

SqlDatasource2.gif
 

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:

SqlDatasource3.gif
 

Clicking the "Next" button will fetch this screen:

SqlDatasource4.gif
 

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 %>"

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

</asp:SqlDataSource>

  SqlDatasource5.gif

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.

  SqlDatasource6.gif

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

  SqlDatasource7.gif

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!

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.