I have used ErikEJ's SQLite/SQL server Compact Toolbox which allows migration from a compact database to SQL Server, but I am trying to implement this process into my ASP.NET application.
The user will have an already completed SQL Server Compact database.
The process will be
1) User selects the database
2) Existing SQL Server database will be deleted
3) Compact database will be scripted/migrated into SQL Server
The part I am unsure of is how I should script or migrate the SQL Compact database. Deleting and creating a new database on the server itself seems easy enough. I have been digging around in the System.Data.SqlServerCe class for a while and am unsure what the best approach would be.
Using my scripting API, you can do something like this:
using (IRepository ceRepository = new DB4Repository(@"Data Source=C:\Data\SQLCE\Test\nw40.sdf"))
{
string fileName = Path.GetTempFileName();
var generator = new Generator4(ceRepository, fileName);
generator.ScriptDatabaseToFile(Scope.SchemaData);
using (IRepository serverRepository = new ServerDBRepository4("Data Source=.;Trusted_Connection=true;Initial Catalog=Test"))
{
serverRepository.ExecuteSqlFile(fileName);
}
}
See my blog post here: http://erikej.blogspot.dk/2013/03/sql-server-compact-code-snippet-of-week_8.html
A more complete implementation (from my Toolbox)