helpinghand
search
needassistance
 
How To
Working with Mimer Compact Data Provider, a simple example
Categories: ADO.NET, Programming Examples, Tools and Interfaces
Introduction

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.

Description

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.


Prerequisites

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(
ID INTEGER,
NAME NATIONAL CHARACTER VARYING(50),
PHONE CHARACTER VARYING(20),
primary key(ID)
)

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.


The application

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 MimerConnection, one MimerDataAdapter, 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;

Initialization
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 MimerParameters:

dataAdapter = new MimerDataAdapter("execute statement select_contact", conn);

insertCommand = new MimerCommand("execute statement insert_contact", conn);
MimerCommandBuilder.DeriveParameters(insertCommand);

updateCommand = new MimerCommand("execute statement update_contact", conn);
MimerCommandBuilder.DeriveParameters(updateCommand);

deleteCommand = new MimerCommand("execute statement delete_contact", conn);
MimerCommandBuilder.DeriveParameters(deleteCommand);

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.

When all MimerCommands and the corresponding MimerParameters are created we connect the MimerCommands to the MimerDataAdapter:

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:

dataAdapter.Fill(tableContacts);
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 DataTable.


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 MimerDataAdapter:

DataRow newRow = tableContacts.NewRow();
newRow[0]=eForm.Contact.Id;
newRow[1]=eForm.Contact.Name;
newRow[2]=eForm.Contact.Phone;
tableContacts.Rows.Add(newRow);
dataAdapter.Update(tableContacts);
tableContacts.AcceptChanges();

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.

The object 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 MimerDataAdapter:

DataRow selectedRow = tableContacts.Rows[dataGridContacts.CurrentRowIndex];
selectedRow[0] = eForm.Contact.Id;
selectedRow[1] = eForm.Contact.Name;
selectedRow[2] = eForm.Contact.Phone;
dataAdapter.Update(tableContacts);
tableContacts.AcceptChanges();

When the 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 MimerDataAdapter:

tableContacts.Rows[dataGridContacts.CurrentRowIndex].Delete();
dataAdapter.Update(tableContacts);
tableContacts.AcceptChanges();

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.


Summary

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.

Links

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

 

Powered by Mimer SQL

Powered by Mimer SQL