Introducing Microsoft Data Access Components

In 1996, Microsoft released Microsoft Data Access Components (MDAC 1.0). This was the first attempt at creating a unified database access framework to provide developers with a consistent way to develop database applications. Since that version, Microsoft has gradually enhanced their library with newer components, replacing and deprecating older ones. The latest version (MDAC 2.8 SP1) was released on May 10, 2005. Some of its components include ADO (ActiveX Data Objects), ADO.NET, OLE DB (Object-Linking and Embedding Database), and ODBC ( Open Database Connectivity).

Avoiding Deprecated MDAC Components

If you are developing new applications or upgrading existing ones, avoid using the following components because Microsoft will be removing them from future releases.

Microsoft JET (Joint Engine Technology) The first version of JET was developed in 1992. Many Microsoft products, including Microsoft Access, Microsoft Exchange, and Microsoft Visual Basic, used JET as their database engine.
MSDASQL (Microsoft OLE DB Provider for ODBC) Provides client ADO application access to ODBC connections through an OLE DB provider.
MSDADS An OLE DB provider that allows the construction of hierarchical relationships between rowsets.
Oracle ODBC An ODBC driver developed by Microsoft that provides access to Oracle database servers.
MSDAORA An OLE DB provider developed by Microsoft that provides access to Oracle database servers.
RDS A technology that allows retrieval of a set of data from a database server across the Internet or an intranet.
JET and Replication Objects (JRO) A component object model used to manage replication in Microsoft Access databases.
SQL XML Extends the SQL Server OLE DB provider, thereby allowing applications to retrieve XML streams. SQL XML is not deprecated, but is being removed from future MDAC releases.

Outlining the MDAC Architecture

From an architectural point of view, MDAC can be divided into three distinct layers , as shown in Figure 6-1.

Programming interface layer: Location of ADO and ADO.NET components
Database access layer: Location where different database vendors supply their database access providers (ODBC and OLE DB providers)
Database layer

Figure 6-1: MDAC Architecture.

Understanding Open Database Connectivity (ODBC)

ODBC is a standard application programming interface (API) to utilize database management systems (DBMSs). The main objective of ODBC is to provide a generic standard supported by multiple programming languages, database systems, and operating systems. All major operating systems support the ODBC standard including Microsoft Windows, UNIX, Mac OS X, and Linux. There are also drivers for hundreds of DBMSs including Microsoft SQL Server, Microsoft Access, DB2, Oracle, and Sybase.

Creating a Data Source Name

The first step when using an ODBC data source is to create a data source name (DSN). A DSN stores information about how to connect to a data source. Microsoft Windows supports three types of DSNs:

User DSN Visible to the current user
System DSN Visible to all users on a single computer
File DSN Can be shared among multiple computers

Creating an ODBC Data Source

From the Start menu, choose Settings Control Panel.
Double-click the Administrative Tools icon.
Double-click the Data Sources (ODBC) icon.
Select the tab for the type of data source you want to create (User DSN, System DSN, File DSN). For this example, select the User DSN tab.
Click the Add button to add a new data source.

In the Create New Data Source dialog box, select SQL Native Client and click the Finish button.

Note	SQL Native Client allows applications to use the new features of SQL Server 2005. If your application also uses SQL Server 7.0 or 2000 and you do not want to install the new SQL Native Client, you may use the SQL Server driver.

In the Create A New Data Source To SQL Server dialog box, type the name of your choice for the data source in the Name textbox. You may also type a description in the Description textbox. Type the name of the server, or select a server from the Server drop-down listbox. Click Next to continue.
Select the preferred authentication method for the connection. Windows Authentication does not require a login and password because the user is already authenticated to the operating system; however, SQL Server 2005 authentication requires a login and password. Click Next to continue.
Select the Change The Default Database checkbox and select the appropriate database in the Database drop-down listbox. Click Next to continue.
Review the additional options in the final window and click the Finish button.
In the ODBC Microsoft SQL Server Setup dialog box, review the configuration. Test the DSN by clicking the Test Data Source button and then click OK.
Click OK to finish the configuration.

Programming ODBC Applications

