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

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.

Answer 2

Redgate software offers something that may be really useful for you. It's called SQL Packager and it does it's job pretty well.

Features:

• Easy roll-out of database updates across your client base
• Script and compress your schema and data accurately and quickly
• Package any pre-existing SQL script as a .exe, or launch as a C# project
• Simplify deployments and updates for SQL Server 2000, 2005 and 2008

They also offer SQL Source Control which also may be useful to keep things nice and easy.

Answer 3

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 4

As an addon to MadBoy, SQL Packager can also launch the package as a C# project.

Red-Gate's SQL Compare is excellent as well, and as some of the banners on SO indicates, there is new SQL Source Control available as well.

Then they have their SQL Comparison SDK.

Answer 5

I prefer to version using sql scripts. Works pretty well, is free, supports updates, easy to version, works well with traditional source control methods.

First,

1. Create your DB
2. Use the Database Publishing Wizard to publish the database as a .sql script
3. Add a version number to the script
4. Add to your solution
5. Check into source control.

As updates are made,

1. Script updates to the previous schema as .sql files
2. Add a version number to the script that is incremented from the previous version
3. Add to your solution
4. Check into source control.

Answer 6

It sounds like you need a tool like Migrator.NET to manage your database migrations. We use it with a call from our site start-up to migrate the database as needed for any particular version.

I have toyed with an idea for creating a cleaner interface and someday hope to get around to implementing it, but other priorities have pushed that back. For now we are using raw sql strings in our migrations because there isn't a sybase driver implementation (outside of a very ugly hack I have written to manage the versioning table).

Answer 7

Hey mate,

The trick here is to rely on the database being the single source of truth for your Linq schema, not the generated classes.
We use Linq to SQL extensively in our dev shop, and work as follows:
1. Create your database (working copy) from version control (baseline).
2. Modify your database any which way you like.
3. Generate Linq to SQL classes from the (working) database.
4. Create patches to update your baseline database to your working copy.
5. Check in and share these patches with all developers.

For a very quick and easy way of generating baseline and working copy databases, try DBSourceTools. http://dbsourcetools.codeplex.com

Have fun.