Generic Database Access with Mimer Provider Manager
Authordate of SubmissionUser LevelFredrik Alund03 / 09 / 2004Itermediate
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 ManagerA common problem when developing .NET applications accessing databases is that you have to use different providers for different databases. Because of this you can not 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 .
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. .
via This is done 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 dir Ectly 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.GETDATASOSOSOSOSTRING ()); statusbar.text = "TYPE:" DESC.DBMSTYPENAME; if (desc.dbmsversion.length> 0) statusbar.text = ", Version: DESC. DB msVersion;. 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: mpmconnection mpmconnection = new mpmConnection ();
mpmConnection.ConnectionString = "Data Source Name =" conCombo.SelectedItem.ToString (); mpmConnection.Open ();. Executing a query and showing the resultOkay, 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 sqlTextif (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 MpmDataReaderreader = 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 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 warningsWe 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 MPME rror 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 ") {msg.append (" / r / nsqlstate: "); Msg.append (merr.sqlstate); } F (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 InfubusSperty on The MPMCONNECTION: MPMCON nection.InfoMessage = new MpmInfoMessageEventHandler (HandleWarnings) In these examples we only handle MpmExceptions. Depending on what you do you have to handle more exception types.Native methodsIn 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 ();
