DataSource controls in .NET 2.0

Introduction

In this article, we're going to learn how to connect a database for querying data and binding the result set to ASP.NET controls in a very easy way with no looping or control manipulation required, thus avoiding writing any data source code.

DataSource control is doing the work for me

DataSource is defined as ASP.NET controls that manage the tasks of connecting to a data source and reading and writing data. Data source controls do not render any user interface but instead act as an intermediary between a particular data store (such as a database, business object, or XML file) and other controls on the ASP.NET Web page. It's acting as our business entities' placeholder in the application logic.

The data source controls include any control that implements the IDataSource interface.

The .NET Framework includes the following data source controls.

• ObjectDataSource: It enables you to work with a business object or other class and create Web applications that rely on middle-tier objects to manage data.
• SqlDataSource: It allows you to connect to any data source that has an ADO.NET data provider (Microsoft SQL Server, OLE DB, ODBC, or Oracle).
• AccessDataSource: It enables you to work with a Microsoft Access database.
• XmlDataSource: It enables you to work with an XML file.
• SiteMapDataSource: It is used for our site navigation.

Using a DataSource control in a Web application is fairly simple. Open a Web Form, and then, from the Toolbox at the Data tab, drag and drop a DataSource control to the form. This object represents business entities persisted in any storage medium which uses ADO.NET providers. Now, we must configure the control.

Click on the little icon at the upper right corner for executing a configuration wizard, which allows picking up a database connection according to the actual connection strings in the connectionStrings section of the Web. config file (listing 1.)for query information and objects schema and writing SQL statements or defining store procedure calls for select, insert, update, and delete commands. If you're using parameters, the configuration wizard presents a window for mapping some input information to these parameters. Parameters are always indicated with an @ symbol, as in @Region (listing 4.). You can define as many symbols as you want, but you must map each provider to another value. In this example, the value for the @Region parameter is taken from the ddlRegions.SelectedValue property of the DropDownList Control.

The .NET Framework includes the following parameters.

• Control property: A map of property value from another control on the page.
• Query string: A map to a value from the current query string.
• Session state: A map of the value stored in the current user's session.
• Cookie: A map to a parameter that can be used to bind to the value of a session variable. 
• Profile: A map to a value from the current user's profile.
• Form variable: A map to a value posted to the page from an input control. Usually, you'll use a control property instead, but you might need to grab a value straight from the forms collection if you've disabled view state for the corresponding control.

There are some drawbacks associated with DataSource controls.

• Data access logic is embedded in the page, so it's difficult to change issues for tuning or profiling after the application deploys.
• Some code is duplicated because you have several DataSource control per page. Sometimes, they have the same SQL statement.
• Lack of flexibility because every SQL statement has a separate DataSource, so if you want to provide different views for the data is very complicated.

I develop a little program for explanatory purposes.

Listing 1. is the configuration of the Web Application.

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"/>
  </connectionStrings>
  <!-- ... other configuration settings ... -->
</configuration>

Listing 2. Web. config.

Listing 2 is the code for the configuration of the SQLDataSource1 object, which is querying the database Northwind for gathering all Region that maps to current customers.

using System;
using System.Data;
using System.Data.SqlClient;
namespace YourNamespace
{
    public partial class YourPageName : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindData();
            }
        }
        private void BindData()
        {
            // Retrieve the connection string from the configuration file
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            // Create a new SqlConnection using the retrieved connection string
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Define the SQL query to retrieve distinct values from the "Region" column in the "Customers" table
                string query = "SELECT DISTINCT Region FROM dbo.Customers";
                // Create a SqlDataAdapter to execute the query and retrieve data into a DataTable
                SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                DataTable dataTable = new DataTable();

                // Fill the DataTable with data from the database
                adapter.Fill(dataTable);
                // Now you can use the dataTable to populate a control like a DropDownList
                // For example:
                // DropDownList1.DataSource = dataTable;
                // DropDownList1.DataTextField = "Region";
                // DropDownList1.DataBind();
            }
        }
    }
}

Listing 3. Form1.aspx.

In listing 3, DropDownList WebControl binds all data of SqlDataSource1, which allows the customer to pick up a region. Notice that this control is able to do a postback action.

<asp:DropDownList ID="ddlRegions" runat="server" DataSourceID="SqlDataSource1" DataTextField="Region" AutoPostBack="True"></asp:DropDownList>

Listing 4. Form1.aspx.

When you select a Region, a postback action is triggered, and the second data source retrieves all the customers in that region using the ddlRegions.SelectedValue as a parameter for the query. Here's the definition for the second data source in listing 4.

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand="SELECT CustomerID, ContactName, Address, Country FROM dbo.Customers WHERE Region=@Region">
    <SelectParameters>
        <asp:ControlParameter ControlID="ddlRegions" Name="Region" PropertyName="SelectedValue" />
    </SelectParameters>
</asp:SqlDataSource>

Listing 5. And finally, in listing 5 is the code for the configuration of the GridView control, which binds the data from SqlDataSource2 in the Web Form for showing the group of customers associated with the selected region.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="SqlDataSource2">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" />
        <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" />
        <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
        <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
    </Columns>
</asp:GridView>

Conclusion

This is an explanatory article for showing how to use another easy model of data access developed by the Microsoft.NET architects.