Data Transfer Between SQL Server and SQL Server Compact

Question

I am using VS 2008 with SQL Server 2005 and SQL Server Compact Framework 3.5

I have mobile devices(They have Window Ce 6.0 os).
I installed my program to windows ce devices
I am using .sdf database in device.
.SDF and .MDF database schema are the same

Workers use this devices out of company and save data.

When they arrive at the company I have to save the data from their .sdf files into a SQL Server instance.

I do it like this:

public void TransferData()
{
   SqlCeCommand tempcecommand=new SqlCeCommand("select * from Customer",SDFConnection);
   SqlCeDataReader tempcereader=tempcecommand.ExecuteReader();

   SqlCommand tempmscommad=new SqlCommand("",MDFConnection);

   while(tempcereader.Read())
   {
      tempcommandtext = String.Format("insert into Customer (Column1,Column2) values       ('{0}','{1}')",tempcereader["Column1"],tempcereader["Column2"] ;

      tempmscommand.CommandText = tempmscommand;
      tempmscommand.ExecuteNonQuery();
   }
}

Is there an easier way to transfer the data from the .sdf file into the SQL Server instance?

score 2 · Accepted Answer · edited May 23 '17 at 12:28

A first approach could be

string cmdText = "INSERT INTO CUSTOMER (Column1, Column2) VALUES (@col1, @col2)"
SqlCommand tempmscommad=new SqlCommand(cmdText,MDFConnection);
tempmscommad.Parameters.AddWithValue("@col1", "");
tempmscommad.Parameters.AddWithValue("@col1", "");
while(tempcereader.Read())
{
    tempmscommad.Parameters["col1"].Value = tempcereader["Column1"].ToString();
    tempmscommad.Parameters["col2"].Value = tempcereader["Column2"].ToString();
    tempmscommand.ExecuteNonQuery();
}

This will still loop over your CE data, but remove the string formatting inside the loop and use a more secure parameterized query. However, this will require to execute a command for each record to be inserted.

If you are ABSOLUTELY sure that your values could not be tampered by your user when importing, then it is possible to take advantage of the multiinsert capabilities of Sql Insert

BE AWARE - This way is not really recommended because it use string concatenation, a very dangerous practice when dealing with database commands.
You NEED to be certain that your input values are SANITIZED

StringBuilder cmdText = new StringBuilder("INSERT INTO CUSTOMER (Column1, Column2) VALUES ");
while(tempcereader.Read())
{
    cmdText.AppendFormat("('{0}', '{1}'),", 
            tempcereader["Column1"].ToString(),
            tempcereader["Column2"].ToString())
}
// remove the last comma
cmdText.Length -= 1;

// Just execute one single command 
SqlCommand tempmscommad=new SqlCommand(cmdText.ToString(),MDFConnection);
tempmscommad.ExecuteNonQuery();

Finally, THE BEST OPTION should be to use the class SqlBulkCopy. I have no way to test it because I don't have a SqlCE data available, but, giving the fact that the WriteToServer method accepts an IDataReader instance I suppose that it should work also with an SqlCeDataReader

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MDFConnection))
{
    bulkCopy.DestinationTableName = "CUSTOMER";
    bulkCopy.WriteToServer(tempcereader);
}

Please test it....

Data Transfer Between SQL Server and SQL Server Compact

1 Answers1