//string username = UserName.Text;
public static Customer GetCustomer(int ID)
{
SqlConnection connection = MarinaDB.GetConnection();
Customer s = new Customer();
try
{
string sql = "SELECT ID, FirstName, LastName, Phone, City, UserName, Password" +
" FROM Customer " +
" WHERE ID =" + ID;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
s.FirstName = reader["FirstName"].ToString();
s.LastName = reader["LastName"].ToString();
s.Phone = Convert.ToInt32(reader["Phone"].ToString());
s.City = reader["City"].ToString();
s.UserName = reader["UserName"].ToString();
s.Password = reader["Password"].ToString();
s.ID = Convert.ToInt32(reader["ID"].ToString());
}
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Something went wrong while trying to connect to your database");
}
finally { connection.Close(); }
return(s);
}
public static Customer GetCustomer(string un)
{
SqlConnection connection = MarinaDB.GetConnection();
Customer s = new Customer();
try
{
string sql = "SELECT ID, FirstName, LastName, Phone, City, " +
" UserName, PassWord" +
" FROM Customer WHERE UserName='******'";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
s.ID = Convert.ToInt32(reader["ID"].ToString());
s.FirstName = reader["FirstName"].ToString();
s.LastName = reader["LastName"].ToString();
s.Phone = reader["Phone"].ToString();
s.City = reader["City"].ToString();
s.UserName = reader["UserName"].ToString();
s.PassWord = reader["PassWord"].ToString();
}
}
catch (Exception ex)
{
;
}
finally
{
connection.Close();
}
return(s);
}
public static bool AddCustomer(string FirstName, string LastName,
string Phone, string City, string UserName, string Password)
{
if (FirstName == null || LastName == null || Phone == null ||
City == null || UserName == null || Password == null)
{
return(false);
}
string sql = "INSERT INTO Customer" +
" (FirstName, LastName, Phone, City, UserName, Password) " +
" VALUES " +
"(@FirstName, @LastName, @Phone, @City, @UserName, @Password)";
SqlConnection connection = MarinaDB.GetConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@FirstName", FirstName);
command.Parameters.AddWithValue("@LastName", LastName);
command.Parameters.AddWithValue("@Phone", Phone);
command.Parameters.AddWithValue("@City", City);
command.Parameters.AddWithValue("@UserName", UserName);
// Hash password 20190517 to UPPERCASE hash code
string pwHash = Md5Encrypt32(Password);
command.Parameters.AddWithValue("@Password", pwHash);
if (command.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool UpdateCustomer(int ID, string FirstName, string LastName,
string Phone, string City)
{
if (FirstName == null || LastName == null || Phone == null ||
City == null)
{
return(false);
}
string sql = "UPDATE Customer" +
" Set FirstName=@FirstName, LastName=@LastName, " +
" Phone=@Phone, City=@City " +
" WHERE ID =" + ID;
SqlConnection connection = MarinaDB.GetConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@FirstName", FirstName);
command.Parameters.AddWithValue("@LastName", LastName);
command.Parameters.AddWithValue("@Phone", Phone);
command.Parameters.AddWithValue("@City", City);
if (command.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
public static List <Lease> GetLeasesByCustomerID(int ID)
{
SqlConnection connection = MarinaDB.GetConnection();
List <Lease> result = new List <Lease>();
try
{
string sql = "SELECT ID, SlipID, CustomerID" +
" FROM Lease WHERE CustomerID=" + ID;
SqlCommand command = new SqlCommand(sql, connection);
// return multiple records
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Lease s = new Lease
{
ID = Convert.ToInt32(reader["ID"].ToString()),
SlipID = Convert.ToInt32(reader["SlipID"].ToString()),
CustomerID = Convert.ToInt32(reader["CustomerID"].ToString())
};
result.Add(s);
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
return(result);
}
public static List <Slip> GetSlips(int ID)
{
SqlConnection connection = MarinaDB.GetConnection();
List <Slip> results = new List <Slip>();
try
{
string sql = "SELECT ID, Width, Length, DockID FROM Slip" +
" WHERE DockID=" + ID;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Slip s = new Slip();
s.ID = Convert.ToInt32(reader["ID"].ToString());
s.Width = Convert.ToInt32(reader["Width"].ToString());
s.Length = Convert.ToInt32(reader["Length"].ToString());
s.DockID = Convert.ToInt32(reader["DockID"].ToString());
results.Add(s);
}
}
catch { }
finally
{
connection.Close();
}
return(results);
}
public static List <Dock> GetDocks()
{
SqlConnection connection = MarinaDB.GetConnection();
List <Dock> results = new List <Dock>();
try
{
string sql = "SELECT ID, Name, WaterService, ElectricalService FROM Dock;";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader =
command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Dock s = new Dock
{
ID = Convert.ToInt32(reader["ID"].ToString()),
Name = reader["Name"].ToString(),
WaterService = reader["WaterService"].ToString(),
ElectricalService = reader["ElectricalService"].ToString()
};
/*if (Convert.ToInt32(reader["WaterService"].ToString())==1)
* {
* s.WaterService = "Yes";
* }
* else
* {
* s.WaterService = "No";
* }
* if (Convert.ToInt32(reader["WaterService"].ToString())==1)
* {
* s.ElectricalService = "Yes";
* }
* else
* {
* s.ElectricalService = "No";
* }
*/
results.Add(s);
}
}
catch { }
finally
{
connection.Close();
}
return(results);
}
public static bool AddLease(int slipid, int customerid)
{
string sql = "INSERT INTO Lease" +
" (SlipId, CustomerID) " +
" VALUES " +
"(@slipid, @customerid)";
SqlConnection connection = MarinaDB.GetConnection();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@SlipId", slipid);
command.Parameters.AddWithValue("@CustomerID", customerid);
if (command.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}