In this article we will briefly review the ASP.NET model of data access and data binding, and then move on to look at the new ASP.NET 2.0 way of doing things. On the way we will look at the new GridView control, alongside the new declarative data binding model using a DataSource as the means of accessing the data. We will examine the various DataSources available, in particular the ObjectDataSource.
ASP.NET 1.0 data binding
We will look at a couple of examples of data binding in ASP.NET 1.0, in both cases using the DataGrid, with one example using a DataReader and the other using a DataSet. We normally perform the databinding during the loading of the page. If we have a page that looks like this:<%@ Page language=”c#” Codebehind=”WebForm1.aspx.cs” AutoEventWireup=”false” Inherits=”VSJASP.WebForm1” %> <!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN” > <HTML> <HEAD> <title>WebForm1</title> </HEAD> <body> <form id=”Form1” method=”post” runat=”server”> <asp:DataGrid id=”_authors” runat=”server” /> </form> </body> </HTML>This gives us a DataGrid called _authors that we can then bind to. To bind to this grid you have to provide a data source of some description. This data source could be an IDataReader, a DateSet or something that implements IEnumerable, so an Array or an ArrayList for example. Let’s see how to do this using a DataSource (this is not necessarily the best approach, but it serves as an example).
To bind to the DataSource we provide a PageLoad method that would look something like:
private void Page_Load(object sender,
System.EventArgs e)
{
if(!IsPostBack)
{
DataSet ds = new DataSet();
SqlDataAdapter sqlda = new
SqlDataAdapter(
“select * from authors”,
“server=.;database=pubs;
user=sa;pwd=”);
sqlda.Fill(ds);
_authors.DataSource = ds;
DataBind();
}
}
If you run this code you will get something like the web grid shown in Figure 1.
Figure 1
Notice that there is no paging or sorting involved here. However, we will talk about sorting and paging for the ASP2 example we’ll see shortly.
There is one other thing I need to point out before we move on to the ASP2 example, and that is the use of the hard-coded data source name in the Page_Load code (“server=.;database=pubs;user=sa;pwd=”). Doing this is very bad practice. Better is to store this value in the configuration file and then load the string when needed.
To do this you add the following to web.config:
<configuration> <appSettings> <add key=”dsn” value=”server=.; database=pubs; user=sa; pwd=” /> </appSettings> ... </configuration>Then in the code you change the SqlDataAdapter creation code to the following:
SqlDataAdapter sqlda = new SqlDataAdapter( “select * from authors”, (string) ConfigurationSettings. AppSettings[“dsn”]);Now the DSN becomes a configuration setting and is much easier to change when necessary.
ASP2 declarative data binding
The approach to data binding in ASP2 is very different. Part of the aim of ASP2 is to reduce the amount of code that has to be written. To that end, ASP2 introduces the idea declarative data binding. The declarative data source is specified on the page by adding an ASP control to the page. The data source would look something like:<asp:SqlDataSource runat=”server” ID=”_sqlds” ConnectionString=”server=.; database=pubs;user=sa;pwd=” SelectCommand=”select * from authors” />Once you have this, then the data source can be used by a data bound control as we will see in a moment.
Before moving on I want to examine one more thing. This data source is again using a hard coded connection string, a better approach would be to put the connection string in the configuration file and to refer to it in the control. ASP2 has introduced a new mechanism for doing this. The web.config file would now look like this:
<configuration xmlns=”http://schemas.microsoft .com/.NetConfiguration/v2.0”> <connectionStrings> <add connectionString= ”server=.;database=pubs; user=sa;pwd=” name=”authors”/> </connectionStrings> ... <configuration/>Notice that there is a specific section for connection strings in the configuration file, along with the new section there is a new syntax for accessing these strings. This means that a developer can now declare the data source to use by using an ASP control and then use this, like:
<asp:SqlDataSource runat=”server” ID=”_sqlds” ConnectionString=”<%$ ConnectionStrings:authors %>” SelectCommand=”select * from authors” />The <%$ %> is called an expression and various expression builders are installed by default in ASP2, these are listed in the global web.config:
<expressionBuilders> <add expressionPrefix=”Resources” type=”System.Web.Compilation. ResourceExpressionBuilder” /> <add expressionPrefix= ”ConnectionStrings” type=”System.Web.Compilation. ConnectionStringsExpressionBuilder”/> <add expressionPrefix=”AppSettings” type=”System.Web.Compilation. AppSettingsExpressionBuilder” /> </expressionBuilders>The one we are using is a ConnectionStringExpressionBuilder, you will notice that AppSettings and Resources also have expression builders and can be used with similar syntax within the page.
The GridView
In ASP2 just as in ASP, many controls can be data bound. We are going to concentrate on just one here, the GridView control, this is the replacement for the DataGrid and is likely to be the most widely used data bound control.You would use the GridView something like this:
<form id=”form1” runat=”server”> <div> <asp:SqlDataSource runat=”server” ID=”_sqlds” ConnectionString=”<%$ ConnectionStrings:authors %>” SelectCommand= ”select * from authors” /> <asp:GridView runat=”server” ID=”_authors” DataSourceID=”_sqlds” /> </div> </form>And if you execute this page you will see something like the grid shown in Figure 2.
Figure 2
Notice one immediate difference, the GridView is more intelligent than the DataGrid in that it displays the last column, which is a Boolean, as a checkbox.
The next thing we can do with the GridView is to enable both paging and sorting, like this:
<asp:GridView runat=”server” ID=”_authors” DataSourceID=”_sqlds” AllowSorting=”True” AllowPaging=”True” />If you re-execute the page you will now see this (see Figure 3).
Figure 3
Now without any more code you will be able to click on the column headings and sort the grid, or on the page numbers to move through the pages. Notice that the page size is set by default to 10 but you can set it to any value you want to by using the PageSize attribute of the GridView control.
As you can see, you can get paging and sorting for “free” from a GridView. However, this is only true under certain circumstances, and of course remember that nothing comes exactly for free.
As was mentioned above for the SqlDataSource, you only get paging and sorting under specific circumstances. If you look at the properties of the SqlDataSource you will see a DataSourceMode property. This defaults to “DataSet”. If you use DataSet as the mode then you get paging and sorting without doing any further work.
You can also set the mode to “DataReader” and this is where the limitations come in. If you use DataReader as the mode you cannot use default paging and if you want to have sorting then you SelectCommand must refer to a stored procedure and that stored procedure must be parameterized to accept a sort expression.
When you use the automatic paging and sorting mechanism you will find that each time you get a new page, and each time the grid is sorted, a query is made against the database, which of course is very inefficient. If you only use the SqlDataSource you only get this default paging model, if you want custom paging you have to change the data source to the ObjectDataSource, and we will look at this now.
The object data source
The object data source provides a mechanism whereby you can bind a .NET Object to a data aware control. The MSDN help says this:“ObjectDataSource Class (System.Web.UI.WebControls) represents a business object that provides data to data-bound controls in multi-tier web application architectures”
…and then goes on to say:
“… represents a data-aware middle-tier object or a data-interface object to data-bound controls.”
While an ObjectDataSource can bind business objects to a control, I would strongly recommend against doing this. Your business objects should be domain independent, which means they should have no knowledge of how or where they will be used and in particular should have no parameters, methods or return types that come from a specific usage. If you keep your business objects as ‘neutral’ as possible they can be re-used and they can be tested far more easily.
Anyway, enough of the rant. To use an ObjectDataSource you must first define a .NET class to act as the data source. As we are using the Authors table and we will define an Author class in our language of choice which in this case is C#. If we were doing this in ASP1 we would have to write a separate assembly and have that assembly copied to the web application’s ‘bin’ directory. ASP2 offers an alternative; we can place the code in a directory called App_Code. This directory is a sub-directory of your web application, any code placed there is automatically compiled into the application. ASP2 defines a number of these directories, such as App_LocalResources and App_GlobalResources, to help make your development life easier. While for this example I will put the code into App_Code, you can still use a module if necessary and using a module is something you probably want to do as you can then use your ObjectDataSource in other applications.
The first steps are easy – define a new class and the properties you want to expose from that class. Those properties will match the columns you will retrieve from the database. Note that you don’t have to just retrieve data from a single database as we are doing here, but can execute any SQL query you need to.
Our base Author class looks like this
public class Author
{
String _au_id;
public String Au_id
{
get { return _au_id; }
set { _au_id = value; }
}
String _au_fname;
public String Au_fname
{
get { return _au_fname; }
set { _au_fname = value; }
}
// other properties elided
To use this class as an ObjectDataSource you need to provide a method that can be called by the ObjectDataSource control. You can call this method anything you like but a name related to the operation seems to be the best choice. The method must also have a return type that the data bound control can use, i.e. something that implements IEnumerable – a List seems to be the best choice here. We provide a method that looks like this:
public List<Author> GetAuthors()
{
List<Author> authors =
new List<Author>();
using (SqlConnection conn = new
SqlConnection(ConfigurationManager.
ConnectionStrings[“authors”].
ConnectionString))
{
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText = “select
au_id, au_lname,
au_fname, phone, address,
city, state, zip,
contract from authors”;
conn.Open();
SqlDataReader rdr =
cmd.ExecuteReader();
while (rdr.Read())
{
Author a = new Author();
a.Au_id =
(string)rdr[“au_id”];
a.Au_fname =
(string)rdr[“au_fname”];
a.Au_lname =
(string)rdr[“au_lname”];
a.Phone =
(string)rdr[“phone”];
a.Address =
(string)rdr[“address”];
a.City =
(string)rdr[“city”];
a.State =
(string)rdr[“state”];
a.Zip =
(string)rdr[“zip”];
a.Contract =
(bool)rdr[“contract”];
authors.Add(a);
}
}
}
return authors;
}
Here we get the ConnectionString from the configuration file; execute the SQL select; create an Author for each row returned; and add that author to a List. Notice that we use the generic List class List<Author>. This gives us a type safe collection of authors that we then return from the method. The method used here is an instance method but you could also use a static method and may want to do so for efficiency.
To use this class on your page you define an ObjectDataSource rather than a SqlDataSource. The ObjectDataSource control takes the type of the object to use as the source and the name of the method that will perform the select. The page now looks like this:
<asp:ObjectDataSource ID=”_ods” runat=”server” TypeName=”Author” SelectMethod=”GetAuthors” /> <asp:GridView runat=”server” ID=”GridView1” DataSourceID=”_ods” />If you browse to the page now you will see the full set of data displayed on the GridView. What happens if we turn on paging and sorting?
<asp:GridView runat=”server” ID=”GridView1” DataSourceID=”_ods” AllowSorting=”True” AllowPaging=”True” />Now we get default paging but sorting doesn’t work (how could it?). The default paging means that each time we select a new page the full select is done, then the GridView skips over the first (page-size*page) rows and display only a page-size number of rows. For a large number of rows this gets to be very inefficient very quickly.
To perform custom paging we need to add another method to our Author class. This method will have the same name as the select method but will take two parameters, the maximum number of rows to return and the index of the first row to be returned. The custom paging method will therefore look like this:
public List<Author> GetAuthors(
int maximumRows, int
startRowIndex){}
…which looks something like:
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText = “select TOP “ +
(maximumRows + startRowIndex )+
“ au_id, au_lname, au_fname, phone,
address, city, state, “ +
“ zip, contract from authors”;
conn.Open();
SqlDataReader rdr =
cmd.ExecuteReader();
int count = 0;
while (rdr.Read())
{
if (count++ < startRowIndex)
continue;
Author a = new Author();
a.Au_id = (string)rdr[“au_id”];
a.Au_fname =
(string)rdr[“au_lname”];
a.Au_lname =
(string)rdr[“au_fname”];
a.Phone = (string)rdr[“phone”];
a.Address =
(string)rdr[“address”];
a.City = (string)rdr[“city”];
a.State = (string)rdr[“state”];
a.Zip = (string)rdr[“zip”];
a.Contract =
(bool)rdr[“contract”];
authors.Add(a);
}
}
This reads the minimum number of rows it can from the database, skips the TOP rows that have already been seen and returns the rest. This is a simplified approach to returning the data. A better approach would be to use a stored procedure to fetch only the rows that are needed from the database.
When you enable sorting the ObjectDataSource control it uses reflection to discover if this method exists, and if so calls it.
As well as providing this method you also have to do a couple of more things. You need a method that tells the ObjectDataSource control how many records make up the complete set of data you want to page through. The method would look like this:
public int GetTotalNumberOfAuthors()
{
using (SqlConnection conn =
new SqlConnection(
ConfigurationManager.ConnectionStrings
[“authors”].ConnectionString))
{
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText =
“select count(*) from authors”;
conn.Open();
return =
(int)cmd.ExecuteScalar();
}
}
}
We need to tell the ObjectDataSource control about this method, and we also need to tell the control to use custom paging. The control would now look like:
<asp:ObjectDataSource ID=”_ods” runat=”server” TypeName=”Author” EnablePaging=”true” SelectMethod=”GetAuthors” SelectCountMethod= ”GetTotalNumberOfAuthors”/>So what about sorting? To enable sorting we need to change a couple of things in the code. On the page we first need to enable sorting on the GridView. We do this by setting the AllowSorting attribute to true. As this is an ObjectDataSource it means that we are able to provide the code to manage the sort. To do the sort the SQL will look something like:
select au_id, au_fname from authors order by ‘sort-expression’Where ‘sort-expression’ is the clause used to sort the data. How do we get this sort expression? Typically for a sortable grid the end user selects the column to sort on by clicking on one of the column headers. A value associated with this header is then passed to the server and this value is used as the sort expression. If we use the default behaviour of the grid this value will be the property name of the property associated with the column. We can change this if we were to use data bound columns. This value has to be passed to the method that executes the SQL query, and it can then be used in the order by clause.
To enable this behaviour we need to do two further things, firstly we need to change the GetAuthors method so that it accepts an extra parameter, the “sort expression”:
public List<Author> GetAuthors(int maximumRows, int startRowIndex, string sortExpression)We also have to tell the ObjectDataSource what the name of this extra parameter is, this enables sorting on the datasource and allows the datasource to find this method through reflection:
<asp:ObjectDataSource ID=”_ods” runat=”server” TypeName=”Author” EnablePaging=”true” SelectMethod=”GetAuthors” SelectCountMethod= ”GetTotalNumberOfAuthors” SortParameterName=”sortExpression”/>Notice that the SortParameterName value matches the name of the last parameter to the GetAuthors method.
We now have to make use of the sort expression in the GetAuthors method. This change is relatively straightforward:
using (SqlCommand cmd =
conn.CreateCommand())
{
cmd.CommandText = “select TOP “ +
(maximumRows + startRowIndex) +
“ au_id, au_lname, au_fname,
phone, address, city, state, zip, “
+ “contract from authors”;
if (sortExpression != string.Empty)
cmd.CommandText += “ ORDER BY “
+ sortExpression;
Once this is in place you can rebuild the application and browse to the page and you should be able to select the column to sort on and see sorting and paging working together.
The ObjectDataSource also allows for updating and deleting of the data. This is slightly more complicated than simply displaying the data, but not drastically so. The mechanisms are not covered in this article due to space constraints but the example code that accompanies this article does have the update and delete mechanisms in place.
Conclusions
There are numerous new data controls in ASP.NET 2.0, the most versatile of which is the GridView. ASP2 also offers new ways of getting data into these controls by providing various data source controls. By far the best and most useful of these controls is the ObjectDataSource control. The ObjectDataSource allows developers to provide a data access layer, so isolating the user interface of the application from the underlying database and from the underlying business logic, something which could be achieved in ASP.Net but only with more work than is needed here. Using the GridView and the ObjectDataSource developers have excellent control of managing the display and update of the data, for example paging and sorting are relatively straightforward as is updating and deleting the data.Kevin Jones is an instructor and course author who teaches both Java and .NET courses for DevelopMentor, and is the co-author of Servlets and JavaServer Pages: The J2EE Technology Web Tier, published by Addison-Wesley. He is a regular speaker at industry events, and will be presenting sessions at Bearpark Publishing’s DevWeek conference in February.
Comments