Microsoft Data Access Interfaces

Microsoft went through many stages of data access mechanisms in its products. Call Level Interface, while fast, was not easy to use and had a steep learning curve. Realizing that, Microsoft introduced classes that encapsulated the same functionality but provided a much more programmer-friendly interface. With the advent of Rapid Development Tools (Visual Basic, Delphi, PowerBuilder, etc.), a programmer-friendly interface that allows the user to concentrate on the implementation of the business logic, without being bogged down with low-level manipulations, became a strategic point for Microsoft. The data access objects (classes) followed in the rapid succession: Data Access Objects (DAO), Remote Access Objects (RDO), Active Data Objects (ADO), and finally Active Data Objects.NET. (Some may even recall ODBCDirect — a data access library introduced specifically for Visual Basic programmers to access MS SQL Server 6.0/6.5.) Make no mistake: While serving the same purpose, these interfaces are all different. DAO, for instance, uses the JET Engine — Microsoft Access library — even when connecting to SQL Server, while RDO does not have to use it; the syntax is quite different and not always straightforward.

The component-based architecture became a mainstream in the late 1990s. While who introduced the technology (and when) can be disputed, there is no doubt that it was Microsoft who brought it to the widest audience with its OLE/ActiveX technology (its equivalent in non-Microsoft camp is CORBA). ADO was a hierarchy of the ActiveX objects, assembled in a collection of the ADO data library. It was working with OLEDB data providers — assuming there was one available; for the standard ODBC connection you would have to use the OLEDB provider for ODBC. This meant more and more layers of data access abstraction, which slowed down the process of data retrieval and manipulation. To make things worse, initially ADO was limited to working in connected environment; that is, a permanent connection had to be maintained to the data source); the disconnected data access model was introduced later, and it had problems of its own (e.g., being limited to using Microsoft Internet Explorer version 4 or above). Even now, while the .NET initiative is taking firm hold on the developer community, ADO is still very much alive and will continue for a few more years at least. (Microsoft announced that support for "classic" Visual Basic would cease in 2008.)

The following Visual Basic example illustrates the process of connecting to the IBM DB2 UDB RDBMS and executing an SQL query.

