Data Sources and Controls

In our last month's article on ASP.Net 2.0 (ASP.Net 2.0 Data Connectivity, page 100), we briefly discussed the different sources of data that ASP.Net can use and some of the new controls that can use these sources. This article will delve into these in detail. 

You will recall from our previous article that data source controls are those controls that define the source of the data, whether it is a database, XML file or business object. We do not define the way the page connects to the data source at all. That is, we do not define the Connection, Command, Adapter and other such objects. In fact, in most cases, we do not even define whether we require to use a dataset, datareader or other control to hold the data before rendering.

Let's take a look at how to define a data source that uses an SQL server database.

SelectCommand="SELECT * from Authors"

ConnectionString="...">

As you can see, the SQLDataSource control simply defines its name as SQLDataSource1, a select command (and can define the others-update, insert and delete-as well) and the connection string to connect to the database. An important thing to remember is that the SQLDataSource is a generic control that can connect to any kind of RDBMS, including Oracle and MySQL, provided the connection string is correct. 

To display the results of the query in a table, we can add a GridView control on to the page, which can then directly use the data source control already present. For this, the following lines of code do the trick.

The GridView control directly uses SQLDataSource1 by the DataSourceID attribute. We also define the Key column using the DataKeyName and whether to AutoGenerateColumns or not. By running this page, you will see the contents of the authors table displayed, without having had to write any real code. You can, of course, configure the GridView to display data as designed by you quite easily.

Another very nice feature of data sources is the ability to create custom, parameterized queries based on external values. For instance, if in the above example you wished to filter the authors based on the states
they reside in, you would add a new drop-down control and bind it to another SQL Data source that retrieves the states. Now, what we wish is that when a user selects a state in the drop down, the table displays only those users who reside there. Normally, we'd need to write code to create SQL statements dynamically based on the input. However, with the data source control it becomes very easy. All we need to do is make the query parameterized and point the value of the parameter to the corresponding control. In this instance, the SQLDataSource1 will change to look like this.

SelectCommand="SELECT * FROM WHERE ( = @state)"

ConnectionString="...">




PropertyName="SelectedValue">

There are two major changes to note here. The first is the SQL statement itself. It now adds a new condition (WHERE clause) that includes a parameter called '@state'. Next, it defines a new section called SelectParameters. This is where the data source points to the other control from which the parameter's value should be picked up, by defining it as a ControlParameter. You can also pick up parameters from Querystrings, cookies, session variables and others. The control parameter tag defines the name (state), the datatype (string), the actual control to use (dropdownlist1) and the property of the control that actually holds the value (SelectedValue). Once this is done, the filtering works as required. Again, you do not have to write any code for doing this.

You can create complex queries by using parameters for Datasources

Data sources are not limited to databases. As we mentioned earlier, you can use XML and other sources. One of these sources can also be a business object. If you have architected your application well, you will want to store your business logic in a business layer, rather than in the page itself. For this, you would need to create a business class and have the Page controls, such as the GridView or DropDownLists, use this object instead of directly using SQL sources. Doing this is simple. All you need to do is write a class with methods that return you the data. For instance, if you wanted the states in the drop down to get populated from a business object, you'd first need to write the object itself. The following snippet does this.

Public Class AuthorsDB

Public Function GetStates() As DataSet

Dim sConn As String = "..."

Dim oConn As IDbConnection = New SqlClient.SqlConnection(sConn)

Dim sSQL As String = "select distinct state from authors"

Dim oCmd As IDbCommand = New SqlClient.SqlCommand(sSQL, oConn)

Dim oDA As IDbDataAdapter = New SqlClient.SqlDataAdapter(oCmd)

Dim ds As New DataSet

oDA.Fill(ds)

Return ds

End Function

End Class

This is a class called AuthorsDB with a method called GetStates that returns a dataset with the states filled in. To use this class, simply create a folder called 'Code' in your Web application directory and dump the .VB file containing the class into it. 

Now, when you create a data source for the drop-down control, you will need to do it as follows.

TypeName="AuthorsDB"

SelectMethod="GetStates">

This defines a data source control that refers to a business object referred to as AuthorsDB. It gets the values as required from the method called GetStates. In the dropdownlist, simply set the DataSourceID to the ID of this control and it will work seamlessly. 

As you can see, the combination of the data source controls and the new data aware page controls make a powerful combination for rendering data-driven pages. Also remember that if you are using Visual Studio.Net 2005 or the new Visual Web Developer Express (both at Beta 1 as of this writing), all of the above can be achieved by simply clicking, dragging, dropping and following wizards. You don't have to write even a single line of code! 

We'll come back next month with some more cool stuff that you can do with ASP.Net 2.0.

Vinod Unny

Enterprise InfoTech