Moving from ADO to ADO.NET

asp:cover story

LANGUAGES: VB

TECHNOLOGIES: ADO | ADO.NET | SQL Server

Moving from ADO to ADO.NET

Accessing Data the .NET Way

By Craig Utley

One of the greatest changes introduced by .NET is the new database-access technology known as ADO.NET. It s the next step in the evolution of Microsoft s data-access technology, but it is as great, or greater, a leap as was the leap from ODBC to OLE DB.

ADO.NET introduces a different way of thinking about working with results from a database. You easily can bring back records and run through them in a forward-only fashion, much as you could with ADO. However, when it comes to caching data on the client, ADO.NET gives you a completely new way of dealing with those functions, through the object known as the DataSet.  

Given the major architecture change from ADO to ADO.NET, you might be wondering if you should change all your ADO code to ADO.NET immediately. The decision really depends upon whether you will be updating your ASP files to ASP.NET. If you are taking the time to update your ASP applications, you ll want to update the ADO code to ADO.NET. ADO will run fine inside ASP.NET, but it uses the interoperability layer between .NET and COM, which means the ADO code will have significant overhead if you leave it in your ASP.NET files.

The move to ADO.NET is not trivial. It s not like the change from DAO to Remote Data Objects (RDO) years ago, which got rid of the fat JET layer and allowed you to code directly against the ODBC library. Although many ADO.NET functions should be faster than the equivalent ADO code, the learning curve and difference in design may negate any speed increases initially because of the time it takes to rewrite the application. Still, once you re past the learning curve, you will be much better off writing ADO.NET code in ASP.NET pages.

ADO.NET is Microsoft s new set of objects for accessing data. In the past, you had an underlying technology, such as ODBC or OLE DB. You also had a series of objects sitting on top in the form of DAO and RDO for ODBC, and ADO for OLE DB. Things are slightly different with ADO.NET. It can use OLE DB, but the preferred method is to use native drivers written specifically for the particular back-end database. .NET currently contains a native SQL Server provider and one for OLE DB. There are rumors .NET will have ODBC and Oracle providers in the near future. Unlike drivers in the days before .NET, the providers here come in the form of objects you can manipulate. For example, .NET includes a SqlConnection object and an OleDbConnection object. It is conceivable that someday there could be a Db2Connection, an InformixConnection object, and so on. Although ADO.NET typically has better speed when it uses a native provider, ADO.NET can use OLE DB, as well.

Finally, ADO.NET is very XML-centric. ADO.NET can read XML Schemas and create the equivalent of a relational database schema in memory based on the XML Schema. ADO.NET also can read in data in XML format directly and use that data to populate one or more in-memory tables.

Why Is ADO.NET So Special?

Microsoft has had ADO working for quite a few years, so why the move to ADO.NET? There are several reasons, including the following:

1)        ADO.NET is not a separate technology, like OLE DB or ADO. Instead, it is part of the .NET Framework.

2)        Microsoft officials saw that many applications increasingly were written as distributed, n-tier applications. This meant data often was being passed across process boundaries, and ADO did not always lend itself to efficient data passing. This is one major area in which XML comes to the rescue.

3)        The increase in Web applications made scalability more important than ever. In order to handle hundreds or even thousands of simultaneous users, you need the most efficient database access possible.

Because ADO.NET is part of the .NET Framework, the same objects are available to any language that targets the .NET Framework. In addition, if the .NET Framework is ported to other operating systems (a process that s under way today), ADO.NET may be available on those other platforms.

ADO.NET is very good about passing data and is designed to work directly with XML. First, ADO.NET can serialize data into XML format, and this makes it very easy to pass data across process boundaries. In fact, these processes could be on separate servers, separated by a proxy server or firewall. XML is text, so it readily passes via HTTP. Contrast this with ADO, which stores data in a binary format and must be passed with a mechanism like COM or DCOM. The difference between a binary transport mechanism, which usually does not flow through a firewall, and an HTTP transport is critical when it comes to building distributed applications scattered about on the Web.

