public static List <Dock> GetDocks()
{
List <Dock> docks = new List <Dock>(); // Empty list
SqlConnection connection = MarinaDB.GetConnection();
// Query to get all dock info
string query = "SELECT * " +
"FROM Dock ; ";
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
// Add every found dock to list
docks.Add(new Dock(
(int)reader["ID"],
reader["Name"].ToString(),
((bool)reader["WaterService"]),
((bool)reader["ElectricalService"])
));
}
reader.Close();
return(docks);
}
public static List <Slip> GetAllAvailableSlips()
{
List <Slip> slips = new List <Slip>(); // Empty list
SqlConnection connection = MarinaDB.GetConnection();
// Gets all slips that are available for all docks
string query = "SELECT s.ID AS SlipId, Width, Length, d.ID AS DockId, Name, WaterService, ElectricalService " +
"FROM Slip s JOIN Dock d " +
"ON s.DockId = d.ID " +
"Where ID NOT IN (SELECT DISTINCT ID FROM Lease) ; ";
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
// Adds new slip to list
slips.Add(new Slip(
(int)reader["SlipId"],
(int)reader["Width"],
(int)reader["Length"],
new Dock( // Adds dock to slip
(int)reader["DockId"],
reader["Name"].ToString(),
((int)reader["WaterService"]) == 1,
((int)reader["ElectricalService"]) == 1
)));
}
reader.Close();
return(slips);
}
public static List <Lease> GetAvailableLeasesByCustomer(int CustomerId)
{
List <Lease> leases = new List <Lease>(); // Empty list
SqlConnection connection = MarinaDB.GetConnection();
// Gets all information to build the leases. Need to build a slip, dock, and customer to build a lease object
string query = "SELECT l.ID AS LeaseId, s.ID AS SlipId, s.Width, s.Length, d.ID AS DockId, d.Name, d.WaterService, d.ElectricalService, c.ID AS CustomerId, c.FirstName, c.LastName, c.Phone, c.City " +
"FROM Lease l JOIN Slip s " +
"ON l.SlipId = s.ID " +
"JOIN Customer c " +
"ON l.CustomerID = c.ID " +
"JOIN Dock d " +
"ON s.DockId = d.ID " +
"WHERE c.ID = @CustomerId ; ";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
// Create dock (for slip) , slip, and customer assigned to lease
Dock dock = new Dock(
(int)reader["DockId"],
reader["Name"].ToString(),
((int)reader["WaterService"]) == 1,
((int)reader["ElectricalService"]) == 1);
Slip slip = new Slip(
(int)reader["SlipId"],
(int)reader["Width"],
(int)reader["Length"],
dock);
Customer newcustomer = new Customer(
(int)reader["CustomerId"],
reader["FirstName"].ToString(),
reader["LastName"].ToString(),
reader["Phone"].ToString(),
reader["City"].ToString());
// Add new lease to list
leases.Add(new Lease((int)reader["LeaseId"], slip, newcustomer));
}
reader.Close();
return(leases);
}
public static void LeaseSlip(int customerId, int slipId)
{
SqlConnection connection = MarinaDB.GetConnection();
connection.Open();
string query = "INSERT INTO Lease (SlipId, CustomerId) VALUES " +
"(@SlipId, @CustomerId) ; ";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@SlipId", slipId);
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.ExecuteNonQuery();
}
public static List <Slip> GetAvailableSlipsByDock(int DockId)
{
List <Slip> slips = new List <Slip>(); // Empty list
SqlConnection connection = MarinaDB.GetConnection();
// Selects all available slips assigned to dock
string query = "SELECT s.ID AS SlipId, Width, Length, d.ID AS DockId, Name, WaterService, ElectricalService " +
"FROM Slip s JOIN Dock d " +
"ON s.DockId = d.ID " +
"WHERE s.DockID = @DockId " +
"AND s.ID NOT IN (SELECT DISTINCT SlipID FROM Lease) ; ";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@DockId", DockId);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
// Creates dock object for slip
Dock dock = new Dock(
(int)reader["DockId"],
reader["Name"].ToString(),
((bool)reader["WaterService"]),
((bool)reader["ElectricalService"])
);
// Adds slip to list
slips.Add(new Slip(
(int)reader["SlipId"],
(int)reader["Width"],
(int)reader["Length"],
dock
));
}
reader.Close();
return(slips);
}
public static List <Slip> GetLeasesByCustomerId(int CustomerId)
{
List <Slip> slips = new List <Slip>(); // EMpty list
if (CustomerId == -1) // If customer id is invalid, return empty list
{
return(slips);
}
SqlConnection connection = MarinaDB.GetConnection();
// Gets all slips associated with customer (through lease table)
string query = "SELECT s.ID AS SlipId, Width, Length " +
"FROM Lease l JOIN Slip s " +
"ON l.SlipId = s.ID " +
"WHERE l.CustomerId = @CustomerId ; ";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
// Populates list
slips.Add(new Slip(
(int)reader["SlipId"],
(int)reader["Width"],
(int)reader["Length"]
));
}
reader.Close();
return(slips);
}
public static bool RegisterCustomer(Customer customer, String password)
{
// Allows conversion of first and last name to title case
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
// Makes first and last name title case so we can check with DB properly
customer.FirstName = textInfo.ToTitleCase(customer.FirstName);
customer.LastName = textInfo.ToTitleCase(customer.LastName);
// Generates salt for new customer
byte[] salt;
new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
// Generates hashedpassword from unencrypted password and salt
var pbkdf2 = new Rfc2898DeriveBytes(password, salt, 10000);
byte[] hash = pbkdf2.GetBytes(20);
// Combines salt with hashed password
byte[] hashBytes = new byte[36];
Array.Copy(salt, 0, hashBytes, 0, 16);
Array.Copy(hash, 0, hashBytes, 16, 20);
// Convert to string for DB
string savedPasswordHash = Convert.ToBase64String(hashBytes);
// Gets connection
SqlConnection connection = MarinaDB.GetConnection();
// Test if there is a match for First and Last name in DB. If there is, see if there is no assigned username
string testUsernameExists = "SELECT Username FROM Customer " +
"WHERE Username=@Username ; ";
SqlCommand cmdUser = new SqlCommand(testUsernameExists, connection);
connection.Open();
cmdUser.Parameters.AddWithValue("@Username", customer.Username);
// Run query
SqlDataReader readerUser = cmdUser.ExecuteReader();
if (readerUser.HasRows) // There is a customer with same username so end function
{
readerUser.Close();
connection.Close();
return(false);
}
readerUser.Close();
connection.Close();
// Check if customer first and last name exists in DB
bool registerExistingCustomer = false;
connection = MarinaDB.GetConnection();
// Test if there is a match for First and Last name in DB. If there is, see if there is no assigned username
string testFirstAndLastNameExists = "SELECT Username FROM Customer " +
"WHERE FirstName=@FirstName AND LastName=@LastName AND Username IS NULL ; ";
SqlCommand cmd = new SqlCommand(testFirstAndLastNameExists, connection);
connection.Open();
cmd.Parameters.AddWithValue("@FirstName", customer.FirstName);
cmd.Parameters.AddWithValue("@LastName", customer.LastName);
// Run query
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows) // There is a customer with no username but a first and last name match
{
// Username doesnt exist for customer with first and last name. So update matched record.
registerExistingCustomer = true;
}
reader.Close();
connection.Close();
connection = MarinaDB.GetConnection();
connection.Open();
if (registerExistingCustomer) // Update existing customer with no username
{
string query = "Update Customer SET Phone = @Phone, City = @City, Username = @Username, Password = @Password, Salt = @Salt WHERE " +
"FirstName=@FirstName AND LastName=@LastName ; ";
SqlCommand cmd2 = new SqlCommand(query, connection);
cmd2.Parameters.AddWithValue("@FirstName", customer.FirstName);
cmd2.Parameters.AddWithValue("@LastName", customer.LastName);
cmd2.Parameters.AddWithValue("@Phone", customer.Phone);
cmd2.Parameters.AddWithValue("@City", customer.City);
cmd2.Parameters.AddWithValue("@Username", customer.Username);
cmd2.Parameters.AddWithValue("@Password", savedPasswordHash);
cmd2.Parameters.AddWithValue("@Salt", Convert.ToBase64String(salt));
cmd2.ExecuteNonQuery();
}
else // Register new customer
{
string query = "INSERT INTO Customer (FirstName, LastName, Phone, City, Username, Password, Salt) VALUES " +
"(@FirstName,@LastName,@Phone,@City,@Username,@Password,@Salt) ; ";
SqlCommand cmd2 = new SqlCommand(query, connection);
cmd2.Parameters.AddWithValue("@FirstName", customer.FirstName);
cmd2.Parameters.AddWithValue("@LastName", customer.LastName);
cmd2.Parameters.AddWithValue("@Phone", customer.Phone);
cmd2.Parameters.AddWithValue("@City", customer.City);
cmd2.Parameters.AddWithValue("@Username", customer.Username);
cmd2.Parameters.AddWithValue("@Password", savedPasswordHash);
cmd2.Parameters.AddWithValue("@Salt", Convert.ToBase64String(salt));
cmd2.ExecuteNonQuery();
}
connection.Close();
return(true);
}
/// <summary>
/// Verifies customer login info to gain authentication
/// </summary>
/// <param name="username">Username</param>
/// <param name="password">Unencrypted password</param>
/// <returns></returns>
public static Customer VerifyLogin(String username, String password)
{
// Creates connection
SqlConnection connection = MarinaDB.GetConnection();
// Empty customer
Customer customer;
// Holds password hash and salt from DB
string savedPasswordHash;
byte[] salt;
// Grab all parameters from DB
string query = "SELECT ID, FirstName, LastName, Phone, City, Username, Password, Salt FROM Customer " +
"WHERE Username = @Username ; ";
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
cmd.Parameters.AddWithValue("@Username", username);
// Run query
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
// If username is in DB, retrieve saved password hash and salt and create customer object
if (reader.HasRows)
{
reader.Read();
savedPasswordHash = reader["Password"].ToString();
salt = Convert.FromBase64String(reader["Salt"].ToString());
customer = new Customer(
(int)reader["ID"],
reader["FirstName"].ToString(),
reader["LastName"].ToString(),
reader["Phone"].ToString(),
reader["City"].ToString(),
reader["Username"].ToString()
);
}
else // User doesnt exist, return null
{
return(null);
}
/* Extract the bytes */
byte[] hashBytes = Convert.FromBase64String(savedPasswordHash);
Array.Copy(hashBytes, 0, salt, 0, 16);
/* Compute the hash on the password the user entered */
var pbkdf2 = new Rfc2898DeriveBytes(password, salt, 10000);
byte[] hash = pbkdf2.GetBytes(20);
/* Compare the results */
for (int i = 0; i < 20; i++)
{
if (hashBytes[i + 16] != hash[i])
{
return(null); // If comparison fails, return null
}
}
return(customer); // Returns customer
}