Build database independent ADO.NET programs with Mimer Provider Manager

• Download Visual Studio project - 10 Kb
• Download source files only - 4 Kb

Introduction

This article will show you how to write applications that do not depend on a specific Data Provider or database. This is accomplished by using the Mimer Provider Manager framework.

Introduction to Mimer Provider Manager

When you develop applications that access databases using ADO.NET you use different Data Providers for different databases. Because of this you cannot simply write database independent applications. The Mimer Provider Manager (Mpm) system makes it easy to build efficient applications that are possible to use against different databases without changing any code. This is achieved in Mimer Provider Manager with a unified interface that encapsulates the different vendor specific database interfaces. You can see Mpm as an ADO.NET provider dispatcher that uses different plugins to access different underlying ADO.NET providers. From the application perspective, Mpm is just another ADO.NET provider. Currently, plugins are developed for Oracle, Mimer SQL, SQL Server, ODBC, and OLEDB. There is also a generic plugin that can use any underlying provider using reflection at a slightly higher cost.

Besides the plugins there is a concept of SQL Filters in Mpm. This allows you to write filter that modifies the SQL sent to the database. This way you can do automatic conversion between different SQL dialects and achieve true database independence. A filter for handling different types of parameter markers is included in the first version om Mpm. Since the standard ADO.NET naming scheme is used there is no more work to convert an existing application to use Mpm than it is to convert it to use any other provider. Mimer Provider Manager Administrator is a GUI that is used to define data sources that will be available to the applications. Since you only provide the data source name in your applications you can easily switch database without touching the source code at all. The administrator is also used to tell the system which SQL Filters you want to use and to load new provider plugins. This means you can apply a SQL Filter without changing your code as well. You can for example apply the shipped ParameterMarkers filter that handles different parameter markers used in your application. The data source definitions can be stored in a configuration file and shipped with your application so you do not have to configure anything on the clients. Mimer Provider Manager Administrator also handles the cumbersome connection strings for you. This is done with a Property Grid where you can select amongst all the available options for each provider. Convertion between different providers connection string syntax is done automatically for you.

Mpm is integrated in Visual Studio .NET so you can drag MpmCommands, MpmConnections and so onto your different forms. The documentation is integrated as well so that it can be read along with Microsoft’s standard documentation including support for dynamic help. There is also a mechanism that has been developed whereby new plugins are generated. This is done via an new project wizard in Visual Studio .NET. New plugins will therefore rapidly be added. Mpm supports the concept of side-by-side execution so that several versions can be installed in parallel on a single computer.

The data browser example

As an example of how to work with Mpm we will develop a Windows Forms based data browser. The user can select among all available data sources and their corresponding tables in two combo boxes. When the user has selected a data source and a table and presses the Load button a DataSet will be created and shown in a updateable DataGrid. If the rows are updated and the Load button is pressed the changes are sent to the database. The architecture of ADO.NET allows you to work either connected to the database or in a disconnected manner. The disconnected model uses a DataAdapter where the DataAdapter handles the connecting/disconnecting automatically. This is the way we will do most of the work in this example application. We will, however, use an MpmCommand and MpmDataReader to get the available tables in the database.

The first thing you have to do is to set up the Mpm framework on your machine.

1. Download the latest binary release from developer.mimer.com/mpm or www.sourceforge.net/projects/mimerpm
2. Start the Windows installer. This will install Mpm in the folder you specify, install the required Mpm assemblies in the Global Assembly Cache, and integrate Mpm with Visual Studio .NET if available.
3. Start the Mimer Provider Manager Administrator and create Data Sources for your databases. It’s also possible to create data sources programmatically from your applications if you want to.

When you are working with Mpm you can use the MpmInfo class in the Mimer.Mpm.Data.Extensions namespace to get runtime information about the system, for example registered data sources, plugins and SQL filters. When the data browser is started this is used to populate the ComboBox with data sources:

comboDataSource.Items.AddRange(MpmInfo.DataSourceNames);

When a data source is selected the table ComboBox is populated with the corresponding tables. This is done with an ordinary SQL statement querying the standardized INFORMATION_SCHEMA view. First the DataSourceChanged(...) event handler is called where a new MpmConnection is created. Then the GetTables() method is called that queries the databases and fetches the tables:

private void DataSourceChanged(object sender, System.EventArgs e)
{
   mpmConnection = new MpmConnection();
   mpmConnection.ConnectionString = "Data Source Name=" + 
     comboDataSource.SelectedItem.ToString();
   GetTables();
}


private void GetTables()        
{
   MpmCommand tableCommand = null;
   MpmDataReader reader = null;
   comboTables.Items.Clear();
   try
   {
      //The connection should not be open when we get here
      if(mpmConnection.State == ConnectionState.Open)
      {
         throw new Exception("The connection shouldn't be open here");
      }
      if(mpmConnection.DataSourceDescriptor.DbmsType == MpmDbmsTypes.Oracle)
      {
         throw new Exception("Oracle does not have INFORMATION_SCHEMA");
      }
      mpmConnection.Open();
      tableCommand = mpmConnection.CreateCommand();
      tableCommand.CommandText = 
        "select table_schema, table_name from " + 
        "information_schema.tables where table_type='BASE TABLE'";
      reader = tableCommand.ExecuteReader();
      comboTables.BeginUpdate();
      while(reader.Read())
      {
         comboTables.Items.Add(reader.GetString(0) + "." + 
           reader.GetString(1));
      }
      comboTables.EndUpdate();
   }
   catch(Exception ex)
   {
      errorHandler.ShowException(this, ex);
   }
   finally
   {
      try
      {
         if(reader != null)
         {
            reader.Close();        
         }
      }
      catch(Exception ex)
      {
         errorHandler.ShowException(this, ex);
      }
      try
      {
         if(mpmConnection.State != ConnectionState.Closed)
         {
            mpmConnection.Close();
         }
      }
      catch(Exception ex)
      {
         errorHandler.ShowException(this, ex);
      }
   }
}

