public static void GetScriptsToRun(IDBUpdaterUserInteractionContext view)
{
var runned = new List <string>();
var version = 0;
var files = new List <string>(Directory.GetFiles(Path.GetDirectoryName(Application.ExecutablePath), "*.sql"));
using (var c = new SqlCommand("select * from sysDBVersion", view.Connection))
{
using (var r = c.ExecuteReader())
{
while (r.Read())
{
version = Math.Max(r.GetInt32(0), version);
runned.Add(r.GetString(1));
}
}
}
for (int i = 0; i < files.Count; i++)
{
files[i] = Path.GetFileNameWithoutExtension(files[i]);
}
foreach (var r in runned)
{
var ind = files.FindIndex(sc => sc.EndsWith(r));
if (ind >= 0)
{
files.RemoveAt(ind);
}
}
view.AsyncSetScripts(runned, files);
}
public static void EnsureDBVersionExists(IDBUpdaterUserInteractionContext view)
{
if (!IsDBVersionExists(view))
{
view.AsyncLog("DB doesn't containe sysDBVersion table. Creating...");
CreateSysDbVersionTable(view);
view.AsyncLog("sysDBVersion table has been created");
}
}
public static void EnsureDBVersionExists(IDBUpdaterUserInteractionContext view)
{
if (!IsDBVersionExists(view))
{
view.AsyncLog("DB doesn't containe sysDBVersion table. Creating...");
CreateSysDbVersionTable(view);
view.AsyncLog("sysDBVersion table has been created");
}
}
private static bool IsDBVersionExists(IDBUpdaterUserInteractionContext f)
{
using (var c = new SqlCommand("select * from sys.tables where name = 'sysDBVersion'", f.Connection))
{
using (var r = c.ExecuteReader())
{
return(r.Read());
}
}
}
private static void CreateSysDbVersionTable(IDBUpdaterUserInteractionContext f)
{
using (var c = new SqlCommand(CreateSysDbVersionTableScript, f.Connection))
{
c.LexExecuteNonQuery();
c.CommandText = CreateSysDbVersionTriggerScript;
c.LexExecuteNonQuery();
c.CommandText = GetDBVersionProcSql(0, false);
c.LexExecuteNonQuery();
c.CommandText = CreateUpgradeDBProcedure;
c.LexExecuteNonQuery();
}
}
public static void ReCreateDataBase(IDBUpdaterUserInteractionContext view)
{
try
{
var dbname = view.DBName;
view.Connection.Close();
SqlConnection.ClearAllPools();
using (var newConnection = new SqlConnection(new SqlConnectionStringBuilder
{
DataSource = view.Connection.DataSource,
IntegratedSecurity = true
}.ToString()))
{
newConnection.Open();
using (var c = new SqlCommand(GetDBUsageSql(dbname), newConnection))
{
var ok = true;
var sb = new StringBuilder("Database is using by following clients:");
sb.AppendLine();
using (var r = c.ExecuteReader())
{
while (r.Read())
{
string status = r.GetString(0).Trim();
string host = r.GetString(1).Trim();
string prg = r.GetString(2).Trim();
if ((host.IsNotNull() || prg.IsNotNull()))
{
ok = false;
sb.AppendLine(string.Format("{0} | {1} | {2}", status, host, prg));
}
}
}
if (!ok)
{
sb.AppendLine();
sb.Append("Close ALL connection?");
if (
MessageBox.Show(sb.ToString(), "Close confirmation", MessageBoxButtons.YesNo,
MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
c.CommandText =
string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
dbname);
c.CommandType = CommandType.Text;
c.LexExecuteNonQuery();
}
else
{
return;
}
}
}
using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
c.CommandText = string.Format("CREATE DATABASE [{0}]", dbname);
c.LexExecuteNonQuery();
}
}
view.Connection.Open();
CreateSysDbVersionTable(view);
}
finally
{
if (view.Connection.State != ConnectionState.Open)
{
view.Connection.Open();
}
view.AsyncEnQueueOperation(GetScriptsToRun, "Getting scripts to run...");
}
}
public static void ReCreateDataBase(IDBUpdaterUserInteractionContext view)
{
try
{
var dbname = view.DBName;
view.Connection.Close();
SqlConnection.ClearAllPools();
using (var newConnection = new SqlConnection(new SqlConnectionStringBuilder
{
DataSource = view.Connection.DataSource,
IntegratedSecurity = true
}.ToString()))
{
newConnection.Open();
using (var c = new SqlCommand(GetDBUsageSql(dbname), newConnection))
{
var ok = true;
var sb = new StringBuilder("Database is using by following clients:");
sb.AppendLine();
using (var r = c.ExecuteReader())
{
while (r.Read())
{
string status = r.GetString(0).Trim();
string host = r.GetString(1).Trim();
string prg = r.GetString(2).Trim();
if ((host.IsNotNull() || prg.IsNotNull()))
{
ok = false;
sb.AppendLine(string.Format("{0} | {1} | {2}", status, host, prg));
}
}
}
if (!ok)
{
sb.AppendLine();
sb.Append("Close ALL connection?");
if (
MessageBox.Show(sb.ToString(), "Close confirmation", MessageBoxButtons.YesNo,
MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
c.CommandText =
string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE",
dbname);
c.CommandType = CommandType.Text;
c.LexExecuteNonQuery();
}
else
return;
}
}
using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
c.CommandText = string.Format("CREATE DATABASE [{0}]", dbname);
c.LexExecuteNonQuery();
}
}
view.Connection.Open();
CreateSysDbVersionTable(view);
}
finally
{
if (view.Connection.State != ConnectionState.Open)
{
view.Connection.Open();
}
view.AsyncEnQueueOperation(GetScriptsToRun, "Getting scripts to run...");
}
}
private static void CreateSysDbVersionTable(IDBUpdaterUserInteractionContext f)
{
using (var c = new SqlCommand(CreateSysDbVersionTableScript, f.Connection))
{
c.LexExecuteNonQuery();
c.CommandText = CreateSysDbVersionTriggerScript;
c.LexExecuteNonQuery();
c.CommandText = GetDBVersionProcSql(0, false);
c.LexExecuteNonQuery();
c.CommandText = CreateUpgradeDBProcedure;
c.LexExecuteNonQuery();
}
}
private static bool IsDBVersionExists(IDBUpdaterUserInteractionContext f)
{
using (var c = new SqlCommand("select * from sys.tables where name = 'sysDBVersion'", f.Connection))
{
using (var r = c.ExecuteReader())
{
return r.Read();
}
}
}
public static void GetScriptsToRun(IDBUpdaterUserInteractionContext view)
{
var runned = new List<string>();
var version = 0;
var files = new List<string>(Directory.GetFiles(Path.GetDirectoryName(Application.ExecutablePath), "*.sql"));
using (var c = new SqlCommand("select * from sysDBVersion", view.Connection))
{
using (var r = c.ExecuteReader())
{
while (r.Read())
{
version = Math.Max(r.GetInt32(0), version);
runned.Add(r.GetString(1));
}
}
}
for (int i = 0; i < files.Count; i++)
{
files[i] = Path.GetFileNameWithoutExtension(files[i]);
}
foreach (var r in runned)
{
var ind = files.FindIndex(sc => sc.EndsWith(r));
if (ind >= 0)
{
files.RemoveAt(ind);
}
}
view.AsyncSetScripts(runned, files);
}
