private void btnFind_Click(object sender, EventArgs e)
{
equinox_data_access accessinfo = new equinox_data_access("1", "A", this.textBox1.Text, this.textBox2.Text, "", "", "", "", "", "", "");
hashem comparemeplease = new hashem();
SqlDataReader rdr = null;
String ConnectionString;
String sqlcommand;
ConnectionString = "data source=localhost;uid=sa;password=;database=equinox";
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand mySqlCommand = cn.CreateCommand();
// find user
sqlcommand = "select * from e_customers where customerid=@customerid and fi=@fi";
mySqlCommand.CommandText = sqlcommand;
// build
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
//assign data
mySqlCommand.Parameters["@fi"].Value = 1;
mySqlCommand.Parameters["@customerid"].Value = this.textBox1.Text;
mySqlCommand.Parameters["@pin"].Value = comparemeplease.makeHash(this.textBox2.Text);
rdr = mySqlCommand.ExecuteReader();
while (rdr.Read())
{
tbFirstName.Text = rdr["firstName"].ToString();
tbLastName.Text = rdr["lastname"].ToString();
tbMiddleName.Text = rdr["middlename"].ToString();
tbStreetAddress1.Text = rdr["streetaddress1"].ToString();
tbStreetAddress2.Text = rdr["streetaddress2"].ToString();
tbSuffix.Text = rdr["suffix"].ToString();
tbCity.Text = rdr["city"].ToString();
tbState.Text = rdr["state"].ToString();
tbZipCode.Text = rdr["zipcode"].ToString();
tbHomePhone.Text = rdr["homephone"].ToString();
tbWorkPhone.Text = rdr["workphone"].ToString();
tbCellPhone.Text = rdr["cellphone"].ToString();
tbEmail.Text = rdr["email"].ToString();
textBox2.Text = rdr["pin"].ToString();
}
}
private string database_processing(string fi, string dothis, string customerid, string pin, string institution, int associated, string email, string firstname, string middlename, string lastname, string role)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
hashem comparemeplease = new hashem();
string hashcompare = comparemeplease.makeHash(pin);
// construct a call to e_customers
String ConnectionString;
String sqlcommand;
try
{
ConnectionString = "data source=localhost;uid=sa;password=;database=equinox";
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand mySqlCommand = cn.CreateCommand();
switch (dothis)
{
case "a": // add a record
sqlcommand = "insert into e_customers(fi, customerid, pin, institution, associated, email, firstname, middlename, lastname, role) values(@fi, @customerid, @pin, @institution, @associated, @email, @firstname, @middlename, @lastname, @role)";
mySqlCommand.CommandText = sqlcommand;
// build
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
mySqlCommand.Parameters.Add("@institution", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@associated", SqlDbType.Int, 9);
mySqlCommand.Parameters.Add("@email", SqlDbType.VarChar, 45);
mySqlCommand.Parameters.Add("@firstname", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@middlename", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@lastname", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@role", SqlDbType.VarChar, 10);
//assign data
mySqlCommand.Parameters["@fi"].Value = fi;
mySqlCommand.Parameters["@customerid"].Value = customerid;
mySqlCommand.Parameters["@pin"].Value = hashcompare;
mySqlCommand.Parameters["@institution"].Value = 1;
mySqlCommand.Parameters["@associated"].Value = associated;
mySqlCommand.Parameters["@email"].Value = email;
mySqlCommand.Parameters["@firstname"].Value = firstname;
mySqlCommand.Parameters["@middlename"].Value = middlename;
mySqlCommand.Parameters["@lastname"].Value = lastname;
mySqlCommand.Parameters["@role"].Value = role;
mySqlCommand.CommandText = sqlcommand;
mySqlCommand.ExecuteNonQuery();
break;
case "u": // update existing record
sqlcommand = "update e_customers set fi = @fi, customerid = @customerid, pin = @pin, institution = @institution, ";
if (associated != 0)
{
sqlcommand = sqlcommand + "associated = @associated, ";
}
if (email != "")
{
sqlcommand = sqlcommand + "email = @email, ";
}
if (firstname != "")
{
sqlcommand = sqlcommand + "firstname = @firstname, ";
}
if (middlename != "")
{
sqlcommand = sqlcommand + "middlename = @middlename, ";
}
if (lastname != "")
{
sqlcommand = sqlcommand + "lastname = @lastname, ";
}
if (role != "")
{
sqlcommand = sqlcommand + "role = @role, ";
}
mySqlCommand.CommandText = sqlcommand;
//sqlcommand = "update e_customers set customerid, pin, institution, associated, email, firstname, middlename, lastname values(@fi, @customerid, @pin, @institution, @associated, @email, @firstname, @middlename, @lastname where ";
// build
//assign data
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters["@fi"].Value = fi;
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@customerid"].Value = customerid;
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
mySqlCommand.Parameters["@pin"].Value = hashcompare;
mySqlCommand.Parameters.Add("@institution", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@institution"].Value = fi;
if (associated != 0)
{
mySqlCommand.Parameters.Add("@associated", SqlDbType.Int, 9);
mySqlCommand.Parameters["@associated"].Value = associated;
}
if (email != "")
{
mySqlCommand.Parameters.Add("@email", SqlDbType.VarChar, 45);
mySqlCommand.Parameters["@email"].Value = email;
}
if (middlename != "")
{
mySqlCommand.Parameters.Add("@firstname", SqlDbType.VarChar, 25);
mySqlCommand.Parameters["@firstname"].Value = firstname;
}
if (middlename != "")
{
mySqlCommand.Parameters.Add("@middlename", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@middlename"].Value = middlename;
}
if (lastname != "")
{
mySqlCommand.Parameters.Add("@lastname", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@lastname"].Value = lastname;
}
if (role != "")
{
mySqlCommand.Parameters.Add("@role", SqlDbType.VarChar, 10);
mySqlCommand.Parameters["@role"].Value = role;
}
mySqlCommand.ExecuteNonQuery();
break;
case "A": // Check for the existance of the userid and pin combination. We do this by issuing a count agaist the where.
sqlcommand = "select count(*) from e_customers where customerid=@customerid and pin=@pin and fi=@fi";
mySqlCommand.CommandText = sqlcommand;
// build
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
//assign data
mySqlCommand.Parameters["@fi"].Value = fi;
mySqlCommand.Parameters["@customerid"].Value = customerid;
mySqlCommand.Parameters["@pin"].Value = hashcompare;
try
{
int iMyReturnValue = (int)mySqlCommand.ExecuteScalar();
if (iMyReturnValue == 1)
{
return("True");
}
if (iMyReturnValue == 0)
{
return("False");
}
if (iMyReturnValue > 1)
{
return("ToMany");
}
}
catch
{
return("False");
}
break;
}
}
catch
{
MessageBox.Show("error - e_customer failure", "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return("");
}
private void addUser_Click(object sender, EventArgs e)
{
hashem comparemeplease = new hashem();
String ConnectionString;
String sqlcommand;
try
{
ConnectionString = "data source=localhost;uid=sa;password=;database=equinox";
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand mySqlCommand = cn.CreateCommand();
//
sqlcommand = "insert into e_customers(fi, customerid, pin, firstname, middlename, lastname, suffix, streetaddress1, streetaddress2, city, state, zipcode, homephone, workphone, cellphone, email) values(@fi, @customerid, @pin, @firstname, @middlename, @lastname, @suffix, @streetaddress1, @streetaddress2, @city, @state, @zipcode, @homephone, @workphone, @cellphone, @email)";
mySqlCommand.CommandText = sqlcommand;
// build
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
mySqlCommand.Parameters.Add("@firstname", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@middlename", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@lastname", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@suffix", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@streetaddress1", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@streetaddress2", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@city", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@state", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@zipcode", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@homephone", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@workphone", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@cellphone", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@email", SqlDbType.VarChar, 45);
//assign data
mySqlCommand.Parameters["@fi"].Value = 1;
mySqlCommand.Parameters["@customerid"].Value = this.textBox1.Text;
mySqlCommand.Parameters["@pin"].Value = comparemeplease.makeHash(this.textBox2.Text);
mySqlCommand.Parameters["@firstname"].Value = tbFirstName.Text;
mySqlCommand.Parameters["@middlename"].Value = tbMiddleName.Text;
mySqlCommand.Parameters["@lastname"].Value = tbLastName.Text;
mySqlCommand.Parameters["@suffix"].Value = tbSuffix.Text;
mySqlCommand.Parameters["@streetaddress1"].Value = tbStreetAddress1.Text;
mySqlCommand.Parameters["@streetaddress2"].Value = tbStreetAddress2.Text;
mySqlCommand.Parameters["@city"].Value = tbCity.Text;
mySqlCommand.Parameters["@state"].Value = tbState.Text;
mySqlCommand.Parameters["@zipcode"].Value = tbZipCode.Text;
mySqlCommand.Parameters["@homephone"].Value = tbHomePhone.Text;
mySqlCommand.Parameters["@workphone"].Value = tbWorkPhone.Text;
mySqlCommand.Parameters["@cellphone"].Value = tbCellPhone.Text;
mySqlCommand.Parameters["@email"].Value = tbEmail.Text;
mySqlCommand.CommandText = sqlcommand;
mySqlCommand.ExecuteNonQuery();
}
catch // assume update!
{
ConnectionString = "data source=localhost;uid=sa;password=;database=equinox";
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand mySqlCommand = cn.CreateCommand();
//
sqlcommand = "update e_customers set pin=@pin, firstname=@firstname, middlename=@middlename, lastname=@lastname, suffix=@suffix, streetaddress1=@streetaddress1, streetaddress2=@streetaddress2, city=@city, state=@state, zipcode=@zipcode, homephone=@homephone, workphone=@workphone, cellphone=@cellphone, email=@email where fi=@fi and customerid=@customerid";
mySqlCommand.CommandText = sqlcommand;
// build
mySqlCommand.Parameters.Add("@fi", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@customerid", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@pin", SqlDbType.VarChar, 128);
mySqlCommand.Parameters.Add("@firstname", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@middlename", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@lastname", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@suffix", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@streetaddress1", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@streetaddress2", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@city", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@state", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@zipcode", SqlDbType.VarChar, 50);
mySqlCommand.Parameters.Add("@homephone", SqlDbType.VarChar, 10);
mySqlCommand.Parameters.Add("@workphone", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@cellphone", SqlDbType.VarChar, 25);
mySqlCommand.Parameters.Add("@email", SqlDbType.VarChar, 45);
//assign data
mySqlCommand.Parameters["@fi"].Value = 1;
mySqlCommand.Parameters["@customerid"].Value = this.textBox1.Text;
mySqlCommand.Parameters["@pin"].Value = comparemeplease.makeHash(this.textBox2.Text);
mySqlCommand.Parameters["@firstname"].Value = tbFirstName.Text;
mySqlCommand.Parameters["@middlename"].Value = tbMiddleName.Text;
mySqlCommand.Parameters["@lastname"].Value = tbLastName.Text;
mySqlCommand.Parameters["@suffix"].Value = tbSuffix.Text;
mySqlCommand.Parameters["@streetaddress1"].Value = tbStreetAddress1.Text;
mySqlCommand.Parameters["@streetaddress2"].Value = tbStreetAddress2.Text;
mySqlCommand.Parameters["@city"].Value = tbCity.Text;
mySqlCommand.Parameters["@state"].Value = tbState.Text;
mySqlCommand.Parameters["@zipcode"].Value = tbZipCode.Text;
mySqlCommand.Parameters["@homephone"].Value = tbHomePhone.Text;
mySqlCommand.Parameters["@workphone"].Value = tbWorkPhone.Text;
mySqlCommand.Parameters["@cellphone"].Value = tbCellPhone.Text;
mySqlCommand.Parameters["@email"].Value = tbEmail.Text;
mySqlCommand.CommandText = sqlcommand;
mySqlCommand.ExecuteNonQuery();
}
}