Reading and Writing Images From a Windows Mobile Database using UltraLite 10 (C#)

• Download UltraLite Images sample source - 20.59 KB

Introduction

Periodically, I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet, you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store an image in a database? Well in an environment where you are synchronizing data between a local mobile database and a consolidated (central) database, this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database, this chart could be sent down to the doctor's device once it became available.

For this article, I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink.

Requirements

For this sample, I chose to use UltraLite as the local database. I am also using ADO.NET to access this database. As such, you will require the following components to get started:

• SQL Anywhere 10 Developer Edition: (Please make sure to download and install the SQL Anywhere CE components)
• Visual Studio .NET 2005
• Windows Mobile 5 Software Development Kits (Standard and Professional)

Getting Started

I have included a project which should have everything you need to get started. If you are not familiar with UltraLite or how to add namespace references, I would recommend that you check out my previous article: "10 Steps to Creating a Windows Mobile Database Application Using .NET and UltraLite 10".

Creating the UltraLite Database

The first thing we want the application to do is to connect to a local UltraLite database. There are many ways to create an UltraLite database such as:

1. Sybase Central: Graphical User Interface for configuring all SQL Anywhere 10 Components
2. ULCREATE.exe: Command line utility for initializing a new database
3. Application: From within your application, you can execute code to create a new database

For this sample, we will use the third option and create the database within the application. The way we will do this is to check if the file exists. If it does not, the database and table will be created. If the database does exist, a connection will be created and the application will open. Here is the code that is used to create the database and tables on the device:

// This will create a new database
ULConnectionParms openParms = new ULConnectionParms();
    openParms.DatabaseOnCE = dbf;
    
    // Assumes file %sqlany10%\src\ulcollations\cs\coll_1252LATIN1.cs is
    // also compiled in the current project
    ULConnection.DatabaseManager.CreateDatabase(
        openParms.ToString(), Collation_1252LATIN1.Data, ""
);
    ConnUL.ConnectionString = "dbf=" + dbf + ";cache_size=1M";
    if (ConnUL.State != ConnectionState.Open)
    {
        ConnUL.Open();
}
    ConnUL.DatabaseID = 1000;
    
    // Create table to store images
    string CreateDb = "create table images ( ";
    CreateDb += "image_id integer default autoincrement, ";
    CreateDb += "image_name varchar(30), ";
    CreateDb += "image_binary long binary, ";
    CreateDb += "primary key (image_id) ";
    CreateDb += ")" ;
    
    ULCommand cmd = new ULCommand(CreateDb, ConnUL);
    cmd.ExecuteNonQuery(); 

Reading the File from the File System

The first step in this application will be to allow the user to choose a file from the Windows Mobile Device and then load it. The following code outlines how to do this:

private void menuSave_Click(object sender, EventArgs e)
{
    // Open a file dialog to choose a file and then load the 
    // binary data into byte[] fileData

    FileStream strm;
    byte[] fileData = null;

    OpenFileDialog openFileDialog1 = new OpenFileDialog();

    openFileDialog1.Filter = "All images|*.bmp *.jpeg *.jpg *.gif|All files
	(*.*)|*.*|JPEG files (*.jpeg)|*.jpeg|JPG files (*.jpg)|*.jpg|Bitmap
	files (*.bmp)|*.bmp|GIF files (*.gif)|*.gif";

    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        String fname = openFileDialog1.FileName;
        int n;
        
        string fullFileName = fname;
        
        // lose the extension
        n = fname.LastIndexOf('.');
        if (n >= 0)
        {
            fname = fname.Substring(0, n);
        }
        
        // lose the path
        n = fname.LastIndexOf('\\');
        if (n >= 0)
        {
            fname = fname.Substring(n + 1);
        }
        
        myFile = fname;
        
        try
        {
            strm = new FileStream(fullFileName, System.IO.FileMode.Open);
            
            long iLength = strm.Length;
            fileData = new byte[(int)strm.Length];
            strm.Read(fileData, 0, (int)strm.Length);
            strm.Close();
            
            // add it to the DB (if not pre-canned)
            bool return_code = SaveImage(fileData, iLength, myFile);
            
            //Update the combo box
            UpdateComboFileName();
            comboFileName.Text = myFile;
            ShowPreview();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return;
        }
        
        ShowPreview();
    }
}

Loading the Image into the Database

Now that we have chosen and read in a file, we want to take this file stream and store it in the database. For this sample, we will be storing it in a LONG BINARY column. The structure of the UltraLite table is as follows:

create table images (
    image_id integer default autoincrement,
    image_name varchar(30),
    image_binary long binary,
    primary key (image_id)
) 

The following code shows how we can take the binary data and insert it into the database:

public bool SaveImage(byte[] fileData, long imageLen, String filename)
{
    // Take the binary fileData and insert it into the database
    ULParameter parm;
    long RowsInserted;
    
    try
    {
        using (ULCommand cmd = ConnUL.CreateCommand())
        {
            cmd.CommandText =
                "INSERT INTO images(image_name, image_binary) VALUES (?, ?)";
            cmd.Parameters.Add("image_name", filename);                   
            
            parm = new ULParameter();
            parm.ULDbType = ULDbType.Binary;
            parm.Direction = ParameterDirection.Input;
            parm.Value = fileData;
            parm.Size = Convert.ToInt32(imageLen);
            cmd.Parameters.Add(parm);
            
            RowsInserted = cmd.ExecuteNonQuery();
        }
    }
    catch (Exception err)
    {
        MessageBox.Show("Exception: " + err.Message);
    }
    
    return true;
}

Selecting Images from the Database

Now that we have stored the image in the database, we will also want to be able to display it. For this, we will use a Combo Box which will be populated with a list of files which can be retrieved from the database. When a user chooses a file, the corresponding image will be retrieved and displayed. Once I select the image, I choose to use MemoryStream to store the binary data. Most of the samples I have seen have first stored it to the local file system and then load it directly from there. To me this seems incredibly inefficient, but I am sure there is someone that knows much more about programming then I do who will tell me why they need to do this. The underlying code which is used to display the image is as follows:

private void ShowPreview()
{
    // Based on the selected image filename, select the binary data for the
    // image from the database and display it
    try
    {
        using (ULCommand cmd = ConnUL.CreateCommand())
        {
            cmd.CommandText = "SELECT image_binary from images 
		WHERE image_name = '" + comboFileName.Text +"'";
            ULDataReader rdr = cmd.ExecuteReader();
            
            if (rdr.Read())
            {
                byte[] m_image = (byte[])rdr["image_binary"];
                MemoryStream ms = new MemoryStream(m_image);
                Bitmap bmap = new Bitmap(ms);
                
                pbPreview.Image = (bmap);
            }
            
            rdr.Close();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Deploying and Running the Application

The attached source includes all of the code you need to compile and deploy the application. Please note that you may need to re-attach the coll_1252LATIN1.cs file which is located in...

%sqlany10%\src\ulcollations\cs\coll_1252LATIN1.cs 

...and possibly the ulnet10.dll file which is located in:

%sqlany10%\ultralite\UltraLite.NET\ce\arm\ulnet10.dll

Summary

Images are quickly becoming a popular addition to mobile application. Now that devices are coming standard with image capture devices, the need to store images into a database becomes increasingly important.

In this sample, we used UltraLite as the local data store, however since the application was using standard SQL via ADO.NET, the concept should apply equally well to virtually any Windows mobile database.

History

• 28^th May, 2007: Initial post