Wednesday, 23 April 2008

Database Options For Windows Mobile

In my last post I discussed connecting to MS Access DBs using C# on the full Windows platform. If the target platform is Windows Mobile 5.0, running .NET 2.0, then it is necessary to use a different type of DB. Access DBs are bloated, processor hungry and not available for the Mobile platform. I have found SqlCe to be the best DB option for the Pocket PC since it is lightweight and fully supported by default.

As mentioned in my last post, DataSets are not associated with any particular database or structure until you use them. This means all of the techniques described for OleDb are applicable with SqlCe - just replace OleDb with SqlCe in the code. There are some differences in the connection string which describes the connection but that is the only difference.

To create the initial SQL DB file you will need to use Visual Studio 2005's Data>Add New Data Source and use the Wizard to create a blank .sdf file with password protection.

More Info
Again the MSDN site is very helpful:
  • SQL Syntax and data types for use with SqlCE
  • Windows Mobile can open .sdf DBs and display the contents in tabular form. This can be very helpful when debugging. You could also use it to create the blank DB
  • In SqlCe the 'CE' stands for Compact Edition. This means that not all of the SQL commands are available to use. IF/ELSE/EXISTS for example are not supported
  • Errors in SQL string formatting manifest as 'Token' errors, so if you see references to Tokens, its probably your SQL statement.


The full code for SqlCe
The following code illustrates opening db, creating table, reading table, adding row, and closing connection using the SQL Mobile Server DB. Its very similar to the code in the last post:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.Xml.Serialization;
using System.IO;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
GpsDb myGpsDb = new GpsDb();
myGpsDb.getTable("roads");
}
}
class GpsDb
{
private SqlCeConnection conn = new SqlCeConnection();
private SqlCeDataAdapter adapter = new SqlCeDataAdapter();
private DataSet ds;

public bool Open()
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
else
{
return true;
}
}
catch (SqlCeException ex)
{
Console.WriteLine("SqlCe Error: {0}", ex.Message);
return false;
}
return true;
}

public bool Close()
{
try
{
conn.Close();
Console.WriteLine("Connection Closed");
}
catch (SqlCeException ex)
{
Console.WriteLine("OleDB Error: {0}", ex.Message);
return false;
}
return true;
}//end close

public DataTable getTable(string tableName)
{
Console.WriteLine("File Exists = {0}", File.Exists(@"tax.sdf"));

// create the adapter and fill the DataSet
conn.ConnectionString = "Data Source = '\\Program Files\\ConsoleApplication1\\taxdb.sdf';" +
"Password = 'demeter'; File Mode = 'shared read'; ";

SqlCeCommand command = new SqlCeCommand(
"CREATE TABLE roads (id int IDENTITY (1,1) PRIMARY KEY, road integer, name nvarchar (50), toll integer, x integer, y integer)", conn);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

command = new SqlCeCommand("SELECT * from roads", conn);
adapter.SelectCommand = command;
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(adapter);
Open();
ds = new DataSet("roads");
adapter.Fill(ds, "roads");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0} Costs {1} per mile", dr["name"].ToString(), dr["toll"].ToString());
}

DataRow newRoadsRow = ds.Tables["roads"].NewRow();
newRoadsRow["road"] = 5664;
newRoadsRow["name"] = "St Chris";
newRoadsRow["toll"] = 7;
ds.Tables["roads"].Rows.Add(newRoadsRow);
adapter.Update(dt);
Close();

return dt;
}//end getTable

}
}

No comments: