From ADO to ADO.NET: A Gradual Approach
Although some of Microsoft's marketing materials have presented ADO.NET (the data access layer in the Microsoft .NET Framework) as a simple upgrade to ADO, that's a rather misleading way to look at it. ADO.NET is really an almost completely new architecture for data access. This means that inevitably the developer faces a learning curve when moving from ADO to ADO.NET. But how can you remain productive while working your way up that learning curve?
Fortunately, Microsoft thought about that problem before releasing the .NET Framework. The developers of .NET worked hard to provide interoperability between COM applications (developed with tools such as Visual Basic 6.0 or Visual C++ 6.0) and .NET applications. In this article, I'll review some of the differences between ADO and ADO.NET, and then show you how you can use existing ADO-based COM components from your new ADO.NET-based .NET applications.
From Recordset to DataSet
In ADO through version 2.8 (sometimes called "classic ADO") the basic object for holding a group of related records in an application is the Recordset. A Recordset is, roughly, a single table or view stored in memory. It also has a direct and intimate connection with the original data source. Depending on your cursor settings, a Recordset may retrieve batches of records as you move through the data. Each Recordset has a pointer to a current record, which you can edit. Edits to the current record are saved or discarded before you move to another record. The Recordset and the other classic ADO objects are universal, applying equally well to any type of data.
If you've been working with ADO for any length of time, you've probably memorized all of those Recordset facts, and now assume that this is how data access is supposed to work. Well, in ADO.NET, things are almost completely different. Here are some basic facts about the DataSet, which is the new core data object in ADO.NET:
- A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
- A DataSet is designed to work without any continuing connection to the original data source.
- Data in a DataSet is bulk-loaded, rather than being loaded on demand.
- There's no concept of cursor types in a DataSet.
- DataSets have no current record pointer You can use For Each loops to move through the data.
- You can store many edits in a DataSet, and write them to the original data source in a single operation.
- Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.
Obviously the differences between the Recordset and the DataSet are profound. And yet, if you've been following Microsoft's architectural recommendations, you probably have a sizeable investment in data access layer components that return Recordsets. Do you need to discard that entire investment to move to .NET?
I'm happy to say that the answer to that question is "no". Microsoft has provided ways to draw data from a Recordset into a DataSet. In the next section of this article, I'll show you a simple example of the code to do this.
A Simple COM Server
To demonstrate how this interoperability between COM and .NET works, I'll start with a simple COM server writte in VB 6.0. In fact, it's so simple that it's under ten lines of code:
Public Function GetCustomers(strCountry As String) As Recordset Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);" & _ "Initial Catalog=Northwind;Integrated Security=SSPI" cnn.Open rst.Open "SELECT * FROM Customers WHERE Country = '" & strCountry & "'", cnn Set GetCustomers = rst End Function
This code resides in a class named Customers in a project named DataLayer. When you invoke the GetCustomers method of the Customers class with a string specifying the name of a country, it returns a Recordset object containing all of the customers in the country from the SQL Server version of the Northwind sample database. Compiling this code produces a COM server named DataLayer.dll
From COM to .NET
Suppose your existing code uses COM servers similar to DataLayer.dll to return Recordsets with data of interest. How can you use that data in a .NET application? Here's a step-by-step approach to building a .NET client for this COM server.
First, create a new Visual Basic .NET Windows application. I gave my application the uninspired name "Client." Right-click on the References node in Solution Explorer and select Add Reference. In the Add Reference dialog box, shown in Figure 1, select the COM tab. Click the Browse button and browse to the DataLayer.dll file to add it to the Selected Components list. Then click OK to add the reference to the project.
Under the covers, there's quite a lot going on when you add this reference. You'll notice that the ADODB library (containing the classic ADO objects) and your DLL both show up in the References node in Solution Explorer. Visual Studio .NET automatically creates runtime-callable wrappers (RCWs) for the two COM libraries. An RCW is a .NET library which wraps a COM library and presents the same interfaces to your .NET code that the COM component exposes. RCWs are the only mechanism through which a .NET component can call a COM server.
Next, add a DataGrid control to the default form in the project. Switch to code view, and add references to the .NET namespaces containing the data access components:
Imports System.Data Imports System.Data.OleDb
Now, write code to execute when the form is loaded:
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Create an instance of the COM server Dim c As DataLayer.Customers = New DataLayer.Customers() ' Create a DataSet to hold the .NET data Dim ds As DataSet = New DataSet() ' Retrieve a classic ADO recordset from the COM server Dim rs As ADODB.Recordset = c.GetCustomers("France") ' Move the data from the Recordset to the DataSet Dim da As OleDbDataAdapter = New OleDbDataAdapter() da.Fill(ds, rs, "Customers") ' Bind the data to the user interface dgCustomers.DataSource = ds dgCustomers.DataMember = "Customers" End Sub
The comments should make it clear what's going on in this code, but take a moment to appreciate the results. Even though COM and .NET are two very different ways to write applications, the .NET designers went the extra distance to make sure they can be seamlessly connected. In particular, this code instantiates COM components and works with them just like native .NET components. It also uses an overloaded form of the OleDataAdapter.Fill method to shuffle the data from the Recordset to the DataSet. Does it work? Sure! Figure 2 shows the .NET form displaying data from the COM server.
There is much more to COM-.NET interoperability than I can show you in a short example. If you need to move an application from the COM world to the .NET world, I recommend purchasing a copy of Adam Nathan's excellent book .NET AND COM: THE COMPLETE INTEROPERABILITY GUIDE (Sams, 2002).
Enabling Gradual Migration
The interoperability between COM servers and .NET clients is one of the secrets to making the transition from old code to new code. If you're maintaining a large existing code base of VB6 or VC6 components, it's likely that you've divided it into functional clients and servers. In that case, there's no need to do a "big bang" migration of all the components at once. Instead, you can keep your existing COM servers, and write new client components as .NET components (or vice versa; it's also possible for COM clients to use .NET servers). This way you can slide new .NET components into your existing system without overhauling interfaces and without undertaking the risk of a complete rewrite. It's a winning situation all around.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD 70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.