Loading a SQL Server Compact Edition Table From a DataTable in C#

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()

    {

      return string.Format(

        “DataSource=\”{0}\”; Password='{1}'”

        , this._CacheDatabase

        , this._Password);

    }

 

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)

        _connection.Open();

    }

 

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)

      {

        return;

      }

 

      // Make sure database is open for business

      this.Open();

 

      // 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)

      {

        fields.Append(col.ColumnName);

        parameters.Append(“@” + col.ColumnName.ToLower());

 

        if (col.ColumnName !=

          myDataTable.Columns[myDataTable.Columns.Count

                 – 1].ColumnName)

        {

          fields.Append(“, “);

          parameters.Append(“, “);

        }

      }

      sql.Append(fields.ToString() + “) “);

      sql.Append(“values (“);

      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)

      {

        SqlCeCommand cmd

          = new SqlCeCommand(sql.ToString(), _connection);

 

        foreach (DataColumn col in myDataTable.Columns)

        {

          cmd.Parameters.AddWithValue(“@”

            + col.ColumnName.ToLower()

            , row[col.ColumnName]);

        }

        // 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).

        rowCnt++;

        if (_CacheMessage != null)

        {

          if ((rowCnt % 100) == 0)

            _CacheMessage(“Loading “ + tableName

              + ” Row “ + rowCnt.ToString()

              + ” of “ + totalRows);

        }

 

        try

        {

          // Here’s where all the action is

          // sports racers! This is what sends

          // our insert statement to our local table.

          cmd.ExecuteNonQuery();

        }

        catch (Exception ex)

        {

          // You’ll probably want to be a bit more

          // elegant here, but for an example it’ll do.

          throw ex;

        } 

      } 

    }

 

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: 

    myMethod.Messenger(

         new MyClass.CacheMessageHandler(ShowStatus))

 

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.

About these ads

6 Responses to “Loading a SQL Server Compact Edition Table From a DataTable in C#”

  1. Vitaliy Says:

    Great routine!! You just saved me a lot of time.

    Thanks!

  2. martynas Says:

    You’re doing a awesome job. Hope to find here more about SSCE in the future.

    Respect!

  3. Lucas Says:

    This is good… I implemented something similar myself. It would be much better however if the framework did that for you.

    eg you could get a dataset filled by one dataadapter and then use another dataadapter to populate another database.

  4. Thakur Says:

    good stuff!

  5. Buy ShamWows Says:

    I Love this article!! I could not of of said it any better myself

  6. King Says:

    great article… it is good to put field names into [ ] brackets… i had problem without brackets


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: