Monday, 28 April 2008

Student Robotics Blog

As well as this blog, I have also started a blog dedicated to Student Robotics activities, electronics and ideas. It is at: http://chris-sr.blogspot.com

Setting up Windows Mobile for SQL CE

My battle against Windows Mobile 5 continues. Set back initially by its limited support for databases, i was forced to adapt my (poorly written) code to use SqlCe which doesn't even fully support the SQL dictionary.

With some - even if limited - database implementation I hoped to move from the VS Emulator to the actual device. Predictable this was not easy. Despite SqlCe .sdf file capabilities and even a program already on the device to open such files - my program refused to run.

Missing libraries. Installing these librarys was more painful than it should have been. Originally i hoped that the compiler and linker would copy all necessary code into the executable file. After all the program ran in the emulator fine. Unable to find an installer for just SqlServerCe which would run on the device itself, i manually hunted out the necessary .CAB files (as spotted in the output pane of VS during deployment) onto the device, learning during the process that they must be installed to 'This Device' and none of the other options for .CAB installation.

These tricks eventually coaxed windows into running my program. Fortunately my luck changed at this point.

I discovered a very useful way of quickly starting the emulator without having to wait for the deployment of all the library files each time. Basically, once it has successfully deployed once, enter the settings>memory>running programs and then stop all processes. Save the state (File>Save State) and close the window. Next time you hit debug it will load much quicker.

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

}
}

Tuesday, 22 April 2008

Connecting to an Access Database using C#

This may seem like a common task, and it is, however I found it ridiculously difficult to learn how to do it. The MSDN website has articles on each class involved but nowhere does it explain how to put everything together. After reading this blog you should be able to:
  • Open a connection to an existing MS Access (.mdb) database
  • Perform SELECT queries
  • Extract the results from queries
  • Modify/Add row of a table
  • Update the changes to the Database
  • Close the connection
  • Figure out for yourself how to do other operations
Opening a connection
I will assume that there already exists an MS Access database with at least one table already created. This is fairly easy to do using MS Access and there are plenty of tutorials showing you how to do this. The DB should be in the same folder as the executable file (/bin/debug). To configure a connection to a database:

OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=tax.mdb");
OleDbConnection is a special class which deals with creating a connection or 'handle' to the database. The first part of the string describes the database type, the last part is the name/location of the database file. To actually open a connection use:

conn.Open();

You should enclose this in a try/catch statement and test for OleDbExceptions. The connection is now open. To close the connection:

conn.Close();


Adapters
.NET provides support for accessing many different database types. Programmers generally don't want to worry about what particular database they are using, they just want the data. This is where Adapters come in. Adapters (Specific to each database type) present the data structures as a generic 'DataSet' object. Theoretically the database type could be changed without affecting all the code.

OleDbDataAdapter adapter = new OleDbDataAdapter();

The above statement creates a new Adapter object. OleDb is just the name of the adapter specific to Access DBs. The next step is to attach a command to the Adapter for it to execute on the DB.

OleDbCommand command = new OleDbCommand("SELECT * from roads", conn);

This statement creates a command object which will return all the rows from the table 'roads' using the connection we made earlier. Lets attach this command to the Adapter we created:

adapter.SelectCommand = command;
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

Whats actually going on here is a little more complicated. By defining the SelecCommand property of the Adapter, the OleDbCommandBuilder() function will automatically build the SQL statements necessary to update, delete and add to the database. This will come in useful later.

DataSet -> DataTable -> DataRow
As mentioned earlier, C# uses the generic 'DataSet' to store results from DB queries. Therefore we need to create a DataSet object to store the results of our query.

ds = new DataSet("roads");

The "roads" argument is optional and just gives the DataSet a name. Now we must associate our new DataSet with the results from our query:

adapter.Fill(ds, "roads");

The Fill() method will make cause the adapter to execute the SELECT command, get the results and store them in the DataSet 'ds'. 'ds' now contains a local copy of part of our database. The structure of the DataSet is identical to the structure of the DB. We can now access the data easily. DataSets contain Tables, which contain Rows which contain Columns. To access a table:

DataTable dt = ds.Tables[0];

A numerical index or the table name (string) can be used as the array index. We could iterate the rows of a table as follows:

foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0} Costs {1} per mile", dr["name"].ToString(), dr["toll"].ToString());
}

Notice the hierarchy: DataSet.DataTable.DataRow['columnName']. Using a local DataSet rather than referring to the DB all the time frees up the DB, allowing multiple connections to be made to it.
Adding Rows to the database
As mentioned before DataSets are generic, they have no default structure, no column names, no associated data types. They inherit their structure from the Data Base thanks to the OleDbAdapter. With this in mind, to add a row we therefore need to create a new object 'newRoadsRow' say, which is of the same format as the DB Table where it will end up:

DataRow newRoadsRow = ds.Tables["roads"].NewRow();

We can now enter data into the new row, addressing it as follows:

newRoadsRow["road"] = 5664;
newRoadsRow["name"] = "St Chris";
newRoadsRow["toll"] = 7;

As before, the indexes are the column names as defined in the DB. Now our data is stored, we must add this row to our local DataSet.

ds.Tables["roads"].Rows.Add(newRoadsRow);

Finally our changes must be saved to the database. Again, we need to use our Adapter:

adapter.Update(dt);

We are now in a position to close the DB connection.

More Info
For more info on these objects/classes visit the MSDN Site:

The above scripts give only a very basic grasp on DBs. There are many more features than I have described here but you should now have the knowledge to research these yourself. The full code is reproduced here, please reference this website if you use it in any projects:


//Simple DB Operations
//Requires existing DB called tax.mdb in /debug/bin folder
//
using System;
using System.Data;
using System.Data.OleDb;
using System.Xml.Serialization;

public class TaxDB
{
private OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=tax.mdb");
private OleDbDataAdapter adapter = new OleDbDataAdapter();
private DataSet ds;

private bool open()
{
// create and open the connection
try
{
conn.Open();
}
catch (OleDbException ex)
{
Console.WriteLine("OleDB Error: {0}", ex.Message);
return false;
}

return true;
}//end db open

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

public DataTable getTable(string tableName)
{
// create the adapter and fill the DataSet
OleDbCommand command = new OleDbCommand("SELECT * from roads", conn);
adapter.SelectCommand = command;
OleDbCommandBuilder cb = new OleDbCommandBuilder(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
}
public class Program
{
static void Main()
{
TaxDB myTax = new TaxDB();
myTax.getTable("roads");
}
}