Click here to Skip to main content
Click here to Skip to main content

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

, 28 May 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
The purpose of this article is to help developers who are looking to access and store images on Windows Mobile applications using C# and UltraLite.
Screenshot - windows_mobile_5_images.jpg

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:

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

  • 28th May, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Liam Cavanagh

United States United States
Liam Cavanagh currently works as a Sr. Program Manager for Microsoft focusing new cloud services in the SQL Azure organization. Within this group, Liam has created a number of new services including SQL Azure Data Sync, Microsoft Codename "Data Transfer", and Eye on Earth leveraging the Windows Azure infrastucture.
 
Additionally, he works with enterprise corporations implementing enterprise cloud and mobile solutions and conducts training seminars worldwide.
 
Liam holds a Bachelor of Mathematics degree in Business and Information Systems from the University of Waterloo in Waterloo, Ontario, Canada.
 
Specialties: Windows Azure, SQL Azure, SQL Server, Cloud, Mobile, Replication and Database Synchronization Technologies
 
Liam Cavanagh is the founder of Cotega, a data notificaton and scheduling service for cloud databases. You can contact Liam at his cloud data blog.

Comments and Discussions

 
GeneralPrint Image from Mobile Application PinmemberOksana Borisevich20-Oct-10 12:35 
Generalerror in reading image from sqlite using c# and windows mobile pocketpc 5 sdk Pinmemberyokzie7-Feb-10 5:56 
SQLiteConnection sqlite_con = new SQLiteConnection("Data Source=|DataDirectory|dbasedict.s3db;Version=3;New=False;Compress=True;");
String querry2 = "select id, word, iimages from dictionario where word = '" + searchBox.Text + "'";
SQLiteDataAdapter adap3 = new SQLiteDataAdapter(querry2, sqlite_con);
DataSet set = new DataSet();
adap3.Fill(set, "dictionario");
DataTable dataTable = new DataTable();
dataTable = (DataTable)set.Tables[0];
foreach (DataRow row in dataTable.Rows)
{
byte[] data = (byte[])row["iimages"];
MemoryStream ms = new MemoryStream(data);
Bitmap bmp = new Bitmap(ms);
pictureBox1.Image = bmp;
}
 
i try this code and it gives me an error saying: exception unhandled
please help in debugging my code. please, please, please...

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 28 May 2007
Article Copyright 2007 by Liam Cavanagh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid