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; }