Dynamically Creating and Loading a SQL Server Compact Table

I needed to create a local SQL Server Compact (SQL CE) copy of a remote data source to work with, so I came up with some helper methods which will:

• Dynamically infer a CREATE TABLE statement for the local SQL CE database based upon a source schema (being one of; SQL Server, SQL Server Compact, or a DataTable).
• Load the newly created SQL CE table with data from the source.
• Convert between .NET types and SQL CE compatible types.

All this is encapsulated in some static methods in a class called SqlCeHelper.

Download Locations

• The SqlCeHelper class documentation can be found here
• The SqlCeHelper class source code can be found here

Code Example

The following code connects to a remote SQL server and runs a select statement. It will then use SqlCeHelper to create a local SQL CE table compatible with the results coming back from the select statement (correct column names, SQL CE data-types, correct column lengths, etc). Finally it will use SqlCeHelper to load the SQL CE table with the results from the local server.

private void button3_Click(object sender, EventArgs e)
{
// Open a Connection to our local SQL CE Database
string constring = "Data Source=Local.sdf";
SqlCeConnection ceCon = new SqlCeConnection(constring);
ceCon.Open();

// Open a connection to SQL Server
SqlConnection con = new SqlConnection(@"Data Source=MyRemoteServer;Initial Catalog=gib_dist;Integrated Security=True");
con.Open();

// Prepare to read some data in from our source SQL Server
SqlCommand cmd = new SqlCommand("SELECT * FROM MySourceTable", con);
using (SqlDataReader reader = cmd.ExecuteReader())
{
 // Get the schema of the result set coming from the source server
 DataTable schema = reader.GetSchemaTable();

 // Create the target table on our local SQL CE database
 string tableNameToInsertTo = "MyTargetCeTableName";
 string createTableText = SqlCeHelper.GetCreateTableStatement(tableNameToInsertTo, schema);
 SqlCeCommand createTableCommand = new SqlCeCommand(createTableText, ceCon);
 createTableCommand.ExecuteNonQuery();

 // Get a command ready which can insert data to our new SQL CE table from our
 // source reader.
 SqlCeCommand ceInsertCommand = SqlCeHelper.GenerateInsertCommand(ceCon, tableNameToInsertTo, schema);

 // Loop through each row and load the data into the SQL CE table
 while (reader.Read())
 {
     SqlCeHelper.SetSqlCeCommandParametersFromSqlReader(ceInsertCommand, reader);
     ceInsertCommand.ExecuteNonQuery();
 }
}
}