Part V: Appendixes

Common Cross-Provider Concerns

Microsoft .NET data providers offer better control and performance because each provider can be tuned for the specific back-end with which it's designed to communicate. However, the move to separate .NET data providers has caused some confusion in the developer community.

Writing Provider-Portable Code

Let's say you've built an application that uses the SQL Client .NET Data Provider. Time goes by and, due to customer demand, you decide to modify your application so it can communicate with either SQL Server or Oracle databases. The more code you have in your application that relies on the objects in the SQL Client .NET Data Provider, the more code you'll have to change in your application.

However, if you've separated your code into components and you've made sure that the components interact through generic interfaces, such as DataSet, DataTable, IDataReader, and DbDataAdapter, you'll have to change code in only specific components.

Let's look at two examples using this approach. In the first example, we'll create a function that uses the SQL Client .NET Data Provider internally but returns data through the generic DataTable interface. We'll then show how we can convert the function to use a different provider without having to change the code that calls the function. The second example uses a similar approach but relies on a function that returns a DataAdapter using the generic IDbDataAdapter.

The following code snippet calls a function called GetOrdersForCustomer that accepts a string that contains a value for the CustomerID column and returns a DataTable that contains the orders for that particular customer. The function uses a parameterized SqlDataAdapter internally.

Visual Basic .NET

Dim strCustomerID As String = "ALFKI" Dim tblOrders As DataTable tblOrders = GetOrdersForCustomer(strCustomerID) Private Function GetOrdersForCustomer(CustomerID As String) As DataTable     Dim strSQL, strConn As String     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _              "FROM Orders WHERE CustomerID = @CustomerID"     strConn = "Data Source=(local)\NetSDK;" & _               "Initial Catalog=Northwind;Trusted_Connection=Yes;"     Dim da As New SqlDataAdapter(strSQL, strConn)     Dim param As SqlParameter     param = da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5)     param.Value = CustomerID     Dim tbl As New DataTable("Orders")     da.Fill(tbl)     Return tbl End Function

Visual C# .NET

string strCustomerID = "ALFKI"; DataTable tblOrders; tblOrders = GetOrdersForCustomer(strCustomerID); private DataTable GetOrdersForCustomer(string CustomerID) {     string strSQL, strConn;     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +              "FROM Orders WHERE CustomerID = @CustomerID";     strConn = "Data Source=(local)\\NetSDK;" +               "Initial Catalog=Northwind;Trusted_Connection=Yes;";     SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);     SqlParameter param;     param = da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);     param.Value = CustomerID;     DataTable tbl = new DataTable("Orders");     da.Fill(tbl);     return tbl; }

As we described in the scenario earlier in the chapter, you might need to modify your application to support other back-end databases. The GetOrdersForCustomer function uses the SQL Client .NET Data Provider internally but uses generic data types—string and DataTable—for its parameter and return value. So we can rewrite the function to use a different .NET data provider without having to change the code that consumes the DataTable. In the following code snippet, we've changed the code inside the GetOrdersForCustomer function but we have not changed the function's signature.

Visual Basic .NET

Private Function GetOrdersForCustomer(CustomerID As String) As DataTable     Dim strSQL, strConn As String     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _              "FROM Orders WHERE CustomerID = :CustomerID"     strConn = "Data Source=MyOracleDatabaseAlias;" & _               "User ID=MyUserID;Password=MyPassword;"     Dim da As New OracleDataAdapter(strSQL, strConn)     Dim param As OracleParameter     param = da.SelectCommand.Parameters.Add(":CustomerID", OracleType.Char, 5)     param.Value = CustomerID     Dim tbl As New DataTable("Orders")     da.Fill(tbl)     Return tbl End Function

Visual C# .NET

private DataTable GetOrdersForCustomer(string CustomerID) {     string strSQL, strConn;     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +              "FROM Orders WHERE CustomerID = :CustomerID";     strConn = "Data Source=MyOracleDatabaseAlias;" +               "User ID=MyUserID;Password=MyPassword;";     OracleDataAdapter da = new OracleDataAdapter(strSQL, strConn);     OracleParameter param;     param = da.SelectCommand.Parameters.Add(":CustomerID", OracleType.Char, 5);     param.Value = CustomerID;     DataTable tbl = new DataTable("Orders");     da.Fill(tbl);     return tbl; }

Another approach is to have your data access code return objects via the generic interfaces that the various .NET data providers support. For example, we could have created a function that instantiates a SqlDataAdapter and returns the DataAdapter via the generic IDbDataAdapter interface.

Visual Basic .NET

Dim daOrders As IDbDataAdapter = GetOrdersAdapter() Dim strCustomerID As String = "ALFKI" Dim param As IDbDataParameter param = CType(daOrders.SelectCommand.Parameters(0), IDbDataParameter) param.Value = strCustomerID Dim ds As New DataSet() Dim tblOrders As DataTable = ds.Tables.Add("Orders") daOrders.Fill(ds) Private Function GetOrdersAdapter() As IDbDataAdapter     Dim strSQL, strConn As String     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _              "FROM Orders WHERE CustomerID = @CustomerID"     strConn = "Data Source=(local)\NetSDK;" & _               "Initial Catalog=Northwind;Trusted_Connection=Yes;"     Dim da As New SqlDataAdapter(strSQL, strConn)     da.TableMappings.Add("Table", "Orders")     da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5)     Return da End Function

Visual C# .NET

