Over a million developers have joined DZone.
Platinum Partner

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

The Performance Zone is brought to you in partnership with New Relic. New Relic APM provides constant monitoring of your apps so you don't have to.

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);
    using (IRepository serverRepository = new ServerDBRepository4("Data Source=.;Trusted_Connection=true;Initial Catalog=Test"))

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.

The Performance Zone is brought to you in partnership with New Relic. New Relic’s SaaS-based Application Performance Monitoring helps you build, deploy, and maintain great web software.


Published at DZone with permission of Erik Ejlskov Jensen , DZone MVB .

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}