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 56 posts at DZone. You can read more from them at their website. View Full User Profile

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

03.05.2013
| 1257 views |
  • submit to reddit

 

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!


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