Although VB.NET and C# can be written to use ODBC directly, developers seldom take this approach because Microsoft provides simpler APIs with which to program ODBC applications, such as ADO and ADO.NET. For directly using the ODBC functions, Microsoft Visual C++ can be used. The following code tests a connection using the C language. This code can be accessed from \Ch06\Sample01.c in the sample files.

 #include <stdio.h> #include <sql.h> #include <sqlext.h> main() {   ExtractError   SQLHENV env;   SQLHDBC dbc;   SQLHSTMT stmt;   SQLRETURN ret;   SQLCHAR outstr[1024];   SQLSMALLINT outstrlen;   /* Environment Handle */   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);   /* ODBC 3 support */   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);   /* Connection Handle */   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);   /* Open Connection to AdventureWorks */   ret = SQLDriverConnect(dbc, (void *)1, "DSN=AdventureWorks;", SQL_NTS,                 outstr, sizeof(outstr), &outstrlen,                 SQL_DRIVER_COMPLETE);   if (SQL_SUCCEEDED(ret))     {     printf("Connected\n");     SQLDisconnect(dbc);     }   else     {     fprintf(stderr, "Failed to connect\n");     }   /* Free handles */   SQLFreeHandle(SQL_HANDLE_DBC, dbc);   SQLFreeHandle(SQL_HANDLE_ENV, env); }

Note	This example uses C language because demonstrating how to create an ODBC connection is clearer in C. This connection can be created with other languages, but the complexity of the code would hide what needs to be demonstrated here, which is the connection definition.

Understanding OLE DB and ADO

OLE DB is an API designed by Microsoft to access different data stores in a uniform manner. Instead of using a single interface, such as ODBC, OLE DB is a set of interfaces implemented using the Component Object Model (COM). OLE DB is a successor of ODBC and extends ODBC functionality to provide access to nonrelational databases, such as spreadsheets and text files.

ADO is a set of COM components that provides a high-level interface to OLE DB. Its main objects are the Connection , Command , and Recordset objects. The following code (which you can access from \Ch06\Sample02.bas in the sample files) uses the ADO object model to read a list of departments and is written using Visual Basic for Applications.

 Public Function ReadData() As ADODB.Recordset     Dim cn As ADODB.Connection     Dim cmd As ADODB.Command     Dim rs As ADODB.Recordset     ' Setup Connection     Set cn = New ADODB.Connection     cn.Provider = "SQLOLEDB"     cn.ConnectionString = _        "Server=ILUVATAR;Database=AdventureWorks;Trusted_Connection=yes"     ' Setup Command     Set cmd = New ADODB.Command     cmd.CommandText = "SELECT Name FROM HumanResources.Department"     cmd.CommandType = adCmdText     ' Read Data     cn.Open     cmd.ActiveConnection = cn     Set rs = cmd.Execute()     cn.Close     Set ReadData = rs End Function

The architecture of ADO can be summarized as shown in Figure 6-2.

Figure 6-2: ADO Architecture.

ADO offers a simpler model than its predecessors RDO (Remote Data Objects) and DAO (Data Access Objects). For example, when using ADO, you may create a recordset without a connection. Internally, when the recordset is opened, the connection will be created and opened. When the recordset is closed, the connection will also be closed.

Using an ADO Connection Object

The Connection object is used to represent the link to the database and the database provider. Through the ADO Connection object, you can set the connection string, connect to the database, open the connection, and close the connection. The ADO connection also is used to manage transactions. You will learn more about transactions in Chapter 10, Using Transactions to Provide Safe Database Concurrency in Microsoft SQL Server 2005: Applied Techniques Step by Step .

The trickiest part of the ADO connection is to set the correct connection string. An easy way to accomplish this is to create a Microsoft Data Link (.udl) file and then copy and paste the string.

Creating an Appropriate Connection String

In Windows Explorer, navigate to My Documents.
From the File menu, choose New Text Document.
Name the file Database.txt .

Rename the file Database.udl . The icon of the file should change.

Tip	By default, Windows Explorer hides extensions of known file types. To display file extensions in Windows Explorer, choose Folder Options from the Tools menu. Select the View tab. In the Advanced Settings section, uncheck the Hide Extension For Known File Types option.

