public static Specialty[] GetAll()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM specialties;";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Specialty> output = new List <Specialty> ();
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string name = rdr.GetString(1);
output.Add(new Specialty(name, id));
}
DB.Close(conn);
return(output.ToArray());
}
public static void ClearAllStylists()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM stylists;";
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public void DeleteClient()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM clients WHERE Id = @clientId;";
cmd.Parameters.AddWithValue("@clientId", Id);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public static void ResetAllByStylistId(int stylistId)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE clients SET stylist_id = 0 WHERE stylist_id = @stylistId;";
cmd.Parameters.AddWithValue("@stylistId", stylistId);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public static void DeleteAll()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM clients;";
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public static int CreateSpecialty(string name)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO specialties (name) VALUES (@name);";
cmd.Parameters.AddWithValue("@name", name);
cmd.ExecuteNonQuery();
DB.Close(conn);
return((int)cmd.LastInsertedId);
}
public static void AddStylist(int stylistId, int specialtyId)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO specialties_stylists (specialty_id, stylist_id) VALUES (@specialtyId, @stylistId);";
cmd.Parameters.AddWithValue("@specialtyId", specialtyId);
cmd.Parameters.AddWithValue("@stylistId", stylistId);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public void DeleteStylist()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM stylists WHERE id = @stylistId;";
MySqlParameter description = new MySqlParameter();
cmd.Parameters.AddWithValue("@stylistId", this.Id);
cmd.ExecuteNonQuery();
DB.Close(conn);
Client.ResetAllByStylistId(this.Id);
}
public void EditClientInfo(string firstName, string lastName, string phoneNumber)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE clients SET first_name = @firstName, last_name = @lastName, phone = @phoneNumber WHERE id = @clientId;";
cmd.Parameters.AddWithValue("@clientId", Id);
cmd.Parameters.AddWithValue("@firstName", firstName);
cmd.Parameters.AddWithValue("@lastName", lastName);
cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public static int CreateStylist(string firstName, string lastName)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO stylists (first_name, last_Name) VALUES (@firstName, @lastName);";
MySqlParameter description = new MySqlParameter();
cmd.Parameters.AddWithValue("@firstName", firstName);
cmd.Parameters.AddWithValue("@lastName", lastName);
cmd.ExecuteNonQuery();
DB.Close(conn);
return((int)cmd.LastInsertedId);
}
public void EditName(string firstName, string lastName)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE stylists SET first_name = @firstName, last_name = @lastName WHERE id = @stylistId;";
MySqlParameter description = new MySqlParameter();
cmd.Parameters.AddWithValue("@stylistId", Id);
cmd.Parameters.AddWithValue("@firstName", firstName);
cmd.Parameters.AddWithValue("@lastName", lastName);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public static Specialty GetSpecialty(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM specialties WHERE id = @id";
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
rdr.Read();
Specialty foundSpecialty = new Specialty(rdr.GetString(1));
foundSpecialty.Id = rdr.GetInt32(0);
DB.Close(conn);
return(foundSpecialty);
}
public void AddSpecialty(Specialty specialty)
{
if (specialty.GetID() == -1 || _id == -1)
{
throw new Exception("Can't add a specialty unless both objects are saved;");
}
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = $"INSERT INTO stylists_specialties (stylist_id, specialty_id) VALUES ({_id}, {specialty.GetID()});";
cmd.ExecuteNonQuery();
DB.Close(conn);
}
public void Delete()
{
if (_id == -1)
{
throw new Exception("Can't delete a client that hasn't been saved.");
}
else
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = $"DELETE FROM clients WHERE id = {_id};";
cmd.ExecuteNonQuery();
DB.Close(conn);
_id = -1;
}
}
public static Stylist GetStylist(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM stylists WHERE id = @id";
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
rdr.Read();
Stylist foundStylist = new Stylist();
foundStylist.Id = rdr.GetInt32(0);
foundStylist.FirstName = rdr.GetString(1);
foundStylist.LastName = rdr.GetString(2);
DB.Close(conn);
return(foundStylist);
}
public static List <Specialty> GetAll()
{
List <Specialty> specialties = new List <Specialty> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM specialties;";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Specialty newSpecialty = new Specialty(rdr.GetString(1));
newSpecialty.Id = rdr.GetInt32(0);
specialties.Add(newSpecialty);
}
DB.Close(conn);
return(specialties);
}
public static Client GetClient(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM clients WHERE id = @id";
cmd.Parameters.AddWithValue("@id", id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
rdr.Read();
Client foundClient = new Client(rdr.GetInt32(1));
foundClient.Id = rdr.GetInt32(0);
foundClient.FirstName = rdr.GetString(2);
foundClient.LastName = rdr.GetString(3);
foundClient.Phone = rdr.GetString(4);
DB.Close(conn);
return(foundClient);
}
public static List <Specialty> GetAllByStylist(int stylistId)
{
List <Specialty> specialties = new List <Specialty> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT DISTINCT specialties.* FROM stylists JOIN specialties_stylists ON (stylists.id = specialties_stylists.stylist_id) JOIN specialties ON (specialties_stylists.specialty_id = specialties.id) WHERE stylist_id = @stylistId;";
cmd.Parameters.AddWithValue("@stylistId", stylistId);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Specialty newSpecialty = new Specialty(rdr.GetString(1));
newSpecialty.Id = rdr.GetInt32(0);
specialties.Add(newSpecialty);
}
DB.Close(conn);
return(specialties);
}
public static List <Stylist> GetAll()
{
List <Stylist> stylists = new List <Stylist> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM stylists;";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Stylist newStylist = new Stylist();
newStylist.Id = rdr.GetInt32(0);
newStylist.FirstName = rdr.GetString(1);
newStylist.LastName = rdr.GetString(2);
stylists.Add(newStylist);
}
DB.Close(conn);
return(stylists);
}
public static List <Client> GetAll()
{
List <Client> clients = new List <Client> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM clients;";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Client newClient = new Client(rdr.GetInt32(1));
newClient.Id = rdr.GetInt32(0);
newClient.FirstName = rdr.GetString(2);
newClient.LastName = rdr.GetString(3);
clients.Add(newClient);
}
DB.Close(conn);
return(clients);
}
public static Specialty GetByID(int searchID)
{
Specialty result = null;
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = $"SELECT * FROM specialties WHERE (id = {searchID});";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string name = rdr.GetString(1);
result = new Specialty(name, id);
}
DB.Close(conn);
return(result);
}
public void SetName(string newName)
{
if (_id == -1)
{
_name = newName;
}
else
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = $"UPDATE stylists SET name = @Name WHERE id = {_id};";
MySqlParameter insertName = new MySqlParameter();
insertName.ParameterName = "@Name";
insertName.Value = newName;
cmd.Parameters.Add(insertName);
cmd.ExecuteNonQuery();
DB.Close(conn);
}
}
public static List <Client> GetAllByStylist(int stylistId)
{
List <Client> clients = new List <Client> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM clients WHERE stylist_id = @stylistId;";
cmd.Parameters.AddWithValue("@stylistId", stylistId);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Client newClient = new Client(rdr.GetInt32(1));
newClient.Id = rdr.GetInt32(0);
newClient.FirstName = rdr.GetString(2);
newClient.LastName = rdr.GetString(3);
clients.Add(newClient);
}
DB.Close(conn);
return(clients);
}
public static List <Stylist> GetAllBySpecialty(int specialtyId)
{
List <Stylist> stylists = new List <Stylist> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT DISTINCT stylists.* FROM specialties JOIN specialties_stylists ON (specialties.id = specialties_stylists.specialty_id) JOIN stylists ON (specialties_stylists.stylist_id = stylists.id) WHERE specialty_id = @specialtyId;";
cmd.Parameters.AddWithValue("@specialtyId", specialtyId);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
Stylist newStylist = new Stylist();
newStylist.Id = rdr.GetInt32(0);
newStylist.FirstName = rdr.GetString(1);
newStylist.LastName = rdr.GetString(2);
stylists.Add(newStylist);
}
DB.Close(conn);
return(stylists);
}
