//check login in database public static bool CheckLogin(List <TextBox> lt) { bool successfull = false; int count; // define connection SqlConnection connection = TraverExpertsDB.GetConnection(); // define the select query command string checkUserQuery = "select count(*) " + "from Customers " + "where CustUserId ='" + lt[0].Text + "'"; SqlCommand checkUserCommand = new SqlCommand(checkUserQuery, connection); try { // open the connection connection.Open(); count = Convert.ToInt32(checkUserCommand.ExecuteScalar()); } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connecto no matter what } if (count == 1)//have match in database { string checkPasswordQuery = "select Password " + "from Customers " + "where CustUserId ='" + lt[0].Text + "'"; SqlCommand checkPassCommand = new SqlCommand(checkPasswordQuery, connection); try { connection.Open(); string password = checkPassCommand.ExecuteScalar().ToString(); if (password == lt[1].Text)//have match in database { successfull = true; } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connecto no matter what } } return(successfull); }
//insert new customer to database public static int AddCustomer(Customer cust) // returns generated customer id { int custID = 0; // prepare connection SqlConnection connection = TraverExpertsDB.GetConnection(); // prepare the statement string insertString = "insert into Customers " + "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId, CustUserID, Password) " + "values(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @AgentId, @CustUserID, @Password)"; SqlCommand insertCommand = new SqlCommand(insertString, connection); insertCommand.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName); insertCommand.Parameters.AddWithValue("@CustLastName", cust.CustLastName); insertCommand.Parameters.AddWithValue("@CustAddress", cust.CustAddress); insertCommand.Parameters.AddWithValue("@CustCity", cust.CustCity); insertCommand.Parameters.AddWithValue("@CustProv", cust.CustProv); insertCommand.Parameters.AddWithValue("@CustPostal", cust.CustPostal); insertCommand.Parameters.AddWithValue("@CustCountry", cust.CustCountry); insertCommand.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone); insertCommand.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone); insertCommand.Parameters.AddWithValue("@CustEmail", cust.CustEmail); insertCommand.Parameters.AddWithValue("@AgentId", DBNull.Value); insertCommand.Parameters.AddWithValue("@CustUserID", cust.CustUserID); insertCommand.Parameters.AddWithValue("@Password", cust.Password); try { // open connection connection.Open(); // execute the statement int i = insertCommand.ExecuteNonQuery(); if (i == 1) // one record inserted { // retrieve customer id from the added record string selectString = "select ident_current('Customers') " + "from Customers"; SqlCommand selectCommand = new SqlCommand(selectString, connection); custID = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!! } } catch (Exception ex) { throw ex; // pass the buck } finally { connection.Close(); } return(custID); }
//retrieve Customer data from database public static Customer GetCustomer(string custUserId) { Customer cust = null; // found customer // define connection SqlConnection connection = TraverExpertsDB.GetConnection(); // define the select query command string selectQuery = "select CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, " + "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId, " + "CustUserId, Password " + "from Customers " + "where CustUserId = @CustUserId"; SqlCommand selectCommand = new SqlCommand(selectQuery, connection); selectCommand.Parameters.AddWithValue("@CustUserId", custUserId); try { // open the connection connection.Open(); // execute the query SqlDataReader reader = selectCommand.ExecuteReader(); // process the result if any if (reader.Read()) // if there is customer { cust = new Customer(); cust.CustomerId = (int)reader["CustomerID"]; cust.CustFirstName = reader["CustFirstName"].ToString(); cust.CustLastName = reader["CustLastName"].ToString(); cust.CustAddress = reader["CustAddress"].ToString(); cust.CustCity = reader["CustCity"].ToString(); cust.CustProv = reader["CustProv"].ToString(); cust.CustPostal = reader["CustPostal"].ToString(); cust.CustCountry = reader["CustCountry"].ToString(); cust.CustHomePhone = reader["CustHomePhone"].ToString(); cust.CustBusPhone = reader["CustBusPhone"].ToString(); cust.CustEmail = GetNullableString(reader, "CustEmail"); cust.AgentId = GetNullableInt(reader, "AgentId"); cust.CustUserID = reader["CustUserID"].ToString(); cust.Password = reader["Password"].ToString(); } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connecto no matter what } return(cust); }
//verify if UserId is unique public static bool checkUserId(string custUserId) { bool successfull = false; int count; // define connection SqlConnection connection = TraverExpertsDB.GetConnection(); // define the select query command string checkUserQuery = "select count(*) " + "from Customers " + "where CustUserId = @CustUserId"; SqlCommand checkUserCommand = new SqlCommand(checkUserQuery, connection); checkUserCommand.Parameters.AddWithValue("@CustUserId", custUserId); try { // open the connection connection.Open(); count = Convert.ToInt32(checkUserCommand.ExecuteScalar()); if (count >= 1) { successfull = false; } else { successfull = true; } } catch (Exception ex) { throw ex; // let the form handle it } finally { connection.Close(); // close connecto no matter what } return(successfull); }
//modify customer data public static bool UpdateCustomer(Customer customer, Customer old_customer) { bool successful = false; SqlConnection connection = TraverExpertsDB.GetConnection(); string updateString = "update Customers set " + "CustFirstName = @NewCustFirstName, " + "CustLastName = @NewCustLastName, " + "CustAddress = @NewCustAddress, " + "CustCity = @NewCustCity, " + "CustProv = @NewCustProv, " + "CustPostal = @NewCustPostal, " + "CustCountry = @NewCustCountry, " + "CustHomePhone = @NewCustHomePhone, " + "CustBusPhone = @NewCustBusPhone, " + "CustEmail = @NewCustEmail, " + "AgentId = @NewAgentId, " + "CustUserID = @NewCustUserID, " + "Password = @NewPassword " + "where " + // update succeeds only if record not changed by other users "CustFirstName = @OldCustFirstName and " + "CustLastName = @OldCustLastName and " + "CustAddress = @OldCustAddress and " + "CustCity = @OldCustCity and " + "CustProv = @OldCustProv and " + "CustPostal = @OldCustPostal and " + "CustCountry = @OldCustCountry and " + "CustHomePhone = @OldCustHomePhone and " + "(CustBusPhone = @OldCustBusPhone or " + "CustBusPhone Is NULL and @OldCustBusPhone Is NULL) and " + "(CustEmail = @OldCustEmail or " + "CustEmail Is NUll and @OldCustEmail IS NULL) and " + "(AgentId = @OldAgentId or " + "AgentId IS NULL and @OldAgentId is NULL) and " + "CustUserID = @OldCustUserID and " + "Password = @OldPassword"; SqlCommand updateCommand = new SqlCommand(updateString, connection); updateCommand.Parameters.AddWithValue("@OldCustFirstName", old_customer.CustFirstName); updateCommand.Parameters.AddWithValue("@OldCustLastName", old_customer.CustLastName); updateCommand.Parameters.AddWithValue("@OldCustAddress", old_customer.CustAddress); updateCommand.Parameters.AddWithValue("@OldCustCity", old_customer.CustCity); updateCommand.Parameters.AddWithValue("@OldCustProv", old_customer.CustProv); updateCommand.Parameters.AddWithValue("@OldCustPostal", old_customer.CustPostal); updateCommand.Parameters.AddWithValue("@OldCustCountry", old_customer.CustCountry); updateCommand.Parameters.AddWithValue("@OldCustHomePhone", old_customer.CustHomePhone); if (old_customer.CustBusPhone != null) { updateCommand.Parameters.AddWithValue("@OldCustBusPhone", old_customer.CustBusPhone); } else { updateCommand.Parameters.AddWithValue("@OldCustBusPhone", DBNull.Value); } if (old_customer.CustEmail != null) { updateCommand.Parameters.AddWithValue("@OldCustEmail", old_customer.CustEmail); } else { updateCommand.Parameters.AddWithValue("@OldCustEmail", DBNull.Value); } if (old_customer.AgentId != null) { updateCommand.Parameters.AddWithValue("@OldAgentId", old_customer.AgentId); } else { updateCommand.Parameters.AddWithValue("@OldAgentId", DBNull.Value); } updateCommand.Parameters.AddWithValue("@OldCustUserID", old_customer.CustUserID); updateCommand.Parameters.AddWithValue("@OldPassword", old_customer.Password); updateCommand.Parameters.AddWithValue("@NewCustFirstName", customer.CustFirstName); updateCommand.Parameters.AddWithValue("@NewCustLastName", customer.CustLastName); updateCommand.Parameters.AddWithValue("@NewCustAddress", customer.CustAddress); updateCommand.Parameters.AddWithValue("@NewCustCity", customer.CustCity); updateCommand.Parameters.AddWithValue("@NewCustProv", customer.CustProv); updateCommand.Parameters.AddWithValue("@NewCustPostal", customer.CustPostal); updateCommand.Parameters.AddWithValue("@NewCustCountry", customer.CustCountry); updateCommand.Parameters.AddWithValue("@NewCustHomePhone", customer.CustHomePhone); if (customer.CustBusPhone != null) { updateCommand.Parameters.AddWithValue("@NewCustBusPhone", customer.CustBusPhone); } else { updateCommand.Parameters.AddWithValue("@NewCustBusPhone", DBNull.Value); } if (customer.CustEmail != null) { updateCommand.Parameters.AddWithValue("@NewCustEmail", customer.CustEmail); } else { updateCommand.Parameters.AddWithValue("@NewCustEmail", DBNull.Value); } if (customer.AgentId != null) { updateCommand.Parameters.AddWithValue("@NewAgentId", customer.AgentId); } else { updateCommand.Parameters.AddWithValue("@NewAgentId", DBNull.Value); } updateCommand.Parameters.AddWithValue("@NewCustUserID", customer.CustUserID); updateCommand.Parameters.AddWithValue("@NewPassword", customer.Password); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count == 1) { successful = true; } } catch (Exception ex) { throw ex; } finally { //connection.Close(); } return(successful); }