public static List <Lease> GetCustomerLeases()
{
List <Lease> leases = new List <Lease>(); //empty list
Lease ls; //just for reading variables expressed before the commands!
// Dummy variable for the customer ID - We need to use Session["ID"] - Not working so far.
int custID = (int)HttpContext.Current.Session["ID"];
//create the connection
SqlConnection connection = MarinaDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT ID, SlipID, CustomerID " +
"FROM Lease " +
"WHERE CustomerID = " + custID +
" ORDER by ID";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
ls = new Lease();
ls.ID = (int)reader["ID"]; //[] indexer from chapter 13
ls.SlipID = (int)reader["SlipID"];
ls.CustomerID = (int)reader["CustomerID"];
leases.Add(ls);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of states
return(leases);
}
// 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);
}
//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);
}
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);
}
// insert new row to table Customers
// return new CustomerID
public static int AddCustomer(Customer cust)
{
int custID = 0;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatement =
"INSERT INTO Customers(FirstName, LastName, Phone, City) " +
"OUTPUT inserted.CustomerID " +
"VALUES(@FirstName, @LastName, @Phone, @City)";
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);
try
{
connection.Open();
// execute insert command and get inserted ID
custID = (int)cmd.ExecuteScalar();
//cmd.ExecuteNonQuery();
// retrieve generate customer ID to return
//string selectStatement =
// "SELECT IDENT_CURRENT('Customers')";
//SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
//custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
// // (int) does not work in this case
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(custID);
}
public static List <Slip> GetSlips()
{
List <Slip> slips = new List <Slip>(); //empty list
Slip s; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = MarinaDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
"FROM Slip " +
"ORDER by Id";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
s = new Slip();
s.ID = (int)reader["ID"]; //[] indexer from chapter 13
s.Width = (int)(reader["Width"]);
s.Length = (int)(reader["Length"]);
s.DockID = (int)(reader["DockID"]);
s.BookingStatus = (int)(reader["BookingStatus"]);
slips.Add(s);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of slips
return(slips);
}
public static List <Customer> GetCustomer()
{
List <Customer> customers = new List <Customer>(); //empty list
Customer cstmr; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = MarinaDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT CustomerID, FirstName, LastName, Phone, City " +
"FROM Customers " +
"ORDER by LastName";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) // while there are customers
{
cstmr = new Customer();
cstmr.ID = (int)reader["CustomerID"];
cstmr.FirstName = reader["FirstName"].ToString();
cstmr.LastName = reader["LastName"].ToString();
cstmr.Phone = reader["Phone"].ToString();
cstmr.City = reader["City"].ToString();
customers.Add(cstmr);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of states
return(customers);
}
public static List <Dock> GetSelectedDocks(int ID)
{
List <Dock> docks = new List <Dock>(); //empty list
Dock d; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = MarinaDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT ID, Name, WaterService, ElectricalService " +
"FROM Dock " +
"WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@ID", ID);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
d = new Dock();
d.ID = (int)reader["ID"]; //[] indexer from chapter 13
d.Name = reader["Name"].ToString();
d.WaterService = reader["WaterService"].ToString();
d.ElectricalService = reader["ElectricalService"].ToString();
docks.Add(d);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of states
return(docks);
}
public static Slip UpdateSlipStatus(int slipID)
{
Slip slip = null;
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create SELECT command
string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
"FROM Slip " +
"WHERE ID = " + slipID;
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@CustomerID", slipID);
// run the SELECT query
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
{
slip = new Slip();
slip.ID = slipID;
slip.Width = (int)reader["Width"];
slip.Length = (int)reader["Length"];
slip.DockID = (int)reader["DockID"];
slip.BookingStatus = 1;
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(slip);
}
public static bool AddLease(int slipID) // changed Customer names in brackets
{
bool success = false; // did not update
int custID = (int)HttpContext.Current.Session["ID"];
//int custID = 1;
// connection
SqlConnection connection = MarinaDB.GetConnection();
// update command
string insertStatement =
"INSERT INTO Lease(SlipID, CustomerID) " +
"VALUES(@SlipID, @CustomerID)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@SlipID", slipID);
cmd.Parameters.AddWithValue("@CustomerID", custID);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static bool UpdateSlip(int slipID) // changed Customer names in brackets
{
bool success = false; // did not update
// connection
SqlConnection connection = MarinaDB.GetConnection();
// update command
string updateStatement =
"UPDATE Slip SET " +
"BookingStatus = @BookingStatus " +
"WHERE ID = @ID";
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("@BookingStatus", 1);
cmd.Parameters.AddWithValue("@ID", slipID);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
// retrieve customers from given state
public static List <Slip> GetSlipsByDocks(int dockID)
{
List <Slip> slips = new List <Slip>(); // empty list
Slip slip = null; // for reading
// create connection
SqlConnection connection = MarinaDB.GetConnection();
// create SELECT command
string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
"FROM Slip " +
"WHERE DockID = @DockID AND BookingStatus =0";
SqlCommand cmd = new SqlCommand(query, connection);
// supply parameter value
cmd.Parameters.AddWithValue("@DockID", dockID);
// run the SELECT query
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// add customers to the list
while (reader.Read()) // while there are customers
{
slip = new Slip();
slip.ID = (int)reader["ID"];
slip.Width = (int)reader["Width"];
slip.Length = (int)reader["Length"];
slip.DockID = (int)reader["DockID"];
slip.BookingStatus = (int)reader["BookingStatus"];
slips.Add(slip);
}
reader.Close();
return(slips);
}
public static List <Slip> GetAvailableSlipsDoNotUse()
{
List <Slip> slips = new List <Slip>(); //empty list
Slip s; //just for reading variables expressed before the commands!
//create the connection
int selectedDockID;
//We need to know which dock has been selected
System.Web.UI.Page currentPage = HttpContext.Current.Handler as System.Web.UI.Page;
//Find controls of the LeaseSlip.aspx here
DropDownList dropDown = (DropDownList)currentPage.FindControl("ddlLeaseSlipDock");
//Sets the selectedDockID value to the index of the selected item in the dropdownlist
selectedDockID = dropDown.SelectedIndex + 1;
SqlConnection connection = MarinaDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT ID, Width, Length, DockID, BookingStatus " +
"FROM Slip " +
"WHERE (BookingStatus = 0 " +
"AND DockID = " + selectedDockID + ") " +
"ORDER by Id";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
s = new Slip();
s.ID = (int)reader["ID"]; //[] indexer from chapter 13
s.Width = (int)(reader["Width"]);
s.Length = (int)(reader["Length"]);
s.DockID = (int)(reader["DockID"]);
s.BookingStatus = (int)(reader["BookingStatus"]);
slips.Add(s);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of slips
return(slips);
}
