private bool PerformSelectDataBase()
{
string serverInstance = tbServerInstance.Text.Trim();
string dbName = tbDBName.Text.Trim();
UseWaitCursor = true;
bool disposeConnetion = true;
var s = new SqlConnection(new SqlConnectionStringBuilder
{
DataSource = serverInstance,
IntegratedSecurity = true,
}.ToString());
var Dbs = new List<string>();
try
{
s.Open();
var c = new SqlCommand("EXEC sp_databases", s);
using (var r = c.LexExecuteReader())
{
while (r.Read())
{
Dbs.Add(r.GetString(0));
}
}
if (Dbs.IndexOf(dbName) < 0)
{
if (MessageBox.Show(this, string.Format("DataBase '{0}' was not found in {1}. Do you want to create it?", dbName, serverInstance), "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
c.CommandText = @"CREATE DATABASE [" + dbName + "]";
c.LexExecuteNonQuery();
}
else
{
return false;
}
}
s.Close();
s.ConnectionString = new SqlConnectionStringBuilder
{
DataSource = serverInstance,
IntegratedSecurity = true,
InitialCatalog = dbName
}.ToString();
s.Open();
disposeConnetion = false;
var f = new frmDBStatus(s, this, dbName);
f.Show(this);
return true;
}
catch (SqlException se)
{
MessageBox.Show(this, se.Message, string.Format("Error on openning '{0}'", serverInstance), MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
finally
{
UseWaitCursor = false;
if (disposeConnetion)
{
s.Dispose();
}
}
}
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();
}
}
public static Action<IDBUpdaterUserInteractionContext> UpgrateToNextVersion(int dbVersion, string sqlscript, string scriptFileName)
{
return frmDBStatus =>
{
using (var transaction = frmDBStatus.Connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
using (var cmd = new SqlCommand("select dbo.GetDBVersion()", transaction.Connection)
{
Transaction = transaction
})
{
try
{
var v = cmd.LexExecuteScalar<int>();
if (v != dbVersion)
{
throw new InvalidDBVersionException(v);
}
foreach (var cmdText in SplitSqlCommands(sqlscript))
{
cmd.CommandText = cmdText;
cmd.LexExecuteNonQuery();
}
cmd.CommandText = GetDBVersionProcSql(dbVersion + 1, true);
cmd.ExecuteScalar();
cmd.CommandText = "INSERT INTO sysDBVersion (VersionNumber, ScriptFileName, Date) VALUES (@version, @filename, @date)";
cmd.Parameters.Assign(new
{
version = dbVersion,
filename = scriptFileName,
date = DateTime.Now
});
cmd.LexExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
};
}
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 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;
}