Assuring Database Portability with ODBC and .NET

• Download source files - 9.33 Kb

Introduction

Most of the time (at least me) we are developing applications aimed to a specific database server and platform. But what happens if we need to develop a solution that might access different database engines? If we do not have a development platform that might access several databases, we will have to create separate code for each database engine, ending in a maintenance nightmare. With OOP programming, the code needed to access multiple databases becomes more maintainable, but we still need to write specific code for each database; you can see an example at Pet Shop 3.0 blueprints.

ODBC, although not a magical or perfect solution, it allows us to access several databases with a common code. In this article I present some points to consider when using ODBC, and also a builder class designed to generate SQL ODBC sentences with the appropriate escapes. To find more information about ODBC you can go to these links:

• ODBC at Microsoft
• Universal Data Access
• ODBC Drivers

ODBC

ODBC is an additional layer that translates generic SQL sentences with ODBC escapes into specific database SQL syntax, allowing accessing multiple databases with common code. The only thing needed to access a different database is to specify another data source.

It is important to note that ODBC does not replace the database client software. It is only the translation layer and always requires the database client software installed on the computer that accesses the database. Also, a driver is needed for the database we are connecting to. Actually, most databases do have an ODBC driver.

One of the major drawbacks of ODBC is the speed. This has several reasons:

• It is an additional layer that abstracts the SQL syntax and has to translate the SQL ODBC sentences into database specific SQL syntax.
• It is also a communication bridge. The application does not communicate directly to the database client. All the data is passed through ODBC, forwards and backwards, which adds some extra time.
• There might be some data types transformation.
• The application has to be scaled down to a minimum common specification, not using those powerful functions and features provided with specific database engines. For example, if you rely on triggers for your design, you can not use your application with MS Access.

Another drawback is compatibility. As mentioned, ODBC is an abstraction layer and requires a database driver. In fact, for some databases you might find more than one driver. Applications that work fine with one driver might fail with a different one. Also there might be performance differences between diverse drivers for the same database.

ODBC escapes

Let’s think of ODBC escapes as a new SQL syntax that will be translated by the driver into specific SQL syntax for the database engine we are accessing. The ODBC escape tokes must be enclosed within “{}”.

JOINS “oj”

table_l: Table at the left
table_r: Table at the right
pk: Primary key
fk: Foreign key

LEFT OUTER JOIN

SELECT [fields list] FROM {oj table_l 
      LEFT OUTER JOIN table_r ON table_l.pk = table_r.fk }

INNER JOINS

For INNER JOINs, a generic SQL sentence like this can be used:

SELECT [fields list]  FROM table_l, table _r WHERE table _l.pk = table _r.fk

Or using the ODBC escape:

SELECT [fields list] FROM {oj table_l INNER JOIN 
             table_r ON table_l.pk = table_r.fk }

Date, Time and Timestamp “d, t and ts”

The WHERE, INSERT and UPDATE clauses require the following escapes to reference date, time and timestamps:

Date: {d ‘yyyy-mm-dd’ }
Time: {t ‘hh:mm:ss’ }
Timestamp: {ts ‘yyyy-mm-dd hh:mm:ss’ }

Functions “fn”

The “fn” escape allows executing scalar functions.

Examples:

{fn UCASE(field) } LIKE ‘search string%’     // Starts with
{fn UCASE(field) } LIKE ‘%search string%’    // Contains

A reference to all the escape functions can be found here.

Data Source Names and Connection string

To connect to a database with ODBC, you can use Data Source Names or dynamic connection strings.

Regardless the string used, the connection is open the same way:

OdbcConnection con = new OdbcConnection(connectionString);
con.Open();

Where the connectionString might be either one.

Data Source Names

A data source name (DSN) has to be created with the ODBC panel. The information can be found here.

Connection strings

Dynamic connection strings can be defined at run time without having to go through the ODBC control panel, being more flexible and configurable. In the provided example, dynamic connection strings are used.

Connection strings for different database servers can be found here.

Designing the databases

There are several points to consider when designing databases that are going to be used with the same code through ODBC.

• For integers and decimals, use specific types (Integer, Double, Smallint) instead of scale and precision (Numeric(x,y)). This will prevent casting exceptions when reading the data.
• Always test your application with all the databases that you are targeting. Do no rely on one database and bet that if it works with SQL Server it will work perfectly with Oracle or DB2.
• Document the ODBC and the driver versions; it is helpful to support your application when deployed. There might be compatibility problems with different drivers for the same database.
• Develop a standard “ID generator”. All the databases provide a different mechanism to obtain a unique ID and ODBC does not accomplish this.

Installing the ODBC provider

The Visual Studio .NET with the 1.0 framework does not include the ODBC provider. The provider can be downloaded here.

Remember to add a reference to Microsoft.Data.Odbc.dll to your project.

The code

Writing SQL ODBC sentences can be monotonous and error prone. The .NET framework provides the SqlCommandBuilder class, but can only be used to create the SQL sentences using parameters. To create dynamic SQL ODBC sentences, a class that handles the different data types and escapes will be useful.

Using it

With the builder class, INSERT and UPDATE SQL ODBC sentences can be created.

To instantiate the class:

QueryBuilder qb = new QueryBuilder();

To create an UPDATE, a key or keys must be defined:

qb.AddKey(field name, field value, FieldType);

The keys will be used to construct the WHERE clause. If no keys are defined, an INSERT clause will be created.

The builder class can handle (FieldType):

• String, replacing single quotes for double quotes.
• Double.
• Integer.
• Boolean, converting true to 1 and false to 0.
• Date, using the {d ‘yyyy-mm-dd’ } ODBC Escape.
• TimeStamp, using the {ts ‘yyyy-mm-dd hh:mm:ss’ } ODBC Escape.
• Time, using the {t ‘hh:mm:ss’} ODBC Escape.

Internationalization is handled for the doubles, dates and times.

To obtain the SQL ODBC sentence:

// It will generate an INSERT or UPDATE depending on the keys
qb.ToString();  

INSERT example:

QueryBuilder qb = new QueryBuilder();
qb.Table = "person";
qb.AddField("id", 1, FieldType.Integer);
qb.AddField("name", "My name's", FieldType.String);
qb.AddField("hireDate", System.DateTime.Now , FieldType.TimeStamp);
qb.AddField("active", true, FieldType.Boolean);
qb.AddField("salary", 1234.56, FieldType.Double);
textBox.Text = qb.ToString();

UPDATE example:

QueryBuilder qb = new QueryBuilder();
qb.Table = "person";
qb.AddKey("id", 1, FieldType.Integer);
qb.AddField("name", "My name's", FieldType.String);
qb.AddField("hireDate", System.DateTime.Now , FieldType.TimeStamp);
qb.AddField("active", true, FieldType.Boolean);
qb.AddField("salary", 1234.56, FieldType.Double);
textBox.Text = qb.ToString();

Conclusion

ODBC is very powerful and flexible to port applications to different databases, and with the help of a query builder that handles ODBC escapes and international formatting, the development time to develop for multiple RDBMS is significantly reduced.

Performance? Yes, there is a performance price to pay, but if you pin-point the application, you will find that only a very small part of the application can be improved using specific database code. Data entries (Inserts, Updates, Read and Deletes) are probably the less prone to be optimized with specific code, so we will only have to concentrate on few specific business processes and develop them with specific code; may be, using factories, so we can still use the same application with different RDBMS.

On web sites with hundreds or thousands of simultaneous users, scalability might be a problem and you have to measure the impact of using ODBC.