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