public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO bands(name) VALUES(@name);";
MySqlParameter nameParameter = new MySqlParameter();
nameParameter.ParameterName = "@name";
nameParameter.Value = _name;
cmd.Parameters.Add(nameParameter);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static Band Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM bands WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int bandId = 0;
string bandName = "";
while (rdr.Read())
{
bandId = rdr.GetInt32(0);
bandName = rdr.GetString(1);
}
// Constructor below no longer includes a itemCategoryId parameter:
Band newBand = new Band(bandName, bandId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newBand);
}
public void Delete()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM venues WHERE id = @thisId; DELETE FROM bands_venues WHERE venue_id = @thisId;";
MySqlParameter idParameter = new MySqlParameter();
idParameter.ParameterName = "@thisId";
idParameter.Value = _id;
cmd.Parameters.Add(idParameter);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO venues (name, address, capacity) VALUES (@name, @address, @capacity);";
MySqlParameter name = new MySqlParameter();
name.ParameterName = "@name";
name.Value = _name;
cmd.Parameters.Add(name);
MySqlParameter address = new MySqlParameter();
address.ParameterName = "@address";
address.Value = _address;
cmd.Parameters.Add(address);
MySqlParameter capacity = new MySqlParameter();
capacity.ParameterName = "@capacity";
capacity.Value = _capacity;
cmd.Parameters.Add(capacity);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public List <Venue> GetVenues()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT venues.* FROM bands
JOIN venues_bands ON (bands.id=venues_bands.band_id)
JOIN venues ON (venues_bands.venue_id=venues.id)
WHERE bands.id=@BandId;";
cmd.Parameters.Add(new MySqlParameter("@BandId", _id));
var rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Venue> venues = new List <Venue> {
};
while (rdr.Read())
{
int venueId = rdr.GetInt32(0);
string venueName = rdr.GetString(1);
string venueDescription = rdr.GetString(2);
string venueLocation = rdr.GetString(3);
int venueCapacity = rdr.GetInt32(4);
Venue newVenue = new Venue(venueName, venueDescription, venueLocation, venueCapacity, venueId);
venues.Add(newVenue);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(venues);
}
//Saves Venue to venues datatable
public void SaveVenue()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO venues (venue_name) VALUES (@Venue);";
MySqlParameter venueName = new MySqlParameter();
venueName.ParameterName = "@Venue";
venueName.Value = this._venueName;
cmd.Parameters.Add(venueName);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static Band Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM bands WHERE id = @thisId;";
MySqlParameter band = new MySqlParameter();
band.ParameterName = "@thisid";
band.Value = id;
cmd.Parameters.Add(band);
int bandId = 0;
string bandName = "";
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
bandId = rdr.GetInt32(0);
bandName = rdr.GetString(1);
}
Band foundBand = new Band(bandName, bandId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundBand);
}
public List <Venue> GetVenues()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT venues.* FROM bands
JOIN bands_venues ON (bands.id = bands_venues.band_id)
JOIN venues ON (bands_venues.venue_id = venues.id)
WHERE bands.id = @BandId;";
MySqlParameter bandIdParameter = new MySqlParameter();
bandIdParameter.ParameterName = "@BandId";
bandIdParameter.Value = Id;
cmd.Parameters.Add(bandIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Venue> venues = new List <Venue> {
};
while (rdr.Read())
{
int venueId = rdr.GetInt32(0);
string venueName = rdr.GetString(1);
Venue newVenue = new Venue(venueName, venueId);
venues.Add(newVenue);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(venues);
}
public List <Band> GetBands()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT bands.* FROM venue
JOIN bands_venues ON ( venue.id = bands_venues.venue_id)
JOIN bands ON ( bands_venues.band_id = bands.id) where venue.id = @venueId;";
MySqlParameter venueId = new MySqlParameter();
venueId.ParameterName = "@venueId";
venueId.Value = _id;
cmd.Parameters.Add(venueId);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Band> Bands = new List <Band> {
};
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string name = rdr.GetString(1);
string song = rdr.GetString(2);
Band MyBand = new Band(name, song, id);
Bands.Add(MyBand);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(Bands);
}
public static Venue Find(int venue_id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM venues WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = venue_id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int venueId = 0;
string venueName = "";
string venueLocation = "";
while (rdr.Read())
{
venueId = rdr.GetInt32(0);
venueName = rdr.GetString(1);
}
Venue newVenue = new Venue(venueName, venueLocation, venueId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newVenue);
}
public void UpdateVenue(string VenueName, string VenueLocation)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE venues SET name= @newName, location = @newLocation WHERE id = @searchId;";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = _venue_id;
cmd.Parameters.Add(searchId);
MySqlParameter name = new MySqlParameter();
name.ParameterName = "@newName";
name.Value = VenueName;
cmd.Parameters.Add(name);
MySqlParameter location = new MySqlParameter();
location.ParameterName = "@newLocation";
location.Value = VenueLocation;
cmd.Parameters.Add(location);
cmd.ExecuteNonQuery();
_name = VenueName;
_location = VenueLocation;
if (conn != null)
{
conn.Dispose();
}
}