DataSource controls in .NET 2.0

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.