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 codeThe 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.
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 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 codeNow the SQL looks like the following:
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();
}
DELETE FROM CustomerIf 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.
WHERE CustomerId = @CustomerId
