Accessing ODBC Data Sources

for RuBoard

Just as with the OleDb provider that ships with VS .NET, the Odbc provider is an example of a generic provider that can used to access a variety of data sources. As we discussed on Day 1, "ADO.NET in Perspective," ODBC was a major advance in data access technologies because it standardized the way that clients access data by abstracting the data store specifics into an ODBC driver. Clients could then program against the ODBC API and rely on the ODBC Driver Manager to load the appropriate drivers on behalf of the application, pass queries to the driver, and manage aspects of the communication such as pooling connections. As a result, when using the Odbc provider, your application architecture appears as shown in Figure 14.1.

Figure 14.1. ODBC architecture. This diagram depicts the architecture of a .NET application using the Odbc provider.

Come and Get It

Although not included in the release of VS .NET, the ODBC .NET Data Provider is available for download from Microsoft on the MSDN site (msdn.microsoft.com). You can find it by going to the MSDN site and typing "ODBC .NET Data Provider" in the Search For box. When you navigate to the page, you can download the installation package by clicking on the Download link. To install, simply execute the odbc_net.msi file that you download and follow the instructions. Note that you must have MDAC 2.7 or higher installed on your machine, which is the same version that installs with VS .NET.

After it is installed, the Odbc provider assembly (Microsoft.Data.Odbc.dll) will be installed in the Global Assembly Cache (GAC), so you can reference it in your projects through the Add Reference dialog.

You should also keep in mind that Microsoft notes in its documentation that the provider supports only the Microsoft SQL Server ODBC Driver, the Microsoft ODBC Driver for Oracle, and the Microsoft Access ODBC Driver. As a result, your mileage might vary when using other drivers. To keep up to date on how other drivers are or are not working, consult the microsoft.public.dotnet.framework.odbcnet newsgroup. For example, this newsgroup contains some good information on using the MySQL ODBC driver and the ODBC driver from Oracle.

As you can infer from Figure 14.1, the cost of using the abstraction that ODBC provides is that you must traverse layers of software, which typically slows performance. However, because so many ODBC drivers exist, using the Odbc provider extends the reach of your .NET applications. In addition, you'll notice that there are several kinds of ODBC drivers. Some drivers process both the ODBC calls and the SQL directly, whereas others process the ODBC calls and pass on the SQL to the data store. The former is typical of file-based data stores that have no active database engine, such as Excel and dBASE, whereas the latter is used with enterprise data stores such as Oracle and SQL Server.

Note

Keep in mind that you can't use the OLE DB Provider for ODBC Drivers (MSDASQL) with the OLE DB .NET Data Provider. Using the Odbc provider is your only option for accessing data available only through ODBC.

