Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Accessing Mimer SQL from ADO.NET using Mimer Data Provider
Categories: ADO.NET, Programming Examples, SQL, Tools and Interfaces
Introduction

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

The ADO.NET interface allows you to develop applications in the language best suited for the task. Whether you chose to use Visual Basic.NET, C#, J# or C++, your application will use the same ADO.NET library and the database logic will look the same.

The Mimer Data Provider is used to connect to Mimer SQL from .NET, in order to execute queries, fetching the result, and so on.

The provider is very well integrated in both Microsoft Visual Studio 2005 and Microsoft Visual Studio 2008. For example, you can do the following:
  • drag objects like MimerCommands and MimerConnections directly onto your different forms

  • work with the Server Explorer

  • read the documentation along with Microsoft’s standard documentation (including support for dynamic help)

  • use the concept of side-by-side execution allowing that several versions can be installed in parallel on a single computer.

The Mimer Data Provider is also available for the .NET Compact Framework that is used on Windows Mobile and Windows CE based devices.

Example

The data browser example
As an example on how to work with the Mimer Data Provider, we will develop a Windows Forms based data browser. The user can select among all available databases, and their corresponding tables, in two combo boxes. When the Load button is pressed, after selecting a database and a table, a DataSet will be created and shown in an updateable DataGrid. If the rows are updated and the Load button is pressed again, 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 MimerDataAdapter which handles the connecting/disconnecting business automatically. This is the way most of the work in this example application will be carried out. We will, however, use a MimerCommand and MimerDataReader to get the available tables in the selected database.




Installation
The first thing you have to do is to install the Mimer Data Provider.
  1. Download the latest release from developer.mimer.com/downloads

  2. Start the Windows installer. This will install the Mimer Data Provider in the folder you specify, install the required assemblies in the Global Assembly Cache, and integrate the Mimer Data Provider with Visual Studio (if available).


The application
When you are working with the Mimer Data Provider you can use the MimerDataSourceEnumerator class in the Mimer.Data.Client namespace to get a list of available databases. This manner is used to populate a combo box in the dialog that appears when the user presses the Select DB button:

DataSet ds = new DataSet();
ds.Tables.Add(MimerDataSourceEnumerator.Instance.GetDataSources());
comboDB.DataSource = MimerDataSourceEnumerator.Instance.GetDataSources();
comboDB.DisplayMember = "ServerName";

When the user has finished the database dialog, 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 ButtonDBSelect(...) event handler is called where a new MimerConnection is created. Then the GetTables() method is called that queries the databases and fetches the tables:

private void ButtonDBSelect(object sender, System.EventArgs e){
try
{
dataGrid.Enabled = false;
buttonLoad.Enabled = false;
buttonUpdate.Enabled = false;
comboTables.Text ="";
dbDialog.ShowDialog(this);

if(dbDialog.ConnectionString != "")
{
mimerConnection = new MimerConnection(dbDialog.ConnectionString);
GetTables();
}
}
catch(Exception ex)
{
errorHandler.ShowException(this, ex);
}
}

private void GetTables()
{
MimerCommand tableCommand = null;
MimerDataReader reader = null;
comboTables.Items.Clear();

try
{
//The connection should not be open when we get here
if(mimerConnection.State == ConnectionState.Open)
{
throw new Exception("The connection shouldn't be open here");
}
mimerConnection.Open();
tableCommand = mimerConnection.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(mimerConnection.State != ConnectionState.Closed)
{
mimerConnection.Close();
}
}
catch(Exception ex)
{
errorHandler.ShowException(this, ex);
}
}
}


The class called errorHandler is used to handle possible exceptions. This class will be described more in detail below.

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 by a MimerDataAdapter. When executed, a DataSet is created that we will bind to our DataGrid. This is all that is needed to show the content of any table in a Mimer SQL database:

private void LoadDataSet( string tableName)
{
try
{
StringBuilder selCmd = new StringBuilder();
selCmd.Append("select * from ");
selCmd.Append(tableName);
mimerDataAdapter = new MimerDataAdapter(selCmd.ToString(), mimerConnection);
ds = new DataSet();
mimerDataAdapter.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. It is called when the user has edited some columns and pressed the Update button in the GUI. A new DataSet that contains all the changed rows is created. If the new DataSet contains any rows, a MimerCommandBuilder is attached to the MimerDataAdapter and the Update method is called on the MimerDataAdapter:

private void UpdateDataSet()
{
try
{
DataSet ds2 = ds.GetChanges();
if(ds2 != null)
{
MimerCommandBuilder mBuild = new MimerCommandBuilder(mimerDataAdapter);
mimerDataAdapter.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 Mimer SQL you can catch MimerException and use the MimerError class to get more information.

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


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

private void ExtractErrors(MimerError mErr, StringBuilder msg)
{
if(mErr.Message != null && mErr.Message.Length > 0)
{
msg.Append("\r\nError message: ");
msg.Append(mErr.Message);
}
if(mErr.SQLState != null && 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 != null && 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 the Mimer Data Provider, 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, MimerInfoMessageEventArgs e)
{
StringBuilder msg = new StringBuilder();
foreach(MimerError mErr in e.Errors)
{
ExtractErrors(mErr, msg);
}
MessageBox.Show(msg.ToString(),
"Received a MimerInfoMessageEvent",
MessageBoxButtons.OK);
}

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

MimerConnection:mimerConnection.InfoMessage += new MimerInfoMessageEventHandler(HandleWarnings)

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

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 MimerTransaction class.

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

All SQL statements that we want to 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 MimerCommand or by setting the Transaction property. When we are done with the work, we simply call MimerTransaction.Commit() or MimerTransaction.Rollback() to commit or roll back the transaction.

try
{
MimerConnection con = new MimerConnection("...");
con.Open();

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

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

//Execute and commit
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
trans.Commit();
con.Close();
}
catch(MimerException oe)
{
//Show errors
try
{
trans.Rollback();
}
catch(MimerException oe2)
{
//Show errors
}
}

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


Summary
As we have shown in this article, ADO.NET programming can be really simple and yet powerful. The connected model of programming with ADO.NET is quite similar to programming with JDBC and ODBC. On the other hand the disconnected model, using DataAdapters and DataSets, is kind of 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.

Links

The complete example can be found in the DataSetBrowser.zip archive (right-click and 'Save Link Target As...'). The example contains project files for both Microsoft Visual Studio 2002, Visual Studio 2003, and Visual Studio 2008.

Last updated: 2009-10-26

 

Powered by Mimer SQL

Powered by Mimer SQL