Source project: MimerProviderMgr.zip 11 KB
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
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 new Mimer Provider Manager (Mpm) initiative makes it possible for the .NET developer to build efficient applications that are possible to use against databases from different vendors without any modifications. 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, SQL Server, Odbc, and OleDb. There is also a generic plugin that can use any underlying provider using reflection at a sligthly higher cost.
Besides the plugins there is a concept of SQL Filters in Mpm. This allows you to write filter that will modify 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.
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 dont 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 Microsofts 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.
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.
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 connecting/disconnecting is handled automatically by the DataAdapter. In this simple example we will work directly connected to the Database.
Connecting to the database
The first thing you have to do is to set up the Mpm framework on your machine.
Download the latest binary release from http://developer.mimer.com/mpm or http://www.sourceforge.net/projects/mimerpm
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.
Start the Mimer Provider Manager Administrator and create Data Sources for your databases. Its 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, as we do below:
conCombo.Items.AddRange(MpmProviderInfo.DataSourceNames);
When you know the name of the data source you want to work with you can use the MpmDataSourceDescriptor class to get more information about the specific data source. In this example we use this functionality to show database type and version in the statusbar when the user selects a data source:
MpmDataSourceDescriptor desc = MpmProviderInfo.GetDataSource(conCombo.SelectedItem.ToString());
statusBar.Text = "Type: " + desc.DbmsTypeName;
if(desc.DbmsVersion.Length > 0)
statusBar.Text += ", version: " + desc.DbmsVersion;
We will not show the error handling with each example, but of course everyting is contained in try-catch clauses. We will show how to catch and display errors and warnings later on.
If you know the data source name or you dont want any extra information you dont have to use this information at all, just connect as you would using any other Data Provider.
To create a connection to the database with Mpm you use the class MpmConnection. The connection string used by MpmConnection can be very simple by just giving 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
Okay, now we are connected to some kind of database, lets 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 just 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 or 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 dont 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, lets 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 dont 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 dont have to iterate over any MpmDataReader. Instead we crate 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, 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 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 connectin 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 more exception types.
Native methods
In some circumstances you might want to access a provider specific feature, and Mpm doesnt prevent this. On the contrary, Mpm have methods for letting you work with the native provider and doing it in a way thats 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 to 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 http://developer.mimer.se/mpm or the Sourceforge project at http://www.sourceforge.net/projects/mimerpm. You can find a article that shows that working with transactions, datadapters and datasets works just as it does with any other Data Provider at http://developer.mimer.se/mpm/howto/howto_56.htm
|
