Quickest way to transfer 50k rows from a SqlCE database to a Sql Server 2008 database

May 18 2010 2:20 PM

I have a simple database, containing 1 table, 6 columns and approx 50,000 rows on both a SqlCe database on a mobile device and a server sql server 2008 database.

I wish to transfer this entire database contents from the sql ce database to our Sql Server 2008. The code needs to be on the Windows Mobile device, running c# .net Compact Framework

At present I have looked at using Merge Replilcation but it looks to be an overkill as we don't need conflict resolution, etc. I also looked at using RDA but as I only want to do this big push or pull and are not bothered about any other replication, it seems a massive overkill having to use IIS, etc etc just to copy some data across.

At the present, I'm doing this:

  DataTable t = new DataTable();
    SqlCeCommand clientCmd = clientConnection.CreateCommand();

    Debug.WriteLine("Getting data..");
    strSql = "SELECT name, redeemed, redeemedTime, barcode, postcode, ticketName, status, digits  FROM tickets";

              using (SqlCeDataAdapter a = new SqlCeDataAdapter(
                     strSql, clientConnection))

foreach (DataRow r in t.Rows)

             //insert code would go here

As you can see I'm not actually inserting anything to the server yet, just retrieving rows and looping around. I would then put an insert statement where the WriteLine(x) is.

However just this looping is taking approx 30 minutes to run over 50,000 loops so I'm doing something wrong - it's taking far too long. I can download all this data over GPRS in 2 minutes so I'm not sure why its taking so long just to loop around the records?

Is there any simpler way? The database will be identical on both so I'm sure there must be an easier way. I've looked at sqlBulkCopy but its not supported on Compact Framework?


Answers (1)