Home > Uncategorized > Convert SQLite database to SQL Compact Edition

Convert SQLite database to SQL Compact Edition

This is a script I came up with that takes data from a SQLite database, and writes it into a SQL CE (SDF) database. This is a very simple use-case, where there is only one table, no indexes, views, or primary keys. Also, the SDF file has been pre-created, and set up with the same schema as the SQLite database.

var db = new SQLiteConnection(@”Data Source=C:\source.db;Version=3″);
db.Open();
string sql = “PRAGMA table_info( yourtable);”;
SQLiteCommand command = new SQLiteCommand(sql, db);
SQLiteDataReader reader = command.ExecuteReader();
List<string> strColumnNames = new List<string>();
while (reader.Read())
{
strColumnNames.Add(reader[1].ToString());
}
db.Close();

db.Open();
sql = “select * from yourtable”;
command = new SQLiteCommand(sql, db);
reader = command.ExecuteReader();

var conn = new SqlCeConnection(@”Data Source = C:\destination.sdf”);
conn.Open();

SqlCeCommand cmd = conn.CreateCommand();

while (reader.Read())
{
try
{
sql = “insert into dictionary (” + String.Join(“,”, strColumnNames.ToArray()) + “) values (“;
var strColumnValues = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
{
strColumnValues.Add(“N'” + reader[i] + “‘”);
}
sql += String.Join(“,”, strColumnValues.ToArray());
sql += “)”;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Console.WriteLine(sql);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}

This requires the System.Data.SqlServerCe and System.Data.SQLite; namespaces

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: