Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

· Performance Zone ·
Free Resource

Built by operators for operators, the Sensu monitoring event pipeline empowers businesses to automate their monitoring workflows and gain deep visibility into their multi-cloud environments. Get started for free today.

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.



Download our guide to mitigating alert fatigue, with real-world tips on automating remediation and triage from an IT veteran.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}