Working with Mimer Compact Data Provider, a simple example
Tools and Interfaces
This article will show how you can use Mimer Compact Data Provider to access a Mimer SQL database from your Windows CE/Pocket PC based device using ADO.NET. The example application that is used can access both Mimer SQL Mobile and Mimer SQL Engine databases and you can use them in both Mobile and Engine mode.
The small application shown in this article is a simple contact manager where you can browse, add, and edit contacts that are stored in a Mimer SQL database.
To be able to use the sample you must have Microsoft Visual Studio .NET 2003 (VS.NET) and a Microsoft Pocket PC 2002 emulator (or later), for example the Microsoft Pocket PC 2003 emulator, installed. You can, of course, use a real Pocket PC device as well.
You also need the Mimer Compact Data Provider and a Mimer SQL database running on your Pocket PC device, in your emulator, or on some other computer you can access. For more information about getting started with Mimer SQL, visit developer.mimer.se.
If you have not yet installed the Mimer Compact Data Provider it is time to do that now. When the installation is completed, extract the application zip file, ContactsPPC.zip, and open the project (.csdproj) file with VS.NET.
Now you have to modify the
ConnectionString that defines which database to connect to. You can use Mimer SQL Engine or Mimer SQL Mobile on any machine as the database server, or you can use a local Mimer SQL Mobile database on the device. Then, select Run to deploy and execute the application.
The one and only table needed in this application is created with the following SQL:
create table CONTACTS(
NAME NATIONAL CHARACTER VARYING(50),
PHONE CHARACTER VARYING(20),
To use Mimer SQL in Mobile mode you have to create the needed statements before exporting the database to your device. In this application, the following statements are used.
create statement update_contact
update CONTACTS set ID = ?, NAME = ?, PHONE = ?
where ID = ?;
create statement delete_contact
delete from CONTACTS
where ID = ?;
create statement select_contact
select ID, NAME, PHONE from CONTACTS;
create statement insert_contact
insert into CONTACTS(ID, NAME, PHONE)
values(?, ?, ?);
Note! Since you are working in a disconnected manner the update and delete statements are not entirely safe. Database modifications done by other users can be overwritten. To handle this you have to check that NAME and PHONE contain the values that you expect.
Before deploying and testing, you also have to add a project reference to the Mimer Compact Data Provider. This is done in the standard Visual Studio way; select Add reference and select the Mimer Compact Data Provider entry.
To describe how you can work with Mimer Compact Data Provider a quick walkthrough of the important parts of the example application might help.
If you have done any programming in ADO.NET before, you are probably familiar with the two different ways to work with databases. The more traditional way is to work in a connected way where you connect to the database, issue a command, read the result, and then close the connection. The other way is to use the Data Adapter technology where you leave a lot of the work to the Data Provider. In its easiest form you only have to specify a select statement that is used to define what data you want to work with. In our case the SQL statement
select ID, NAME, PHONE from CONTACTS is used. The
MimerDataAdapter will use this statement to create the update, delete, and insert statements when they are needed. To do this a
MimerCommandBuilder is used. This means that all you need to select, insert, update, and delete information from a table is a simple select statement.
With Mimer SQL you can chose to run your databases in standard Engine mode or in a very low footprint mode called Mobile mode. In the Mobile mode you can only call already compiled SQL statements. This means that the
MimerCommandBuilder can not create the needed statements in runtime when the database runs in Mobile mode. We will still use
MimerCommandBuilder, but for another task. To be able to show you how to work both in Mobile and Engine mode the application supports them both.
Only the ADO.NET parts will be discussed in this article. There are better resources if you want to know how to build applications using .NET Compact Framework.
To accomplish what we want to do in the example we need three ADO.NET objects to work with Mimer SQL in Engine mode; one
MimerCommandBuilder, and one
DataTable. To support the Mobile mode as well we need three more objects; three
MimerCommands. This gives the following complete list of ADO.NET objects needed:
private DataTable tableContacts;
private MimerDataAdapter dataAdapter;
private MimerConnection conn;
private MimerCommandBuilder cBuilder;
private MimerCommand insertCommand;
private MimerCommand updateCommand;
private MimerCommand deleteCommand;
The only part where the application differs between Engine and Mobile mode is in the constructor where we create all ADO.NET objects that we need. For Engine mode it is very simple. All we have to do is to create a MimerDataAdapter with the select command as argument and a
MimerCommandBuilder with the newly created
MimerDataAdapter as argument:
dataAdapter = new MimerDataAdapter("select id,name,phone from contacts", conn);
cBuilder = new MimerCommandBuilder(dataAdapter);
The last line above connects the
MimerDataAdapter to the
MimerCommandBuilder and makes the
MimerCommandBuilder handle the creation of the insert, update, and delete commands when they are needed.
To use the Mobile syntax you have to create the statements as shown in the chapter Prerequisites. Since we will use the
MimerCommandBuilder in another way in Mobile mode we do not need to create any instances of it. Just as with Engine mode, a new instance of the
MimerDataAdapter is created, but this time we pass it an
execute statement instead. Besides this we have to manually create the different
MimerCommands needed as well as their associated
dataAdapter = new MimerDataAdapter("execute statement select_contact", conn);
insertCommand = new MimerCommand("execute statement insert_contact", conn);
updateCommand = new MimerCommand("execute statement update_contact", conn);
deleteCommand = new MimerCommand("execute statement delete_contact", conn);
As you can see, a
MimerCommand has to be created for each statement. For each
MimerCommand we call the static
MimerCommandBuilder.DeriveParameters(MimerCommand) method. This will create all the
MimerParameters we need. Another way would be to manually create a
MimerParameter for each parameter marker in each statement, but this would require more coding.
Note that the
CommandBuilder in most other Data Providers only handles stored procedures, but the
MimerCommandBuilder supports all kinds of statements and this saves us a lot of code in this case.
MimerCommands and the corresponding
MimerParameters are created we connect the
MimerCommands to the
dataAdapter.InsertCommand = insertCommand;
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.DeleteCommand = deleteCommand;
Since we set the update, insert, and delete
MimerCommands of the
MimerDataAdapter to the newly created
MimerCommands we do not need any instances of the
MimerCommandBuilder, only the static method above is used. All that is left now is to create a new instance of the
DataTable and then all initialization is done.
As can be seen, when working in Mobile mode there are only a few more lines of code than in Engine mode.
Loading and displaying the data
Now when the initialization is done the usage of the
MimerDataAdapter is easy. To display the data a
DataGrid is used. All that is needed is to fill our
DataTable with data and then connect it to the
DataGrid. The following two lines of code will accomplish our goal to be able to load and display data:
dataGridContacts.DataSource = tableContacts;
Yes, the two code lines shown above will show all contacts in a
DataGrid. What happens here is that the select statement is executed and the
DataTable is populated with the result. The
DataGrid then simply shows the content of the
Updating the database
To insert new rows into the database is almost as easy as selecting data. In short all you have to do is to create a new
DataRow, set its columns, add the
DataRow to the
DataTable and then call the
Update() method of the
DataRow newRow = tableContacts.NewRow();
If you look at the example above you can see that we do not have to use any SQL and that there are no differences between using the Engine and Mobile mode. When the
Update() method is called, the insert command of the
MimerDataAdapter is executed after its parameters have been set to the new values.
eForm above is simply a
WindowsForm that contains a Struct where the values to be used are stored. The last call to
AcceptChanges() tells the
DataTable that all changes have been stored.
If existing rows are to be updated, the same approach is taken. The difference compared to inserting is that we do not create a new row. Instead we find the selected row and then we update the row and call
Update() on the
DataRow selectedRow = tableContacts.Rows[dataGridContacts.CurrentRowIndex];
selectedRow = eForm.Contact.Id;
selectedRow = eForm.Contact.Name;
selectedRow = eForm.Contact.Phone;
Update() method is called this time, the update statement is executed.
To delete a row is even easier, simply call the
Delete() method on the selected row and the
Update() on the
As you might have guessed, this time the delete statement is executed.
In fact, you can insert one or more rows, update one or more rows and delete one or more rows in sequence and only call
Update() once. This will result in the execution of the each individual update, insert, and delete statement.
For further details, please see the source code for this simple example found in ContactsPPC.zip.
With the Mimer Compact Data Provider and Mimer SQL Mobile you can now work with databases and ADO.NET on your handheld device just as you would on an ordinary computer. This gives you a very powerful tool, but still a tool that is very easy to use. With only a few lines of code we can select, insert, update, and delete data in Mimer SQL. Thanks to the support for all kind of statements in the
MimerCommandBuilder we only have to write a few more lines of code to support the very small footprint mode of Mimer SQL Mobile.
Read a little more about the platform in the article Mimer Compact Data Provider on .NET Compact Framework 1.0.
Last updated: 2005-04-18