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?

+2  A: 

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.

+1, if you're fully on the MS stack, a Database Project is really the standard operating procedure for exactly this situation. It's not going to be in C#, but it's a neat concept for managing a database as a VS project that can be source controlled, and comes with a lot of the trimmings you'd expect from Visual Studio - tons of options, ability to change settings for different build configurations, auto-deploy directly from VS, the ability to generate diff deployment scripts on a target DB, etc.
+1  A: 

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


  • 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.

Actually Source Control from redgate is probably closer:
WE thought about the same thing :-) I think that SQL Packager will do as I believe it can deliver updates as well too. Both products are great :-)
+4  A: 

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.

Awesome! This is exactly the type of thing I was looking for. I had found Wizardby, but it looked abandoned, and it's still writing sql and note C#. Those first three look like they are exactly what I'm looking for. Thanks so much!
+1  A: 

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.

+2  A: 

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


  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.
+2  A: 

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).

Bill Barry
+1  A: 

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.

Have fun.