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