Performance Zone is brought to you in partnership with:

Erik Ejlskov Jensen is a .NET Data Developer, and SQL Server Compact MVP. He is also the author of a number of tools for SQL Server Compact. He has been working in IT for too many years, and enjoy blogging (http://erikej.blogspot.com) and tweeting (@ErikEJ) Data Development related news and tips. Erik Ejlskov is a DZone MVB and is not an employee of DZone and has posted 60 posts at DZone. You can read more from them at their website. View Full User Profile

SQL Server Compact Code Snippet of the Week #9 : migrate a SQL Compact database to SQL Server

03.11.2013
| 1313 views |
  • submit to reddit

This week’s snippet directly follows the one from previous week, demonstrating my SQL Server Compact scripting API once again.

This time I will demonstrate how to migrate a complete SQL Server Compact database to SQL Server (LocalDB/Express/Full). The requirements are simply that the current user has access to an empty SQL Server database somewhere. Then all tables, constraints, indexes and data will be moved to the empty SQL Server database, all in just 6 lines of code:

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);
    }
}

The code requires the following using statements:

using ErikEJ.SqlCeScripting;
using System;
using System.IO;

The ServerDBRepository constructor simply requires any valid SQL Server ADO.NET connection string.

The ScriptDatabaseToFile creates a script file with all content of the database, and the ExecuteSqlFile method runs the script against a SQL Server database.



Published at DZone with permission of Erik Ejlskov Jensen, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)