SQL table change distribution

Question

I have a WinForm application built with VS 2008 (C#) and SQL Server Express 2008 that is currently deployed on test users PC.

I have modifications to do on the database structure (add fields, tables, etc.) and I wonder what's the best method to distribute the changes on the users PC (without loosing the users data).

From what I've learned so far, I will need to write the changes in a script file. But what's the best method to distribute the script files without running them manually in the SQL Server Management Studio? Is there any automatic distribution method you suggest? Do I have to write my own update application?

I currently use ClickOnce to distribute the changes in the EXE file. Is ClickOnce still a viable solution when you have to do changes in the database with the new version?

TIA

Answer 1

You could write your application on startup to read a script file from the application folder and execute it. You could have the script update a value in the database once its done to prevent it from being run again.

You can deploy the new script file as content and the application will execute it on the next startup, upgrading your database

Answer 2

There are lots of ways you can do this - either build up your own homegrown version somehow (e.g. we are currently packaging database change scripts into a DLL as embedded resources, and have an engine that'll run those on the client machine at install and/or update time), or you can look at readily available .NET products like Red-Gate SQL Packager which can save you lots of time and effort.

Cheers! Marc

Answer 3

You can either call osql or sqlcmd which ship with MSSQL.

Or you can use SMO to run your database change scripts. See this blog how it's done.