// Reads sql commands from the StreamReader and runs them on the DB and updates the version.
// Rolls back on failure
public void Update(IEnumerable<string> sql, DbVersion oldVersion, DbVersion newVersion)
{
if(newVersion<=oldVersion) throw new ArgumentException("New version must be greater than oldVersion");
var currentDbVersion=GetVersion();
if(!currentDbVersion.Equals(oldVersion)) throw new InvalidOperationException(string.Format("The current DB version is {0} but expected {1}", currentDbVersion, oldVersion));
using(DbConnection connection=CreateConnection()) {
connection.Open();
using(DbTransaction transaction=connection.BeginTransaction()) {
foreach(var line in sql) {
ExecuteNonQuery(line, connection, transaction);
}
setVersion(newVersion, connection, transaction);
transaction.Commit();
}
}
}
void setVersion(DbVersion version, DbConnection connection, DbTransaction transaction)
{
string setVersionCmd=string.Format("INSERT INTO {0} (Version, Timestamp) VALUES ('{1}', '{2}')", versionTableName, version, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
ExecuteNonQuery(setVersionCmd, connection, transaction);
}
public void Initialise(IEnumerable<string> sql, DbVersion initialVersion)
{
var currentVersion=GetVersion();
if(currentVersion!=null) throw new DbVersionException(string.Format("A database already exists with version {0}", currentVersion.ToString()));
using(DbConnection connection=CreateConnection()) {
connection.Open();
using(DbTransaction transaction=connection.BeginTransaction()) {
foreach(var line in sql) {
ExecuteNonQuery(line, connection, transaction);
}
string createMetaCmd=string.Format("CREATE TABLE {0} (Id INT IDENTITY (1, 1) NOT NULL, Version NVARCHAR(50) NOT NULL, Timestamp DateTime NOT NULL)", versionTableName);
ExecuteNonQuery(createMetaCmd, connection, transaction);
setVersion(initialVersion, connection, transaction);
transaction.Commit();
}
}
}
