Click here to Skip to main content
15,868,141 members
Articles / Mobile Apps

10 Steps to Creating a Windows Mobile Database Application Using .NET and SQL Anywhere 10

Rate me:
Please Sign up or sign in to vote.
4.20/5 (4 votes)
24 May 2007CPOL6 min read 142.5K   755   56   32
The purpose of this article is to help get a user started in building a mobile database application using SQL Anywhere and Visual Studio .NET (C#)

Introduction

I chose to create this sample after completing my previous article: 10 Steps to Creating a Windows Mobile Database Application Using .NET and UltraLite 10. I have had a few requests to write a similar article, this time targeting SQL Anywhere 10.

The purpose of this article is to help get a user started in building a mobile database application using SQL Anywhere 10 and Visual Studio .NET (C#).

What is SQL Anywhere 10?

SQL Anywhere is an extremely powerful enterprise caliber relational database which is available on platforms such as Linux, Windows, UNIX, MacOSX and Windows Mobile. From a Windows Mobile developer perspective, SQL Anywhere can be very attractive because of its support for enterprise database features such as stored procedures, triggers, database events and a full transaction log for rollback and recovery. Although SQL Anywhere is larger (~5.6 MB) than many of the "embedded" databases currently on the market for Windows Mobile, it also has a far greater subset of SQL support and the optimizer allows for extremely complicated queries to be executed on Windows Mobile in a very short period of time. SQL Anywhere is also binary compatible across platforms which means that you can create the SQL Anywhere database on an operating system such as Linux and physically copy the database (.db and .log) to any supported platform to get equivalent functionality. In Windows Mobile environments, SQL Anywhere is typically involved in database synchronization, however, 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 SQL Anywhere database, such as Sybase Central which is a GUI tool for administering database and synchronization. For this tutorial, I will be using command line utilities to create the database and populate it with some tables and data. To do this, please download the attached project and execute the following batch file:

create_db.bat 

After the batch file has completed, you should have a database that looks like the following. You can verify this by executing: dbisql -c "dbf=simplesa.db;uid=dba;pwd=sql" and then executing the query: Select * from customer

Screenshot - Sybase_Central2.jpg
Sybase_Central

Step 2 - Create the Project

The next 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 SimpleSA, choose a location to save the project and press OK.

Visual Studio SQL Anywhere

Step 3 - Add SQL Anywhere Namespace

Now that we have created a template C# application, we will now want to add the SQL Anywhere ADO.NET namespace. To do this, choose Project | Add Reference | Browse. Then locate the iAnywhere.Data.SQLAnywhere.dll file which is located in your SQL Anywhere 10 install directory under \ce\assembly\v2 and press OK.
Note: If the component is not listed, please ensure that you have installed the SQL Anywhere 10 CE components within the main install (it is not installed by default).

Visual Studio SQL Anywhere Namespace

Step 4 - Add SQL Anywhere Database Engine Components

During your install of SQL Anywhere 10, you likely have already installed SQL Anywhere 10 to your device. This installed multiple megabytes of files which you may very well not use within your application. As such, I prefer to only include the components I require and include them within my project. For our simple sample, we only need the database components (e.g. we do not need the synchronization or messaging components) so we can choose to include only the following components:

  • dbdata10.dll: Support file (e.g. error messages, etc.)
    • NOTE: As of 10.0.1 this file is now built into iAnywhere.Data.SQLAnywhere.dll and as such is not required
  • dblgen10.dll: English language DLL
  • dbsrv10.exe: Main database server engine

To include these components, choose Project | Add Existing Item. In the File Name directory, switch to the SQL Anywhere 10 installation directory and choose \ce\arm.50. Next change the File Types to "All Files (*.*)". Choose the above three files and press Add. Choose these three files 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".

For more information about deploying specific components, please refer to this documentation.

Visual Studio SQL Anywhere Database Components

Step 5 - Add SQL Anywhere Database

We are now going to include the SQL Anywhere database within the application. To attach the database, choose File | Add Existing Item and switch to the directory in which you ran create_db.bat in Step 1. Choose simplesa.db and simplesa.log and press Add. Just as we did in the previous step, choose the simplesa.db and simplesa.log files 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".

Visual Studio SQL Anywhere Database Files

Step 6 - Add Form Controls

Next we will want to start adding controls to query the database. For this we will choose a combo box. As such drag a combo box on to the form and name it comboCustomers as follows:

Visual Studio SQL Anywhere Database Add Controls

Step 7 - Add Reference to SQL Anywhere Namespace

To do this right click on the form and choose "View Code". At the top near the other references add a reference to the SQL Anywhere namespace by adding the following line:

C#
using iAnywhere.Data.SQLAnywhere;

Step 8 - Connect and Query the SQL Anywhere Database

Now that we have created and attached an SQL Anywhere database, the next step is to add the logic to connect and query the database. To start, we will simply select from the database. For additional capabilities please see the SQL Anywhere 10 Online Help.

Once again switch to the code for the Form1.cs and add the following line to the end of the Form1 function:

C#
Connect();

Next add a new function as follows:

C#
private void Connect()
{
    SAConnection conn = new SAConnection
            ("DBF=\\Program Files\\SimpleSA\\SIMPLESA.DB;UID=DBA;PWD=sql");

    try
    {
        conn.Open();

        SACommand cmd = new SACommand
            ("select last_name + ', '+ 
            first_name from customers order by last_name", conn);
        SADataReader reader = cmd.ExecuteReader();

        while (reader.Read())
            comboCustomers.Items.Add(reader.GetString(0));

        reader.Close();
        conn.Close();
    }
    catch (SAException ex)
    {
        MessageBox.Show(ex.Errors[0].Message);
    }
}

Notice how we can refer to the SQL Anywhere database file and have the application automatically start and stop the engine as required.

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 deploy 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\simplesa.

Windows Mobile Emulator - SQL Anywhere

Step 10 - Run the Applications

From the device emulator, click on the SimpleSA application. When the application launches, it will connect and populate the Customer combo box as follows:

Windows CE Emulator - SQL Anywhere Application

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 (don't forget to include the additional DLL and EXE components)

Summary

As we have seen in the above project, even though SQL Anywhere is an enterprise caliber database, it is also an extremely embeddable database. As you start using the database, you will also appreciate the performance and functionality included in the database engine. The complex database optimizer and superior database functionality make it a perfect companion for even the most complex mobile application.

License

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


Written By
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

 
Questionmerge replication Pin
Member 1091547813-Oct-14 19:31
Member 1091547813-Oct-14 19:31 
QuestionHow to do this Using C#.net 2008 Pin
Chanuri7-Jan-09 21:58
Chanuri7-Jan-09 21:58 
Generali am receivein reference missing errors Pin
Meteor Garden17-Oct-08 23:34
Meteor Garden17-Oct-08 23:34 
GeneralI just cannot run create_db.bat!!! Pin
littleting12-Oct-07 15:55
littleting12-Oct-07 15:55 
Generalmobile application Pin
pratima119-Aug-07 23:30
pratima119-Aug-07 23:30 
Generallicense file could not be found Pin
jambo.shan14-Aug-07 15:44
jambo.shan14-Aug-07 15:44 
GeneralRe: license file could not be found Pin
Qulor24-Feb-10 4:57
Qulor24-Feb-10 4:57 
Generalpblm with windows mobile database application Pin
jambo.shan13-Aug-07 17:10
jambo.shan13-Aug-07 17:10 
QuestionDeploy costs? Pin
rdab10010-Jun-07 1:58
rdab10010-Jun-07 1:58 
AnswerRe: Deploy costs? Pin
Liam Cavanagh11-Jun-07 4:36
Liam Cavanagh11-Jun-07 4:36 
GeneralQuestion Pin
v_cs7-Jun-07 13:10
v_cs7-Jun-07 13:10 
AnswerRe: Question Pin
Liam Cavanagh7-Jun-07 16:09
Liam Cavanagh7-Jun-07 16:09 
GeneralRe: Question Pin
v_cs8-Jun-07 8:12
v_cs8-Jun-07 8:12 
AnswerRe: Question Pin
Liam Cavanagh8-Jun-07 8:24
Liam Cavanagh8-Jun-07 8:24 
GeneralRe: Question Pin
v_cs9-Jun-07 4:06
v_cs9-Jun-07 4:06 
GeneralRe: Question Pin
Liam Cavanagh11-Jun-07 4:47
Liam Cavanagh11-Jun-07 4:47 
GeneralRe: Question Pin
Magnock9-Nov-07 7:05
Magnock9-Nov-07 7:05 
Questionfailure code 1? Pin
spdygnlz4-Jun-07 18:31
spdygnlz4-Jun-07 18:31 
AnswerRe: failure code 1? Pin
Liam Cavanagh5-Jun-07 3:02
Liam Cavanagh5-Jun-07 3:02 
GeneralConn.Open() MissingMethodExceptions Pin
lytpus29-May-07 7:42
lytpus29-May-07 7:42 
AnswerRe: Conn.Open() MissingMethodExceptions Pin
Liam Cavanagh29-May-07 9:12
Liam Cavanagh29-May-07 9:12 
GeneralSynchronize Pin
lytpus24-May-07 10:01
lytpus24-May-07 10:01 
AnswerRe: Synchronize Pin
Liam Cavanagh24-May-07 10:12
Liam Cavanagh24-May-07 10:12 
GeneralRe: Synchronize Pin
lytpus24-May-07 10:35
lytpus24-May-07 10:35 
AnswerRe: Synchronize Pin
Liam Cavanagh24-May-07 10:42
Liam Cavanagh24-May-07 10:42 

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

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