public static object CreateDataBase(IDBUpdaterInteractionContext context)
{
var dbname = context.DBName;
using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
{
context.Connection.Close();
newConnection.Open();
using (var c = new SqlCommand(string.Format("CREATE DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
}
}
context.Connection.ConnectionString = new SqlConnectionStringBuilder(context.Connection.ConnectionString)
{
InitialCatalog = dbname
}.ToString();
context.Connection.Open();
CreateSysDbVersionTable(context);
return(true);
}
public static object GetScriptsToRun(IDBUpdaterInteractionContext context)
{
var runned = new List <string>();
var version = 0;
var files = new List <string>(Directory.GetFiles(context.DBScriptsPath, "*.sql"));
using (var c = new SqlCommand("select * from sysDBVersion", context.Connection))
{
using (var r = c.LexExecuteReader())
{
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);
}
}
return(new KeyValuePair <IList <string>, IList <string> >(runned, files));
}
public static object DropDataBase(IDBUpdaterInteractionContext context)
{
var dbname = context.DBName;
context.Connection.Close();
SqlConnection.ClearAllPools();
using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
{
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.LexExecuteReader())
{
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(false);
}
}
}
using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
}
}
return(true);
}
public static object EnsureDBVersionExists(IDBUpdaterInteractionContext context)
{
if (!IsDBVersionExists(context))
{
CreateSysDbVersionTable(context);
}
return(true);
}
public static object EnsureDBVersionExists(IDBUpdaterInteractionContext context)
{
if (!IsDBVersionExists(context))
{
CreateSysDbVersionTable(context);
}
return true;
}
public static object DropDataBase(IDBUpdaterInteractionContext context)
{
var dbname = context.DBName;
context.Connection.Close();
SqlConnection.ClearAllPools();
using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
{
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.LexExecuteReader())
{
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 false;
}
}
using (var c = new SqlCommand(string.Format("DROP DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
}
}
return true;
}
private static bool IsDBVersionExists(IDBUpdaterInteractionContext f)
{
using (var c = new SqlCommand("select * from sys.tables where name = 'sysDBVersion'", f.Connection))
{
using (var r = c.LexExecuteReader())
{
return(r.Read());
}
}
}
public void AsyncSetScripts(IDBUpdaterInteractionContext context, object result)
{
var r = (KeyValuePair <IList <string>, IList <string> >)result;
var runnedScripts = r.Key;
var scriptsToRun = r.Value;
Invoke((Action <frmDBStatus, List <string>, List <string> >)((v, rn, fs) =>
{
FillListView(v.lvScripts, fs, true);
FillListView(v.lvRunned, rn, false);
}), this, runnedScripts, scriptsToRun);
}
public void AsyncSetScripts(IDBUpdaterInteractionContext context, object result)
{
var r = (KeyValuePair<IList<string>, IList<string>>) result;
var runnedScripts = r.Key;
var scriptsToRun = r.Value;
Invoke((Action<frmDBStatus, List<string>, List<string>>)((v, rn, fs) =>
{
FillListView(v.lvScripts, fs, true);
FillListView(v.lvRunned, rn, false);
}), this, runnedScripts, scriptsToRun);
}
private static object CreateSysDbVersionTable(IDBUpdaterInteractionContext 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();
}
return(true);
}
private static object CreateSysDbVersionTable(IDBUpdaterInteractionContext 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();
}
return true;
}
private static bool IsDBVersionExists(IDBUpdaterInteractionContext f)
{
using (var c = new SqlCommand("select * from sys.tables where name = 'sysDBVersion'", f.Connection))
{
using (var r = c.LexExecuteReader())
{
return r.Read();
}
}
}
public static object GetScriptsToRun(IDBUpdaterInteractionContext context)
{
var runned = new List<string>();
var version = 0;
var files = new List<string>(Directory.GetFiles(context.DBScriptsPath, "*.sql"));
using (var c = new SqlCommand("select * from sysDBVersion", context.Connection))
{
using (var r = c.LexExecuteReader())
{
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);
}
}
return new KeyValuePair<IList<string>, IList<string>>(runned, files);
}
public static object CreateDataBase(IDBUpdaterInteractionContext context)
{
var dbname = context.DBName;
using (var newConnection = GetNewConnectionWithoutDBRefference(context.Connection))
{
context.Connection.Close();
newConnection.Open();
using (var c = new SqlCommand(string.Format("CREATE DATABASE [{0}]", dbname), newConnection))
{
c.LexExecuteNonQuery();
}
}
context.Connection.ConnectionString = new SqlConnectionStringBuilder(context.Connection.ConnectionString) { InitialCatalog = dbname }.ToString();
context.Connection.Open();
CreateSysDbVersionTable(context);
return true;
}