After you add a reference to the Odbc provider assembly in your project, you can use its types by importing (or using in C#) the Microsoft.Data.Odbc namespace. The classes shown in Figure 14.2 are analogous to those in the other providers, as you might expect.

Figure 14.2. Odbc provider architecture. Just as with the other providers, the Odbc provider includes the standard provider classes.

In addition to the classes shown in Figure 14.2, the namespace contains an OdbcType enumeration analogous to the SqlDbType and OleDbType enumerations. This enumeration is used to specify the type when creating OdbcParameter objects.

Note

To view the mappings of the enumeration to the Common Type System (CTS) types, see the ODBC .NET Data Provider documentation and navigate to the OdbcType enumeration topic.

In the remainder of this section, you'll learn how connection strings and commands are specified and how connection pooling occurs, and you'll look at a simple example of using the Odbc provider to create Excel spreadsheets.

Opening Connections

Perhaps the most difficult part of using the Odbc provider is determining how to formulate a connection string. Just as with ODBC in other environments, you can use either a user or system DSN, a File DSN, or a DSN-less connection. The DSN and File DSN connections can be configured in the ODBC Data Source Administrator found in the Administrative Tools group . An example of each of these three options is shown in the following code snippet:

 // User or System DSN OdbcConnection con = new OdbcConnection("DSN=ComputeBooks"); //File DSN OdbcConnection con = new OdbcConnection("FileDSN=ComputeBooks.dsn"); // DSN-less OdbcConnection con = new OdbcConnection( "Driver={SQL Server} ;Server=ssosa;trusted_connection=yes;Database=ComputeBooks"); 

As you can see, when using the DSN-less connection, the attributes of the connection string outside of the Driver are those specified by the driver itself. In this case, the Server , trusted_connection , and Database attributes are particular to SQL Server. Just as with the other ADO.NET connection objects, some of the attributes correspond to read-only properties of the OdbcConnection object, such as Database , DataSource , and Driver . It should also be noted that the OdbcConnection object doesn't support the Persist Security Info attribute. However, it acts as if it were set to false , so if you do embed the password in the connection string, it won't be accessible after the connection has been opened.

Note

If the ConnectionString property of the OdbcConnection object doesn't include the Driver , FileDSN , or DSN attributes, an OleDbException won't be thrown until the Open method is called. This is unlike OleDbConnection , where the connection string is parsed at the time it is populated (through the constructor or independently).

Although you can use any of the three options to formulate a connection string, the DSN-less connection is preferred because it doesn't incur the overhead of an extra file or registry access. However, because connection strings support only 1024 characters, you must use a DSN or File DSN if you need to set a lot of attributes that would extend the connection string beyond 1024 characters .

Connection Pooling

If you're going to use the Odbc provider for enterprise applications, you need to take connection pooling into consideration because it reduces the load on the data store by allowing clients to reuse connections. The ODBC implementation is analogous to the connection pooling used by the SqlClient provider and the session pooling used by the OleDb provider.

ODBC connection pooling is enabled by the ODBC Driver Manager in version 3.5 (the version installed with MDAC 2.7), and is turned on by default for all ODBC drivers. In a nutshell , ODBC will create up to n pools, where n is the number of processors in the server, for each process (application) on the server that connects using ODBC. Creating multiple pools helps alleviate lock conflicts when multiple threads are running within the application. The pools for each process contain all the connections that have been initiated from the process. When the process initiates a new connection, the ODBC Driver Manager locks and traverses the pools, looking for connections that were created with the same attributes. If a connection is found, it is assigned from the pool and the pool is unlocked. As a result, and as with the other providers, the connection string you use must be identical in order for the connection to be retrieved from the pool.

When you're finished with the OleDbConnection object, you can call its Close method to release it back to the pool. In addition, after all the connections initiated by the application have been closed, you can call the shared ReleaseObjectPool method to release the ODBC environment handle. Calling ReleaseObjectPool when connections are active has no effect. As a result, you'd call ReleaseObjectPool only if the application has closed its connections and will no longer use any ODBC connections.

ODBC connection pooling works only with 32-bit drivers that are thread safe and (under ODBC 3.5) have the string value CPTimeout in the Registry under HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\ driver. The CPTimeout is used to configure how long a connection remains in the pool before it's destroyed . The default value is 60 seconds, but can be changed so that connections remain in the pool for different periods (under ODBC 3.0, connection pooling couldn't be disabled or configured).

