static void Main(string[] args) { string connectionString = "Integrated Security=true;" + "database=userFiles;" + "Data Source=.;"; // local machine SqlConnectionStringBuilder sqlStrBldr = new SqlConnectionStringBuilder(connectionString); sqlStrBldr.UserID = "sa"; sqlStrBldr.Password = "******"; SqlConnection connection = new SqlConnection(sqlStrBldr.ConnectionString); // new SqlConnection(connectionString); SqlCommand testCommand = new SqlCommand("INSERT INTO musicUsers VALUES('Test5', 'Test5PW')"); testCommand.Connection = connection; testCommand.Connection.Open(); testCommand.ExecuteNonQuery(); testCommand.Connection.Close(); Console.WriteLine(testCommand.ToString()); SqlCommand command = new SqlCommand("checkMusicUser", connection); command.CommandType = CommandType.StoredProcedure; SqlParameter userName = new SqlParameter("@uname", SqlDbType.VarChar); userName.Direction = ParameterDirection.Input; userName.Value = "Test1"; userName.Size = 50; command.Parameters.Add(userName); SqlParameter password = new SqlParameter("@passw", SqlDbType.VarChar); password.Direction = ParameterDirection.Input; password.Value = "Test1PW"; password.Size = 50; command.Parameters.Add(password); SqlParameter returnValue = new SqlParameter("@retval", SqlDbType.Int); returnValue.Direction = ParameterDirection.Output; command.Parameters.Add(returnValue); command.Connection.Open(); command.ExecuteNonQuery(); Console.WriteLine("TEST"); Console.WriteLine("Return result: " + command.Parameters["@retval"].Value.ToString()); Console.ReadKey(); }
/// <summary> /// //////////////////////////////connexion to server //////////////////////////////// public static void save_points_in_db(double x, double y, double z, int frame, string color, SqlConnection myConnection) { try { myConnection.Open(); } catch (Exception e) { Console.WriteLine(e.ToString()); } SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Points_saved (colx, coly, colz,frame,color) Values ('" + x + "','" + y + "','" + z + "','" + frame + "','" + color + "')", myConnection); // SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.Points_saved (colx, coly, colz,frame,color) Values (0.1,0.3,0.5,1,'red')", myConnection); Console.WriteLine(myCommand.ToString()); myCommand.ExecuteNonQuery(); try { myConnection.Close(); } catch (Exception e) { Console.WriteLine(e.ToString()); } }
private void btnRegist_Click(object sender, EventArgs e) { bool finished = true; string adminID = this.textBoxID.Text.Trim(); string adminName = this.textBoxName.Text.Trim(); string adminSex = this.textBoxSex.Text.Trim(); string adminBirth = this.textBoxBirth.Text.Substring(0, 10).Trim(); if (adminID == "" || adminName == "" || adminSex == "" || adminBirth == "") { MessageBox.Show("请输入完整信息","提示信息",MessageBoxButtons.OK,MessageBoxIcon.Information); return; } if (this.textBoxPassword1.Text.Trim() != this.textBoxPassword2.Text.Trim()) { MessageBox.Show("两次输入密码不一致","错误信息",MessageBoxButtons.OK,MessageBoxIcon.Error); this.textBoxPassword1.Text = ""; this.textBoxPassword2.Text = ""; return; } string adminPassword = this.textBoxPassword1.Text.Trim(); string comStr = string.Format("select * from dboSys where Sys_IID = \'{0}\'", adminID); SqlConnection sqlconnection=new SqlConnection(conStr); DataSet dataSet = new DataSet(); try { dataSet = SqlCommondClass.GetDataSet(comStr, "result", sqlconnection); } catch (System.Exception) { finished = false; MessageBox.Show("打开数据库失败", "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } finally { sqlconnection.Close(); } if (dataSet.Tables[0].Rows.Count >= 1) { MessageBox.Show("该管理员编号已经存在,可以直接登录", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } else { comStr = string.Format("insert into dboSys(Sys_IID,Sys_NAME,Sys_SEX,Sys_BIRTHDAY,Sys_PASSWORD) values(\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\')", adminID,adminName,adminSex,adminBirth, coding(adminPassword)); MessageBox.Show(comStr); try { sqlconnection.Open(); SqlCommand command = new SqlCommand(comStr, sqlconnection); MessageBox.Show(command.ToString()); command.ExecuteNonQuery(); } catch (System.Exception) { finished = false; MessageBox.Show("创建管理员失败", "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } finally { sqlconnection.Close(); } if (finished) { MessageBox.Show("创建管理员成功"); } } }
private void ApplySystemDefaultToAll_Click(object sender, EventArgs e) { DialogResult result = MessageBox .Show( "This action will overwrite all Pedigree GUI Preferences in the riskApps database. Are you sure this is what you want to do?", "Irreversible Action", MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (result == DialogResult.OK) { TypeConverter converter = TypeDescriptor.GetConverter(typeof(Font)); SqlCommand cmdProcedure = null; try { using (SqlConnection connection = new SqlConnection(BCDB2.Instance.getConnectionString())) { connection.Open(); cmdProcedure = new SqlCommand("sp_3_UpdateLkpGuiPrefsEnMasse", connection); cmdProcedure.CommandType = CommandType.StoredProcedure; cmdProcedure.CommandTimeout = 300; //change command timeout from default to 5 minutes //SessionManager.Instance.MetaData.SystemWideDefaultPedigreePrefs.ConsumeSettings(this.preferences); //GUIPreference p = SessionManager.Instance.MetaData.SystemWideDefaultPedigreePrefs; cmdProcedure.Parameters.Add("@modifiedDate", SqlDbType.DateTime); cmdProcedure.Parameters["@modifiedDate"].Value = DateTime.Now; cmdProcedure.Parameters.Add("@pedigreeZoomValue", SqlDbType.Int); cmdProcedure.Parameters["@pedigreeZoomValue"].Value = preferences.GUIPreference_zoomValue; cmdProcedure.Parameters.Add("@pedigreeVerticalSpacing", SqlDbType.Int); cmdProcedure.Parameters["@pedigreeVerticalSpacing"].Value = preferences.GUIPreference_verticalSpacing; cmdProcedure.Parameters.Add("@modifiedBy", SqlDbType.NVarChar); cmdProcedure.Parameters["@modifiedBy"].Value = SessionManager.Instance.ActiveUser.userLogin; cmdProcedure.Parameters.Add("@ShowRelIds", SqlDbType.Bit); cmdProcedure.Parameters["@ShowRelIds"].Value = preferences.GUIPreference_ShowRelIds; cmdProcedure.Parameters.Add("@PedigreeBackground", SqlDbType.NVarChar); cmdProcedure.Parameters["@PedigreeBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_PedigreeBackground); cmdProcedure.Parameters.Add("@nameWidth", SqlDbType.Int); cmdProcedure.Parameters["@nameWidth"].Value = preferences.GUIPreference_nameWidth; cmdProcedure.Parameters.Add("@limitedEthnicity", SqlDbType.Bit); cmdProcedure.Parameters["@limitedEthnicity"].Value = preferences.GUIPreference_limitedEthnicity; cmdProcedure.Parameters.Add("@limitedNationality", SqlDbType.Bit); cmdProcedure.Parameters["@limitedNationality"].Value = preferences.GUIPreference_limitedNationality; cmdProcedure.Parameters.Add("@ShowTitle", SqlDbType.Bit); cmdProcedure.Parameters["@ShowTitle"].Value = preferences.GUIPreference_ShowTitle; cmdProcedure.Parameters.Add("@ShowName", SqlDbType.Bit); cmdProcedure.Parameters["@ShowName"].Value = preferences.GUIPreference_ShowName; cmdProcedure.Parameters.Add("@NameFont", SqlDbType.NVarChar); cmdProcedure.Parameters["@NameFont"].Value = converter.ConvertToString(preferences.GUIPreference_NameFont); cmdProcedure.Parameters.Add("@ShowUnitnum", SqlDbType.Bit); cmdProcedure.Parameters["@ShowUnitnum"].Value = preferences.GUIPreference_ShowUnitnum; cmdProcedure.Parameters.Add("@UnitnumFont", SqlDbType.NVarChar); cmdProcedure.Parameters["@UnitnumFont"].Value = converter.ConvertToString(preferences.GUIPreference_UnitnumFont); cmdProcedure.Parameters.Add("@ShowDob", SqlDbType.Bit); cmdProcedure.Parameters["@ShowDob"].Value = preferences.GUIPreference_ShowDob; cmdProcedure.Parameters.Add("@DobFont", SqlDbType.NVarChar); cmdProcedure.Parameters["@DobFont"].Value = converter.ConvertToString(preferences.GUIPreference_DobFont); cmdProcedure.Parameters.Add("@TitleSpacing", SqlDbType.Int); cmdProcedure.Parameters["@TitleSpacing"].Value = preferences.GUIPreference_TitleSpacing; cmdProcedure.Parameters.Add("@TitleBackground", SqlDbType.NVarChar); cmdProcedure.Parameters["@TitleBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_TitleBackground); cmdProcedure.Parameters.Add("@TitleBorder", SqlDbType.NVarChar); cmdProcedure.Parameters["@TitleBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_TitleBorder); cmdProcedure.Parameters.Add("@ShowLegend", SqlDbType.Bit); cmdProcedure.Parameters["@ShowLegend"].Value = preferences.GUIPreference_ShowLegend; cmdProcedure.Parameters.Add("@LegendBackground", SqlDbType.NVarChar); cmdProcedure.Parameters["@LegendBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_LegendBackground); cmdProcedure.Parameters.Add("@LegendBorder", SqlDbType.NVarChar); cmdProcedure.Parameters["@LegendBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_LegendBorder); cmdProcedure.Parameters.Add("@LegendRadius", SqlDbType.Int); cmdProcedure.Parameters["@LegendRadius"].Value = preferences.GUIPreference_LegendRadius; cmdProcedure.Parameters.Add("@LegendFont", SqlDbType.NVarChar); cmdProcedure.Parameters["@LegendFont"].Value = converter.ConvertToString(preferences.GUIPreference_LegendFont); cmdProcedure.Parameters.Add("@ShowComment", SqlDbType.Bit); cmdProcedure.Parameters["@ShowComment"].Value = preferences.GUIPreference_ShowComment; cmdProcedure.Parameters.Add("@CommentBackground", SqlDbType.NVarChar); cmdProcedure.Parameters["@CommentBackground"].Value = System.Drawing.ColorTranslator.ToHtml(preferences.GUIPreference_CommentBackground); cmdProcedure.Parameters.Add("@CommentBorder", SqlDbType.NVarChar); cmdProcedure.Parameters["@CommentBorder"].Value = this.ParseBorderStyle(preferences.GUIPreference_CommentBorder); cmdProcedure.Parameters.Add("@CommentFont", SqlDbType.NVarChar); cmdProcedure.Parameters["@CommentFont"].Value = converter.ConvertToString(preferences.GUIPreference_CommentFont); cmdProcedure.Parameters.Add("@LegendX", SqlDbType.Int); cmdProcedure.Parameters["@LegendX"].Value = preferences.GUIPreference_LegendX; cmdProcedure.Parameters.Add("@LegendY", SqlDbType.Int); cmdProcedure.Parameters["@LegendY"].Value = preferences.GUIPreference_LegendY; cmdProcedure.Parameters.Add("@LegendHeight", SqlDbType.Int); cmdProcedure.Parameters["@LegendHeight"].Value = preferences.GUIPreference_LegendHeight; cmdProcedure.Parameters.Add("@LegendWidth", SqlDbType.Int); cmdProcedure.Parameters["@LegendWidth"].Value = preferences.GUIPreference_LegendWidth; cmdProcedure.Parameters.Add("@TitleX", SqlDbType.Int); cmdProcedure.Parameters["@TitleX"].Value = preferences.GUIPreference_TitleX; cmdProcedure.Parameters.Add("@TitleY", SqlDbType.Int); cmdProcedure.Parameters["@TitleY"].Value = preferences.GUIPreference_TitleY; cmdProcedure.Parameters.Add("@TitleHeight", SqlDbType.Int); cmdProcedure.Parameters["@TitleHeight"].Value = preferences.GUIPreference_TitleHeight; cmdProcedure.Parameters.Add("@TitleWidth", SqlDbType.Int); cmdProcedure.Parameters["@TitleWidth"].Value = preferences.GUIPreference_TitleWidth; cmdProcedure.Parameters.Add("@CommentX", SqlDbType.Int); cmdProcedure.Parameters["@CommentX"].Value = preferences.GUIPreference_CommentX; cmdProcedure.Parameters.Add("@CommentY", SqlDbType.Int); cmdProcedure.Parameters["@CommentY"].Value = preferences.GUIPreference_CommentY; cmdProcedure.Parameters.Add("@CommentHeight", SqlDbType.Int); cmdProcedure.Parameters["@CommentHeight"].Value = preferences.GUIPreference_CommentHeight; cmdProcedure.Parameters.Add("@CommentWidth", SqlDbType.Int); cmdProcedure.Parameters["@CommentWidth"].Value = preferences.GUIPreference_CommentWidth; cmdProcedure.Parameters.Add("@VariantFoundText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantFoundText"].Value = preferences.GUIPreference_VariantFoundText; cmdProcedure.Parameters.Add("@VariantFoundVusText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantFoundVusText"].Value = preferences.GUIPreference_VariantFoundVusText; cmdProcedure.Parameters.Add("@VariantNotFoundText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantNotFoundText"].Value = preferences.GUIPreference_VariantNotFoundText; cmdProcedure.Parameters.Add("@VariantUnknownText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantUnknownText"].Value = preferences.GUIPreference_VariantUnknownText; cmdProcedure.Parameters.Add("@VariantNotTestedText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantNotTestedText"].Value = preferences.GUIPreference_VariantNotTestedText; cmdProcedure.Parameters.Add("@VariantHeteroText", SqlDbType.NVarChar); cmdProcedure.Parameters["@VariantHeteroText"].Value = preferences.GUIPreference_VariantHeteroText; cmdProcedure.Parameters.Add("@hideNonBloodRelatives", SqlDbType.NVarChar); cmdProcedure.Parameters["@hideNonBloodRelatives"].Value = preferences.GUIPreference_hideNonBloodRelatives; cmdProcedure.ExecuteNonQuery(); } //bw 6/2/2014 - added annotation placements foreach (PedigreeAnnotation pa in preferences.annotations) { ParameterCollection pc = new ParameterCollection(); pc.Add("unitnum", "-2"); pc.Add("area", pa.area); pc.Add("slot", pa.slot); pc.Add("annotation", pa.annotation); pc.Add("user", SessionManager.Instance.ActiveUser.userLogin); BCDB2.Instance.RunSPWithParams("sp_3_Save_PedigreeAnnotations", pc); } MessageBox.Show( "Current GUI Preferences applied to all patients in database.", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { Logger.Instance.WriteToLog("[sp_3_UpdateLkpGuiPrefsEnMasse] Executing Stored Procedure - " + cmdProcedure.ToString() + "; " + ex.ToString()); } } else { return; } }
/// <summary> /// 将其它SQL命令参与到当前事务中来 /// </summary> /// <param name="sqlCmd"></param> public void JohnTrans(SqlCommand sqlCmd) { this.ClearErrInfo(); try { sqlCmd.Transaction = this.trans; } catch (Exception exp) { this.errMsg = sqlCmd.ToString() + "参与sql事务失败," + exp.Message; this.WriteErr(); } }
protected void ButtonAddDvd_Click(object sender, EventArgs e) { string res = ""; //hidden sql connection result variable SqlConnection conn; SqlCommand comm, commdet; string connectionString = ConfigurationManager.ConnectionStrings["DVDconnstring"].ConnectionString; conn = new SqlConnection(connectionString); comm = new SqlCommand("INSERT INTO DVDtable (DVDtitle, DVDartist, DVDrating, DVDprice) " + " VALUES (@DVDtitle, @DVDartist, @DVDrating, @DVDprice); SELECT SCOPE_IDENTITY();", conn); commdet = new SqlCommand("INSERT INTO Details(DVDID, Description, PicURL) " + "VALUES ( @DVDID, @Description, @PicURL)", conn); try //declare parameters and enter data { comm.Parameters.Add("@DVDtitle", System.Data.SqlDbType.NVarChar); comm.Parameters["@DVDtitle"].Value = textboxDVDTitle.Text; // "meow"; // comm.Parameters.Add("@DVDartist", System.Data.SqlDbType.NVarChar); comm.Parameters["@DVDartist"].Value = textboxDVDArtist.Text; //"meow"; // comm.Parameters.Add("@DVDrating", System.Data.SqlDbType.Int); comm.Parameters["@DVDrating"].Value = Convert.ToInt32(textboxDVDRating.Text); // 1; // comm.Parameters.Add("@DVDprice", System.Data.SqlDbType.Money); comm.Parameters["@DVDprice"].Value = Convert.ToDouble(textboxDVDPrice.Text); // 1; // } catch //catch errors in comm declarations such as text entered into numeric fields { dbErrorLabel.Text = "Something was entered wrong."; res += comm.ToString() + "\n" + comm.Parameters.ToString() + "\n"; } try //connect to SQL { dbErrorLabel.Text = "Connecting..."; conn.Open(); if (textboxDVDDescription.Text != "" && textboxDVDDescription.Text != null) { commdet.Parameters.Add("@DVDID", System.Data.SqlDbType.Int); commdet.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 500); commdet.Parameters.Add("@PicURL", System.Data.SqlDbType.NVarChar, 100); commdet.Parameters["@DVDID"].Value = comm.ExecuteScalar(); dbErrorLabel.Text = " DVD named " + comm.Parameters["@DVDtitle"].Value + " was added! "; commdet.Parameters["@Description"].Value = textboxDVDDescription.Text; commdet.Parameters["@PicURL"].Value = textboxDVDPicURL.Text; dbErrorLabel.Text += " " + commdet.ExecuteNonQuery() + " detail added."; }else { comm.ExecuteScalar(); dbErrorLabel.Text = " DVD named " + comm.Parameters["@DVDtitle"].Value + " was added! "; } InterfaceClearFields(); } catch (SqlException se) //catches sql failures and rejections { dbErrorLabel.Text += "There was an error adding this DVD entry to the system. "+ se.ToString(); res += se.ToString() + "\n" + e.ToString() + "\n"; } finally { res += "results of execution. \n" + comm.Parameters[0].Value + "\n" + conn.ToString() + " " + conn.State + "\n closing connection... "; conn.Close(); res += conn.State +"\n"; } }
public SqlDataReader CheckUpdate(string table1, string table2, string pkey1, string pkey2, ArrayList compareFields1, ArrayList compareFields2, ArrayList additionalFields, int adField, SqlConnection sqlConn, LogFile log) { // adField holds the value of which table holds values from ad to check the AD field fomat distinguished name vs samaccount / id if ad field is 0 neither should be checked // managerADtype if true manager field holds an AD object cn=name,ou=blah,dc=blah else it is a samaccount field // NULL not handeled as blanks // additionalFields takes the field names " table.field," // Assumes table1 holds the correct data and returns a data reader with the update fields columns from table1 // compare fields 1 & 2 should have the same number of items or it is likely that all rows will be found needing updating // returns fields from comparefields // returns the rows which table2 differs from table1 // Assumes table1 holds the correct data and returns a data reader with the update fields columns from table1 // returns the rows which table2's concatenated update fields differ from table1's concatenated update fields // eliminates rows which do not have a matching key in both tables // // additional fields table2.data2, table1.data5 //************************************************************************************************* //| Table1 | Table2 | Returned result //************************************************************************************************* //| ID Data Data5 | ID Data Data2 | | Table1.ID Table1.DATA table1.data5 table2.data2 //| 1 a ty | 1 a e | NOT RETURNED | //| 2 b e | null null r | NOT RETURNED | //| 3 c uyt | 3 null f | RETURNED | 3 c uyt f //| 4 d tr | 4 e w | RETURNED | 4 e tr w //| 4 f sr | 4 f w | NOT RETURNED | string compare1 = ""; string compare2 = ""; string fields = ""; string notnull = ""; string additionalfields = ""; bool managerADtype = false; int i = 0; //int i; //string debugRecordCount = ""; //string debug = ""; //debug = " total users from AD \n"; //SqlCommand sqlDebugComm = new SqlCommand("select top 20 * FROM " + table2, sqlConn); //SqlDataReader debugReader = sqlDebugComm.ExecuteReader(); //int debugFieldCount = debugReader.FieldCount; //for (i = 0; i < debugFieldCount; i++) //{ // debug += debugReader.GetName(i) + ", "; //} //debug += "\n"; //while (debugReader.Read()) //{ // for (i = 0; i < debugFieldCount; i++) // { // debug += (string)debugReader[i] + ", "; // } // debug += "\n"; //} //sqlDebugComm = new SqlCommand("select count(" + pkey2 + ") FROM " + table1, sqlConn); //debugReader.Close(); //debugRecordCount = sqlDebugComm.ExecuteScalar().ToString(); //MessageBox.Show("table " + table2 + " has " + debugRecordCount + " records \n " + debugFieldCount + " fields \n sample data" + debug); //check to see what format the manager is in and what table it is in if (adField == 1) { foreach (string key in compareFields1) { if (key == "CN") { } if (key == "manager") { SqlCommand sqlCheck = new SqlCommand("select top 1 manager FROM " + table1, sqlConn); // create the command object SqlDataReader checkReader; try { sqlCheck.CommandTimeout = 360; checkReader = sqlCheck.ExecuteReader(); log.addTrn(sqlCheck.CommandText.ToString(), "Query"); checkReader.Read(); if ((string)checkReader[0].ToString().Substring(0, 2) == "CN=") { managerADtype = true; } checkReader.Close(); } catch (Exception ex) { log.addTrn("Failed SQL command " + sqlCheck.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error"); } } } } else if (adField == 2) { foreach (string key in compareFields2) { if (key == "manager") { SqlCommand sqlCheck = new SqlCommand("select top 1 manager FROM " + table2, sqlConn); // create the command object SqlDataReader checkReader; try { sqlCheck.CommandTimeout = 360; checkReader = sqlCheck.ExecuteReader(); log.addTrn(sqlCheck.ToString(), "Query"); checkReader.Read(); if ((string)checkReader[0].ToString().Substring(0, 2) == "CN=") { managerADtype = true; } checkReader.Close(); } catch (Exception ex) { log.addTrn("Failed SQL command " + sqlCheck.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error"); } } } } // need a comand builder and research on the best way to compare all fields in a row // this basically will just issue a concatenation sql query to the DB for each field to compare //foreach (string key in compareFields1) //{ // if (managerADtype == false && key == "manager" && adField == 2) // { // //add code for substring of manager field // compare1 = compare1 + "case when " + table2 + "." + compareFields2[i] + " <> '' then (substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + "; // fields += "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5), "; // } // else // { // compare1 = compare1 + "case when " + table2 + "." + compareFields2[i] + " <> '' then (" + table1 + "." + key + " COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + "; // fields += table1 + "." + key + ", "; // } // i++; //} //i = 0; foreach (string key in compareFields1) { if (managerADtype == false && key == "manager" && adField == 2) { //add code for substring of manager field compare1 = compare1 + "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS + "; fields += "substring(" + table1 + "." + key + ",4, charindex('ou=', " + table1 + "." + key + ")-5), "; } else { compare1 = compare1 + "ltrim(rtrim(" + table1 + "." + key + ")) COLLATE SQL_Latin1_General_CP1_CS_AS + "; fields += table1 + "." + key + ", "; } i++; } i = 0; //compare1 = compare1 + table1 + "." + pkey1; foreach (string key in compareFields2) { if (managerADtype == false && key == "manager" && adField == 1) { //add code for substring of manager field compare2 = compare2 + "case when ltrim(rtrim(" + table2 + "." + compareFields2[i] + ")) <> '' then (substring(" + table2 + "." + key + ",4, charindex('ou=', " + table2 + "." + key + ")-5) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + "; notnull += "case when len(ltrim(rtrim(" + table2 + "." + key + "))) > 3 then substring(" + table2 + "." + key + ",4, charindex('ou=', " + table2 + "." + key + ")-5) else '' end <> '' OR "; } else { compare2 = compare2 + "case when ltrim(rtrim(" + table2 + "." + compareFields2[i] + ")) <> '' then (ltrim(rtrim(" + table2 + "." + key + ")) COLLATE SQL_Latin1_General_CP1_CS_AS ) else '' end + "; //fields += table2 + "." + key + ", "; notnull += "ltrim(rtrim(" + table2 + "." + key + ")) <> '' OR "; } i++; } //compare2 = compare2 + table2 + "." + pkey2; foreach (string key in additionalFields) { additionalfields += key; } // remove trailing comma and + compare2 = compare2.Remove(compare2.Length - 2); compare1 = compare1.Remove(compare1.Length - 2); fields = fields.Remove(fields.Length - 2); notnull = notnull.Remove(notnull.Length - 3); additionalfields = additionalfields.Remove(additionalfields.Length - 2); SqlCommand sqlComm; if (additionalFields.Count > 0) { sqlComm = new SqlCommand("SELECT DISTINCT " /*+ compare2 + "," + compare1 + "," + table1 + "." + pkey1 + "," + table2 + "." + pkey2 + ","*/ + fields + ", " + additionalfields + " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2 + " AND (" + compare2 + ") <> (" + compare1 + ") INNER JOIN " + table2 + " as [" + table2 + "temp] ON " + table1 + "." + pkey1 + " = [" + table2 + "temp]." + pkey2 + " AND " + table2 + "." + pkey2 + " IS NOT NULL WHERE " + notnull, sqlConn); } else { sqlComm = new SqlCommand("SELECT DISTINCT " + fields + " FROM " + table1 + " LEFT JOIN " + table2 + " ON " + table1 + "." + pkey1 + " = " + table2 + "." + pkey2 + " AND (" + compare2 + ") <> (" + compare1 + ") INNER JOIN " + table2 + " as [" + table2 + "temp] ON " + table1 + "." + pkey1 + " = [" + table2 + "temp]." + pkey2 + " AND " + table2 + "." + pkey2 + " IS NOT NULL WHERE " + notnull, sqlConn); } //AND " + table2 + "." + pkey2 + " != NULL try { sqlComm.CommandTimeout = 360; SqlDataReader r = sqlComm.ExecuteReader(); log.addTrn(sqlComm.CommandText.ToString(), "Query"); return r; } catch (Exception ex) { log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error"); } return null; }
protected void ButtonSignin_Click(object sender, EventArgs e) { if (txtPassword.Text == "" || txtUserID.Text == "") { if (txtPassword.Text == "") { LabelPasswordWarning.Text = "*Required"; LabelPasswordWarning.Visible = true; } if (txtUserID.Text == "") { LabelIDWarning.Text = "*Required"; LabelIDWarning.Visible = true; } } else { string userName = txtUserID.Text, userPassword = txtPassword.Text; SqlConnection db = new SqlConnection(SqlDataSource1.ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "SELECT COUNT(*) FROM [User] WHERE ([User_Email] = '" + userName + "')"; cmd.Connection = db; db.Open(); LabelIDWarning.Text = cmd.ToString(); int cat = (int)cmd.ExecuteScalar(); /************************************************************************************************ * * function: Looks for the Username entered by the client. * ************************************************************************************************/ try { if (cat == 1) { LabelIDWarning.Text = "Success!"; LabelIDWarning.Visible = true; LabelPasswordWarning.Visible = false; } else { LabelIDWarning.Text = "User Name not found"; LabelIDWarning.Visible = true; cat = 0; } } catch { LabelIDWarning.Text = "Error!"; LabelIDWarning.Visible = true; } finally { db.Close(); } /************************************************************************************************ * * function: Looks for the password of the matching username above. * ************************************************************************************************/ cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "SELECT COUNT(*) FROM [User] WHERE ([User_Password] = '" + userPassword + "')"; cmd.Connection = db; db.Open(); cat += (int)cmd.ExecuteScalar(); try { if (cat == 2) { LabelIDWarning.Text = "Success!"; LabelIDWarning.Visible = true; LabelPasswordWarning.Visible = false; Response.Redirect("/home.aspx"); } else { LabelPasswordWarning.Text = "Incorrect Password"; LabelPasswordWarning.Visible = true; cat = 0; } } catch { LabelIDWarning.Text = "Error!"; LabelIDWarning.Visible = true; } finally { db.Close(); } } }