.NET Development by Eric

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