If you begin to encounter errors (which could indicate that the driver isn't thread safe), you can disable connection pooling on a per-driver basis in ODBC 3.5 by removing the CPTimeout value in the Registry key. Other than setting CPTimeout , you have no way to configure how many connections remain in the pool or to preallocate connections before they're initiated by a process (something you could do with the Min and Max Pool Size attributes of SqlClient).

The ODBC Driver Manager also uses a retry wait time of 120 seconds that can be changed in the ODBC Data Source Administrator utility. Basically, this setting is used to tell ODBC to wait for 120 seconds before attempting to reconnect if it determines that a data source is unavailable.

Creating Commands

After your connection has been established, you need to create OdbcCommand objects to encapsulate the statement or procedure you want to execute. Although working with the OdbcCommand and OdbcParameter objects is very similar to the command and parameter objects described on Day 10, "Using Commands," there a couple of differences you'll need to be aware of.

First, the OdbcCommand objectsupports only positional (rather than named) arguments when using both inline SQL and a procedure. In addition, it uses a question mark as the placeholder for a parameter. In other words, if you're using inline SQL ( CommandType set to Text ), you should specify your SQL statement like so:

 SELECT * FROM Titles WHERE ISBN = ? AND Title = ? 

Then, when you create your OdbcCommand object, you would add the parameters to the command positionally as follows :

 OdbcCommand com = new OdbcCommand(   "SELECT * FROM Titles WHERE ISBN = ? AND Title = ?", con); com.Parameters.Add(new OdbcParameter("isbn", OdbcType.Text)); com.Parameters.Add(new OdbcParameter("title", OdbcType.Text)); 

Note that it doesn't matter what names you give the parameters; only their position in the parameters collection matters. When the command is executed, the parameters will be plugged into the SELECT statement going left to right, so the parameter at position 0 will be used to specify the ISBN and the one at position 1 will be used to specify the Title .

When calling a procedure ( CommandType set to StoredProcedure ), you must use positional arguments in addition to the ODBC calling syntax using the call keyword as follows:

 {[?=]call procedure-name[([parameter][,[parameter]]...)]} 

Simply populating the CommandText property with the name of the procedure is not sufficient. Notice that to capture the return value from a procedure, you would then need to specify it as the first parameter in the collection. To illustrate this syntax, the following code snippet calls the usp_GetTitles stored procedure:

 OdbcCommand ocom = new OdbcCommand("{call usp_GetTitles(?, ?)} ",ocon); ocom.CommandType = CommandType.StoredProcedure; ocom.Parameters.Add(new OdbcParameter("@isbn",OdbcType.NText)); ocom.Parameters.Add(new OdbcParameter("@title",OdbcType.NText)); 

Note that although the usp_GetTitles stored procedure actually accepts six parameters, you needn't specify them if you're not going to use them because they are defaulted to NULL in SQL Server. If you want to specify that a default value is to be used, most ODBC drivers support using the null keyword in place of the question mark.

Using the Odbc Provider

Of course, one of the primary benefits of using the Odbc provider is that it gives you access to a variety of data stores that would otherwise be difficult to read to and write from. To illustrate this point, Listing 14.1 shows a method called CreateActivityReport . This method is used to create an Excel workbook that contains the total units sold and the revenue generated for each book from a given publisher within a given date range.

Listing 14.1 Creating an Excel workbook. This method creates a workbook and populates it with data from SQL Server.

 virtual void CreateActivityReport(string fileName, string publisher,     DateTime startDate, DateTime endDate) {     // Kill the file if it exists     if (File.Exists(fileName))     {         File.Delete(fileName);     }     // Access SQL Server to get the data     SqlConnection con = new SqlConnection(_connect);     SqlCommand com = new SqlCommand("usp_ActivityReport",con);     // Configure the SqlCommand     com.CommandType = CommandType.StoredProcedure;     com.Parameters.Add(new SqlParameter("@publisher",publisher));     com.Parameters.Add(new SqlParameter("@startDate",startDate));     com.Parameters.Add(new SqlParameter("@endDate",endDate));     // Create the Excel ConnectionString     StringBuilder excelConnect = new StringBuilder();     excelConnect.Append("Driver={Microsoft Excel Driver (*.xls)} ;");     excelConnect.Append("FirstRowHasNames=1;ReadOnly=False;");     excelConnect.Append("Create_DB=" + fileName + ";DBQ=" + fileName);     OdbcConnection oCon = new OdbcConnection(excelConnect.ToString());     try     {         // Create the worksheet         oCon.Open();         OdbcCommand createWS = new OdbcCommand(           "CREATE TABLE ActivityReport (ISBN TEXT,Title TEXT, Author TEXT, " +           "TotalUnits NUMBER, Revenue CURRENCY)",oCon);         createWS.ExecuteNonQuery();         // Build the INSERT statement         StringBuilder ins = new StringBuilder();         ins.Append("INSERT INTO ActivityReport (");         ins.Append("ISBN, Title, Author, TotalUnits, Revenue) ");         ins.Append("VALUES (?, ?, ?, ?, ?)");         // Build the Insert command         OdbcCommand  insCom= new OdbcCommand(ins.ToString(),oCon);         insCom.Parameters.Add(new OdbcParameter("isbn",OdbcType.Text));         insCom.Parameters.Add(new OdbcParameter("title",OdbcType.Text));         insCom.Parameters.Add(new OdbcParameter("author",OdbcType.Text));         insCom.Parameters.Add(new OdbcParameter("units",OdbcType.Int));         insCom.Parameters.Add(new OdbcParameter("revenue",OdbcType.Double));         // Get the data from SQL Server         con.Open();         SqlDataReader dr = com.ExecuteReader();         while (dr.Read())         {           insCom.Parameters["isbn"].Value = dr["ISBN"].ToString();           insCom.Parameters["title"].Value = dr["Title"].ToString();           insCom.Parameters["author"].Value = dr["Author"].ToString();           insCom.Parameters["units"].Value = Convert.ToInt32(dr["TotalUnits"]);           insCom.Parameters["revenue"].Value = dr["Revenue"];           insCom.ExecuteNonQuery();         }         dr.Close();     }     catch (SqlException e)     {         // SQL Server error occurred     }     catch (OdbcException e)     {         // ODBC error occurred     }     catch (Exception e)     {         // Other error occurred     }     finally     {         con.Close();         // Close the spreadsheet         oCon.Close();     } } 

You'll notice in Listing 14.1 that the data for the report comes from the usp_ActivityReport stored procedure in the ComputeBooks database encapsulated in the SqlCommand com . This procedure accepts the publisher and start and end dates as parameters passed into the method.

After the SQL Server objects are instantiated and configured, the method uses a StringBuilder to build the ConnectionString property of the OdbcConnection object. In this case, the Microsoft Excel ODBC Driver is specified. This driver is shipped with the Microsoft ODBC Desktop Database Drivers (which also include drivers for Access, dBASE, Paradox, and Text) and is a Jet-based driver.

Note

There are other ways to access Excel from .NET. Options include using COM-based automation through the interoperation services in .NET to program against the Excel object model, and using the Jet OLE DB provider, which is also capable of opening Excel workbooks. Using automation, for example, gives you more control over the output because you can programmatically change fonts, colors, and cell sizes. The technique shown here is meant to show only how you could use the Odbc provider to move data from one data source to another.

In the connection string, the Driver attribute specifies the ODBC driver, and the driver-specific attributes FirstRowHasNames , ReadOnly , and Create_DB are used to specify that the first row of the worksheet will contain the column names, that the worksheet is not read-only, and that the file specified in the DBQ attribute is to be created when the connection is opened, respectively.

Within the try block, the OdbcConnection is opened, which creates the Excel workbook. The worksheet is then created by executing the createWS OdbcCommand object, whose CommandText property is set to a CREATE TABLE statement. A second OdbcCommand object is then created to encapsulate the INSERT statement that will be used to insert the individual rows. Next, the SQL Server data is retrieved using the ExecuteReader method and is traversed. Within the while loop, the insCom command's parameters are populated with the data from data reader, and the command is executed to insert the new row into the Excel worksheet. Each row is inserted by executing the insCom OdbcCommand .

You'll notice that the various catch blocks can be used to differentiate between errors generated by the Odbc provider and those generated by the SqlClient provider. The finally block closes both the connection to SQL Server and the workbook by closing the OdbcConnection object.

A client could then call the method like so:

 CreateActivityReport("sams.xls","Sams",   new DateTime(2000,1,1), new DateTime(2002,12,31)); 

for RuBoard