Programmatically script or migrate SQL Compact database for importing into SQL Server

Question

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.

ErikEJ · Accepted Answer · 2017-06-14 18:51:46Z

1

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)

https://github.com/ErikEJ/SqlCeToolbox/blob/master/src/GUI/SqlCe35Toolbox/Commands/SqlServerDatabaseMenuCommandsHandler.cs#L370

answered Jun 14, 2017 at 18:51

ErikEJ

42.1k5 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sosil Over a year ago

This is really great, thanks for creating these tools

Collectives™ on Stack Overflow

Programmatically script or migrate SQL Compact database for importing into SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related