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