private void LoadDatabases()
{
// Load the listbox with the list of databases on the server and
// while we're at it we'll extract the master database's data
// and log file folders.
//
// Note that we're not going to add the following system databases
// to the list:
//
// master
// model
// msdb
// tempdb
// ReportServer
// ReportServerTempDB
Dictionary <string, bool> ignoreDBs = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase);
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
WaitForm waitForm;
string dbName;
ignoreDBs.Add("master", true);
ignoreDBs.Add("model", true);
ignoreDBs.Add("msdb", true);
ignoreDBs.Add("tempdb", true);
ignoreDBs.Add("ReportServer", true);
ignoreDBs.Add("ReportServerTempDB", true);
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Scanning databases...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext(conString);
try
{
ctx.Open();
// Get the databases (note that the sp_databases sproc does not
// exist on SQL Azure).
if (wizard.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as DATABASE_NAME from sys.sysdatabases");
}
else
{
cmd = ctx.CreateSPCall("sp_databases");
}
dt = ctx.ExecuteTable(cmd);
databaseList.Items.Clear();
foreach (DataRow row in dt.Rows)
{
dbName = SqlHelper.AsString(row["DATABASE_NAME"]);
if (ignoreDBs.ContainsKey(dbName))
{
continue;
}
databaseList.Items.Add(dbName);
if (String.Compare(dbName, (string)wizard.SetupState["database"], true) == 0)
{
databaseList.SelectedIndex = databaseList.Items.Count - 1;
}
}
if (!wizard.IsSqlAzure && (masterDataPath == null || masterLogPath == null))
{
// Get the master database file paths
cmd = ctx.CreateSPCall("sp_helpdb");
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = "master";
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["1"];
foreach (DataRow row in dt.Rows)
{
string file;
int pos;
if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("data") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterDataPath = file.Substring(0, pos + 1);
}
}
else if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("log") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterLogPath = file.Substring(0, pos + 1);
}
}
}
// Set the paths to the empty string if all else fails
if (masterDataPath == null)
{
masterDataPath = string.Empty;
}
if (masterLogPath == null)
{
masterLogPath = string.Empty;
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
}
private void LoadAccounts()
{
// Load the listbox with the list of existing server logins, filtering
// out the SA, Windows, or server role accounts as well as any built-in
// accounts that begin with "##".
SqlConnectionInfo conInfo;
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
WaitForm waitForm;
string login;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Scanning accounts...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
// Connect to the master database.
conInfo = new SqlConnectionInfo(conString);
conInfo.Database = "master";
ctx = new SqlContext(conInfo.ToString());
try
{
ctx.Open();
// Get the accounts (note that the sp_helplogins sproc does not exist on SQL Azure).
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as 'LoginName' from sys.sql_logins");
}
else
{
cmd = ctx.CreateSPCall("sp_helplogins");
}
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["0"];
accountList.Items.Clear();
foreach (DataRow row in dt.Rows)
{
login = SqlHelper.AsString(row["LoginName"]);
// Append the account, skipping any that are empty or
// appear to be a server role, a Windows domain account,
// or a built-in account.
if (login == null)
{
continue; // Empty
}
if (login.IndexOf('\\') != -1)
{
continue; // Windows account
}
if (String.Compare(login, "sa", true) == 0)
{
continue; // SA account
}
if (login.StartsWith("##"))
{
continue; // Built-in account
}
accountList.Items.Add(login);
if (String.Compare(login, (string)wizard.SetupState["account"], true) == 0)
{
accountList.SelectedIndex = accountList.Items.Count - 1;
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Cannot load database accounts.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
}
/// <summary>
/// Creates the application database account if one doesn't already exist.
/// </summary>
private void CheckAppAccount()
{
List <string> accounts;
string login;
string cs;
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
bool exists;
if (account == null)
{
return; // Looks like we're using integrated security
}
// Get the current set of accounts from the database
cs = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// Get the accounts (note that the sp_helplogins sproc does not exist on SQL Azure).
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as 'LoginName' from sys.sql_logins");
}
else
{
cmd = ctx.CreateSPCall("sp_helplogins");
}
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["0"];
accounts = new List <string>();
foreach (DataRow row in dt.Rows)
{
login = SqlHelper.AsString(row["LoginName"]);
// Append the account, skipping any that are empty or
// appear to be a server role or a Windows domain account.
if (login != null && login.IndexOf('\\') == -1)
{
accounts.Add(login);
}
}
}
finally
{
ctx.Close();
}
// Create the account, recreating it if it already exists.
exists = false;
for (int i = 0; i < accounts.Count; i++)
{
if (String.Compare(account, accounts[i], true) == 0)
{
exists = true;
break;
}
}
cs = string.Format("server={0};database=master;{1}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
if (exists)
{
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("drop login '{0}'", account);
}
else
{
cmd = ctx.CreateSPCall("sp_droplogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
}
ctx.Execute(cmd);
}
if (ctx.IsSqlAzure)
{
ctx.CreateCommand("create login {0} with password='******'", account, password);
}
else
{
cmd = ctx.CreateSPCall("sp_addlogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@passwd", SqlDbType.VarChar).Value = password;
}
ctx.Execute(cmd);
}
finally
{
ctx.Close();
}
}
