| Downloads |
|---|
| 24025.zip |
Many existing applications, especially Web and n-tier applications, use ADO as the preferred way of interacting with database management systems. ADO was created for use with connected and mostly two-tier applications. Over time, Microsoft extended the ADO object model design to meet new user requirements such as support for disconnected scenarios, n-tier development, and XML integration. The Microsoft .NET Framework, however, presents a new object model, raising crucial questions for developers and software architects: To port or not to port? Should you port applications gradually or rewrite them from scratch? Should you stay with ADO, or should you go with ADO.NET?
The .NET Framework comes with a new data-access layer that two main groups of classes—the ADO.NET and XML core classes—provide. Under the umbrella of ADO.NET, you find classes such as DataSet and DataTable that implement database-independent data containers. You also find database-oriented tools such as SQL and OLE DB commands, managed data providers and connections, data readers, and data adapters. ADO.NET represents the .NET way of working with data irrespective of data source, data format, or physical location. It comes with a new object model and, more important, pushes new concepts such as the DataSet and the DataReader. It also features a data-centric design center different from ADO's database-centric vision and closer to today's Web programming needs.
Starting with beta 2, ADO.NET has provided some interesting options to import ADO code and convert ADO objects into ADO.NET classes that are more usable in .NET applications. Because of this conversion ability, backward compatibility is guaranteed. Although ADO and ADO.NET are substantially different, they possess objects whose roles are functionally similar. At the highest level of abstraction, the way you access data in ADO.NET isn't much different from the way you access it in ADO.
To use ADO objects from within .NET applications, you need to be familiar with some tools and techniques. Although functional, using ADO in .NET isn't a recommended practice; apply it only as a transition step on the way to a full porting of the application to ADO.NET. Let's look at the tools first; then I'll demonstrate a powerful technique for importing an ADO Recordset object into an ADO.NET DataTable object. This importation is ideal if you have middle-tier COM objects to deal with in your applications.
Using ADO from ADO.NET
Although rich and powerful, the ADO.NET object model doesn't cover all the functions of ADO. For example, at least in version 1.0, ADO.NET lacks support for server cursors and the ADO Extensions (ADOX) object model that you can use to programmatically manage table schemas. If you can't work around these problems and you still need to begin porting the application to .NET, you must stick with ADO and find a way to use the application from .NET.
ADO objects are contained in a COM library called msado15.dll, which is usually in the C:\program files\common files\system\ado folder. Don't be fooled by the 15 in the DLL's name: It doesn't mean that your version of ADO is 1.5, nor that you're looking at the wrong file. Any later version of ADO (from 2.1 to 2.7) resides in a file with that name.
To use ADO in .NET applications, you need to create a class, wrapping it as a .NET-compliant class, because .NET applications can't just call COM objects such as those in the ADO library. After you install the .NET Framework, you have a managed executable file called adodb.dll in the C:\program files\microsoft.net\primary interop assemblies folder. This file is what you need to import ADO code into any model of .NET application—Windows Forms, Web Forms, and Web Services. Especially if you're still using beta versions of .NET for test and evaluation purposes, you might not have the right version of the adodb.dll file. You can regenerate this file at any time by using a .NET Frameworkprovided utility, tlbimp.exe. You run the utility from the console command line as follows:
tlbimp "C:\Program Files\Common Files\Systemado\msado15.dll"
The tlbimp.exe utility is available in the Bin subdirectory of the path where you installed the .NET Framework. Usually, this path is C:\program files\microsoft visual studio .net\frameworksdk\bin. The adodb.dll file contains .NET classes that expose the same programming interface as the original COM-based ADO library. Your .NET application talks to these classes, which in turn communicate with ADO objects.
For Web applications, make sure that you have a copy of adodb.dll in the Bin subfolder of the application's virtual directory. If your application lacks a virtual folder, place adodb.dll in the Web server's root directory (i.e., C:\inetpub\wwwroot\bin). After you have the correct file in place, you're ready to use ADODB.Recordset and other ADO objects from any .NET application. To see how to use ADO in .NET, let's consider a simple ASP.NET Web Forms page.
A Web Forms page is nothing more than a text file with an .aspx extension. So to write it, you don't strictly need Visual Studio .NET installed; any text editor (e.g., Notepad) will work fine. For this demonstration, I use Notepad as the editor and C# to code the sample application. First, you link the adodb.dll managed executable to the ASP.NET page by adding an @ Assembly directive at the top of the file:
<%@ Assembly Name="ADODB" %>
To call any object in the ADODB assembly, you must use the ADODB namespace prefix. So to create a new Recordset object, you must use the following syntax:
ADODB.Recordset adoRS = new ADODB.Recordset();
However, if you also add an ad hoc Import directive such as
<%@ Import Namespace="ADODB" %>
you can declare a Recordset object by using the following, more compact syntax:
Recordset adoRS = new Recordset();
From here on, you can write your code the ADO way. For example, to fetch a Recordset and walk through the returned records, you use the code that Listing 1 shows. This code is part of a sample application (which you can download at http://www.sqlmag.com, InstantDoc ID 24025) that fetches a Recordset and creates a series of check-box input controls. Figure 1 shows the output of the sample application. Listing 2 shows the main part of the code—the ImportAdo.aspx file.
The code in Listing 2 connects to the database, using the ADO Recordset object to retrieve some records, then builds an HTML string made up of a collection of check boxes. Of particular importance is the use of the StringBuilder object. .NET treats strings as closed and immutable objects. Although high-level languages offer operators such as + and += to concatenate strings, using such operators isn't the most efficient way of concatenating the strings. Because strings are immutable, when you concatenate two strings, your code just creates a new string of the appropriate size and places the contents of the two input strings into it. Especially in a Web-form scenario, using the StringBuilder object to build strings incrementally is the recommended technique because it always appends to the first string without creating a new string object every time.
Should I Stay, or Should I Go?
The fact that you can use ADO objects from within .NET applications doesn't necessarily mean that you always should. Importing ADO classes into a .NET context might save you from a radical porting of existing ADO applications because you just reuse your database skills and code, although in a new application context. However, it doesn't save you from having to carefully review the existing code. You need to consider two factors before you decide whether to stay with ADO or move to ADO.NET.
One major factor that can influence your decision is the wide array of languages supported on the .NET platform. The three most important .NET languages are C#, Visual Basic .NET, and JScript .NET. C# is a new language available only with .NET. Visual Basic .NET has a lot in common with Visual Basic (VB) 6.0 and VBScript, but it also has several new features and many new default behaviors that could easily break existing code. Of the three, JScript .NET most closely matches the set of features of its unmanaged, pre-.NET counterpart (i.e., JScript). However, little ADO code has been written with JScript.
Most existing ADO code is written in VB and, consequently, isn't totally compatible with Visual Basic .NET. In addition to new constructs and a new vision, many little changes divide VB and Visual Basic .NET. (See the sidebar "VB vs. Visual Basic .NET," for a summary of the main differences between VB and Visual Basic .NET.) Overall, choosing to stick with ADO and Recordsets doesn't guarantee that your code will remain unchanged in the .NET world.
The second factor to carefully consider is .NET data binding—that is, the ability to bind data to UI controls. Storing your records in a legacy data container such as the ADO Recordset doesn't let you take advantage of .NET-specific data-bound controls. Among these controls are a few—such as the DataGrid control, the DataList control, and the DropDownList control—that can really change the face of your applications, instantaneously making the look and feel more user-friendly while increasing overall developer productivity.
From Recordsets to DataTables
Let's see how to convert an ADO Recordset object into an ADO.NET object that you can more easily integrate with .NET applications. The code in Listing 1 uses a manually written While loop to scan a Recordset and produce a list of check boxes. In native ADO.NET code, you can obtain the same result with code that's surprisingly simpler and more compact, as Listing 3, page 40, shows. Note that you need to include the System.Data.OleDb assembly to use the class OleDbDataAdapter—the class that transforms the Recordset into an ADO.NET object.
The code in Listing 3 converts the Recordset into an ADO.NET object—the DataTable—that you can then bind to any controls that support data binding. Although not functionally identical, the DataTable object can be considered the ADO.NET counterpart of a disconnected Recordset object. In ADO, you obtain a disconnected Recordset by using a static, client-side cursor.
You accomplish the Recordset-to-DataTable conversion by using the Fill method of the OleDbDataAdapter object:
DataTable dt = new DataTable(); OleDbDataAdapter oda = new OleDbDataAdapter(); oda.Fill(dt, adoRS);
The Fill method populates the DataTable object with the contents of the specified Recordset object. While performing the operation, the Fill method creates any needed columns and adds rows. If primary key information exists, the Fill method matches any new row with any existing row. If it finds a match, it replaces the existing row with the new row. If no match is found or if no primary key information is available, the Fill method simply appends the Recordset row to the DataTable object.
By design, the method leaves the Recordset open, and the programmer is responsible for closing it programmatically. This requirement gives you great flexibility because you can load each result set in the Recordset into a distinct DataTable object.
If you just need to load all the result sets into one container object, you use the DataSet object rather than the DataTable. A DataSet is simply a collection of DataTable objects. To import the Recordset into a DataSet object, you use one of the overloads of the Fill method. The overload you see in action in the code below takes a DataSet object and a Recordset object, plus a third argument that I discuss later:
OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); da.Fill(ds, adoRS, "MyTable");
In the code above, the Fill method automatically closes the Recordset on completion. All the result sets in the original Recordset are available as distinct tables within the DataSet. In this case, though, you have little control over how each DataTable is named. When you call Fill by passing a DataSet object, you can specify a table name—MyTable in the code snippet above. That will be the name of the first table created—that is, the first result set in the ADO Recordset. Additional result sets are automatically named the same as the first one with a number appended—MyTable1, MyTable2, and so on. After the DataSet has been created, you can rename the child tables:
ds.Tables\["MyTable1"\].TableName = "NewNameOfTable1"; ds.Tables\["MyTable2"\].TableName = "NewNameOfTable2";
The Middle Tier
The ability to connect to a data-bound control is an important feature of the ASP.NET programming model. Many controls in the .NET Framework—both in the Web Forms and the Windows Forms models—let the data source populate their UI. The data source for such data-bound controls must be a collection object. The ADO Recordset is a collection object, but not a .NET-specific collection object. That's why you need to convert a Recordset into something else (e.g., the DataTable object) to bind to a data-bound control such as the CheckBoxList or the DropDownList. This example illustrates the need to handle ADO and Recordsets with extreme care in .NET code. In Web Forms and Windows Forms, a Recordset can only be an intermediate object that caches data from an OLE DB data provider but can't make it available to specialized data-bound controls.
The OleDbDataAdapter class's Fill method helps you work around this important limitation. This method helps you import into a new .NET application the data that existing business and data-access objects can return: an ADO Recordset. In practice, instead of importing the ADO library, you just import your middle-tier objects into the .NET applications. As long as those business objects return ADO Recordset objects to callers, you can easily convert the Recordsets to ADO.NET objects, then leverage the power of data binding.
As you've just seen, you can import an ADO Recordset into ADO.NET, but the .NET Framework doesn't provide a feature for doing the reverse—exporting a DataTable to a Recordset. So although you can still use ADO to fetch data, you must rely on ADO.NET to perform updates to that data.
A lot of code is written in ADO and VB, and porting it to a new platform and a new language is a serious matter. You can preserve your investments by importing Recordset objects into .NET applications. But in general, using ADO in .NET applications is only a temporary solution. To move to ADO.NET, you need to familiarize yourself with some new objects and concepts. Fortunately, they require a fairly short learning curve if you look at them from a real-world ADO perspective.