' this example uses late bound objects ' (see note later in the chapter), which ' makes it possible to use in ASP projects ' and makes it less DLL-versions-error prone ' there is a price to pay in terms of speed and ' typing errors (no IntelliSense help for the ' late-bound ActiveX objects Dim objConnection Dim objCommand Dim objADORecordset Dim strConnectString Dim strSQL ' ' assemble the connection string ' uses Microsoft ODBC bridge for OLEDB ' the user DSN ACME_DSN was created earlier in the chapter ' strConnectString = "Provider=MSDASQL;DSN=ACME_DSN;" strConnectString = strConnectString & "User ID=ACME;Password=ACME;" ' ' create late-bound ADODB Connection object ' Set objConnection = CreateObject("ADODB.Connection") ' ' open connection to the IBM DB2 UDB database ' objConnection.Open strConnectString ' ' create late-bound ADODB Command object ' Set objCommand = CreateObject("ADODB.Command") ' ' assign objConnection to its ActiveConnection property ' objCommand.ActiveConnection = objConnection ' ' assign SQL statement to the CommandText property ' objCommand.CommandText = strSQL ' ' set type of the command to adCmdText ' meaning that raw SQL statement would be executed ' as opposed to stored procedure, for instance ' objCommand.CommandType = 1 ' ' the execute command returns an ADO Recordset (if succeeded) ' Set objADORecordset = objCommand.Execute ' ' scroll the recordset ' With objADORecordset ' ' position the cursor on the very first row ' in the recordset ' .MoveFirst Do While Not objADORecordset.EOF ' ' display the value of the first field ' of the result set in a message box ' MsgBox .Fields(1).Value ' 'move to the next record ' .MoveNext Loop End With ' ' clean up: close all the connections ' and destroy objects; usually done ' automatically by Visual Basic once the object ' goes out of scope objADORecordset.Close Set objADORecordset = Nothing Set objCommand = Nothing objConnection.Close Set objConnection = Nothing

ADO.NET is the latest incarnation of the Microsoft data access mechanisms, and it was specifically designed to work with the new .NET framework, which is supposed to be a programming paradigm shift. While retaining part of the old name, ADO.NET is a completely different data access mechanism. First, it is not an external library that you link one way or another into your application and distribute afterward. ADO.NET is a part of the .NET framework (and — eventually — a part of the Windows operating system itself); second, it is not OLE/ActiveX based; that is, it has nothing to do with COM (Microsoft Component Object Model).

ADO.NET was designed to support a connected environment as well as disconnected one, and to support XML natively. To maintain compatibility with the previous interfaces, there are .NET Data Providers for OLEDB.

The following example, of a console application written in VB.NET, uses OLEDB provider for Microsoft SQL Server and ADO.NET classes to connect to a local server and retrieve some information:

 Sub Main() Dim objDataReader As SqlClient.SqlDataReader Dim strConn As String Dim strSQL As String Dim lCount As Integer = 0 ' ' assemble connection string using SQLOLEDB provider ' strConn = "Provider=SqlOleDb;Data Source=localhost;" strConn = "Initial Catalog=Acme;" strConn = "User ID=acme;Password=acme;" ' ' assemble SQL query ' strSQL = "SELECT * FROM customer" strSQL = strSQL & " WHERE CUST_NAME_S LIKE 'WI%'" ' ' write comments onto console ' Console.WriteLine("An example of using SQLOLEDB") Console.WriteLine("Provider with VB.NET") Console.WriteLine("-------------------") ' ' connect to the SQL Server and fetch the data ' Try ' ' create new Connection object ' using the connection string Dim objConnection As New SqlClient.SqlConnection(strConn) ' ' open connection to the SQL server ' objConnection.Open() ' ' create a command object, with the SQL statement ' and connection object as initialization params ' Dim objCommand As New SqlClient.SqlCommand(strSQL, objConnection) ' ' retrieve results into DataReader object ' objDataReader = objCommand.ExecuteReader() ' ' scroll the data reader, and reteieve ' value from the 4th field in the recordset ' CUST_NAME_S, and increment counter ' Do While objDataReader.Read Console.WriteLine (objDataReader.GetSqlString(4).ToString) lCount = lCount + 1 Loop ' ' output the final message ' Console.WriteLine("-------------------") Console.WriteLine("Total records:" & lCount) ' ' wait for the input ' Console.ReadLine() ' ' close the connection within ' an appropriate scope ' objConnection.Close() Catch e As SqlClient.SqlException Console.WriteLine(e.Message) Finally ' ' close DataReader ' objDataReader.Close() End Try End Sub

The results of the execution of the code above are shown in Figure 16-7.

Figure 16-7: Connecting to Microsoft SQL Server 2000 through OLEDB

The functionality of ADO.NET is contained in the following Namespaces (a .NET concept but fairly intuitive — similar to the library, header file, or Java class), listed in Table 16-7. Both are used with VB.NET and C# (pronounced C sharp).

Table 16-7: ADO.NET Top Level Namespaces

Namespace	Description
System.Data	The top level class in the ADO.NET hierarchy
System.Data.Common	Shared "groundwork" classes for .NET providers
System.Data.OleDb	.NET for OLEDB classes
System.Data.SqlClient	SQL Server specific classes; optimized for SQL server 7.0 and later
System.Data.SqlTypes	Mapping RDBMS data types to handle return values

We illustrate ADO.NET usage with one of the .NET family languages, C#; the syntax for the other languages ported to .NET (VB.NET, COBOL, Eiffel, C++ with managed extensions, to name a few) might differ, but the underlying mechanisms remain exactly the same, since ADO.NET is incorporated into the .NET framework.

Microsoft DBLIB Legacy

DBLIB (stands for DataBase Library) is a Microsoft proprietary Call Level Interface consisting of C functions and macros (compare to Oracle OCI) that can be used to access Microsoft SQL server 2000 from the applications programmed in third-generation languages (mostly C). It was the original interface, and it became a legacy interface with introduction of Microsoft SQL Server version 7.0.

The DB-Library API has not been enhanced beyond the level of SQL Server version 6.5, which means that even if you are able to use it for communication with Microsoft SQL Server 2000, you would be limited to only the feature set of its 6.5 version (which was all the rave back in 1997).

The principles of DBLIB do not differ much from Oracle's OCI interface: you include the header files to get all the definitions into your C program, then you compile and link it. Samples of the DBLIB applications are installed with the Microsoft SQL Server 2000 in default directory \Microsoft SQL Server\80\Tools\Devtools\Samples\DBLib.

DBLIB is the fastest interface to the MS SQL Server. You can find more help about programming DBLIB in the Microsoft documentation, but keep in mind that this interface quickly becomes obsolete (though you might not have much choice if your server is version 6.5, or 6, or — just don't tell anyone — version 4.2, capable of running in MS-DOS and OS/2 environments).

The following code snippet in C# demonstrates using ADO.NET SQLData to connect to Microsoft SQL Server 2000. To try the example, start the Visual Studio.Net IDE (Integrated Development environment), select Console Application, name it SQL_Connect, then type in the code shown below, then compile it and run. Keep in mind that C# is a case-sensitive language, and SqlConnection is not the same as SQLConnection. You may or may not use the namespace that automatically appears in your code pane.

using System; using System.Data; using System.Data.SqlClient; class SQL_Connect { static void Main(string[] args) { // //prepare connection string //use integrated security (Windows Authentication) //as opposed to UserID/Password pair // string s_conn = @"server=(local);" + "Integrated Security=true;" + "database=ACME"; // //Create SqlConnection object instance // SqlConnection connSQL = new SqlConnection(s_conn); try { // //open connection to the local instance //of your SQL Server // connSQL.Open(); string s_SQL = "SELECT * FROM customer WHERE CUST_NAME_S LIKE 'MA%'"; // //create command object and pass to it //connection object and SQL query string // SqlCommand commSQL = new SqlCommand(s_SQL,connSQL); // //retrieve all the records returned by the //query into SqlDatReader object // SqlDataReader sqlRead=commSQL.ExecuteReader(); Console.WriteLine("Example demonstrating .NET Data Provider"); Console.WriteLine("connection to Microsoft SQL Server 2000"); // //scroll the result set and print the output //onto standard output console // while (sqlRead.Read()) { Console.WriteLine("{0}|{1}", sqlRead["CUST_NAME_S"].ToString().PadRight(30), sqlRead["CUST_ID_N"].ToString()); Console.WriteLine("--------------"); } } catch(Exception e) { // // in the case an error occurred // display a message, including error source // Console.WriteLine("Error occurred:" + e.Message); Console.WriteLine("Error Source:" + e.Source); } finally { // //Close connection to SQL Server// connSQL.Close(); Console.WriteLine("Connection closed."); // //wait for user input //to keep MS-DOS Window // Console.ReadLine(); } } }

If you are using VisualStudio.NET IDE, run the program either from the taskbar or by pressing F5 button on your keyboard, or from the command line by going into the directory where the executable was compiled. Figure 16-8 shows the output results produced by the above program.

Figure 16-8: Results of the database C# program

Tip 

Oracle's native .NET Data Provider is not included with the Visual Studio.NET and must be downloaded separately from Microsoft site. Once downloaded and installed, you need to add a reference to System.Data.OracleClient.dll assembly to your project.