/// <summary>
/// Adds a person to the database and returns the customer ID
/// </summary>
public int AddPersonToDatabase(Person person, SqlConnection connection)
{
SqlConnection myConnection = connection;
int tID = 0;
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "spAddContact";
SqlParameter paramFirstName = new SqlParameter("@firstName", SqlDbType.VarChar);
SqlParameter paramLastName = new SqlParameter("@lastName", SqlDbType.VarChar);
SqlParameter paramSSN = new SqlParameter("@SSN", SqlDbType.VarChar);
SqlParameter paramEmailAdress = new SqlParameter("@emailAdress", SqlDbType.VarChar);
SqlParameter paramID = new SqlParameter("@newID", SqlDbType.Int);
paramID.Direction = ParameterDirection.Output;
paramFirstName.Value = person.FirstName;
paramLastName.Value = person.LastName;
paramSSN.Value = person.SocialSecurity;
paramEmailAdress.Value = person.EmailAdress;
myCommand.Parameters.Add(paramFirstName);
myCommand.Parameters.Add(paramLastName);
myCommand.Parameters.Add(paramSSN);
myCommand.Parameters.Add(paramID);
myCommand.Parameters.Add(paramEmailAdress);
myCommand.ExecuteNonQuery();
tID = (int)paramID.Value;
}
catch (Exception e) { Console.WriteLine(e.Message); }
finally { myConnection.Close(); }
return tID;
}
protected void bAddUser_Click(object sender, EventArgs e)
{
List<Person> contacts = new List<Person>();
if ((txFirstName.Text == "") || (txLastName.Text == ""))
{
lSSNExists.Visible = true;
lSSNExists.Text = "Please provide first and last names";
return;
}
Person thisPerson = new Person();
try
{
thisPerson = new Person(txFirstName.Text, txLastName.Text, txSocialSecurity.Text, txEmailAdress.Text);
}
catch (Exception ex)
{
lSSNExists.Visible = true;
lSSNExists.Text = ex.Message;
return;
}
if (sql.PersonExits(thisPerson, contactConnection))
{
lSSNExists.Visible = true;
lSSNExists.Text = "Social security " + txSocialSecurity.Text + " already exist ";
return;
}
contacts.Add(thisPerson);
int thisID = sql.AddPersonToDatabase(contacts.Last(), contactConnection);
txFirstName.Text = "";
txLastName.Text = "";
txSocialSecurity.Text = "";
txEmailAdress.Text = "";
bAddUser.Enabled = false;
}
/// <summary>
/// Checks if a person exists in the db
/// </summary>
public bool PersonExits(Person person, SqlConnection connection)
{
SqlConnection thisConnection = connection;
thisConnection.Open();
string sqlQuery = "SELECT Count(*) FROM CONTACT ";
sqlQuery += "WHERE SSN = '" + person.SocialSecurity + "'";
SqlCommand myCommand = new SqlCommand(sqlQuery, thisConnection);
int usersWithThisSSN = (int)myCommand.ExecuteScalar();
thisConnection.Close();
if (usersWithThisSSN > 0)
return true;
else
return false;
}
public void ChangePersonInDatabase(Person person, int IDToChange, SqlConnection connection)
{
SqlConnection myConnection = connection;
int thisID = IDToChange;
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "spChangeContact";
SqlParameter paramFirstName = new SqlParameter("@firstName", SqlDbType.VarChar);
SqlParameter paramLastName = new SqlParameter("@lastName", SqlDbType.VarChar);
SqlParameter paramID = new SqlParameter("@ID", SqlDbType.Int);
paramFirstName.Value = person.FirstName;
paramLastName.Value = person.LastName;
paramID.Value = thisID;
myCommand.Parameters.Add(paramFirstName);
myCommand.Parameters.Add(paramLastName);
myCommand.Parameters.Add(paramID);
myCommand.ExecuteNonQuery();
}
catch (Exception e) { Console.WriteLine(e.Message); }
finally { myConnection.Close(); }
}
