Of all the situations you can be in using SSCE, I think one of the most common will be to pull data from a larger database provider and populate a table in your local SSCE database. While SSCE does have some nifty replication support, it only works with SQL Server. Like many of you I have to support data coming from many different places, such as Oracle, Access, and XML to name but a few.
The easiest way to pull the data is to get it into an ADO.Net DataTable, but what then? I sure didn’t want to have to create the insert statements by hand each time, plus I wanted to be able to reuse my routines. Being a lazy individual, I wanted a routine I could pass any DataTable into and push the data into my SSCE table.
I started with a blank class, and created a few class level variables. One was out of a SqlCeConnection which I named _connection. I also had two strings to hold the database name and password. Next, I created a “ConnectionString” method, identical to what I described a few days ago.
private string ConnectionString()
I then created an Open method, which is pretty simple:
public void Open()
if (_connection == null)
_connection = new SqlCeConnection(this.ConnectionString());
if (_connection.State == ConnectionState.Closed)
OK, now for the grand finale.
public void Populate(DataTable myDataTable
, string tableName)
// If the datatable has no rows, we’re wasting
// our time, get outta dodge.
if (myDataTable.Rows.Count == 0)
// Make sure database is open for business
// Use a string builder to hold our insert clause
StringBuilder sql = new StringBuilder();
sql.Append(“insert into “ + tableName + ” (“);
// Two more, one for the list of field names,
// the other for the list of parameters
StringBuilder fields = new StringBuilder();
StringBuilder parameters = new StringBuilder();
// This cycles thru each column in the datatable,
// and gets it’s name. It then uses the column name
// for the list of fields, and the column name in
// all lower case for the parameters
foreach (DataColumn col in myDataTable.Columns)
parameters.Append(“@” + col.ColumnName.ToLower());
if (col.ColumnName !=
sql.Append(fields.ToString() + “) “);
sql.Append(parameters.ToString() + “) “);
// We now have our Insert statement generated.
// At this point we are ready to go through
// each row and add it to our SSCE table.
int rowCnt = 0;
string totalRows = myDataTable.Rows.Count.ToString();
foreach (DataRow row in myDataTable.Rows)
= new SqlCeCommand(sql.ToString(), _connection);
foreach (DataColumn col in myDataTable.Columns)
// Optional: I created a delegate called message delegate,
// and assign it to the class level variable Cachemessage
// It’s a simple method that takes one string and displays
// the results in a status bar. If you want to simplify
// things, just remove this entire section (down to
// the try).
if (_CacheMessage != null)
if ((rowCnt % 100) == 0)
_CacheMessage(“Loading “ + tableName
+ ” Row “ + rowCnt.ToString()
+ ” of “ + totalRows);
// Here’s where all the action is
// sports racers! This is what sends
// our insert statement to our local table.
catch (Exception ex)
// You’ll probably want to be a bit more
// elegant here, but for an example it’ll do.
My code comments elaborate pretty well on what’s going on, a quick read should be all you need. Only two things to really point out. First, I create a delegate to send a progress message. I’ll go into more on delegates another day, if need be, there’s a ton of info on them out on the web. Let me show you the declarations I used for my delegates so you can repeat them in your class:
private CacheMessageHandler _CacheMessage;
public void Messenger(CacheMessageHandler messageRoutine)
_CacheMessage = messageRoutine;
I created a method in the calling program called ShowStatus. Very simple, takes one string as a parameter and displays it somewhere to the user. (I chose a status bar, you might use a label). All I had to do was call the Messenger method like so:
In retrospect I could also have created a CacheMessage property, I just didn’t think of it at the time. If you paste in the declarations you should be able to use the method even though you never use the delegate (note I check to see if _CacheMessage is null) but if you have issues, just delete that small section, it’s not that important to the process of loading the table.
The other major thing, and a gold star to you if you already noticed this: in order for this method to work, the column names in your DataTable must match exactly with the column names in your SSCE table!
Personally this seems like a small price to pay, and frankly if you are replicating data it will make your debugging and programming life much easier as you work through bugs. I do this as a standard, which is why this kind of routine fits well into my environment.
This wraps up (for now) my exploration of SQL Server Compact Edition. If you have decided to leverage SSCE in your own apps, please leave a comment, let us all know what your experiences have been, problems, benefits, and the like.