public async Task<bool> CreateDatabase(string dbname, string mdf, string[] ndfArray = null, string[] ldfArray = null)
{ // Create with attach multiple secondary ldf and ndf files
if (!DbnameCheck(dbname)) return false;
var sbCreate = new StringBuilder();
sbCreate.Append("CREATE DATABASE [@dbname] ");
sbCreate.Replace("@dbname", dbname);
sbCreate.Append("ON ");
sbCreate.Append("(FILENAME='@mdf'),");
sbCreate.Replace("@mdf", mdf);
if (ndfArray == null && ldfArray == null)
{ // Remove comma at end
var s = sbCreate.ToString();
sbCreate.Clear();
sbCreate.Append(s.Substring(0, s.Length - 1));
}
else
{
if (ndfArray != null)
foreach (var ndf in ndfArray)
{
sbCreate.Append("(FILENAME='@ndf'),");
sbCreate.Replace("@ndf", ndf);
}
if (ldfArray != null)
{
foreach (var ldf in ldfArray)
{
sbCreate.Append("(FILENAME='@ldf'),");
sbCreate.Replace("@ldf", ldf);
}
}
var s = sbCreate.ToString();
sbCreate.Clear();
sbCreate.Append(s.Substring(0, s.Length - 1));
}
sbCreate.Append(" FOR ATTACH ");
var createDatabase = Task.Run(() => ExecuteNonQueryAsync(sbCreate.ToString(), 15));
await createDatabase;
var oDatabase = new SqlDatabase(this, dbname, "ONLINE", 0);
DatabasesCollection.Add(oDatabase);
return createDatabase.Result;
}
private async void btnCreate_Click(object sender, EventArgs e)
{
var dbname = txtName.Text;
if (! Utils.IsStringValid(dbname))
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = dbname + " is not a valid name.";
txtName.Focus();
return;
}
if (_instance.DatabasesCollection.FirstOrDefault(s => s.Name == dbname) != null)
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = dbname + " exists yet.";
txtName.Focus();
return;
}
btnCreate.Enabled = false;
var sbCreate = new StringBuilder("CREATE DATABASE [@dbname];");
sbCreate.Append("ALTER DATABASE [@dbname] SET RECOVERY @recovery;");
if (ckbCreate.GetItemChecked(0))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_CLOSE ON;");
}
else if (!ckbCreate.GetItemChecked(0))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_CLOSE OFF;");
}
if (ckbCreate.GetItemChecked(1))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_CREATE_STATISTICS ON;");
}
else if (!ckbCreate.GetItemChecked(1))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_CREATE_STATISTICS OFF;");
}
if (ckbCreate.GetItemChecked(2))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_UPDATE_STATISTICS ON;");
}
else if (!ckbCreate.GetItemChecked(2))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_UPDATE_STATISTICS OFF;");
}
if (ckbCreate.GetItemChecked(3))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_UPDATE_STATISTICS_ASYNC ON;");
}
else if (!ckbCreate.GetItemChecked(3))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;");
}
if (ckbCreate.GetItemChecked(4))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_SHRINK ON;");
}
else if (!ckbCreate.GetItemChecked(4))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET AUTO_SHRINK OFF;");
}
if (ckbCreate.GetItemChecked(5))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET ENABLE_BROKER;");
}
else if (!ckbCreate.GetItemChecked(5))
{
sbCreate.Append("ALTER DATABASE [@dbname] SET DISABLE_BROKER;");
}
sbCreate.Replace("@dbname", txtName.Text);
sbCreate.Replace("@recovery", cmbRecovery.SelectedItem.ToString());
var result = Task.Run(() => _instance.ExecuteNonQueryAsync(sbCreate.ToString()));
try
{
await result;
Utils.WriteLog("result is::: " + result.Result);
if (result.Result)
{
lblStatus.ForeColor = Color.Green;
lblStatus.Text = dbname + " created!";
var oDatabase = new SqlDatabase(_instance, dbname, "ONLINE", 0);
_instance.DatabasesCollection.Add(oDatabase);
}
}
catch (Exception)
{
lblStatus.ForeColor = Color.Red;
lblStatus.Text = string.Format("{0} not created, check the log.", txtName.Text);
txtName.Focus();
return;
}
btnCreate.Enabled = true;
}
public bool CreateDatabaseSync(string dbname)
{
// create for attach (ldf is optional).
// Invoked by the create form and the main function that restores a database collection.
if (!DbnameCheck(dbname)) return false;
var sbCreate = new StringBuilder();
sbCreate.Append("CREATE DATABASE [@dbname] ");
sbCreate.Replace("@dbname", dbname);
using (var conn = GetConnection())
{
using (var cmd = new SqlCommand(sbCreate.ToString(), conn))
{
try
{
conn.Open();
int databaseCreated = cmd.ExecuteNonQuery();
if (databaseCreated == -1)
{
var oDatabase = new SqlDatabase(this, dbname, "ONLINE", 0);
DatabasesCollection.Add(oDatabase);
return true;
}
return false;
}
finally
{
conn.Close();
}
}
}
}
public async Task<bool> CreateDatabase(string dbname, FileInfo mdf, FileInfo ldf = null)
{
// create for attach (ldf is optional).
// Invoked by the create form and the main function that restores a database collection.
if (!DbnameCheck(dbname)) return false;
var sbCreate = new StringBuilder();
sbCreate.Append("CREATE DATABASE [@dbname] ");
sbCreate.Replace("@dbname", dbname);
if (mdf != null) // it's for attach
{
sbCreate.Append("ON (FILENAME='@mdf')");
sbCreate.Replace("@mdf", mdf.FullName);
if (ldf != null)
{
sbCreate.Append(",(FILENAME='@ldf')");
sbCreate.Replace("@ldf", ldf.FullName);
}
sbCreate.Append(" FOR ATTACH ");
}
var createDatabase = Task.Run(() => ExecuteNonQueryAsync(sbCreate.ToString(), 15));
await createDatabase;
if (createDatabase.Result)
{
var oDatabase = new SqlDatabase(this, dbname, "ONLINE", 0);
DatabasesCollection.Add(oDatabase);
}
return createDatabase.Result;
}
//public List<SqlView> listViews = new List<SqlView>();
public SqlSchema(SqlDatabase parent, string name)
{
this.parent = parent;
this.name = name;
}
public void GetDbList(bool systemdbEnabled)
{
if (IsOnline != true)
{
MessageBox.Show("Can't connect now to " + _instance + ", please try again.");
return;
}
DatabasesCollection = new List<SqlDatabase>();
const string qry = @"SELECT name, state_desc, user_access,
CASE is_distributor
WHEN 1 then 'true'
ELSE 'false'
END
FROM sys.databases ORDER BY name";
using (var conn = GetConnection())
{
using (var cmd = new SqlCommand(qry, conn))
{
try
{
conn.Open();
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var name = rdr.GetString(0);
var status = rdr.GetString(1);
var userAccess = (sbyte)rdr.GetByte(2);
var distributor = bool.Parse(rdr.GetString(3));
if (SystemNames.Any(name.Contains) || distributor)
{
if (systemdbEnabled && name != "tempdb")
{
var oDatabase = new SqlSystemDatabase(this, name, status, userAccess);
DatabasesCollection.Add(oDatabase);
}
}
else
{
var oDatabase = new SqlDatabase(this, name, status, userAccess);
DatabasesCollection.Add(oDatabase);
}
}
}
catch (SqlException ex)
{
Utils.WriteLog(ex.Message);
}
catch (Exception ex)
{
Utils.WriteLog(ex.Message);
}
finally
{
conn.Close();
}
}
}
}
private void EnableButtons(SqlInstance instance, SqlDatabase database)
{
tlsEditConfig.Enabled = true;
tlsEnableSystemDbs.Enabled = true;
// instance unreachable, connecting or busy
if (instance.IsOnline == null || instance.IsConnecting || instance.IsBusy)
{
return;
}
if (instance.IsOnline != true) return;
foreach (ToolStripMenuItem i in mnuServer.DropDown.Items)
{
i.Enabled = true;
}
foreach (var c in tbxMain.Items.OfType<ToolStripButton>().Where(c => c.Name != "tlsClearLog" && c.Name != "tlsTools_CancelAll"))
{
c.Enabled = true;
}
tlsBackupAll.Enabled = true;
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "server"))
{
i.Enabled = true;
}
txtDatabase_Rename.Enabled = true;
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items.OfType<ToolStripMenuItem>().Where(i => (string)i.Tag == "server"))
{
i.Enabled = true;
}
// Database busy or null
if (database == null || database.IsBusy) return;
if(database is SqlSystemDatabase)
{
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "sysdb"))
{
i.Enabled = true;
}
tlsDatabase_BackupWith.Enabled = true;
mnuDatabase_Check.Enabled = true;
mnuDatabase_Backup.Enabled = true;
return;
}
switch (database.Status)
{
// Database offline (Control.Tag == "offline")
case "OFFLINE":
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>().Where(i => (string) i.Tag == "offline"))
{
i.Enabled = true;
}
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items)
{
if ((string) i.Tag == "offline")
{
i.Enabled = true;
}
}
//mnuDatabase_BackupWith.Enabled = true; //
break;
// Database online
case "ONLINE":
foreach (var i in tbxDatabase.Items.OfType<ToolStripButton>())
{
i.Enabled = true;
}
tlsDatabase_BackupWith.Enabled = true;
tlsDatabase_Online.Enabled = false;
foreach (ToolStripMenuItem i in mnuDatabase.DropDown.Items)
{
i.Enabled = true;
}
// Online single user
if (database.Access == "multi")
{
tlsDatabase_Multi.Enabled = false;
}
// Online Multi User
else
{
tlsDatabase_Single.Enabled = false;
}
break;
default:
tlsDatabase_Drop.Enabled = true;
break;
}
}
private static bool CanContinue(SqlDatabase database)
{
if (database == null)
{
MessageBox.Show(ERROR_NO_DATABASE);
return false;
}
if (database.IsBusy)
{
MessageBox.Show(database.Name + WARNING_IS_BUSY);
return false;
}
if (database.Parent.IsBusy)
{
MessageBox.Show(database.Parent + WARNING_IS_BUSY);
return false;
}
return true;
}
private void UIOperationClosed(object sender, SqlDatabase database, bool batchOperation = false)
{
if (batchOperation) SetStatus(sender, database, "END", "INFO"); // only logs instance operations
var serverIndex = SqlInstance.ListServers.IndexOf(database.Parent);
if (_oDatabase != null) database.IsBusy = false;
_progressBars[serverIndex].Style = ProgressBarStyle.Blocks;
_progressBars[serverIndex].Hide();
UISelectObject(database);
if (SqlInstance.ListServers.Count > 0)
{
dgvDatabases.ClearSelection();
dgvDatabases.Refresh();
}
}
private void dgvDatabases_Click(object sender, EventArgs e)
{
dgvDatabases.Refresh();
if (_databaseCollectionBindingList == null || _databaseCollectionBindingList.Count == 0)
{
return;
}
_oDatabaseIndex = dgvDatabases.CurrentCell.RowIndex;
_oDatabase = _oInstance.DatabasesCollection[_oDatabaseIndex];
Utils.WriteLog(string.Format("Connected to {0}@{1}", _oDatabase.Name, lstMain_Servers.SelectedItem));
ButtonDisable();
EnableButtons(_oDatabase.Parent, _oDatabase);
}
private void UIOperationStarted(object sender, SqlDatabase database, string message = "")
{
SetStatus(sender, database, "BEGIN", "INFO", message);
var index = SqlInstance.ListServers.IndexOf(database.Parent);
ButtonDisable();
_progressBars[index].Style = ProgressBarStyle.Marquee;
_progressBars[index].Show();
_progressBars[index].Update();
database.IsBusy = true;
}