.Net Create Database for SQLite

June 14, 2013 Uncategorized Comments Off on .Net Create Database for SQLite

I am converting a project from Linq to Sql to Entity Framework with Sqlite.

One of the methods I was using is CreateDatabase function. When first called the function it returned the following exception:

CreateDatabase is not supported by the provider.

As I looked online most of the results mentioned to create the new structure from the existing schema.

By using the connection getschema method I noticed that the create statements are returned as a column named TABLE_DEFINITION when GetSchema is called for tables.

My end result is the following which creates a new Sqlite database with the schema from an existing database.

public class SQLiteDatabaseCreate {
    public void CreateDatabase(string newDatabase, string schemaDatabase) {
        var connectionString = string.Format("data source='{0}'", schemaDatabase);
        var dt = new System.Data.DataTable();
        using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) {
            conn.Open();
            dt = conn.GetSchema(System.Data.SQLite.SQLiteMetaDataCollectionNames.Tables);
        }
 
        using (var conn = CreateConnectionForSchemaCreation(newDatabase)) {
            if (conn.State != System.Data.ConnectionState.Open) {
                conn.Open();
            }
            foreach (System.Data.DataRow dr in dt.Rows) {
                var createSql = dr["TABLE_DEFINITION"].ToString();
                var cmd = new System.Data.SQLite.SQLiteCommand(createSql, conn);
                cmd.ExecuteNonQuery();
            }
        }
    }
 
    public System.Data.SQLite.SQLiteConnection CreateConnectionForSchemaCreation(string filename) {
        var conn = new System.Data.SQLite.SQLiteConnection();
        conn.ConnectionString = new System.Data.Common.DbConnectionStringBuilder {
            {"Data Source", filename},
            {"Version", "3"},
            {"FailIfMissing", "False"},
        }.ConnectionString;
        conn.Open();
        return conn;
    }
}

To use it call it as follows:

var newDatabase = Path.Combine(Application.StartupPath, "NewDatabase.db");
var schemaDatabase = Path.Combine(Application.StartupPath, "SchemaDatabase.db");
var sdbc = new SQLiteDatabaseCreate();
sdbc.CreateDatabase(newDatabase, schemaDatabase);

Comments are closed.