public static void InsertCustomer(Customer customer)
{
string sql = "INSERT INTO Customer "
+ "(FirstName, LastName, Phone, City,Username,Password) "
+ "VALUES (@FirstName, @LastName, @Phone, @City, @Username, @Password)";
try
{
if (customer.FirstName != null && customer.LastName != null &&
customer.Phone != null && customer.City != null &&
customer.Username != null && customer.Password != null)
{
using (SqlConnection con = MarinaDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("FirstName", customer.FirstName);
cmd.Parameters.AddWithValue("LastName", customer.LastName);
cmd.Parameters.AddWithValue("Phone", customer.Phone);
cmd.Parameters.AddWithValue("City", customer.City);
cmd.Parameters.AddWithValue("Username", customer.Username);
cmd.Parameters.AddWithValue("Password", customer.Password);
con.Open();
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception)
{
}
}
// update customer
// retirn indicator of success
public static bool UpdateCustomer(Customer old_Customer, Customer customer) // changed Customer names in brackets
{
bool success = false; // did not update
// connection
SqlConnection connection = MarinaDB.GetConnection();
// update command
string updateStatement =
"UPDATE Customers SET " +
"FirstName = @NewFirstName, " +
"LastName = @NewLastName, " +
"Phone= @NewPhone, " +
"City = @NewCity, " +
"WHERE CustomerID = @OldCustomerID " + // identifies ccustomer
"AND FirstName = @OldFirstName " + // remaining - for otimistic concurrency
"AND LastName = @OldLastName " +
"AND Phone = @OldPhone " +
"AND City = @OldCity ";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
// change customer.Name, etc to match line 164
// change properties for Update Method, DataObjectName, Conflict Detection to Compared All Values
cmd.Parameters.AddWithValue("@NewFirstName", customer.FirstName);
cmd.Parameters.AddWithValue("@NewLastName", customer.LastName);
cmd.Parameters.AddWithValue("@NewPhone", customer.Phone);
cmd.Parameters.AddWithValue("@NewCity", customer.City);
cmd.Parameters.AddWithValue("@OldCustomerID", old_Customer.ID);
cmd.Parameters.AddWithValue("@OldFirstName", old_Customer.FirstName);
cmd.Parameters.AddWithValue("@OldLastName", customer.LastName);
cmd.Parameters.AddWithValue("@OldPhone", old_Customer.Phone);
cmd.Parameters.AddWithValue("@OldCity", old_Customer.City);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
// retrieve customer with given ID
public static List <Customer> GetCustomersByLease(int ID)
{
List <Customer> customers = new List <Customer>(); // empty list
Customer cust = null; // for reading
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create SELECT command
string query = "SELECT CustomerID, FirstName, LastName, Phone, City " +
"FROM Customers " +
"WHERE Lease = @ID";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@ID", ID);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// add customer to the list
while (reader.Read()) // while there are customers
{
cust = new Customer();
cust.ID = (int)reader["CustomerID"];
cust.FirstName = reader["FirstName"].ToString();
cust.LastName = reader["LastName"].ToString();
cust.Phone = reader["Phone"].ToString();
cust.City = reader["City"].ToString();
customers.Add(cust);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(customers);
}
public static bool DeleteCustomer(Customer cust)
{
bool success = false;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create DELETE command
string deleteStatement =
"DELETE FROM Customers " +
"WHERE CustomerID = @CustomerID " + // needed for identification
"AND FirstName = @FirstName " + // the rest - for optimistic concurrency
"AND LastName = @LastName " +
"AND Phone = @Phone " +
"AND City = @City ";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@CustomerID", cust.ID);
cmd.Parameters.AddWithValue("@FirstName", cust.FirstName);
cmd.Parameters.AddWithValue("@LastName", cust.LastName);
cmd.Parameters.AddWithValue("@Phone", cust.Phone);
cmd.Parameters.AddWithValue("@City", cust.City);
try
{
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
//get customer
//check if login is successfull
//return customer ID if exist, otherwise, return -1
//[DataObjectMethod(DataObjectMethodType.Select)]
public static int getCustomer(string userName, string password)
{
int id = -1; //default to negative value
string sql = "SELECT ID " +
"FROM Customer " + "WHERE Username = @uName AND Password = @pwd";
SqlConnection connection = MarinaDB.GetConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@uName", userName);
cmd.Parameters.AddWithValue("@pwd", password);
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build customer object to return
if (reader.Read()) // if there is a customer with this ID
{
Customer cust = new Customer();
//fill data from reader
cust.ID = (int)reader["ID"];
id = cust.ID;
//cust.Name = reader["Name"].ToString();
//cust.Address = reader["Address"].ToString();
//cust.City = reader["City"].ToString();
//cust.State = reader["State"].ToString();
//cust.ZipCode = reader["ZipCode"].ToString();
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(id);
}
