Database schema is an important part of your source code and you would like to release it along with your application especially if it is a web application. The other reason to version your database is for continuous integration. Every time a team member makes some change in the code the build server picks it up and rebuilds the whole application again. If that change contains a database update, you would want that to happen on the build server automatically. Here comes your database script runner tool.
I have been looking for tools like that over the internet but could not find an open source script runner. The one which does the job is not open source and the one which is open source does not do the job right. So I decided to write my own.
This is a class library which takes in a connection string, a file format string and the path to the scripts directory. Connection string is off course the address of your database and the file format is an handy feature so you can use any name format for your sql scripts. For instance: a file name “my_%.sql” will read all files of the format my_<sequence number>.sql where sequence number is a 4 digit integer.
Here is the code. I use it as a library and run it at the start up on one of my services but you can create a console application and reference the assembly in it to use it as a standalone tool. I might create that console application later and will share it here if I do.
public class SchemaUpdater { private readonly string _connectionString; private readonly string _nameformat; private readonly string _scriptdir; public SchemaUpdater(string connectionstring, string format, string scriptpath) { _connectionString = connectionstring; _nameformat = format; _scriptdir = scriptpath; } public void Update() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var scriptseparator = new[] {"\nGO"}; // Make sure we have a schema versions table var scriptfile = string.Format("{0}\\{1}", _scriptdir, "versions-table.sql"); var transaction = connection.BeginTransaction(IsolationLevel.Serializable); try { Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries), (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery())); transaction.Commit(); } catch(Exception) { transaction.Rollback(); throw; } // Now run the baseline scriptfile = string.Format("{0}\\{1}", _scriptdir, "base.sql"); transaction = connection.BeginTransaction(IsolationLevel.Serializable); try { Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries), (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery())); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } // Now run all the files var command = new SqlCommand("SELECT Version FROM SchemaVersion", connection); var version = command.ExecuteScalar(); command.Dispose(); var start = version == null ? 0 : Convert.ToInt32(version)+1; var filename = _nameformat.Replace("%", start.ToString("0000")); scriptfile = string.Format("{0}\\{1}", _scriptdir, filename); transaction = connection.BeginTransaction(IsolationLevel.Serializable); try { while (File.Exists(scriptfile)) { Array.ForEach(File.ReadAllText(scriptfile).Split(scriptseparator, StringSplitOptions.RemoveEmptyEntries), (sql => new SqlCommand(sql, connection, transaction).ExecuteNonQuery())); start++; filename = _nameformat.Replace("%", start.ToString("0000")); scriptfile = string.Format("{0}\\{1}", _scriptdir, filename); } new SqlCommand(string.Format("Update SchemaVersion SET Version={0}", start-1), connection, transaction).ExecuteNonQuery(); transaction.Commit(); } catch(Exception) { transaction.Rollback(); throw; } } } }
This is a small piece of code but it works for me, also takes care of the GO separator in the sql scripts and runs all commands in a transaction. It uses my scripts “version-tables.sql” which creates a new version tables in the database if it does not exist and “base.sql” which contain any sql statements which you want to run to create a baseline schema.
del.icio.us it!
MSDN!
Technet!
Social!
