//string username = UserName.Text;
public static Customer GetCustomer(int ID)
{
SqlConnection connection = MarinaDBConnnection.GetConnection();
Customer customer = 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())
{
customer.FirstName = reader["FirstName"].ToString();
customer.LastName = reader["LastName"].ToString();
customer.Phone = Convert.ToInt32(reader["Phone"].ToString());
customer.City = reader["City"].ToString();
customer.UserName = reader["UserName"].ToString();
customer.Password = reader["Password"].ToString();
customer.ID = Convert.ToInt32(reader["ID"].ToString());
}
}
catch (Exception excp)
{
MessageBox.Show($"Something went wrong while trying to connect to your database, {excp}");
}
finally { connection.Close(); }
return(customer);
}
public static List <string> getSlipsByCustomer(int ID)
{
List <string> leasedSlips = new List <string>();
SqlConnection connection = MarinaDBConnnection.GetConnection();
try
{
string sql = "SELECT d.Name as DockName, d.ElectricalService + ',' + d.WaterService as Services, l.SlipID as Slip#, CONCAT_WS(' x ', s.Length, s.Width) as SlipDimensions(LxW);";
sql += " FROM Lease l";
sql += "JOIN Slip s on s.ID = l.SlipID";
sql += "JOIN DOCK d on s.DockID = d.ID ";
sql += "JOIN Customer c on c.ID = l.CustomerID";
sql += "WHERE l.CustomerID = " + ID;
sql += "ORDER BY l.ID DESC";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
leasedSlips.Add(reader["DockName"].ToString()); //0
leasedSlips.Add(reader["Services"].ToString()); //1
leasedSlips.Add(reader["Slip#"].ToString()); //2
leasedSlips.Add(reader["SlipDimensions(LxW)"].ToString()); //3
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
connection.Close();
}
return(leasedSlips);
}
public static string getCustomerInfoBy(int ID)
{
String customerName = "";
SqlConnection connection = MarinaDBConnnection.GetConnection();
try
{
string sql = "SELECT FirstName + ', ' + LastName as FullName ";
sql += "FROM Customer WHERE ID = " + ID;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
customerName = reader["FullName"].ToString();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
connection.Close();
}
return(customerName);
}
public static List <string> getSlipInfoBy(int ID)
{
List <string> slipInfo = new List <string>();
SqlConnection connection = MarinaDBConnnection.GetConnection();
try
{
string sql = "SELECT s.ID, s.Length, s.Width, d.Name, d.ElectricalService, d.WaterService ";
sql += "FROM Slip s ";
sql += "JOIN DOCK d on s.DockID = d.ID ";
sql += "WHERE s.ID = " + ID;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
slipInfo.Add(reader["ID"].ToString()); //0
slipInfo.Add(reader["Length"].ToString()); //1
slipInfo.Add(reader["Width"].ToString()); //2
slipInfo.Add(reader["Name"].ToString()); //3
slipInfo.Add(reader["ElectricalService"].ToString()); //4
slipInfo.Add(reader["WaterService"].ToString()); //5
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
connection.Close();
}
return(slipInfo);
}
public static bool isCustomerExist(string username)
{
SqlConnection connection = MarinaDBConnnection.GetConnection();
string sql = "SELECT * FROM Customer WHERE UserName = @UserName";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@UserName", username);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(reader.HasRows);
}
public static void AddCustomer(string FirstName, string LastName, string Phone, string City, string UserName, string Password)
{
string sql = "INSERT INTO Customer" +
" (FirstName, LastName, Phone, City, UserName, Password) " +
" VALUES " +
"(@FirstName, @LastName, @Phone, @City, @UserName, @Password)";
SqlConnection connection = MarinaDBConnnection.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);
command.Parameters.AddWithValue("@Password", Password);
command.ExecuteNonQuery();
}