Using Stored Procs on SQL CE and Windows Mobile

A cool pattern as described in the Windows Mobile Line of Business Solution Accelerator 2008 show how to implement Stored Procedures in SQL CE on Windows Mobile with the Compact Framework.

OK they are not really Stored Procedures as known in the full version of SQL Server as this wouldn't make sense on devices as SQL CE is embedded and runs inproc in the callers context. But this pattern shows a simple example of extracting that ugly SQL code from our lovely OO C#/VB code.

NOTE: We *have* to use SQL and hand cranked data models etc on devices due to the lack of support for ORM's on devices.

Take the following code (using Mobile Client Software Factory Data Access Block) which embeds SQL code in C#:

//Bad code
DbCommand cmd = database.DbProviderFactory.CreateCommand();
DbParameter id = cmd.CreateParameter();
id.DbType = System.Data.DbType.Int32;
id.ParameterName = "@CustomerId";
id.Value = guid;

cmd.Parameters.Add(id);

int i = 0;
try
{
   //Now for the SQL.
   cmd.CommandText = "delete from Customer where " +
         "customerId = @CustomerId";
   i = database.ExecuteNonQuery(cmd, null);
}
catch (SqlCeException)
{
   throw;
}
finally
{
   cmd.Dispose();
}

The above is quite ugly you probably agree or should agree :) Imagine though that there are loads of methods doing all sorts of complex stuff. It would be a nightmare to maintain that SQL. In addition how would you run that SQL on the desktop if you wanted to script stuff. Imagine also that you might have a method that creates the database from scratch, now that would be ugly in code.

The solution is to abstract the SQL out of the code into resource files containing *.sql scripts that your code calls. The added bonus with this pattern is it allows you to localize the SQL too if you wanted.

Take the good code:

//Good code
DbCommand cmd = connection.DbProviderFactory.CreateCommand();
DbParameter id = cmd.CreateParameter();
id.DbType = System.Data.DbType.Int32;
id.ParameterName = "@CustomerId";
id.Value = customerId;

cmd.Parameters.Add(id);

try
{
   //Now for the SQL.
   cmd.CommandText = Properties.StoredProcs.Customer_Del_ByCustomerId;
   connection.ExecuteNonQuery(cmd, null);
}
catch (SqlCeException)
{
    throw;
}
finally
{
    cmd.Dispose();
}

Now the SQL looks like the following:

DELETE FROM Customer
       WHERE CustomerId = @CustomerId

If you wanted to localize it, we simply add a satalite resource with another locale, easy. Of course depending on what you are doing the scripts will port over to the desktop.