public void ExecuteScriptAndUpdateVersionTable(FileInfo fi)
{
bool success = true;
Exception exc = null;
string scriptText = null;
if (string.IsNullOrWhiteSpace(_props.Encoding))
{
scriptText = File.ReadAllText(fi.FullName);
}
else
{
scriptText = File.ReadAllText(fi.FullName, ArgumentHelper.GetEncoding(_props.Encoding));
}
using (SqlConnection sqlConnection = new SqlConnection(_props.ConnectionString))
{
Microsoft.SqlServer.Management.Common.ServerConnection svrConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(svrConnection);
string fileName = fi.FullName.Substring(fi.FullName.IndexOf(_props.VersionScriptsFolder, StringComparison.Ordinal));
bool hasRows = false;
using (SqlDataReader reader = server.ConnectionContext.ExecuteReader($"SELECT * FROM {_props.VersionTable} WHERE FileName = '{fileName}'"))
{
hasRows = reader.HasRows;
}
if (!hasRows)
{
server.ConnectionContext.BeginTransaction();
try
{
server.ConnectionContext.ExecuteNonQuery(scriptText);
}
catch (Exception ex)
{
success = false;
exc = ex;
server.ConnectionContext.RollBackTransaction();
}
if (success)
{
server.ConnectionContext.CommitTransaction();
UpdateVersion(fileName, scriptText);
}
}
}
if (!success)
{
throw exc;
}
}
public void ExecuteScriptAndUpdateVersionTable(int versionNo, System.IO.FileInfo fi)
{
bool success = true;
Exception exc = null;
string scriptText = null;
if (string.IsNullOrWhiteSpace(_props.Encoding))
{
scriptText = File.ReadAllText(fi.FullName);
}
else
{
scriptText = File.ReadAllText(fi.FullName, ArgumentHelper.GetEncoding(_props.Encoding));
}
using (SqlConnection sqlConnection = new SqlConnection(_props.ConnectionString))
{
Microsoft.SqlServer.Management.Common.ServerConnection svrConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(svrConnection);
server.ConnectionContext.BeginTransaction();
try
{
server.ConnectionContext.ExecuteNonQuery(scriptText);
}
catch (Exception ex)
{
success = false;
exc = ex;
server.ConnectionContext.RollBackTransaction();
}
if (success)
{
server.ConnectionContext.CommitTransaction();
this.UpdateVersion(versionNo, scriptText);
}
}
if (!success)
{
throw exc;
}
}