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