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 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. (listing 4.)
The .NET Framework includes the following parameters:
- Control property: A map to property value from another control on the page.
- Query string: A map to a value from the current query string.
- Session state: A map to 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 to DataSource controls.
- Data access logic is embedded in the page, so it's difficult to change issues for tuning or profiling after the application deploying.
- Some code is duplicate because you have several DataSource control per page sometimes they have the same Sql statement.
- Lack of flexibility because every Sql statement has separate DataSource, so if you want to provide different views for the data is very complicated.
I develop a little program for explanatory purpose.
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>
...
</configuration>
Listing 1. Web.config
Listing 2 is the code for the configuration of SqlDataSource1 object which is querying the database Northwind for gathering all Region which maps to current customers.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="select distinct Region from dbo.Customers"></asp:SqlDataSource>
Listing 2. 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 3. 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 parameter for the query. Here's the definition for the second data source in the 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 4.
And finally in the 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 customer associated to 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>
Listing 5.
Conclusion
This is an explanatory article for showing how to use another easy model of data access developed by the Microsoft.NET architects.