Introduction
The purpose of this article is to help get a user started in building a mobile database application using UltraLite and Visual Studio .NET (C#).
If you are interested in creating a .NET database project using SQL Anywhere 10, please see my alternate article: 10 Steps to Creating a Windows Mobile Database Application Using .NET and SQL Anywhere 10.
What is UltraLite?
UltraLite is an extremely small and powerful relational database targeted at mobile devices such as Windows Mobile, Symbian, Palm and Win32. Since the database has an extremely small footprint, it is perfect for embedding in mobile database applications. Although it is typically involved in data replication, it can also be used as a simple standalone database.
Requirements
The following tools are required to get started with developing the application:
Step 1 - Create the Database
There are multiple ways to create an UltraLite database, but the easiest way is to use Sybase Central which is a GUI tool for administering database and synchronization. Open Sybase Central by choosing Start | Programs | SQL Anywhere 10 | Sybase Central.
When Sybase Central opens, you will be presented with a set of common task choices. Under UltraLite 10 choose "Create a database".
This will launch a database creation wizard. Start out by pressing Next to the introduction page. In the "New database target platform" choose "Windows CE including Windows Mobile" and press Next. In the "New database file name" window enter a directory and file name such as C:\articles\SimpleUL\simpleul.udb and press Finish (NOTE: There are a number of additional options, but to keep things simple we will use the defaults).
This step should have created a new UltraLite database and connected to it within Sybase Central.
Next we will want to create a new table. To do this, right-click on the Table folder and choose New | Table.
We will name the table customers and press Finish.
Enter the following columns and save the table.
You will be requested to choose a unique column for the table. This is a requirement when data replication is used. As such, choose cust_id as the unique column and press Finish.
Step 2 - Add Data to the Database
Now that we have created a new database and table we will want to enter some data to be used on the mobile device. Since the UltraLite database is binary compatible across desktops and Windows CE devices, we can add the data now and deploy the database with the applications.
To add data, click on the customers table in Sybase Central and then choose the "Data" tab in the right pane. Add 5 customers as follows by right-clicking in the right-pane and choose "Add Row". Feel free to change the data if you like.
We are now done creating the UltraLite database. Close Sybase Central.
Step 3 - Create the Project
The first step is to create a new project. To do this, open Visual Studio .NET 2005 and choose New | Project. In the new project types window expand Visual C# | Smart Devices and choose "Windows Mobile 5.0 Pocket PC". In the template window, choose "Device Application". Finally name the project SimpleUL, choose a location to save the project and press OK.
Step 4 - Add UltraLite Namespace
Now that we have created a template C# application, we will now want to add the UltraLite namespace and reference the UltraLite database engine runtime. To do this choose Project | Add Reference. Choose the iAnywhere.Data.UltraLite (CE) component and press OK. Note: If the component is not listed, it can be found in the SQL Anywhere installation directory under \ultralite\UltraLite.NET\assembly\v2.
Step 5 - Add UltraLite Runtime
We will also need to include the UltraLite runtime in the application. We can do this by adding it as an "Existing Item". To do so, choose Project | Add Existing Item. In the File Name directory, switch to the SQL Anywhere 10 installation directory and choose \ultralite\ultralite.NET\ce\arm\. Next change the File Types to "All Files (*.*)". Choose ulnet10.dll and press Add.
In the Solution Explorer click on the ulnet10.dll file and change the "Copy to Output Directory" property to "Copy always".
Step 6 - Add UltraLite Database
We are also going to include the UltraLite database within the application. It is important to note that although we created the database using Sybase Central, there is no reason why it could not have been created on the fly when the application was initially started. The main advantage to pre-creating the database is that data can be pre-populated and included with the application.
To attach the database choose File | Add Existing Item and switch to the directory in which you saved the database from Step 1. Choose simpleul.udb and press Add. Just as we did in the previous step, choose the simpleul.udb file in the Solution Explorer and change the "Copy to Output Directory" property to "Copy always". You will also need to mark the "Build Action" property as "Content".
Step 7 - Connect to the UltraLite Database
Now that we have created and attached an UltraLite database, the next step is to add the logic to connect to the database. To do this, double click on the Form and add a reference to the UltraLite namespace by adding the following line:
using iAnywhere.Data.UltraLite;
Next we will add the following code to connect to the UltraLite database.
private ULConnection ConnUL = new ULConnection();
public Form1()
{
InitializeComponent();
Connect();
}
private void Connect()
{
try
{
String dbf = "\\Program Files\\SimpleUL\\simpleul.udb";
if (System.IO.File.Exists(dbf))
{
ConnUL.ConnectionString = "dbf=" + dbf + ";cache_size=1M";
if (ConnUL.State != ConnectionState.Open)
{
ConnUL.Open();
}
ConnUL.DatabaseID = 1000;
}
else
{
MessageBox.Show("Database is not available", "Error");
Application.Exit();
}
}
catch (System.Exception t)
{
MessageBox.Show(t.Message, "Connection failed");
return;
}
}
Step 8 - Query the UltraLite Database
Next we will want to start adding controls to query the database. To start, we will simply select from the database. For additional capabilities, please see the SQL Anywhere 10 Online Help.
The first step will be to add a control. For this we will choose a combo box. As such drag a combo box on to the form and name it comboCustomers as follows:
Once again switch to the code for the Form1.cs and add the following line to the Form1 function:
RefreshList();
Next add a new function as follows:
private void RefreshList()
{
try
{
using (ULCommand cmd = ConnUL.CreateCommand())
{
cmd.CommandText = "select last_name + ', '+ first_name from customers order by last_name";
ULDataReader reader = cmd.ExecuteReader();
comboCustomers.Items.Clear();
while (reader.Read())
comboCustomers.Items.Add(reader.GetString(0));
}
}
catch (Exception err)
{
MessageBox.Show(
"Exception in Pages List: " + err.Message);
}
}
Step 9 - Compile and Deploy the Applications
For this sample, I will use the Windows Mobile emulator as the application target. To compile and deploy, make sure the target is one of the Windows Mobile 5 emulator (top left combo box). Next choose Build | Deploy Solution. You should see the application compile, start the emulator and deployed the application.
Once the deployment is completed, you should be able to see the following files listed in the device File Explorer under \program files\simpleul.
Step 10 - Run the Applications
From the device emulator click on the SimpleUL application. When the application launches, it will connect and populate the Customer combo box as follows:
Next Steps
Some next steps you might want to take to enhance the application might be to:
- Populate a text box with a
customer address once a customer is chosen - Add the ability to update or delete a
customer - Add database replication using MobiLink
Summary
As we have seen in the above project, UltraLite is an extremely embeddable database. As you start using the database, you will also appreciate the performance and functionality included in the database engine. The small footprint also makes it a perfect companion for any mobile database application.
