Microsoft Data Access Interfaces

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.

Click To expand 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




The top level class in the ADO.NET hierarchy


Shared "groundwork" classes for .NET providers


.NET for OLEDB classes


SQL Server specific classes; optimized for SQL server 7.0 and later


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.

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_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.

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

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.