dimanche 13 septembre 2015

Create SQLite db file runtime

I've spent a lot of time to find how create SQLite DB file runtime using EntityFramework having only model classes and DbContext

public class A1
{
    public int Key {get;set;} 
    public string Name {get;set;} 
}

There was a task to create DB file if it was not found using EF tool + System.Data.SQLite. Found solutuion were related to sqlite.net (that was not actually approved for using or other custom hand written classes).

Finally I've found a solution using 2 Nuger packages: System.Data.SQLite и SQLite.CodeFirst.

Ny own DBContext overrides OnModelCreating functions and involved SQLite.CodeFirst feature.

public sealed class SampleDbContext : DbContext
{
    public SampleDbContext(SQLiteConnection sqLiteConnection)
        : base(sqLiteConnection, false)
    {
    }

    public DbSet<A1> A1{ get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var initializer =
            new SQLite.CodeFirst.SqliteDropCreateDatabaseAlways<SampleDbContext>(modelBuilder);
        Database.SetInitializer(initializer);

        base.OnModelCreating(modelBuilder);
    }
}

A code to create db file.

    public void CreateDatabaseFile(string path)
    {
        try
        {
        using (SQLiteConnection sqLiteConnection = CreateConnection(path))
        using (SampleDbContext context = new SampleDbContext(sqLiteConnection))
        {
            DropCreateDatabaseAlways<SampleDbContext> initializator =
                new DropCreateDatabaseAlways<SampleDbContext>();
            Database.SetInitializer(initializator);

            context.SaveChanges();
            // do not delete this log trace. we need some DB touch
            _log.Trace("Db Context validated. Found {0} Customer(s)", context.A1.Count());
        }
        }
        catch (Exception ex)
        {
        _log.Error(ex);
        }
    }

    public static SQLiteConnection CreateConnection(string path)
    {
        SQLiteConnectionStringBuilder builder =
        (SQLiteConnectionStringBuilder) SQLiteProviderFactory.Instance.CreateConnectionStringBuilder();
        builder.DataSource = path;
        builder.FailIfMissing = false;

        return new SQLiteConnection(builder.ToString());
    }

And some magic in app.config (few lines added by manually). Does anybody know more beatifull solution ?

  <system.data>
                <DbProviderFactories>
                  <remove invariant="System.Data.SQLite.EF6" />
                  <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
              <remove invariant="System.Data.SQLite" />
                  <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
            </DbProviderFactories>
          </system.data>
          <entityFramework>
                <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
                  <parameters>
                        <parameter value="v12.0" />
                  </parameters>
                </defaultConnectionFactory>
                <providers>
                  <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
              <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
                  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
            </providers>
          </entityFramework>

Aucun commentaire:

Enregistrer un commentaire