Creating Portable Database Applications

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.

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 byte[]. Data will return as byte[] columns.

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 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 dbCommand = db.GetStoredProcCommand("GetProductsByCategory");
    
    db.AddInParameter(dbCommand, "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 compare logic either to be case insensitive or to force the application to store only one case for columns used in a compare 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 by order instead of by names, so it is important that you add the collections 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.
  • 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.

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The latest Enterprise Library information can be found at the Enterprise Library site.
Show: