Overview

My goal is to be able to write .NET 2.0 provider support code that is database independent. The Provider model allows you to write database dependent classes (for membership, role, etc), but I really wanted to be able to build applications using database independent classes and the provider model.

There are some specific cases where it seems I have to use database specific code:

1. Creation of the database.
2. Creation of database table structure (using a bunch of database specific SQL text has always made me feel icky)
3. General SQL statements (quoted identifiers vs. brackets, different key words, data types, etc).

I decided to create my own class architecture and wrap it around the DbProviderFactory to extend the functionality. I tried to make it address my immediate needs, and keep it open for greater extensiveness in the future.

I was not sure if what I created was going to be considered a Factory or a Provider, so I decided to not use either terminology.  I was creating a way to store information in a database.  I thought I may want to store information in other places too (like a web service, the file system, etc) later, so I created an object and called it BalStorageDatabase.

The DbProviderFactory is still available as a public property, so none of the functionality already available in .NET 2.0 is lost.  If you want to use specific code outside of the generic BalStorageDatabase, you can always check the type of the DbProviderFactory that is returned and write some database specific code.

I added methods to address my three immediate needs to encapsulate database specific code:

1. CreateDataStore() - Creates a database specific file in the directory specified in the configuration file.
2. CreateTable(DataTable loTable) - Creates a table in the database (if one does not exist) based on the column names and types in the DataTable that is passed to it.
3. GetCommandText(string lsBaseCommandText) - Modifies generic database SQL to be database specific.  Right now it's a simple text replace functionality, but it can be extended in the future.

Details

Configuration

Just like the DbFactoryProvider, the database specific classes are specified in the application's configuration file. Getting the configuration file right seemed to be the most challenging task, since errors in configuration are only shown at run time.

The first thing is to make sure that the config file is set up to support the .NET 2.0 DbProviderFactory subclasses that will be used.  These are the configs that are already in my machine.config:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" 
description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" 
type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" 
description=".Net Framework Data Provider for Oracle" 
type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" 
description=".Net Framework Data Provider for SqlServer" 
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" 
description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" 
type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>		  		

These are the ones I added to the app.config support Firebird and SQLite.

<system.data>
<DbProviderFactories>
<add name="Firebird Data Provider" invariant="FirebirdSql.Data.FirebirdClient" 
description=".Net Framework Data Provider for Firebird" 
type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory, FirebirdSql.Data.FirebirdClient" />
<add name="SQLLite Data Provider" invariant="System.Data.SQLite" 
description=".Net Framework Data Provider for SQLLite" 
type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>
</system.data>

To be able to specify providers for the BalStorageDatabase, I needed to be able to have a section like the DbProviderFactories section in the configuration file. I created a class called BalStorageDatabaseSection that allows specifying the providers, and a static class called BalStorageConfiguration that I could easily use to get the configuration information.

In the configuration file, I have to "register" my special configuration class.

<configSections>
<sectionGroup name="BalConsultingNet.Storage">
<section name="BalStorage.Database"
type="BalConsultingNet.Data.Storage.BalStorageDatabaseSection,BalStorage" 
allowDefinition="Everywhere"
allowExeDefinition="MachineToApplication" 
restartOnExternalChanges="true" />
</sectionGroup>
</configSections>

Then I could specify the database specific classes that implment BalStorageDatabase in the configuration file.

<BalConsultingNet.Storage>
<BalStorage.Database defaultProvider="Access" dataDirectory="app_data">
<providers>
<add name="SQLite" 
type="BalConsultingNet.Data.Storage.SQLiteStorageDatabase,BalStorage"
connectionStringName="SQLiteConnection" />
<add name="Firebird" 
type="BalConsultingNet.Data.Storage.FbStorageDatabase,BalStorage"
connectionStringName="FirebirdConnection" />
<add name="Access" 
type="BalConsultingNet.Data.Storage.AccessStorageDatabase,BalStorage"
connectionStringName="AccessConnection" />
</providers>
</BalStorage.Database>
</BalConsultingNet.Storage>

The final part of the configuration is the connection strings that will be used.

<connectionStrings>
<add name="FirebirdConnection" 
connectionString="Database=|DataDirectory|EmbedFirebird.fdb;Integrated Security=True;server=server;Password=PassDefault;ServerType=1;UserID=UserDefault"
providerName="FirebirdSql.Data.FirebirdClient" />
<add name="SQLiteConnection" 
connectionString="Data Source=|DataDirectory|EmbedSQLite.db3"
providerName="System.Data.SQLite" />
<add name="AccessConnection" 
connectionString="Data Source=|DataDirectory|EmbedAccess.mdb;Provider=Microsoft.Jet.OLEDB.4.0"
providerName="System.Data.OleDb" />
</connectionStrings>

Usage

Using the code is a matter of getting an instance of BalStorageDatabase using the static BalStorageDatabaseManager class.

BalStorageDatabase loDatabase = BalStorageDatabaseManager.DefaultDatabase;
//Create the database
loDatabase.CreateDataStore();
//Create a table
DataTable loTable = new Table();
loTable.TableName = "tPerson";
loTable.Columns.Add("id",new Guid().GetType());
loTable.Columns.Add("NameFirst",Type.GetType("System.String"));
loTable.Columns.Add("NameLast",Type.GetType("System.String"));
loTable.Columns.Add("NameMiddle",Type.GetType("System.String"));
DataColumn[] loKeys = new DataColumn[1];
loKeys[0] = _oDataTable.Columns["id"];
loDataTable.PrimaryKey = loKeys;
loDatabase.CreateTable(loTable);
//Execute a query
string lsQry = "SELECT [NameFirst],[NameLast] FROM [tPerson] WHERE [NameMiddle]='a'";
string lsCommandText = loDatabase.GetCommandText(lsQry);
DbCommand loCommand = loDatabase.CreateCommand();
loCommand.CommandText = lsCommandText;
DataTable loTable = BalStorageDatabase.ExecuteDataTable(loCommand);

BalStorage-1.0.zip (666.00 bytes)