One of the big advantages of ADO.NET is that it simplifies many tasks that caused developers trouble. With ADO, you could open a recordset by using the Execute method of the ADO Connection object, the Execute method of the ADO Command object, or the Open method of the ADO Recordset object. Only the Open method allowed you to specify the cursor type and lock types. So, although it was used less frequently, it was often a preferable method. ADO.NET removes such areas of confusion by limiting developers so they re only able to perform most actions one way. This forces developers to perform those actions the most efficient way.

Finally, Microsoft officials wanted to make database access as scalable as possible. This means they wanted to keep as few connections to the server open as possible while providing the fastest possible access to the data. In ADO, you typically gained speed by keeping the connection open, which allowed the fastest access to the data. In ADO.NET, however, you take a copy of the data and store it in memory, and then you close the connection to the actual data source. This means you can serve the data quickly as it is requested, but you minimize the connections to the database. This leads to a massive increase in the potential scalability of your applications.

The Next Generation of Database Access

ADO was built around a connected model, which meant you maintained the connection to the database while you were working with the data. Much has been written about ADO.NET s disconnected paradigm, and there are several issues to consider when talking about ADO and ADO.NET.

First, ADO supports a disconnected model. In ADO, it is possible to save an ADO recordset and store it locally. You have to choose a static cursor type, which, in effect, makes a snapshot of the data. Then, you can close the connection object but continue working with the recordset in memory.

Despite what you might have read, ADO.NET actually has both a connected and a disconnected model, as well. Microsoft officials believe most applications will use the disconnected model, but there are many reasons to use the connected model, especially in Web applications.

Take a look at your application and ask yourself several questions. First, will you need to perform data updates? Most applications need to, but not all do. Second, if you need to make data updates, are concurrency conflicts an issue? In many Web applications, concurrency issues may be minimal or nonexistent. For example, an online shopping cart is only for a particular user, so concurrency issues are typically not a problem.

If you are not going to perform data updates, or if concurrency issues are not a major problem, you ll want to focus on ADO.NET s connected model, which uses the datareader. If concurrency is a major issue, you ll have to focus on ADO.NET s disconnected model, centered around the dataset. Most Web applications will be able to focus on the datareader.

Upgrading Your Code

If you happen to have a Visual Basic 6 project with some ADO code in it, and you use the Visual Basic .NET Upgrade Wizard, the ADO code is not upgraded to ADO.NET. This is because the model is fundamentally different. Therefore, upgrading ADO to ADO.NET always will be a manual process. Like when using ADO, you ll start by making a connection.

The ADO.NET connection objects depend on what you are trying to access. There are actually two objects: SqlConnection and OleDbConnection. SqlConnection is the object to instantiate in order to use the native SQL Server driver. If your back-end database is SQL Server 7.0 or 2000, you ll want to use the SqlConnection object because it will give you better performance than the OleDbConnection object. OleDbConnection uses OLE DB as its underlying access mechanism and so is useful for any data source other than SQL Server 7.0 or 2000. You can use the OleDbConnection with SQL Server still, but you will have to go through the OLE DB layer, which will slow you down.

There are a couple of caveats when using ADO.NET connection objects. First, they are not closed automatically when they fall out of scope. You ve always heard it s a good idea to close any open resource explicitly. Now, it s a necessity. Second, unlike ADO, there is no Execute or similar method on the connection object. You ll have to use some other object to execute your SQL commands.

The SqlConnection object has a ConnectionString property that is very similar to the one in ADO. You can specify the server name (using the data source, server, address, addr, or network-address keywords), the user ID, the password (using either the password or pwd keyword), and many other options. One thing you do not specify is a driver or provider because the SqlConnection object works with SQL Server only. Assuming you have an Imports System.Data statement (or the equivalent Using statement or Import directive) at the top of your code, the code to make the connection should look similar to that shown here:

Dim sConn As String = "Initial Catalog=Pubs;" & _

 "Data Source=localhost;User ID=sa;Password="

