Fredrick Lackey - 120 - Mar 20, 2014

What is the cleanest / safest / most elegant way of seeding a database and updating a schema using OrmLite and LocalDb (in debug only, if possible)?  I’d like to mimic EF’s logic where the DB is recreated if the schema gets out of sync.  Currently, I have a CreateSchema() method which is called the first time any of the repositories in a DAL assembly are hit.  Unfortunately, this requires me to manually drop or purge the database… which really sucks in a CI scenario.

I’m always using: db.CreateTableIfNotExists<T>();
It doesn’t compare schema’s but will recreate the table if it doesn’t exist.

Fredrick Lackey:

I was doing this initially, however exceptions were being thrown if a FK was violated mismatched.

Yeah in that case you wont be able to drop and create single tables like that, you’d need to drop them in order then create them in order.

If you’re using InMemory sqlite it’s really fast and dropping/creating each time is not a noticeable problem.

Fredrick Lackey:

Kinda figured.  Unfortunately, SQLServer & LocalDb are my targets.  Can you think of a pattern that would assist with this?  Maybe not checking for the FK itself, but resorting to a rebuild if a certain exception is thrown during the CreateTableIfNotExists?

There’s a db.TableExists() method that you can use to determine whether the “root” table exists and if not create all related tables. Basically group the work into more cachable chunks.

Fredrick Lackey:

Yeah, that’s what I’m currently doing… pretty much for this reason.  I’ll let you know if I think of some MagicDoItAll method.  Thanks, again.

Fredrick Lackey:

+Demis Bellot Will db.DropTable<>() throw if the table does not exist?  I’m trying to think through a recovery routine to regenerate the schema.  My goal is to purge all tables and rebuild.