// methods to interact with the Band class
// a method to add a new link between a band and venue instance
public void AddBand(Band newBand)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO events (venue_id, band_id) VALUES (@VenueId, @BandId);", conn);
SqlParameter venueIdParameter = new SqlParameter();
venueIdParameter.ParameterName = "@VenueId";
venueIdParameter.Value = this.GetId();
cmd.Parameters.Add(venueIdParameter);
SqlParameter bandIdParameter = new SqlParameter();
bandIdParameter.ParameterName = "@BandId";
bandIdParameter.Value = newBand.GetId();
cmd.Parameters.Add(bandIdParameter);
cmd.ExecuteNonQuery();
if (conn != null)
{
conn.Close();
}
}
public List <Band> GetBands()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT bands.* FROM venues JOIN bands_venues ON (venues.id = bands_venues.venue_id) JOIN bands ON (bands_venues.band_id = bands.id) WHERE venues.id = @VenueId;", conn);
SqlParameter venueIdParameter = new SqlParameter();
venueIdParameter.ParameterName = "@VenueId";
venueIdParameter.Value = this.GetId();
cmd.Parameters.Add(venueIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
List <Band> bands = new List <Band> {
};
while (rdr.Read())
{
int bandId = rdr.GetInt32(0);
string bandName = rdr.GetString(1);
Band foundBand = new Band(bandName, bandId);
bands.Add(foundBand);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(bands);
}
public static Venue Find(int id)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM venues WHERE id = @VenueId;", conn);
SqlParameter venueIdParameter = new SqlParameter();
venueIdParameter.ParameterName = "@VenueId";
venueIdParameter.Value = id.ToString();
cmd.Parameters.Add(venueIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
int foundVenueId = 0;
string foundVenueName = null;
while (rdr.Read())
{
foundVenueId = rdr.GetInt32(0);
foundVenueName = rdr.GetString(1);
}
Venue foundVenue = new Venue(foundVenueName, foundVenueId);
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(foundVenue);
}
public static List <Venue> GetAll()
{
List <Venue> allVenues = new List <Venue> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM venues", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int venueId = rdr.GetInt32(0);
string venueName = rdr.GetString(1);
Venue newVenue = new Venue(venueName, venueId);
allVenues.Add(newVenue);
}
DB.CloseSqlConnection(rdr, conn);
return(allVenues);
}