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

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.sql