} //end of checking
/// <summary>
/// Check the authentication user is db owner
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
private bool IsDBOwner(string connectionString)
{
string dbOwnerCheckingSql = "select is_member('db_owner')";
int owner;
bool isDbOwner;
owner = (int)SqlTool.ExecuteScalar(connectionString, dbOwnerCheckingSql);
isDbOwner = (owner == 1);
return(isDbOwner);
}
/// <summary>
/// Check whether the selected database is empty
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
private bool IsEmptyDatabase(string connectionString)
{
bool isEmptyDatabase;
DataTable dt;
string emptyDBCheckingSql = "select name from sysobjects where xtype = 'U' and status>=0";
dt = SqlTool.ExecuteDataTable(connectionString, emptyDBCheckingSql);
isEmptyDatabase = (dt.Rows.Count == 0);
return(isEmptyDatabase);
}
private string GetVersionID(string connectionString)
{
string dbVersionCheckingSql = ConfigurationSettings.AppSettings["GetVersionScript"];
string version;
try
{
version = (string)SqlTool.ExecuteScalar(connectionString, dbVersionCheckingSql);
}
//If the get version script doesn't exist, it means wrong schema
catch
{
version = "";
}
return(version);
}
private SqlDataReader GetDatabaseList()
{
string serverName;
SqlUserType userType;
string userName, password;
string connectionString;
//bool connectionOK;
string sql = "select name from master.dbo.sysdatabases where dbid>4";
//Get Form data
serverName = this.cboServer.Text;
if (this.rdoWindowsUser.Checked)
{
userType = SqlUserType.IntegratedUser;
}
else
{
userType = SqlUserType.SQLUser;
}
userName = this.txtUsername.Text;
password = this.txtPassword.Text;
//Get database list
connectionString = SqlTool.GetConnectionString(serverName, "Master", userType, userName, password);
try
{
SqlDataReader reader = SqlTool.ExecuteReader(connectionString, sql);
return(reader);
}
catch (Exception exp)
{
MessageBox.Show("Connection failed.\n" + exp.Message);
return(null);
}
} // end of database list
} // end of database list
/// <summary>
/// Check whether the form data are valid
/// 1. Check whether the user is db_owner
/// 2. Check the database schema
/// 2.1 Check whether the database is empty
/// 2.1.1 Yes.
/// 2.1.1.1 If the existing schema is the same version as new version, Whether we use this exiting schema?
/// 2.1.1.2 If the existing schema is the old version, whether upgrade this old schema
/// 2.1.2 No. go to step 2.2
/// 2.2 Check whether the database contains right schema
/// 2.1.1 Yes. Whether we use this exiting schema?
/// 2.1.2 No. Can't use this database
/// </summary>
/// <returns></returns>
private bool SaveState()
{
string serverName, databaseName;
SqlUserType userType;
string userName, password;
string connectionString;
DialogResult r;
bool isDBOwner;
string version;
bool emptyDatabase;
string dbVersionContains = ConfigurationSettings.AppSettings["VersionContains"];
string newVersion = ConfigurationSettings.AppSettings["NewVersionID"];
string newVersionType = ConfigurationSettings.AppSettings["NewVersionType"];
//A) Get Form data
serverName = this.cboServer.Text;
databaseName = this.cboDatabase.Text;
if (this.rdoWindowsUser.Checked)
{
userType = SqlUserType.IntegratedUser;
}
else
{
userType = SqlUserType.SQLUser;
}
userName = this.txtUsername.Text;
password = this.txtPassword.Text;
emptyDatabase = false;
connectionString = SqlTool.GetConnectionString(serverName, databaseName, userType, userName, password);
try
{
// 1. Check whether the user is db_owner
isDBOwner = this.IsDBOwner(connectionString);
if (!isDBOwner)
{
MessageBox.Show("The selected login don't have dbo privileges");
return(false);
}
// 2. Check the database schema
// 2.1 Check whether the database is empty
// 2.1.1 Yes. Whether install schema to this empty database?
// 2.1.2 No. go to step 2.2
emptyDatabase = this.IsEmptyDatabase(connectionString);
if (emptyDatabase)
{
//"Upgrade" only upgrade existing database
if (newVersionType == "Upgrade")
{
MessageBox.Show("The selected database is empty, this application only upgrades existing database.", "Empty Database", MessageBoxButtons.OK);
return(false);
}
version = "-"; //Install new schema
r = MessageBox.Show("The selected database is empty. Do you wish to install the database schema into this empty database?", "Empty Database", MessageBoxButtons.YesNo);
if (r == DialogResult.No)
{
return(false);
}
}
// 2.2 Check whether the database contains right schema
// 2.1.1 Yes.
// 2.1.1.1 If the existing schema is the same version as new version, Whether we use this exiting schema?
// 2.1.1.2 If the existing schema is the old version, whether upgrade this old schema
// 2.1.2 No. Can't use this database
else
{
version = this.GetVersionID(connectionString);
if (version.IndexOf(dbVersionContains) >= 0)
{
if (newVersion == version)
{
version = ""; //use existing database
r = MessageBox.Show("The selected database already contains the schema for " + newVersion + ". Do you wish to continue with this existing database?", "Use Existing Database", MessageBoxButtons.YesNo);
if (r == DialogResult.No)
{
return(false);
}
}
else
{
//"New" only install new database schema in empty database
if (newVersionType == "New")
{
MessageBox.Show("The selected database is not empty, this application only installs new database schema in empty database.", "Invalid Database", MessageBoxButtons.OK);
return(false);
}
r = MessageBox.Show("The selected database contains the schema for " + version + ". Do you wish to upgrade to " + newVersion + "?", "Upgrade Existing Database", MessageBoxButtons.YesNo);
if (r == DialogResult.No)
{
return(false);
}
}
}
else
{
MessageBox.Show("The selected database is not empty, it contains other system schema.", "Invalid Data", MessageBoxButtons.OK);
return(false);
}
} //end of schema checking
}
catch (Exception exp)
{
MessageBox.Show("Connection failed.\n" + exp.Message);
return(false);
}
//C) If the data are valid, save data
ApplicationState.ServerName = serverName;
ApplicationState.DatabaseName = databaseName;
ApplicationState.DboUserType = userType;
ApplicationState.DboUsername = userName;
ApplicationState.DboPassword = password;
ApplicationState.InstallVersion = version;
return(true);
} //end of checking
/// <summary>
/// Check whether the form data are valid
/// 1. If web SQL login is NT User
/// and need to be granted access permission
/// 1.1 NT user name is required
/// 1.2 This user must be SQL server login
/// 2. If the login is SQL user
/// 2.1 User name and password are required
/// 2.2 This login must be valid
/// </summary>
/// <returns></returns>
private bool SaveState()
{
SqlUserType userType;
string ntUserName;
string userName, password;
string connectionString;
bool grantPermission;
DataTable dt;
string ntUserCheckingSql = "select * from master.dbo.syslogins where name='{0}'";
bool isValid;
//Get Form data
if (this.rdoWindowsUser.Checked)
{
userType = SqlUserType.IntegratedUser;
}
else
{
userType = SqlUserType.SQLUser;
}
ntUserName = this.txtNTUser.Text;
userName = this.txtUsername.Text;
password = this.txtPassword.Text;
grantPermission = this.rdoGrantPermissionYes.Checked;
isValid = true;
// 1. If web SQL login is NT User
// and need to be granted access permission
// 1.1 NT user name is required
// 1.2 This user must be SQL server login
if (userType == SqlUserType.IntegratedUser && grantPermission)
{
connectionString = SqlTool.GetConnectionString(ApplicationState.ServerName, "Master", ApplicationState.DboUserType, ApplicationState.DboUsername, ApplicationState.DboPassword);
try
{
dt = SqlTool.ExecuteDataTable(connectionString, string.Format(ntUserCheckingSql, ntUserName.Replace("'", "''")));
if (dt.Rows.Count == 0)
{
MessageBox.Show("The selected NT login doesn't exist in the SQL server " + ApplicationState.ServerName, "Invalid Login", MessageBoxButtons.OK);
isValid = false;
}
else
{
isValid = true;
}
}
catch (Exception exp)
{
MessageBox.Show("Connection failed.\n" + exp.Message);
isValid = false;
}
}
// 2. If the login is SQL user
// 2.1 User name and password are required
// 2.2 This login must be valid
if (userType == SqlUserType.SQLUser)
{
connectionString = SqlTool.GetConnectionString(ApplicationState.ServerName, "Master", userType, userName, password);
isValid = SqlTool.CheckConnection(connectionString);
}
//If the data are valid, save data
if (isValid)
{
ApplicationState.WebUserType = userType;
ApplicationState.WebNTUsername = ntUserName;
ApplicationState.WebUsername = userName;
ApplicationState.WebPassword = password;
ApplicationState.WebGrantPermission = grantPermission;
}
return(isValid);
} //end of checking
/// <summary>
/// Installs the database
/// </summary>
public void InstallDatabase()
{
string connectionString;
string sql;
string webUserName;
string newVersion;
string containsRptSQLJob;
containsRptSQLJob = "yes";
connectionString = "";
webUserName = "";
try
{
if (ApplicationState.RptWebUsername != null)
{
if (ApplicationState.RptWebUserType.Equals(SqlUserType.IntegratedUser))
{
webUserName = ApplicationState.RptWebNTUsername;
}
}
if ((webUserName == "") || (webUserName == null))
{
webUserName = ApplicationState.RptWebUsername;
}
}
catch
{
DialogResult r = MessageBox.Show("ApplicationState.Exception trying to read UserName", "info", MessageBoxButtons.OK);
}
try
{
containsRptSQLJob = ConfigurationSettings.AppSettings["ContainsRptSQLJob"];
}
catch
{
DialogResult r = MessageBox.Show("ConfigurationSettings.AppSettings.Exception trying to read ContainsRptSQLJob", "info", MessageBoxButtons.OK);
}
try
{
try
{
connectionString = SqlTool.GetConnectionString(ApplicationState.RptServerName, ApplicationState.RptDatabaseName, ApplicationState.RptDboUserType, ApplicationState.RptDboUsername, ApplicationState.RptDboPassword);
}
catch
{
DialogResult r = MessageBox.Show("ConfigurationSettings.AppSettings.Exception trying to build connectionstring", "info", MessageBoxButtons.OK);
}
//DialogResult r2 = MessageBox.Show("connectionString=" + connectionString, "info", MessageBoxButtons.YesNo);
//1. Install schema
String ErrorNum = "0";
if (ApplicationState.RptInstallVersion != "")
{
try
{
MigrationSettings settingsa = (MigrationSettings)ConfigurationSettings.GetConfig("MigrationSettings");
ErrorNum = "1";
}
catch
{
DialogResult r = MessageBox.Show("ConfigurationSettings.GetConfig.Exception trying to read MigrationSettings", "info", MessageBoxButtons.OK);
}
try
{
MigrationSettings settingsb = (MigrationSettings)ConfigurationSettings.GetConfig("MigrationSettings");
VersionSetting versionSettingb = settingsb.GetVersion(ApplicationState.RptInstallVersion);
ErrorNum = "2";
}
catch
{
DialogResult r = MessageBox.Show("settings.GetVersion.Exception trying to read RptInstallVersion", "info", MessageBoxButtons.OK);
}
try
{
MigrationSettings settingsc = (MigrationSettings)ConfigurationSettings.GetConfig("MigrationSettings");
VersionSetting versionSettingc = settingsc.GetVersion(ApplicationState.RptInstallVersion);
NameValueCollection stepsc = versionSettingc.Steps;
ErrorNum = "3";
}
catch
{
DialogResult r = MessageBox.Show("NameValueCollection.Exception trying to read versionSettingc.Steps", "info", MessageBoxButtons.OK);
}
MigrationSettings settings = (MigrationSettings)ConfigurationSettings.GetConfig("MigrationSettings");
VersionSetting versionSetting = settings.GetVersion(ApplicationState.RptInstallVersion);
NameValueCollection steps = versionSetting.Steps;
ErrorNum = "4";
int stepsCount, step;
step = 0;
stepsCount = steps.Count;
foreach (string description in steps.AllKeys)
{
try
{
step++;
if (this.cancel)
{
System.Windows.Forms.Application.Exit();
}
sql = "";
ErrorNum = "5";
try
{
sql = SqlTool.ReadScript(steps[description]);
}
catch
{
DialogResult r = MessageBox.Show("Null NAME in NAME-VALUE collection", "info", MessageBoxButtons.OK);
}
/*
* -- Parameters:
* -- This values will be set in the deployment process
* -- {0} Databasename
* -- {1} WEB SQL Login
* -- {2} Salt Administrator username
* -- {3} Salt Administrator password
*/
//-- We don't want language translation {0} type place holders to be replaced.
if (description == "Optimising Reports" || description == "Creating SQL server Job")
{
sql = String.Format(sql,
ApplicationState.RptDatabaseName.Replace("'", "''"),
webUserName.Replace("'", "''"),
ApplicationState.RptAdminUsername.Replace("'", "''"),
ApplicationState.RptAdminPassword.Replace("'", "''")
);
}
ErrorNum = "6";
if (this.cancel)
{
System.Windows.Forms.Application.Exit();
}
this.lblStatus.Text = description + "...";
SqlTool.Execute(connectionString, sql);
this.prgStatus.Value = Convert.ToInt16((step * 1.0 / stepsCount) * 90.00);
}
catch (Exception ex)
{
this.lblError.Text += ex.Message + "\n";
MessageBox.Show(" Error " + ErrorNum + ex.Message + Environment.NewLine + ex.StackTrace);
Thread.Sleep(1000);
}
}
//Update Version
try
{
this.lblStatus.Text = "Set Application Version" + "...";
sql = ConfigurationSettings.AppSettings["SetVersionScript"];
newVersion = ConfigurationSettings.AppSettings["NewVersionID"];
sql = String.Format(sql, newVersion.Replace("'", "''"));
SqlTool.Execute(connectionString, sql);
}
catch (Exception ex)
{
MessageBox.Show(" Error 77 " + ex.Message + Environment.NewLine + ex.StackTrace);
Thread.Sleep(1000);
}
}
//2. Grant Web SQL Login access permission
try
{
if (ApplicationState.RptWebGrantPermission)
{
this.lblStatus.Text = "Grant Web SQL Login access permission" + "...";
sql = ConfigurationSettings.AppSettings["GrantPermissionScript"];
DialogResult r = MessageBox.Show("Are you sure you wish to Grant permissions to " + webUserName, "Confirm", MessageBoxButtons.YesNo);
bool ok = (r == DialogResult.Yes);
if (ok)
{
sql = String.Format(sql, webUserName.Replace("'", "''"));
SqlTool.Execute(connectionString, sql);
}
//sql = String.Format(sql, webUserName.Replace("'", "''"));
this.prgStatus.Value = 90;
}
}
catch (Exception ex)
{
MessageBox.Show(" The stored procedure that Grants Web SQL Login access permission failed with error:" + ex.Message + Environment.NewLine + ex.StackTrace);
Thread.Sleep(1000);
this.prgStatus.Value = 90;
}
//3. Set database connection
try
{
String strRptServer = "";
String strRptDatabase = "";
SqlUserType strRptWebUserType = SqlUserType.IntegratedUser;
String strRptWebUserName = "";
String strPassword = "";
this.lblStatus.Text = "Set database connection" + "...";
try
{
strRptServer = ApplicationState.RptServerName;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: ApplicationState.RptServerName is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
if (strRptServer == null)
{
MessageBox.Show(" Error 81: ApplicationState.RptServerName is null");
}
try
{
strRptDatabase = ApplicationState.RptDatabaseName;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: ApplicationState.RptDatabaseName is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
if (strRptDatabase == null)
{
MessageBox.Show(" Error 81: ApplicationState.RptDatabaseName is null");
}
try
{
strRptWebUserType = ApplicationState.RptWebUserType;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: ApplicationState.RptWebUserType is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
if (strRptWebUserType == null)
{
MessageBox.Show(" Error 81: ApplicationState.RptWebUserType is null");
}
try
{
strRptWebUserName = ApplicationState.RptWebUsername;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: ApplicationState.RptWebUsername is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
if (strRptWebUserName == null)
{
MessageBox.Show(" Error 81: ApplicationState.RptWebUsername is null");
}
try
{
strPassword = ApplicationState.RptWebPassword;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: ApplicationState.RptWebPassword is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
if (strPassword == null)
{
MessageBox.Show(" Error 81: ApplicationState.RptWebPassword is null");
}
string strWebConnectionString = "";
try
{
strWebConnectionString = SqlTool.GetConnectionString(
strRptServer,
strRptDatabase,
strRptWebUserType,
strRptWebUserName,
strPassword);
}
catch (Exception ex)
{
MessageBox.Show(" Error 80: strWebConnectionString is null" + ex.Message + Environment.NewLine + ex.StackTrace);
}
this.UpdateWebConfig(strWebConnectionString, ApplicationState.RptWebPassword);
this.prgStatus.Value = 100;
}
catch (Exception ex)
{
MessageBox.Show(" Error 80 " + ex.Message + Environment.NewLine + ex.StackTrace);
Thread.Sleep(1000);
this.prgStatus.Value = 100;
}
Thread.Sleep(1000);
this.Hide();
if (containsRptSQLJob.ToLower() == "yes")
{
MessageBox.Show("Please notify the database administrator that SQL Server Agent must be running in order to do background processing");
}
FrmRptComplete.Form.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show("Error 78:" + ex.Message + Environment.NewLine + ex.StackTrace);
Thread.Sleep(1000);
System.Windows.Forms.Application.Exit();
}
}
/// <summary>
/// Installs the database
/// </summary>
public void InstallDatabase()
{
string connectionString;
string sql;
string webUserName;
string newVersion;
string containsSQLJob;
if (ApplicationState.WebUserType == SqlUserType.IntegratedUser)
{
webUserName = ApplicationState.WebNTUsername;
}
else
{
webUserName = ApplicationState.WebUsername;
}
containsSQLJob = ConfigurationSettings.AppSettings["ContainsSQLJob"];
try
{
connectionString = SqlTool.GetConnectionString(ApplicationState.ServerName, ApplicationState.DatabaseName, ApplicationState.DboUserType, ApplicationState.DboUsername, ApplicationState.DboPassword);
//1. Install schema
if (ApplicationState.InstallVersion != "")
{
MigrationSettings settings = (MigrationSettings)ConfigurationSettings.GetConfig("MigrationSettings");
VersionSetting versionSetting = settings.GetVersion(ApplicationState.InstallVersion);
NameValueCollection steps = versionSetting.Steps;
int stepsCount, step;
step = 0;
stepsCount = steps.Count;
foreach (string description in steps.AllKeys)
{
step++;
if (!description.Equals("Optimising Reports"))
{
try
{
if (this.cancel)
{
System.Windows.Forms.Application.Exit();
}
// Skip the OptimizedScripts
this.lblStatus.Text = description + "...";
sql = SqlTool.ReadScript(steps[description]);
/*
* -- Parameters:
* -- This values will be set in the deployment process
* -- {0} Databasename
* -- {1} WEB SQL Login
* -- {2} Salt Administrator username
* -- {3} Salt Administrator password
*/
//-- We don't want language translation {0} type place holders to be replaced.
if (description == "Optimising Reports" || description == "Creating SQL server Job" || description == "Load Data")
{
sql = String.Format(sql,
ApplicationState.DatabaseName.Replace("'", "''"),
webUserName.Replace("'", "''"),
ApplicationState.AdminUsername.Replace("'", "''"),
ApplicationState.AdminPassword.Replace("'", "''")
);
}
else if (description == "Set DB Server Time Zone")
{
sql = sql.Replace("@@@VVV@@@", ApplicationState.DBServerTZ);
}
sql = sql.Replace("{databaseUser}", webUserName.Replace("'", "''"));
sql = sql.Replace("{database}", ApplicationState.DatabaseName.Replace("'", "''"));
if (this.cancel)
{
System.Windows.Forms.Application.Exit();
}
SqlTool.Execute(connectionString, sql);
this.prgStatus.Value = Convert.ToInt16((step * 1.0 / stepsCount) * 90.00);
}
catch (Exception ex)
{
this.lblError.Text += ex.Message + "\n";
MessageBox.Show("Error:" + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
}
//Update Version
this.lblStatus.Text = "Set Application Version" + "...";
sql = ConfigurationSettings.AppSettings["SetVersionScript"];
newVersion = ConfigurationSettings.AppSettings["NewVersionID"];
sql = String.Format(sql, newVersion.Replace("'", "''"));
SqlTool.Execute(connectionString, sql);
}
//2. Grant Web SQL Login access permission
if (ApplicationState.WebGrantPermission)
{
this.lblStatus.Text = "Grant Web SQL Login access permission" + "...";
sql = ConfigurationSettings.AppSettings["GrantPermissionScript"];
sql = String.Format(sql, webUserName.Replace("'", "''"));
SqlTool.Execute(connectionString, sql);
this.prgStatus.Value = 90;
}
//3. Set database connection
this.lblStatus.Text = "Set database connection" + "...";
string strWebConnectionString = SqlTool.GetConnectionString(
ApplicationState.ServerName,
ApplicationState.DatabaseName,
ApplicationState.WebUserType,
ApplicationState.WebUsername,
ApplicationState.WebPassword);
this.UpdateWebConfig(strWebConnectionString, ApplicationState.WebPassword);
//this.UpdateCPDWebConfig(strWebConnectionString, ApplicationState.WebPassword);
this.prgStatus.Value = 100;
Thread.Sleep(1000);
this.Hide();
if (containsSQLJob.ToLower() == "yes")
{
MessageBox.Show("Please notify the database administrator that SQL Server Agent must be running in order to do background processing");
}
//Thread.CurrentThread.Abort();
FrmComplete.Form.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show("Error:" + ex.Message + Environment.NewLine + ex.StackTrace);
System.Windows.Forms.Application.Exit();
}
}
