Back to VSLive! Show Daily Home

ADO and ADO.NET:
Big Changes
Will your investment in ADO and its techniques pay off when you apply these skills to ADO.NET?

VSLive! SF, Day 1, February 12, 2002 — Many ADO "classic" (ADOc) developers are wondering if your existing code will work in Visual Basic.NET or in one of the new .NET languages (such as C# or COBOL). A question you should be asking is: "Will my investment in ADO and techniques training pay off when I apply these skills to ADO.NET?" Unfortunately, the answer is not clear—at least not binary. Yes, many of the architectural decisions you've made over the years will continue to pay off in improved performance and scalability. For example, fundamental practices such as fetching fewer rows, using "firehose" cursors, and managing your round trips wisely still apply. However, many of the techniques you (and I) have been practicing for some time simply don't (yet) migrate to ADO.NET.

Even the decision to leverage existing data access techniques is burdened by many complex issues. In some cases you might want to simply call a COM object containing your ADO code from your .NET application through the new COM Interop layer. Visual Studio.NET makes this relatively painless and without a severe performance penalty—Microsoft did its homework. However, incorporating ADOc calls into an application might prove problematic. Techniques such as named Commands and stored procedures as methods of the Connection object don't work particularly well. Sure, I'm assured that these problems are addressed in the "next" release, but for now you're likely to encounter a number of confusing problems.

Consider that ADO.NET's similarity with ADOc as you know it has mostly to do with the acronym—and not much else. Some of the objects have the same names, but their properties, methods, and events are very different. If you decide to port your data access approach over to ADO.NET code, you'll have much better luck if you're working with the ADOc "disconnected" Recordsets. If you depend on keyset or dynamic cursors or pessimistic locking you'll want to rethink your strategy. ADO.NET is designed to work with your stored procedures, but you must learn how to recode them without SET NO COUNT ON. ADO.NET or Visual Studio .NET's IDE can also construct the SQL or stored procedures needed to update your data, but you might miss the ability to set the update behavior as you can with the Update Criteria property in ADOc.

Yes, there are a number of differences. However, I've found ADO.NET to be fundamentally competent and designed with plenty of room for expansion. There are plenty of new features that ADOc either never had or used complex add-ons to perform. Yes, I think you'll grow to like ADO.NET—once you get used to the fact that it's not just another release of MDAC ADO.

In this example, I invoke the stored procedure "MySP" by name passing in a parameter to the function that calls it. I illustrate two techniques that leverage the fact that all Command objects and stored procedures are exposed as methods of the Connection object. It turns out that this code works in ADO.NET, but only the first time.

Dim cn As adodb.Connection
Dim cmd As adodb.Command
Dim rs As adodb.Recordset

Private Function FindCust(CustWanted _
   As Integer) As Recordset
Set cn = New adodb.Connection
Set cmd = New adodb.Command
With cmd
   .Name = "FindCust"
   .CommandText = "MySP"
   .CommandType = adCmdStoredProc
   .Parameters.Append _
      .CreateParameter("P1", _
      adInteger, adParamInput, , _
      CustWanted)
End With
cn.Open _
   "Provider=sqloledb;
      server=betav9;" & _
   integrated security=sspi"

Set rs = New adodb.Recordset
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic

cn.AddCust CustWanted, rs 
' Call the SP passing a parm 
' returning a Recordset
'
' Or... easier yet -- do one or 
' the other.
'
cn.MySP CustWanted, rs

' This code converts with warnings in 
' VB .NET, and runs the first time,
' but not the second time the Command 
' or SP is invoked.
Set rs.ActiveConnection = Nothing
cn.Close

End Function