Moving from ASP to ASP.NET is for the most part a good thing. ASP.NET, being part of Microsoft’s .NET framework, has a huge library of built-in functions and classes for almost any functionality you could need. For the most part, these classes and functions make the transition easier, since you are able to use some of the built-in classes or function without having to write your own. However, handling databases seems to be an exception to the rule.

That’s not to say that database usage is harder in ASP.NET; it’s just a little more complicated than in ASP. But once you learn the differences between the database functionality for ASP and ASP.NET, you’re set. Read on for more.

Note that this is not a lesson on how to program ASP.NET, or on how to use databases programmatically. The point is to illustrate the differences between the two ways of working with databases.

Classic ASP

Following is a code snippet showing how a database connection and query is done through Classic ASP.

Dim strConnect, strQuery, objConn, objRS

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Northwind.mdb"
strQuery = "QUERY GOES HERE"

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open strConnect
objRS.Open strQuery, objConn

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing

The way of creating and using connection and recordset objects is pretty simple in Classic ASP (using VBScript). It is worth noting that Classic ASP only has one object for storing query results: the recordset. It also only has one type of connection object as well. The first part of the connection string can be modified for different data sources: Access (Provider=Microsoft.Jet.OLEDB.4.0), SQL Server (Driver={SQL Server}), Data Source Sames (DSN=myDsn), etc. You just need to specify the provider, driver, or DSN for whichever database you are using. For some databases, this may involve installing a driver so ASP can interface with the database. Also, I believe that in Classic ASP, database connections go through the ADO database interface, using ODBC for the particular database.

As an aside, a great website for locating connection strings for different databases is Connection Strings.

ASP.NET 1.x

Moving from ASP to ASP.NET, things get somewhat more complicated for three reasons: there are several different connection objects, there are several different objects for holding data resulting from a query, and there are different interfaces for databases.

Making Connections

With regard to the connection objects, there is no universal connection object like there is in Classic ASP. In ASP.NET, the connection object you would use is determined by the type of database you are using, AND in some cases, also by the type of interface available for that database (ODBC or OleDB). There are a few types of connection objects built-in to the .NET framework: OdbcConnection, OleDbConnection, OracleConnection, and SqlConnection. The following list indicates which databases each connection object is for.

  • OdbcConnection: For any database not covered by the two specialized connections, or that doesn’t have an OleDb interface. ODBC is almost universal, but older and slower. Most databases have an ODBC interface, if you prefer to use that, even if there are other interfaces available. Introduced in .NET 1.1.
  • OleDbConnection: For any database not covered by the two specialized connections, but does have an OleDb interface. Access and versions of SQL Server older than 7.0 use this connection.
  • OracleConnection: Duh! For the Oracle database. Introduced in .NET 1.1.
  • SqlConnection: Used by SQL Server version 7.0 and newer. Optimized for SQL Server, of course. You can make use of the OleDbConnection instead, but may as well use the optimizied version if you can.

.NET 1.x does not have built-in support for MySQL, but there is a MySQL/.NET connector object available. You need to download and install it on your computer (or the one where your .NET application resides). There is also an ODBC version available, which is cross-platform, but apparently the .NET version is better if you are using MySQL with .NET (source; scroll down to step 9). It follows the naming conventions set by the other namespaces and connection objects, as shown below.

Imports System.Data.Odbc
Dim conn As OdbcConnection = New OdbcConnection(CONNECTION STRING)
...
Imports System.Data.OleDb
Dim conn As OleDbConnection = New OleDbConnection(CONNECTION STRING)
...
Imports System.Data.OracleClient
Dim conn As OracleConnection = New OracleConnection(CONNECTION STRING)
...
Imports System.Data.SqlClient
Dim conn As SqlConnection = New SqlConnection(CONNECTION STRING)
...
Imports MySQL.Data.MySQLClient
Dim conn As MySQLConnection = New MySQLConnection(CONNECTION STRING)

Running Queries

Okay, so you’ve figured out how to connect to a database. How do you run a query? There’s a few steps involved. First off, you need to create a Command object, corresponding to the database namespace you are using. You then need to create a coresponding DataAdapter (which adapts the results from the query for a data container), and do a few incantations over your current collection of objects to make it all work. The following example will show how to query the Northwind database included with Access.

