helpinghand
search
usingadonet
 
How To
Working with Mpm
Categories: ADO.NET, SQL, Tools and Interfaces
Introduction

ADO.NET is the interface of choice when you develop database applications in the Microsoft .NET framework and it is the natural successor for the ADO (ActiveX Data Objects) interface.

Even though ADO.NET is a simple and yet powerful API when working with databases it has one big drawback, it is designed in a way that will make your application depend on a specific database product. To switch to another database, you need to redefine the target Data Provider and recompile. As a workaround for this problem the Mimer Provider Manager (Mpm) was developed.

Description

As said above, Mpm was developed to overcome the drawback that ADO.NET makes you depend on a specific Data Provider and a specific database.

Instead of working directly towards a specific Data Provider, Mpm lets you work towards the Mpm framework and you can change Data Provider and/or database without changing any code. To accomplish this, Mpm uses plug-ins that works with the underlying Data Provider. Mpm currently comes with plug-ins for ODBC, Sql Server, Oracle and OleDB. There is also a generic plug-in that works with any provider using reflection.

The base classes for Mpm are MpmConnection, MpmCommand, MpmDataReader and MpmTransaction. They are all found in the Mimer.Mpm.Data namespace. As you can see the base classes follows the recommended naming scheme for ADO.NET Data Providers. Since the Mpm base classes have the same functionality as the Data Providers you can simply search in your code for specific Data Provider statements and replace them with the corresponding for Mpm.

The architecture of ADO.NET allows you to work directly 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. On contrary, the DataSet and DataAdapter components are the core of the disconnected model. A reflection is that the API in the connected model is quite similar to Javaís JDBC.

Below, we will initially describe Mpm in the connected model and then later on we will give a short introduction to the disconnected model.


Connect to the database

Letís get started! First you must have Mpm installed on your computer. You also need to have a Data Source defined, which can be done using the Mimer Provider Manager Administrator. When you define a Data Source you provide Mpm with the connection string and Data Provider type, for example an ODBC connection string if you want to use the Data Provider for ODBC that ships with .NET framework 1.1. You can think of a Data Source as a logical name for a connection to a database. Through the DataSource, Mpm determines what underlying Data Provider to use (the underlying Data Provider must be installed as well). For these examples we define the Data Source BookShop that defines a connection to a Mimer SQL database.

Before you can access the database you must create a connection to it. You do this with the MpmConnection class. A connection string is used to tell the system which Data Source to use. The string has the format:
"Data Source Name=name"

You can pass the connection string through the constructor for MpmConnection or you can set the ConnectionString property on an already created object.

