How can I generate database tables from C# in order to version control the database?

Question

Currently, changes to the database are made through the SQL Server Management program. IF a table changes, sqlmetal is run to regenerate the linqtosql classes and development continues. However, this makes deployment a pain, as you have to go through and manually update the deployment database (and any other databases used in the development cycle). It would be nice if we could use C# to generate these changes, as it would help eliminate human error and have the added benefit of being able to keep the database structure in git. Right now, the only representation of the database is in the generated linqtosql classes.

I've been looking around for a nice library that can handle this sort of thing, but the main solutions seem to be: keep a sql generation script, or embed sql in C# classes that can be run to make changes to the database. Both of these seem to be very non-ideal situations, as you lose the nice strong-typing that C# provides. It seems like there should be a way to do this using pure C#.

I've seen hints of being able to do things like generate databases from POCOs using both the entity framework and linqtosql, but I'm having a hardtime finding specific examples of that being used. Additionally, I haven't been able to discover if those have a graceful (i.e. data preserving) way of handling changes to the database after the initial table generation.

Are there any projects out there that solve this problem?

Answer 1

There exist several tools that help you with schema (and data) migrations of your database: RikMigrations, Migrator.Net and Machine.Migrations. Hope that helps.

Wizardby looks also promising: It provides database independent DDL scripts and automated migrations between different versions of a schema.

Answer 2

VS2010 can operate version control on your database schema through a Database Project. There are other tools out there for DB development that offer version control, you'll need to search to find them and compare pricing.