Databases in ASP.Net

Last month we saw how easy it is to create your own set of Web Controls. But what about one of the most important things that most ASP programmers use ASP for: to connect to a database to insert, update, retrieve and display records?

ASP.Net can connect to many different databases, as long as they are OLEDB compliant. SQL Server users get an additional benefit: a number of functions that enhance the performance of your ASP.Net pages when used with SQL Server instead of any other database. You can call it Microsoft’s ‘incentive’ for you to use their own database server over others.

Connecting to a database

Veteran ASP programmers are familiar with ADO (ActiveX Data Objects). In the .Net world, ADO has been naturally renamed to ADO.Net. However, the changes are not just skin deep. ADO.Net has many enhancements to it, particularly in database performance.

Remember the Recordset object that we all love in classic ASP? It is now officially declared dead! Instead, there are two new and more powerful objects: DataSet and DataReader.

The Recordset object was in principle a cursor that could be used either on the client (by this I mean ASP, not the browser) or at the server (this is the Database server). However, bad programmers had a tendency to open updatable cursors on the server that could be very expensive on resources. DataSet, on the other hand, is actually implemented as a relational database in-memory. Each DataSet has a number of other objects within it: DataTables, DataViews, DataRelaions. DataTables further has DataRows containing DataColumns. You can easily fill any of these with data from a database and define relations to enforce integrity in memory itself.

A DataAdapter object is what is used to connect a DataSet to an actual data source. You can fill the DataSet, manipulate it by adding, updating or deleting data, and when you are ready, pass this information back to the DataAdapter to store the results of your manipulations back into the database or to any other data source.

The DataSet is useful in areas where you may wish to save on database accesses. For example, you have a database that contains the names of all the cities in India. You can populate a DataSet with this data and store it into a session scope variable and bind it to all the drop-down lists that require this data in your Web application, there would be only one database call made to retrieve it. Every time a drop-down list needs to be populated, the DataSet is used form memory and the list is built. DataSets are extremely good for retrieving mostly static data for use.

The DataReader, on the other hand, is very much like the good old Recordset of ASP. This provides an extremely fast, forward only, non updateable stream of data that is to be used and discarded. Typically, this would be used for data that is changing often and you’d need database access each time you receive the list. Examples could be of stock quotes or a subscribers list that you wish to view and are updated every few seconds if not minutes. The DataReader is so fast in retrieving records from a database that the DataAdapter object actually uses a DataReader to fill a DataSet. As you can see, both have their uses.

Using managed providers

ASP.Net provides two namespaces that you can use to connect to any OLEDB database and to SQL Server, respectively, as mentioned earlier. The System.Data.OleDb namespace naturally connects to OLEDB databases. The System.Data.SQLClient namespace helps in connecting to SQL Server databases. You can, of course, still connect to SQL Server using the OLEDB method, but it is highly recommended that you do not, as the SQLClient namespace offers much better performance.

Let’s see how a connection is actually made. For comparison’s sake, let me start with connecting to an SQL Server using classic ASP.

sConn= “Provider=SQLOLEDB; Data Source = localhost;

database=Products; uid=sa; pwd=rootpass;”

set oConn = Server.CreateObject(“ADODB.Connection”)

oConn.Open (sConn)

In ASP.Net, you’d do it this way:

Imports System.Data.SQLClient

Dim oConn as SQLConnection

Dim sConn as String

sConn = “Data Source=localhost; Initial_Catalog=Products; User ID=sa;

Password=rootpass”

oConn = new SQLConnection(sConn)

This, of course, creates the connection but does not do anything with it yet. We shall take a look at what you can do a little later.

Here’s a tip for those who hate writing connection strings. Create a blank text file with any name but with an extension of .UDL as in TEST.UDL. Now double-click on this file and complete the wizard that starts up. Open the file in Notepad and copy the connection string you find there to your script.

Databound controls

In classic ASP, displaying data used to be done using a mixture of HTML and ASP with do loops providing the ability to move from one record to another. ASP.Net comes with a large set of extremely powerful and rich databound controls that can work with data with minimum fuss as well as keep your code clean.

For demonstration purposes, let’s first create a simple Access database for my samples to connect to. Give it the parameters in the table.

Once you have created the above database and populated the table with some sample data, you create an ASPX page to connect to it. The file PCQ5-1.aspx on the CD is one that uses a control called the Repeater Control. This is a very basic databound control that simply shows the data in a table.

As you can see, we are importing the System.Data and the System.Data.OleDb namespaces as we are going to be using them in this page. Next, we create a small subroutine called BindListControl that connects to the database, and runs an SQL statement to retrieve all the products in it. The Page_Load method checks whether the page is being shown originally or after a form postback. In case it is originally shown, it calls the above defined BindListControl. The subroutine also has two lines that bind the data retrieved to a Repeater control on the page.

The Repeater control uses templates to distinguish the different parts of the list that it can display. These templates are:

n HeaderTemplate: Sets the header details for the list

n ItemTemplate: Sets the template for the actual item data to be displayed

n AlternatingItemTemplate: Sets the template for each alternating item. This can really benefit lists in which you wish to distinguish between alternate items

n FooterTemplate: Sets a foot text or item

Of these, only the ItemTemplate is required; the others all being optional. Within the ItemTemplate, you can create tables or paragraphs or any other way you wish to format it. The syntax <%# %> lets you specify a field from the bound dataset to use here. So the line <%# Container.DataItem(“sProductName”) %> would insert the value of the SProductName field of the current record in that place. See the screenshot to see what the Repeater looks like for the above code.

There are many other databound controls. However, one of the most powerful ones among them is the DataGrid Control.

You can do a lot of things with the DataGrid control, such as using the same grid for display, edit, update, delete and even automatic paging of records. We’ll discuss DataGrid in our next month’s article.

Vinod Unny