Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Generic database access in Ado.net
Categories: ADO.NET, SQL, Tools and Interfaces
Introduction

A common problem when developing .NET applications accessing databases is that you have to use different providers for different databases. Because of this you cannot simply write database independent applications. The Mimer Provider Manager (Mpm) initiative makes it possible for the .NET developer to build efficient applications that can be used against databases from different vendors without any modifications. This is achieved by a unified interface in Mimer Provider Manager that encapsulates the different vendor specific database interfaces.

This article will try show you how to write applications using ADO.NET that do not depend on a specific Data Provider or database, using the Mimer Provider Manager framework.

For details on the Mpm concept, please see the article Mimer Provider Manager - The solution for ADO.NET!

Description

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 Mimer SQL, Oracle, SQL Server, Odbc, and OleDb. There is also a generic plugin using reflection that support any underlying provider at a slightly higher cost.

Mpm follows the ADO.NET naming scheme. To get a connection, for example, the MpmConnection class is used. This means that it is not more work to convert an existing system to Mpm than it is to convert it to any other ADO.NET provider.

Mpm includes a Mimer Provider Manager Administrator that can be 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. In other words you don’t have to change anything in your code to apply a SQL Filter that for example converts your Microsoft SQL Server specific SQL to Oracle specific SQL. 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.

Mpm is integrated in Visual Studio .NET so you can drag MpmCommands, MpmConnections and so on into your solution. 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 a 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.

Example

To show you how to work with Mpm we will develop a small SQL front end in C# where the user can enter SQL statements and view the result in a grid. The user can select among all available data sources in a combo box and there will be a button that activates Mimer Provider Manager Administrator to create or edit data sources. See the following example screen shot:



The architecture of ADO.NET allows you to work either connected to the database or in a disconnected manner. You can of course work in the same way with Mpm. In the connected model a Data Provider is used for connecting to a database, executing commands, and retrieving results. The disconnected model uses a DataAdapter where the connect/disconnect operations are handled automatically by the DataAdapter. In this simple example we will work directly connected to the Database.

Error handling will be discussed later on in this Example section, where we will show how to catch and display errors and warnings using try-catch clause.

The complete example can be found in the SQL_Frontend.zip archive (right-click and 'Save Link Target As...').


Connecting to 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 http://developer.mimer.com/mpm or http://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 is also possible to create data sources programmatically from your applications if you want to.


As we said above this application will work connected to the database and this means we have to start by opening a connection. When you are working with Mpm you can use the MpmProviderInfo class to get runtime information about the system, for example registered data sources, plugins and SQL filters. With this information you can, for example, create a ComboBox where the user can select data source to work with:
conCombo.Items.AddRange(MpmProviderInfo.DataSourceNames);

When the user has selected a data source, you can use the MpmDataSourceDescriptor class to get more information about that specific data source. In this example we use this functionality to show database type and version in the statusbar:
MpmDataSourceDescriptor d = MpmProviderInfo.GetDataSource(conCombo.SelectedItem.ToString());
statusBar.Text = "Type: " + d.DbmsTypeName;
if(desc.DbmsVersion.Length > 0)
statusBar.Text += ", version: " + d.DbmsVersion;

Another, more straightforward, solution is to just connect to the data source provided by the user. To create a connection to the database with Mpm you use the class MpmConnection. The connection string used by MpmConnection can simply be the data source name. In our example application we can create and open a connection with the following code:
MpmConnection mpmConnection = new MpmConnection();
mpmConnection.ConnectionString = "Data Source Name=" + conCombo.SelectedItem.ToString();
mpmConnection.Open();


Executing a query and showing the result
Now we are connected to a database, so let us execute some queries. Just as you would use OdbcCommand with the ODBC Provider you use MpmCommand with Mpm. In our example we use the MpmConnection to create the command, but we can of course create a new MpmCommand and set the MpmConnection in a later stage. The following code creates the command from the connection:
MpmCommand mpmCommand;
mpmCommand = mpmConnection.CreateCommand();

When we have the connection, we get the SQL to execute from our RichTextBox called sqlText:
//If a text selection exists, only use that part of the sqlText
if(sqlText.SelectedText.Length > 0){
mpmCommand.CommandText = sqlText.SelectedText;
} else {
mpmCommand.CommandText = sqlText.Text;
}

In this example we will use a MpmDataReader to fetch the result and show it in a ListView. To continue our execution we clear the ListView (resultView) and executes the query. This will give us our MpmDataReader:
resultView.Items.Clear();
resultView.Columns.Clear();
//Execute the query and get a MpmDataReader
reader = mpmCommand.ExecuteReader();

Next we use the RecordsAffected property in the MpmReader to see if the query executed returns a result or if it modifies anything. If RecordsAffected is –1 we have a result to work with. The first thing we want to do if we have a result is to get the column names and create the corresponding columns in our ListView. Since the user can enter any arbitrary SQL we don’t know the column names or how many columns there are. We can use the FieldCount property of the MpmDataReader to find out how many columns there are and then iterate and fetch each column name:
int numCols = reader.FieldCount;
for (int cnt = 0; cnt < numCols; cnt++) {
resultView.Columns.Add(reader.GetName(cnt), -2, HorizontalAlignment.Left);
}

Now when we have our table header, let’s iterate over the MpmDataReader and insert each row into the ListView. For each row in the result we iterate over the columns to get each value and insert it in correct column. Since we don’t know the datatype we treat all columns as strings:
ListViewItem listItem;
int numRows = 0;
//Avoid redrawing of the ListView while adding rows
resultView.BeginUpdate();
while(reader.Read()) {
//Create a new ListViewItem for each row
listItem = new ListViewItem);
//Get all column values
for (int cnt = 0; cnt < numCols; cnt++) {
listItem.SubItems.Add(reader.GetValue(cnt).ToString());
}
//Add the row to the ListView
resultView.Items.Add(listItem);
}
//Redraw the ListView
resultView.EndUpdate();

If reader.RecordsAffected is not –1 there were no result and we assume that a INSERT, DELETE or UPDATE statement, or alternatively a Data Definition Language (DDL) statement like CREATE TABLE, was issued. In this case we don’t have to iterate over any MpmDataReader. Instead we create one column header and insert the number of affected rows as the one and only row:
ColumnHeader header = new ColumnHeader();
header.Text="Affected Rows";
header.Width = header.Text.Length * (int)resultView.Font.Size;
resultView.Columns.Add(header);
ListViewItem item = new ListViewItem(reader.RecordsAffected.ToString());
resultView.Items.Add(item);

When we are done with the execution we have to close the MpmDataReader. We do this in a finally block so we are sure it occurs.

Errors and warnings
We have removed all error handling in the examples above to save space and gain simplicity, but of course we enclose all database operations in a try catch block. 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 StringBuilder();
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 connectin you simply add it to the InfoMessage property on the MpmConnection:
mpmConnection.InfoMessage += 
new MpmInfoMessageEventHandler(HandleWarnings)

In the examples in this article we only handle MpmExceptions. Depending on what you do, you have to handle more exception types.

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 is easy to recognise 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 from 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.

Links

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

You can also find an article named Working with Mpm showing that working with transactions, datadapters and datasets in Mpm works just as it does with any other Data Provider.

Last updated: 2004-03-31

 

Powered by Mimer SQL

Powered by Mimer SQL