Imports System.Data.OleDb

Dim strConn, strQuery As String

strConn = New String("C:Northwind.mdb")
strQuery = New String("SELECT ProductName, UnitPrice FROM Products")

Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Northwind.mdb")

conn.Open()

Dim cmd As OleDbCommand(strQuery, conn)
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()
adapter.SelectCommand = cmd

Dim dt As DataTable = New DataTable()
adapter.Fill(dt)

conn.Close()

So, looking through that sample, you can probably see how database usage is slightly more complicated in ASP.NET, what with those particular objects you have to use depending on which database you are using. Although, the nice thing about that is that object and function names, and parameter order, is consistent aross namespaces. I’m sure you also notice the number of intertwined steps involved…telling the command object to be for the current connection, and setting the select command property of the data adapter to be the command object, and telling the data adapter to fill the data container…I think this is one way I wish that ASP.NET was more like Classic ASP!

Storing Query Results

Now we get to data storage. Classic ASP had a single object for storing query results: the recordset. But ASP.NET has some different containers for storing query results. They are: Dataset, DataTable. They are generic in that they are not specific to any type of database, unlike the connection, command, and data adapter objects. So they can be used to store query results from any database. A DataTable contains a single table of related data, whereas a Dataset can contain multiple datatables.

Despite these changes, one thing has remained the same: you still need to create your connection manually. Sure you can store it in your web.config file, and reuse it across your application, but that’s not the point. It still has to be manually created, at least once. And depending on the database, and on the parameters involved in successfully connecting, the connection string can be a complicated beast.

ASP.NET 2.x

It seems that with the advent of ASP.NET 2.x, one issue has been at least partially solved: creating and maintaining connection strings. ADO.NET 2.0 has a nice addition to each of the built-in namespaces for database access (ODBC, OleDb, SqlClient, OracleClient): each namespace has a new object that can build your connection strings for you. You need only to create the appropriate object, and set a few properties representing connection-related information for your database.

There is some initial documentation on the MSDN website. There is also a webpage for the January 2006 issue of MSDN Magazine, which has a short blurb about this feature (which is actually where I found it, in the magazine that appeared in my mailbox today ;-) ).

If I may, I’ll just show an example from the MSDN Magazine webpage, which is using the SqlClient namespace:

SqlConnectionStringBuilder cnBldr = new SqlConnectionStringBuilder();
cnBldr.DataSource = "MyServer";
cnBldr.InitialCatalog = "Northwind";
cnBldr.IntegratedSecurity = true;
using (SqlConnection cn = new SqlConnection(cnBldr.ConnectionString))
{
    ...
}

Looks like that could be a useful object to use when connecting to databases. So all the built-in database namespaces have a matching connection builder. According to the online article, if a particular database vendor does not support these objects, you can write a custom class to inherit from and extend the base connection builder class. I haven’t used this feature yet, but it sounds quite useful.

Conclusion

Hopefully, by now I’ve achieved the purpose of this article: to illustrate the differences in database connections and queries between Classic ASP and the different versions of ASP.NET.

Connecting to and querying databases is a straightforward matter in Classic ASP: there is one connection object, and one query results object. The only real difference was in the string used to estalish a database connection: it can vary greatly depending on the type of database being used. This is also a somewhat difficult spot, as the connection string can be complicated to create and maintain when you take into account the different databases and parameters available.

With ASP.NET 1.x and 2.0, making a database connection, querying the database, and storing the results is noticeably different depending on the type of database being used. Each type of database has to use a specific connection object – there is no generic database connecter like in Classic ASP! Running queries is also more complicated, since, like with the connection objects, each type of database has to use a specific data adapter and command object! When it comes to storing query results, there are some options for containing the data – there is no generic recordset object here either!

When ASP.NET 2.0 was recently released, it brought some new and useful features to the database connection scene: for each type of database connection, there is now a ConnectionBuilder object that can build your connection string for you. So you no longer have to decode a cryptic connection string; just create a connection builder object, give it some information about your database, and away you go.