Is it possible to update old database from dbml file ? (C#, .Net 4, Linq, SQL Server)

Question

Hi all,

I began recently a new job, a very interesting project (C#,.Net 4, Linq, VS 2010 and SQL Server). And immediately I got a very exciting challenge: I must implement either a new tool or integrate the logic when program start, or whatever, but what must happen is the following: the customers have previous application and database (full with their specific data). Now a new version is ready and the customer gets the update. In the mean time we made some modification on DB (new table, columns, maybe an old column deleted, or whatever). I’m pretty new in Linq and also SQL databases and my first solution can be: I check the applications/databases version and implement all the changes step by step comparing all tables, columns, keys, constrains, etc. (all this new information I have in my dbml and the old I asked from the existing DB). And I’ll do this each time the version changed. But somehow I feel, this is NOT a smart solution so I look for a general solution of this problem.

Is there a way to update customers DB from the dbml file? To create a new one is not a problem (CreateDatabase with DataContext), is there any update/alter database methods? I guess I’m not the only one who search for such a solution (I found nothing in internet – or I looked for bad keywords). How did you solve this problem? I look also for an external tool, but first for a solution with C#, Linq or something similar. For any idea thank you in advance!

Best regards, Emil

Answer 1

Basically what you want to be able to do is to script the changes - to be able to run "something" that allows you to update one version of the database to the next and also to make any necessary changes to the data required by that change in the schema.

Good news is that you can do this with SQL, you can write DDL statements to create and modify a database schema.

My solution is to put my database schema maintenance entirely in code, I think this is the best version of the writeup I've done so far:

http://stackoverflow.com/questions/1715691/how-to-create-embedded-sql-2008-database-file-if-it-doesnt-exist/1716021#1716021

Why in code? Because it works. May not be the best solution but its one I have had some success with and the results are consistent and repeatable. Oh and its version controlled too.

The big problem you may have in this specific instance is that you need to establish a baseline - to make sure that the existing databases are consistent in terms of their schema. This is where more complex and clever tools may serve you better - being able to do a schema diff and then update has a lot of appeal as a concept for example but equally you're somewhat dependent on having your reference database perfect and that raises other issues.

Answer 2

What I always do is use Red Gate's SQL Compare to compare the schema of the new database to the schema of the old database. It will generate a change script for you and then you can run that script in code.

We have a table that has a single row in it for program setup information. One of the columns in this table is the database version number. This will instantly tell us what database version the customer has when we do an update. Then we run every script that will update them to the latest version they need to be running. Whenever we release a new version (with database changes), we run the SQL Compare and make a script to go from the previous version to the next. We don't do any scripts that will skip versions, just in case of strange conflicts that may arise from that.

This also gives us the opportunity to do any data massaging we may have to do in between versions by writing a custom script and inserting that into the update scripts. Every update script changes that database version field as well.

This allows us to do a lot of automated updating. Having that database version allows the client to take a peek at that version before the user has a chance to use the application. If it's different and the application needs an update, it will go out to our ftp site and download the update and run the setup automatically.