Dim dbConnection As New SqlClient.SqlConnection(sConn)

dbConnection.Open()

In this code, you instantiate a SqlConnection object and pass the connection string into the constructor. This connection string connects to the Pubs database on the local machine, with a user ID of sa and no password. After the connection object is created, you call the Open method in order to open the connection to the database. The connection object can be closed, and it can be used to manage transactions, but it cannot be used to execute statements, as you could with ADO.

Using the Command Object

There are two command objects: SqlCommand and OleDbCommand. Like ADO, the command object is used to issue SQL commands or call stored procedures. Unlike ADO, however, there are multiple execute methods available. The four execute methods of the command objects are:

1)        ExecuteReader This method returns records from the database into a datareader in a forward-only method, much like the forward-only cursor in ADO.

2)        ExecuteNonQuery This method executes statements that do not return records. For example, you would use this method for inserts, updates, deletes, and most Data Definition Language statements, such as CREATE TABLE, CREATE INDEX, and so forth.

3)        ExecuteScalar This method is used to return a single value, like the count when you issue a SQL statement such as SELECT COUNT(*) FROM AUTHORS.

4)        ExecuteXmlReader This method returns data into an XmlReader object. The SQL statement will have used the For XML clause found in SQL Server.

One advantage of having four different execute methods is that you have a harder time being inefficient. In many ADO applications, people would perform a SQL INSERT or UPDATE statement with the command or connection s Execute method. However, they would fail to specify adExecuteNoRecords, which meant a Recordset object was created even though no records were returned. To perform an INSERT or UPDATE, use the ExecuteNonQuery, which prevents you from creating an object in order to store records.

To set up the command for the command object, you can either pass the command text into the constructor of the command object or set the CommandText property to the command text. For example, the code here uses the constructor to set the command text:

Dim sConn As String = "Initial Catalog=Pubs;" & _

 Data Source=localhost;User ID=sa;Password="

Dim sSQL As String = "Select * from Authors"

Dim dbConnection As New SqlClient.SqlConnection(sConn)

Dim authorCommand As New _

 SqlClient.SqlCommand(sSQL, dbConnection)

In this code snippet, the connection object is created the same as before. But now, a SQL statement is also created. The SqlCommand object is instantiated and passed both the SQL statement and the SqlConnection object.

Retrieving Forward-only Records

In ADO, it was common to open a forward-only (or forward-scrolling) cursor. The forward-only cursor was often named a fire-hose cursor because the data was blasted down as though through a fire hose. This type of cursor is not fully-scrollable. Instead, you can walk through one record at a time, which is exactly what you need for many Web applications. For example, the code in FIGURE 1 illustrates using a fire-hose cursor in ADO.

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim sSQL As String

sSQL = "Select * from Authors"

cn.Open "provider=SQLOLEDB;data source=localhost;" & _

 "initial catalog=Pubs;user id=sa;password=;"

rs.Open sSQL, cn, adOpenForwardOnly, adLockReadOnly

FIGURE 1: Retrieving data using a fire-hose cursor in ADO.

In ADO, you need to use the regular Recordset object to handle a fire-hose cursor. In contrast, ADO.NET provides a specialized object for handling this type of cursor: the SqlDataReader (or OleDbDataReader) object. This data-reader object s sole purpose is to make a single pass through the records coming back from a database.

To create a datareader, you must use the ExecuteReader method of the command object. The code in FIGURE 2 shows how you would create a SqlDataReader in ADO.NET.

Dim sConn As String = "Initial Catalog=Pubs;" & _

 "Data Source=localhost;User ID=sa;Password="

Dim sSQL As String = "Select * from Authors"

Dim cn As New SqlClient.SqlConnection(sConn)

Dim authorCommand As New _

 SqlClient.SqlCommand(sSQL, cn)

Dim drAuthor As SqlClient.SqlDataReader

cn.Open()

drAuthor = authorCommand.ExecuteReader

drAuthor.Read()