IDbDataAdapter daOrders = GetOrdersAdapter(); string strCustomerID = "ALFKI"; IDbDataParameter param; param = (IDbDataParameter) daOrders.SelectCommand.Parameters[0]; param.Value = strCustomerID; DataSet ds = new DataSet(); DataTable tblOrders = ds.Tables.Add("Orders"); daOrders.Fill(ds); private IDbDataAdapter GetOrdersAdapter() {     string strSQL, strConn;     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +              "FROM Orders WHERE CustomerID = @CustomerID";     strConn = "Data Source=(local)\\NetSDK;" +               "Initial Catalog=Northwind;Trusted_Connection=Yes;";     SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);     da.TableMappings.Add("Table", "Orders");     da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);     return da; }

We could later change the GetOrdersAdapter function as shown here to create and return an OracleDataAdapter through the IDbDataAdapter interface without having to change the code that uses the IDbDataAdapter interface.

Visual Basic .NET

Private Function GetOrdersAdapter() As IDbDataAdapter     Dim strSQL, strConn As String     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _              "FROM Orders WHERE CustomerID = :CustomerID"     strConn = "Data Source=MyOracleDatabaseAlias;" & _               "User ID=MyUserID;Password=MyPassword;"     Dim da As New OracleDataAdapter(strSQL, strConn)     da.TableMappings.Add("Table", "Orders")     da.SelectCommand.Parameters.Add(":CustomerID", OracleType.Char, 5)     Return da End Function

Visual C# .NET

private IDbDataAdapter GetOrdersAdapter() {     string strSQL, strConn;     strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +              "FROM Orders WHERE CustomerID = :CustomerID";     strConn = "Data Source=MyOracleDatabaseAlias;" +               "User ID=MyUserID;Password=MyPassword;";     OracleDataAdapter da = new OracleDataAdapter(strSQL, strConn);     da.TableMappings.Add("Table", "Orders");     da.SelectCommand.Parameters.Add(":CustomerID", OracleType.Char, 5);     return da; }

Determining the Correct .NET Provider Data Type

If you understand the basic object model for .NET data providers, writing code for one provider or another is fairly simple. As I jump from one .NET data provider to another, I sometimes forget the appropriate format for that provider's connection strings or how to create parameterized queries for that provider, but that's still rather basic stuff.

On the public ADO.NET newsgroups, one question that has surfaced repeatedly for the ODBC .NET Data Provider is "How do I determine the correct 'provider' data type for my parameters?" Developers most often face this question when they generate their own updating logic code for their DataAdapter objects. For example, say you're using the following query in your DataAdapter object's UpdateCommand:

UPDATE Orders SET CustomerID = ?, OrderDate = ?      WHERE OrderID = ?

What is the correct provider type for the OrderDate parameter? Hopefully, you'll be able to determine the correct type based on the data type for the column in your database. But even then, things aren't entirely clear. If you're working with a SQL Server Northwind database and the ODBC .NET Data Provider, you would set the OdbcParameter object's OdbcType property to OdbcType.DateTime. But if you're using the OLE DB .NET Data Provider with the same data, you would set the OleDbParameter object's OleDbType property to OleDbType.DBTimeStamp. What's a poor developer to do?

The best answer that I can provide is to use the DataReader object's GetSchemaTable method. If the parameter corresponds to a column in your database, create a query that retrieves data from that column. Then use a Command object to execute that query using the ExecuteReader method. Call the GetSchemaTable method on the resulting DataReader object. Each row in the DataTable returned by GetSchemaTable corresponds to a column in the original query. Find the row in the DataTable returned by GetSchemaTable that corresponds to the column for your parameter. Examine the contents of the ProviderType column for that row and translate the integer to the appropriate enumeration.

Here's an example that determines the appropriate OdbcType for the OrderDate column in the Northwind database.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Driver={SQL Server};Server=(local)\NetSDK;" & _           "Database=Northwind;Trusted_Connection=Yes;" Dim cn As New OdbcConnection(strConn) cn.Open() strSQL = "SELECT OrderDate FROM Orders" Dim cmd As New OdbcCommand(strSQL, cn) Dim rdr As OdbcDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly) Dim tbl As DataTable = rdr.GetSchemaTable() rdr.Close() cn.Close() Dim intOrderDateType As Integer intOrderDateType = CType(tbl.Rows(0)("ProviderType"), Integer) Dim odbcOrderDateType As OdbcType odbcOrderDateType = CType(intOrderDateType, OdbcType) Console.WriteLine("OrderDate") Console.WriteLine(vbTab & "ProviderType = " & intOrderDateType) Console.WriteLine(vbTab & "OdbcType = " & odbcOrderDateType.ToString())

Visual C# .NET

string strConn, strSQL; strConn = "Driver={SQL Server};Server=(local)\\NetSDK;" +            "Database=Northwind;Trusted_Connection=Yes;"; OdbcConnection cn = new OdbcConnection(strConn); cn.Open(); strSQL = "SELECT OrderDate FROM Orders"; OdbcCommand cmd = new OdbcCommand(strSQL, cn); OdbcDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly); DataTable tbl = rdr.GetSchemaTable(); rdr.Close(); cn.Close(); int intOrderDateType = (int) tbl.Rows[0]["ProviderType"]; OdbcType odbcOrderDateType = (OdbcType) intOrderDateType; Console.WriteLine("OrderDate"); Console.WriteLine("\tProviderType = " + intOrderDateType); Console.WriteLine("\tOdbcType = " + odbcOrderDateType.ToString());

If you don't feel like writing all this code, you can simply use the Ad Hoc Query Tool that's included on the book's companion CD and is discussed in Appendix B. Figure A-2 shows the Ad Hoc Query Tool displaying schema information for the results of a query, including a column that contains the .NET data provider-specific provider type. As you can see in the figure, the OrderDate column in the database corresponds to the DateTime value in the OdbcType enumeration.

Figure A-2

Using the Ad Hoc Query Tool to retrieve "provider" type information