Changing DBML, how to change SQL database?

Question

A have an app with that uses an SQL database. The application is already released, but now I'm working on an update. In the update I've added an extra column in a table of my database.

I've create the database from my DMBL using DataContext.CreateDatabase() (not the other way around, as I found out to be the more common scenario later)

I there a facility in LINQ in which I can update my SQL database scheme?

Answer 1

No, sorry. I also would like to have such a facility. You have to upgrade your database by hand.

Here's the Method:

• Create a Stored Procedure called something like "spGetDBVersion". This Procedure just returns an Integer of your current deployed Database version.
• At startup/installation time check that procedure if it's result matches the version your Program expect.
• If not -> execute update scripts in the right order. Example: if spGetDBVersion returns 2 and your application expects 5 then you have to execute Update_To_3.sql, Update_To_4.sql and Update_To_5.sql

Hint: After the initial release I never change my database schema with an tool. I always script that change and put that in my next update script.

Not that what you've asked, but it's a practicable solution.

An UpdateScript may look like this:

** PSEUDOCODE**

-- Update to version 2
alter table [MyTable] add newColumn int null
GO
update [MyTable] set [newColumn] = 0
GO
alter table [MyTable] change newColumn int not null
GO
alter procedure spGetDBVersion
as
begin
    select 2 as CurrentVersion
end