Creating Portable Database Applications

There are issues that you must consider if your application must work with multiple database types.

Oracle

If you use the LoadDataSet method to load data, it will not convert Guid and Boolean data types. This is because the architecture cannot determine if the value of the data is Guid or simply an array of Byte. Data will be returned as columns containing an array of Byte.

When you create a DbCommand object for a stored procedure that returns multiple cursors, you must pass an array of objects to the GetStoredProcCommand method. The size of the array must be equal to the number of cursors that are returned by the stored procedure. For example, the following code demonstrates how to pass an object array to GetStoredProcCommand for a stored procedure that returns two cursors.

Database db = DatabaseFactory.CreateDatabase();

object results = new object[2];
DbCommand cmd = db.GetStoredProcCommand("GetCustomersAndSuppliers", results);

'Usage
Dim results As Object = New Object(2) {}
Dim cmd As DbCommand = db.GetStoredProcCommand("GetCustomersAndSuppliers", results)

You do not have to pass an object array if the stored procedure returns only a single cursor.

Suggestions for Creating Portable Database Applications

Here are some suggestions for creating portable database applications:

• Avoid using database-specific tokens with stored procedure parameter names. The Database-derived classes for specific providers include code to adjust parameter names as required. For example, do not include the "@" character when supplying stored procedure parameter names to a SQL Server database. The following code shows how to call the AddInParameter method to create a parameter with the name CategoryID. When this code is executed using the SqlDatabase object, the provider prefaces the parameter name with "@".

  Database db = DatabaseFactory.CreateDatabase();
  DbCommand cmd = db.GetStoredProcCommand("GetProductsByCategory");
  db.AddInParameter(cmd, "CategoryID", DbType.Int32, 100);
  

  'Usage
  Dim db As Database = DatabaseFactory.CreateDatabase()
  Dim cmd As DbCommand = db.GetStoredProcCommand("GetProductsByCategory")
  db.AddInParameter(cmd, "CategoryID", DbType.Int32, 100)
  

• Always retrieve parameter values through the Database object.

• Consider the case sensitivity of the back-end relational database management system (RDBMS). For example, string comparison in SQL Server 2000 is not case sensitive, but it is case sensitive in both Oracle 8i and DB2. To develop a portable application, you should write your string comparison logic either to be case insensitive or to force the application to store only one case for columns used in a comparison operation.

• Avoid using RDBMS-specific data types, such as OracleBlob.

• Avoid using return values when executing your stored procedure. Instead, use output parameters.

• When adding parameters to the parameter collection, make sure that the order in your application code matches the order in the database. The OLE DB provider executes the stored procedure with parameters in the order they appear, instead of allocating them by name, so it is important that you add the parameters to the collection in the correct order.

• If you must use in-line SQL in your application code, make sure your SQL syntax is valid across all the database types that your application will run against. Also, beware of SQL injection risks.

• Avoid passing null values to the stored procedure's parameter of value type. Doing so may not work if you need a portable interface that uses DB2 with SQLJ stored procedures.