//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(); }