EF4 and SQLite

I want to create my datalayer with Entity Framework. But I’m using SQLite as the database. How to create the database if it is a new file?
First create a database to import in EF. Model first just isn’t that simple. Import the model into VS2010 and do your thing. I keep everything as is because I just want to use the mapping to CLR objects. Then install the T4 file located here or direct link and select it as the DDL Generation Template.

Right click somewhere on your model and choose Generate Database from Model … This starts a wizard that creates the SQL statements to create the tables and stores it in a file. (added to the project) This file should be an embedded resource.

Now you can create the structure in a new database by loading the embedded resource and executing it.

public class MyManager
{
    protected virtual EntityConnection Connection
    {
        get;
        set;
    }

    public MyManager(string fileName, bool isNew)
    {
        // construct connectionstring
        var connectionString = string.Format("data source={0};Version=3;New=True;", fileName);
        // use builder to create entity connectionstring
        var builder = new EntityConnectionStringBuilder();
        builder.ProviderConnectionString = connectionString;
        builder.Provider = "System.Data.SQLite";
        builder.Metadata = @"res://*/MyModel.csdl|
                             res://*/MyModel.ssdl|
                             res://*/MyModel.msl";
        // create entity connection
        Connection = new EntityConnection(builder.ToString());
        // create tables if new database is created
        if (isNew == true)
        {
            CreateTables(Connection.StoreConnection);
        }
    }

    protected virtual void CreateTables(DbConnection connection)
    {
        // load assembly
        var assembly = Assembly.GetAssembly(typeof(MyEntity));
        // load resource from assembly with create database script
        using (Stream stream = assembly.GetManifestResourceStream("DataLayer.MyModel.edmx.sql"))
        {
            System.Diagnostics.Debug.Assert(stream != null, "Failed loading embedded resource");
            using (var reader = new StreamReader(stream))
            {
                var sql = reader.ReadToEnd();
                var cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                if (connection.State != System.Data.ConnectionState.Open) connection.Open();
                // execute create database script
                cmd.ExecuteNonQuery();
            }
        }   
    }
}

Sample project for download here

About erictummers

My work as a recruited developer changes almost every month. I like challenges and sharing the solutions with others. On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.
This entry was posted in Development and tagged , , , , , , . Bookmark the permalink.

2 Responses to EF4 and SQLite

  1. Harold Oudshoorn says:

    Hello,

    It seems like the sample project link is broken.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s