FIGURE 2: Creating a SqlDataReader in ADO.NET.

Notice that in ADO.NET, you need to call the Read method of the datareader object in order to begin accessing the data. ADO places you on the first record of a recordset, but this is not the case with ADO.NET.

The accompanying sample file named aspnetpro1.aspx uses a SqlDataReader (see end of article for download details). You ll notice that in this file the Page_Load subroutine uses Response.Write to create a table and display the data. The point of this example is to show the data being retrieved and accessed using the Read method. Notice that inside the loop, there is no need to use a MoveNext method. The Read method advances you to the next record automatically and also checks for the end-of-file marker. You can see the result in FIGURE 3.  

FIGURE 3: Data retrieved and displayed using the ADO.NET SqlDataReader.

Inserting a Record with ADO and ADO.NET

If you want to insert a record in ADO or ADO.NET using the command object (which is different from using an updateable Recordset or DataSet object) you will use the connection and command objects. FIGURE 4 shows the code in ADO, and FIGURE 5 shows the equivalent code in ADO.NET.

Dim cn As ADODB.Connection

Dim sSQL As String

Set cn = New ADODB.Connection

cn.Open "provider=SQLOLEDB;data source=localhost;" & _

 "initial catalog=Pubs;user id=sa;password=;"

sSQL = "Insert Into Authors " & _

 "(au_id,au_lname, au_fname,contract) values " & _

 "('111-11-1111','Utley','Craig',1)"

cn.Execute sSQL, , adExecuteNoRecords + adCmdText

FIGURE 4: Inserting a record in ADO.

Dim sConn As String = "Initial Catalog=Pubs;" & _

 "Data Source=localhost;User ID=sa;Password="

Dim sSQL As String = "Insert Into Authors " & _

 "(au_id,au_lname,au_fname,contract) values " & _

 "('111-11-1111','Utley','Craig',1)"

Dim cn As New SqlClient.SqlConnection(sConn)

Dim insertAuthor As New SqlClient.SqlCommand(sSQL, cn)

cn.Open()

insertAuthor.ExecuteNonQuery()

FIGURE 5: Inserting a record in ADO.NET.

In both cases, you open a connection to the database. In the ADO code, though, you can execute the SQL statement directly against the connection object. ADO.NET doesn t allow you to do this, so you have to create a command object and use the ExecuteNonQuery method.

The DataSet Object

One of the most fundamental changes from ADO to ADO.NET is the addition of the DataSet object. The DataSet object is inherently disconnected. In fact, a DataSet object has no knowledge of underlying data sources. Instead, the dataset represents data, stored in one or more tables, all in memory. This is basically an in-memory database.

You can work with a dataset from your application just as if it was a database. You can insert, update, and delete records. The tables in the DataSet object can have constraints defined for uniqueness. Relationships with foreign key constraints can be defined, also. This means you can enforce at least some of your referential constraints on this in-memory database structure.

By being completely disconnected from the actual data source, the DataSet object allows you to define a schema in any way you see fit. For example, you could combine data from multiple, heterogeneous databases into one logical, in-memory schema. Then, your application would have a more consistent data store from which to pull.

In addition to storing data, the dataset can be updated, and, at any point, you can re-synchronize the data in the dataset with the underlying data sources. This synchronization is not done by the DataSet object itself because it has no knowledge of the underlying data sources. Instead, another object, the DataAdapter object, handles synchronization.

The DataAdapter Object

The dataadapter represents one connection object and four command objects (one each for select, insert, update, and delete). Dataadapters are used to connect to the underlying data source, retrieve records, and populate a dataset. The dataadapter also handles synchronizing the changes in a dataset with the underlying data sources.

There are two DataAdapter objects: the SqlDataAdapter and the OleDbDataAdapter. The DataAdapter object contains a Fill method, which is used to retrieve the data and place it into a DataTable object within the dataset. If a datatable does not exist, one will be created.

Datasets and Concurrency

