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