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

SQL Server Compact Code Snippet of the Week #8 : script a database to SQLite

DZone's Guide to

SQL Server Compact Code Snippet of the Week #8 : script a database to SQLite

· Performance Zone ·
Free Resource

Learn how error monitoring with Sentry closes the gap between the product team and your customers. With Sentry, you can focus on what you do best: building and scaling software that makes your users’ lives better.

 

This next instalment switches gears, and will demonstrate a simple usage of my SQL Server Compact scripting API, available on CodePlex.

This sample demonstrates how to create a script in SQLite format, but the general usage pattern is the same no matter what you are scripting. Notice that only three lines of code are required to complete this task!

In order to use the API, you must have the SQL Server Compact runtime installed and then reference the scripting API files. In order to script then add references to the scripting API DLL files,  SqlCeScripting40.dll and ISqlCeScripting.dll (use SqlCeScripting.dll instead of SqlCeScripting40.dll if you want to script 3.5 database files).

using ErikEJ.SqlCeScripting;

namespace SqlCeScript
{
    class Program
    {
        static void Main(string[] args)
        {
            using (IRepository repository = new DB4Repository(@"Data Source=C:\Data\SQLCE\Test\nw40.sdf"))
            {
                IGenerator generator = new Generator4(repository, @"C:\Temp\nwlite.sql");
                generator.ScriptDatabaseToFile(Scope.SchemaDataSQLite);
            }
        }
    }
}


First you initialize an IRepository instance, which takes a SQL Server Compact connection string as parameter. The you initialize a IGenerator instance, which takes a IRepository instance and an optional file name as parameter.


Then you can simply call ScriptDatabaseToFile which takes a scope a parameter, which defines what to be scripted; data, schema, schema and data, or as in this case schema and data in SQLite format.


The following IRepository implementations are available: DBRepository (SQL Server Compact 3.5 SP2), DB4Repository (SQL Server Compact 4.0) and ServerDBRepository (SQL Server 2005 or later)


In order to create a SQLite database for the script file created (c:\temp\nwlite.sql), you can use the sqlite3.exe command line utility like so:

sqlite3 nwlite.db < nwlite.sqlThis sample demonstrates how to create a script in SQLite format, but 
the general usage pattern is the same no matter what you are scripting. 
Notice that only three lines of code are required to complete this task!


What’s the best way to boost the efficiency of your product team and ship with confidence? Check out this ebook to learn how Sentry's real-time error monitoring helps developers stay in their workflow to fix bugs before the user even knows there’s a problem.

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