Typically, ASP.NET applications do not need to create a dataset and hold it for long periods of time. If you make changes to the data and later try to synchronize those changes back to the data source, you face the very real possibility that someone else has modified the data. For example, say you re using a dataset to keep track of inventory. You have five customers all trying to buy a particular item, but only one is in stock. When you remove the item from each customer s dataset, all is well at least until you attempt to update the data source, and you run into problems. Therefore, concurrency issues are the real challenge. The good news is that you can handle any concurrency conflicts. You just need to define event handlers to handle any conflicts that occur when you perform the update to move changes from the dataset back to the underlying data source. I won t cover the handling of concurrency issues here, but it will be the subject of a later article.

Serializing Datasets

Imagine a Web Service that is called by any client on any platform. The call is simple HTTP, but you might want to return a dataset. How do you do this? With ADO, passing the data was always a challenge: Did you pass it as a Recordset object, as an array of variants, etc. With ADO.NET, you can pass a dataset safely and be confident it is passed efficiently.

In the code shown in FIGURE 6, you create a connection object, a command object, a DataAdapter object, and a DataSet object. Then, you use the GetXML method to return the dataset in XML format. In FIGURE 6, the XML is simply returned as a string.

Dim sConn As String = "Initial Catalog=Pubs;" & _

 "Data Source=localhost;User ID=sa;Password="

Dim cn As New SqlClient.SqlConnection(sConn)

Dim authorCommand As SqlCommand = _

  New SqlCommand("SELECT * FROM Authors", cn)

Dim authorDA As SqlDataAdapter = New SqlDataAdapter()

authorDA.SelectCommand = authorCommand

cn.Open()

Dim authorDS As DataSet = New DataSet()

authorDA.Fill(authorDS, "Authors")

Dim xmlAuthor As String = authorDS.GetXml

Return xmlAuthor

cn.Close()

FIGURE 6: Using the DataSet object s GetXML method.

Returning a string is not necessarily very impressive, even if it is in XML format. The good news is that you don t have to convert the dataset explicitly to XML every time you pass it. For example, if you simply return the dataset as a DataSet object, the dataset is serialized to XML format automatically. You can verify this by creating an XML Web Service and creating a method that returns a dataset as the return type. Then, if you call the method from Internet Explorer, the data shows up as XML. This is because a dataset, when passed across process boundaries, is serialized to XML. You can see this by simply returning a DataSet object from a Web Service. The accompanying sample file named aspnetpro.asmx represents an XML Web Service that returns a dataset, and FIGURE 7 shows the result that is shown in the browser. Notice that you never have to convert the dataset to XML explicitly.

FIGURE 7: A Web Service returning a DataSet object, which is serialized automatically to XML in order to be passed across process boundaries.

Conclusion

ADO.NET represents a new way of looking at your data. For Web applications using fire-hose cursors, the changes may not be significant. Use the DataReader object to access the data in the most efficient manner.

Data changes are best done using INSERT, UPDATE, or DELETE statements and executing them using an ADO.NET Command object. There are several execute methods of the command object, so you can fine-tune your code for optimum performance. If you will not be returning any records, be sure to use the ExecuteNonQuery method to avoid creating a datareader unnecessarily

To perform more complex actions, you can use the DataSet object. This is an in-memory representation of data, and you can make changes to it just as if it was a database. The dataset has no knowledge of the underlying data source. Instead, the dataset is populated by a dataadapter, which acts as a bridge between the dataset and the data source. If you need to perform updates and are able to control any concurrency conflicts, the dataset will be your answer.

The files referenced in this article are available for download.

Craig Utley is president of CIOBriefings LLC, a consulting and training firm focused on helping customers develop enterprise-wide solutions with Microsoft technologies. Craig spends much of his time working with companies on analysis and design of n-tier applications employing .NET, COM+, and SQL Server. A frequent conference speaker as well as a book, courseware, and article author, Craig has recently spent much time writing about VB .NET and ASP.NET, as well as creating courseware for Volant Training. Contact Craig at mailto:[email protected].

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.