Double-click the Database.udl file to display the Data Link Properties window. Select the Provider tab.
Select the SQL Native Client provider and click Next.
In the Datasource textbox, type the server name.
In the Enter Information To Log On To The Server section, choose Use Windows NT Integrated Security if you want to use Windows Authentication or type a username and password.
In the Enter The Initial Catalog To Use drop-down listbox, select the appropriate database. Click the Test Connection button to test the connection, then click OK.
Click OK to confirm the configuration.
Right-click the Database.udl file in Windows Explorer and select Open With from the context menu.
Select Notepad from the list of programs and click OK.
Copy the string connection and use it in your application.

Using an ADO Command Object

The ADO Command object is used to execute database queries that return records in an ADO Recordset object. The ADO Command object can also be used to modify records (update, insert, delete) in the database. The major feature of the ADO Command object is the ability to use parameters and reuse the same object for multiple queries.

Using an ADO Recordset Object

The ADO Recordset object is the most important object in ADO because it is used to hold a set of records that can be navigated and modified by the application. A Recordset object consists of records and fields ( columns ). The following code displays the list of departments stored in the AdventureWorks database using Visual Basic for Applications. You can access the code from \Ch06\OLEDBSample.bas in the sample files.

 Public Sub DisplayDepartments()    Dim rs As New ADODB.Recordset    rs.Open "SELECT Name FROM HumanResources.Department", _       "Provider=SQLNCLI.1;Integrated Security=SSPI;" + _       "Initial Catalog=AdventureWorks;Data Source=(local)"     While Not rs.EOF         MsgBox rs.Fields(0)         rs.MoveNext     Wend End Sub

Understanding ADO.NET

ADO.NET is the evolution of ADO. Its main advantages are listed below.

Interoperability ADO.NET is more interoperable because it uses XML a simple and very flexible format through which to exchange data.
Maintainability In ADO.NET, it is simpler to write and maintain multi-tiered applications because it is designed to exchange disconnected data.
Programmability Strongly typed programming is possible in ADO.NET. The use of strong types makes it easier to write and read code.
Performance ADO requires COM and COM marshalling. This is not necessary in ADO.NET because data is exchanged in XML format.
Scalability Since ADO.NET uses a disconnected dataset, database connections and locks are minimized, thereby increasing the scalability of the application.

The following code (accessed as \Ch06\Sample03.vb in the sample files) uses the ADO.NET object model to read names of departments from the AdventureWorks database.

 Public Function ReadData() As DataSet     Dim da As New SqlClient.SqlDataAdapter(_         "SELECT Name FROM HumanResources.Department", _         "Server=(local);Database=AdventureWorks;Trusted_Connection=yes")     Dim ds As New DataSet     da.Fill(ds)     Return ds End Function

Outlining the ADO.NET Architecture

ADO.NET is a set of classes designed to work with data. ADO.NET uses two main components to provide data access. The first component is the dataset. A dataset is a disconnected, in-memory copy of data. From the developers perspective, a dataset is an in-memory databaseindependent from the sourcethat can be updated and manipulated. After the application has finished working with the dataset, changes can then be sent forward to the database.

The second component of the ADO.NET architecture is the data provider , which is responsible for interacting with the database. The data provider is composed of a set of classes designed to work together, including the C onnection , Command , DataReader , and DataAdapter classes, as shown in Figure 6-3.

Figure 6-3: Classes in the data provider component of ADO.NET.

Using ADO.NET Namespaces

The .NET Framework uses logical namespaces to divide functionality. ADO.NET is primarily implemented in the System.Data namespace. Other namespaces are listed in Table 6-1.

Table 6-1: ADO.NET Namespaces
Namespace	Description	Main Classes
System.Data	Core classes that represent the ADO.NET architecture	Dataset , DataTable
System.Data.Common	Base classes shared by .NET data providers	DbConnection , DbCommand, DbDataReader, DataAdapter
System.Data.SqlClient	SQL Server .NET data provider	SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter
System.Data.OleDb	Generic .NET data provider for OLE DB data sources	OleDbConnection, OleDbCommand, OleDbDataReader, OleDbDataAdapter
System.Data.OracleClient	Microsoft .NET data provider for Oracle	OracleConnection, OracleCommand, OracleDataReader, OracleDataAdapter