internal static int AddMarinaCustomer(MarinaCustomer cust) { int custID = 0; SqlConnection connection = MarinaDB.GetConnection(); string insertStatement = "INSERT INTO Customer(FirstName,LastName,Phone,City,Email,Password) VALUES(@FirstName,@LastName,@Phone,@City,@Email,@Password)"; SqlCommand cmd = new SqlCommand(insertStatement, connection); cmd.Parameters.AddWithValue("@FirstName", cust.FirstName); cmd.Parameters.AddWithValue("@LastName", cust.LastName); cmd.Parameters.AddWithValue("@Phone", cust.Phone); cmd.Parameters.AddWithValue("@City", cust.City); cmd.Parameters.AddWithValue("@Email", cust.Email); cmd.Parameters.AddWithValue("@Password", cust.Password); try { connection.Open(); //custID = (int)cmd.ExecuteScalar(); cmd.ExecuteNonQuery(); string selectQuery = "SELECT IDENT_CURRENT('Customer')"; SqlCommand selectCmd = new SqlCommand(selectQuery, connection); custID = Convert.ToInt32(selectCmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(custID); }
internal static int MarinaCustomerLogin(MarinaCustomer cust) { int idValue = -1; try { SqlConnection connection = MarinaDB.GetConnection(); string selectID = "SELECT ID FROM Customer WHERE Email = @Email AND Password = @Password"; SqlCommand cmd = new SqlCommand(selectID, connection); cmd.Parameters.AddWithValue("@Email", cust.Email); cmd.Parameters.AddWithValue("@Password", cust.Password); //int idValue = Convert.ToInt32(cmd.ExecuteScalar()); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); // test if there is customer if (reader.Read()) { idValue = (int)reader["ID"]; } } catch (Exception ex) { throw ex; } return(idValue); }
//every time customer click on select add the lease to row protected void gvAvailableLeaseSlip_SelectedIndexChanged(object sender, EventArgs e) { //get slipid of row selected if (gvAvailableLeaseSlip.SelectedIndex > -1) { //get the id from the row selected var selectedrow = gvAvailableLeaseSlip.SelectedRow; string cellID = selectedrow.Cells[1].Text; //cellID will be my lease ID int slipID = Convert.ToInt32(cellID); //add a row to the lease table for this customer ID and leaseID SqlConnection con = MarinaDB.GetConnection(); string insertStatement = "INSERT INTO Lease (SlipID,CustomerID) " + "VALUES(@SlipID,@CustomerID)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@SlipID", slipID); cmd.Parameters.AddWithValue("@CustomerID", custID); try { con.Open(); cmd.ExecuteNonQuery(); // run the insert command // get the generated ID - current identity value for Lease table string selectQuery = "SELECT IDENT_CURRENT('Lease') FROM Lease"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); int LeaseID = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value // typecase (int) does NOT work! //change button text from select to succefuly leased selectedrow.Cells[0].Text = "Succefuly leased"; } catch (SqlException ex) { throw ex; } finally { con.Close(); } } }