When the object is created you can open the connection with the Open() method. When you are done you close it with the Close() method. A simple example might illustrate this (in C#):
using System;
using Mimer.Mpm.Data;
. . .
MpmConnection con =
new MpmConnection("Data Source Name=BookShop");
con.Open();
//Do your database stuff
con.Close();

As you can see, this really doesn't do anything. It only connects to the database and then disconnects.


Executing commands

When you want to pass SQL statements to the database for execution you use the MpmCommand class. In contrast to JDBC, ADO.NET only lets you have one type of command object. This object can be used for direct execution, prepared execution and for calling stored procedures.

Before you can use an MpmConnection it has to be associated with a connection. This can be done either with the constructor for MpmCommand or by using the CreateCommand() on the connection to create it. The SQL to execute can be provided in the constructor as well, or by setting the CommandText property on the command.

When the command is created we can call ExecuteNonQuery(), ExecuteScalar() or ExecuteReader():
  • ExecuteNonQuery is used to execute a statement that doesn't return any result, like an UPDATE or INSERT. The number of affected rows is returned.

  • ExecuteScalar() is used when only a simple result is expected, for example a statement like SELECT COUNT(*). It returns the first column of the first row and ignores the rest.

  • ExecuteReader() is used to execute a statement that returns a result. We will take a closer look on that later on.

A simple example that inserts a row in the CATEGORIES table illustrates how to use the ExecuteNonQuery() command:
string sql =
"INSERT INTO CATEGORIES(CATEGORY_ID, CATEGORY) VALUES(12,'A category')";
MpmCommand command;
command = new MpmCommand(sql, con);
//An alternative approach
//command = con.CreateCommand();
//command.CommandText = sql;
//command.Connection = con;
command.ExecuteNonQuery();

As you can see, the above statement is using a static SQL string in a non-prepared statement.

Using parameters via a prepared statement is a convenient way to supply data for INSERT, and it can really boost performance since it reduces the number of SQL compilations. After a statement is prepared it can be executed several times without compilation.

To set parameters for an MpmCommand, simply add them to the Parameters collection. If you want to be able to control what datatype to use, create the MpmParameter explicitly. Otherwise, you can simply add the value with a name identifying the parameter marker to the collection prior to Prepare(). The same example as above can look as follows:
string sql =
"INSERT INTO CATEGORIES(CATEGORY_ID, CATEGORY) VALUES(:id, :cat)";
MpmCommand command = new MpmCommand(sql, con);
MpmParameter idParam = new MpmParameter("id", MpmType.Int);
idParam.Value = 12;
command.Parameters.Add(idParam);
command.Parameters.Add("cat", "A category");
command.Prepare();
command.ExecuteNonQuery();



Error handling

When working with databases, error handling is essential for proper application behaviour. In the above examples we have skipped that part completely. For ADO.NET in general, as well as for Mpm, error handling is carried out using so called try-catch-statements.

Basically all database operations can throw an MpmException that contains information about what went wrong. An error situation can cause several error messages to be generated. The simplest way to present the error is to show the MpmException.Message, but that way we only get the first error. Besides the fact that we can have several errors in one MpmException we can retrieve more error information if we use the MpmException.Errors collection to get all the underlying database errors.

A simple example that prints information about all errors in an exception is shown below:
try
{
con = new MpmConnection("Data Source Name=BookShop");
con.Open();
. . .
con.Close();
}
catch(MpmException me)
{
foreach(MpmError mErr in me.Errors)
{
Console.WriteLine("Message: " + mErr.Message);
Console.WriteLine("Native error: " + mErr.NativeError);
Console.WriteLine("SQLState: " + mErr.SQLState);
Console.WriteLine("Source: " + mErr.Source);
}
}



Warning handling

To handle SQL Warnings we have to write an event handler for MpmInfoMessageEventHandler. To do this, simply write a method with the same signature as MpmInfoMessageEventHandler and couple it to the connection. For example, in the general error handling class ExampleErrorHelper we can write the method HandleInfoEvents as:
public class ExampleErrorHelper
{
public void HandleInfoEvents(object sender, MpmInfoMessageEventArgs e)
{
Console.WriteLine("An event was thrown")
foreach(MpmError mEvent in e.Errors)
{
Console.WriteLine("Errors: " + mEvent.Message);
Console.WriteLine("SQLState: " + mEvent.SQLState);
Console.WriteLine("Native: " + mEvent.NativeError);
}
}
}

The method is then coupled to the connection as:
. . . 
ExampleErrorHelper eeh = new ExampleErrorHelper();
con = new MpmConnection("Data Source Name=BookShop");
con.InfoMessage += new MpmInfoMessageEventHandler(eeh.HandleInfoEvents);

This way, the HandleInfoEvents is called as soon as a warning is issued and the warning is printed.


Fetching the result

When we want to fetch the result of a query we use the MpmDataReader class. MpmDataReader is a fast, read only and forward only representation of the result and we get it by calling ExecuteReader() on a command. When we have the MpmDataReader we can iterate through it using a while loop in conjunction with the Read() method:
. . . 
MpmDataReader reader = command.ExecuteReader();
Console.WriteLine("ISBN\tAuthor\tTitle");
while(reader.Read())
{
Console.Write(reader.GetString(0) + "\t");
Console.Write(reader.GetString(1) + "\t");
Console.WriteLine(reader.GetString(2));
}
reader.Close();

As you can see we iterate throw the MpmDataReader by calling reader.Read() which moves the cursor one row forward. The reader.GetString(0) is used to get the first column and so forth. There are GetXXX() methods for most datatypes, but there is no equivalent method to JDBCís getString("columnname") - only the index can be used which is a pity. This way we depend on the column order in the SELECT statement.


Transactions

So far we haven't used explicit transaction handling and therefore auto-commit has been enabled. This means that all commands have been executed in their own transaction, and that might not be what we want. Fortunately, transaction handling is quite easy and straightforward in ADO.NET using the MpmTransaction class.

A transaction is started by calling the MpmConnection.BeginTransaction(isolationlevel) that returns an instance of MpmTransaction. The different isolation levels are available in System.Data.IsolationLevel. For example, System.Data.IsolationLevel.Serializable can be used.

All SQL statements that should be part of the transaction have to be associated with the transaction object. This can be done by passing the transaction through the constructor of the MpmCommand or by setting the Transaction property. When we are done with the work we simply call MpmTransaction.Commit() or MpmTransaction.Rollback() to commit or roll back the transaction.
try
{
MpmConnection con = new MpmConnection("Data Source Name=BookShop");
con.Open();

//Start the transaction
MpmTransaction trans =
con.BeginTransaction(System.Data.IsolationLevel.Serializable);
MpmCommand command = new MpmCommand(sql, con, trans);

//An alternative but equivalent approach
MpmCommand command2= new MpmCommand(sql2, con);
command2.Transaction=trans;

//Execute and commit
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
trans.Commit();
con.Close();
}
catch(MpmException me)
{
//Show errors
try
{
trans.Rollback();
}
catch(MpmException me2)
{
//Show errors
}
}

As you can see, we simply commit when we are done. If an MpmException is thrown we do a roll back. Note that the trans.RollBack() method can throw an MpmEvent itself and is therefore surrounded by its own try-catch.


DataSet and DataAdapter

As we have shown above, working with ADO.NET directly connected to the database (the connected model) is quite similar to how we work in JDBC and ODBC. The DataSet and DataAdapter in ADO.NET are used to work in an entirely different way (the disconnected model).

A DataSet can be thought of as an in-memory database, which we can be populated from a database or be created directly in a program. The internal format of the DataSet is XML, which enables for openness between different parts of the system. For example, a DataSet can be sent between components, XML can be sent to a DataSet to update it and data can easily be received in XML format.

The bridge between the DataSet and the database is the DataAdapter, in our case an MpmDataAdapter. A DataAdapter is a representation of a connection to the database and the SQL statements used to get and update data. The DataAdapter is used both for filling the DataSet and to update the database.

When you use this method of programming you never open or close connections of your own. You simply create a DataAdapter, supply it with a connection context and at least a SELECT statement. The DataAdapter is then administering the connection for us. When we fill a DataSet, the connection is made, the data is fetched and then the connection is closed immediately.

DataSets are useful when we want to send data between components, when we want to get the data in XML format and when we are developing GUIs. When developing GUIs there are several controls in the .NET framework that can be directly connected to a DataSet. With this model we can show a DataGrid connected to the database with a few lines of code.

A simple example that uses a DataSet to print the data in XML format is shown below:
MpmConnection con = new MpmConnection("Data Source Name=BookShop");
string selectSql = "SELECT CATEGORY_ID, CATEGORY FROM CATEGORIES";
da = new MpmDataAdapter(selectSql, con);
ds = new DataSet("Categories");
da.Fill(ds, "Categoryitem");
Console.WriteLine(ds.GetXml());

As you can see above, we never open the connection ourselves. The output of the example is:
<Categories>
<Categoryitem>
<CATEGORY_ID>1</CATEGORY_ID>
<CATEGORY>Music</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>2</CATEGORY_ID>
<CATEGORY>Books</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>3</CATEGORY_ID>
<CATEGORY>Video</CATEGORY>
</Categoryitem>
<Categoryitem>
<CATEGORY_ID>4</CATEGORY_ID>
<CATEGORY>d</CATEGORY>
</Categoryitem>
</Categories>


Conclusion

As we have shown in this article, ADO.NET programming with the Mimer Provider Manager can be really simple and yet powerful. The connected model of programming with ADO.NET is quite similar JDBC and ODBC and the disconnected model, using DataAdapters and DataSets, is a new way of thinking. Right now there is no equivalent technique available in JDBC, but the RowSet specification will bring a similar functionality to the Java world.

Using the Mimer Provider Manager you donít have to work directly towards a specific Data Provider and hence you donít lock your self to any specific database or Data Provider. You can change both Data Provider and database without changing any code. Instead you simply change the Data Source definition using Mimer Provider Manager Administrator, and thatís it! Feels like a great advantage for portable programming!


Links

The complete example used in this article can be found in MpmExamples.zip.

The Mimer Provider Manager (MPM) home page is found at http://developer.mimer.com/mpm and the project home page at SourceForge is http://sourceforge.net/projects/mimerpm.

Information on ADO.NET from Microsoft can be found at msdn.microsoft.com/en-us/library/aa302325.aspx.

Last updated: 2009-10-12

 

Powered by Mimer SQL

Powered by Mimer SQL