The class called errorHandler is used to handle possible exceptions and we will show it later on in this article. As you can see above the only thing that differs from using any other provider is the Mpm naming schema and a simplified connection string syntax. When the user press the Load button a method named LoadDataSet(string tableName) is called. The table name is used to construct a select command that is used by a MpmDataAdapter to create a DataSet that we bind to our DataGrid. This is all that is needed to show the content of any table in practically any database:

private void LoadDataSet( string tableName)
{
   try
   {    
      StringBuilder selCmd = new StringBuilder();
      selCmd.Append("select * from ");
      selCmd.Append(tableName);
      mpmDataAdapter = new MpmDataAdapter(selCmd.ToString(), 
         mpmConnection);
      ds = new DataSet();
      mpmDataAdapter.Fill(ds, tableName);
      dataGrid.CaptionText = tableName;
      dataGrid.DataSource = ds.Tables[tableName].DefaultView;
   }
   catch(Exception ex)
   {
      errorHandler.ShowException(this, ex);
   }
}

The method that handles the updates is equally simple. The method is called when the user have edited some columns and press the Update button in the GUI. A new DataSet that contains all the changed rows is created. If the new DataSet contains any rows an MpmCommandBuilder is attached to the MpmDataAdapter and the Update method is called on the MpmDataAdapter:

private void UpdateDataSet()
{
   try
   {
      DataSet ds2 = ds.GetChanges();
      if(ds2 != null)
      {
         MpmCommandBuilder mBuild = new MpmCommandBuilder(mpmDataAdapter);
         mpmDataAdapter.Update(ds2, ds.Tables[0].TableName);
         ds.AcceptChanges();
      }
   }
   catch(Exception ex)
   {
      errorHandler.ShowException(this, ex);
   }
}

Errors and warnings

In all examples we are using a class called ErrorHandler to take care of our exceptions. When working with a database with Mpm you can catch MpmException and use the MpmError class to get more information.

catch(MpmException me)
{
   StringBuilder msg = new StringBuilde();
   foreach(MpmError mErr in me.Errors)
   {
      ExtractErrors(mErr, msg);
   }
   MessageBox.Show(msg.ToString(), 
   "Caught a MpmException",
   MessageBoxButtons.OK);
}

ExtractErrors(mErr, msg) is a helper method we can use for both errors and warnings:

private void ExtractErrors(MpmError mErr, StringBuilder msg)
{
   if(mErr.Message.Length > 0)
   {
      msg.Append("\r\nError message: ");
      msg.Append(mErr.Message);
   }
   if(mErr.SQLState.Length > 0)
   {
      msg.Append("\r\nSQLState: ");
      msg.Append(mErr.SQLState);
   }
   if(mErr.NativeError != 0)
   {
      msg.Append("\r\nNative error: ");
      msg.Append(mErr.NativeError);
   }
   if(mErr.Source.Length > 0)
   {
      msg.Append("\r\nSource: ");
      msg.Append(mErr.Source);
   }
}

In our example application we have extracted the above functionality and put it in a helper class called ErrorHandler instead.

To receive warnings in ADO.NET and consequently in Mpm you have to write an event handler and register it on the object to receive events for. We can write the following method for this:

public void HandleWarnings(object sender, MpmInfoMessageEventArgs e)
{
   StringBuilder msg = new StringBuilder();
   foreach(MpmError mErr in e.Errors)
   {
      ExtractErrors(mErr, msg);        
   }
   MessageBox.Show(msg.ToString(), 
    "Received a MpmInfoMessageEvent",MessageBoxButtons.OK);
}

To register the event handler for a connection you simply add it to the InfoMessage property on the MpmConnection:

mpmConnection.InfoMessage += new MpmInfoMessageEventHandler(HandleWarnings);

In these examples we only handle MpmExceptions. Depending on what you do you have to handle different types of exceptions.

Native methods

In some circumstances you might want to access a provider specific feature, and Mpm doesn’t prevent this. On the contrary, Mpm have methods for letting you work with the native provider and doing it in a way that’s easy to recognize in the code. If you, for example, want to use a transaction save point in Sql Server you can do as follows:

MpmConnect connect = new MpmConnect("Data Source Name=SqlSource");
MpmTransaction transaction = connect.BeginTransaction();

//Do some database calls

MpmDataSourceDescriptor dataSource = connect.DataSourceDescriptor;
if (dataSource.DbmsType == MpmDbmsTypes.SqlServer) {
    // SQLServer specific actions
    SqlTransaction sqltransaction = 
      (SqlTransaction) transaction.NativeTransaction;
    sqltransaction.Save("SavepointName");
}

As you can see we use the runtime information to find which native provider to use. We then cast MpmTransaction to a SqlTransaction and call the Save method.

Summary

As you can see there is no difference in the way you program your database logic with Mimer Provider Manager compared to working directly with a specific Data Provider. In fact, Mimer Provider Manager can be seen as just another Data Provider with the difference that it works with any kind of database for which you have a Data Provider. Just as when you connect to a database with another Data Provider you use a connection string to identify what database you want to use. The difference when using Mpm is that this connection string represents a logical name that can point to any type of database and use any kind of Data Provider. Combined with the more advanced features as SQL Filters you can write truly database agnostic applications.

For more information and downloads, visit the Mpm site at developer.mimer.com/mpm or the Sourceforge project at www.sourceforge.net/projects/mimerpm.