public void Save()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO bands (name) OUTPUT INSERTED.id VALUES (@Name);", conn);
cmd.Parameters.Add(new SqlParameter("@Name", this.GetName()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
this._id = rdr.GetInt32(0);
}
DB.CloseSqlConnection(rdr, conn);
}
public void UpdateName(string newName)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE venues SET name = @NewName OUTPUT INSERTED.name WHERE id=@VenueId;", conn);
cmd.Parameters.Add(new SqlParameter("@NewName", newName));
cmd.Parameters.Add(new SqlParameter("@VenueId", this.GetId()));
SqlDataReader rdr = cmd.ExecuteReader();
//can't i just set _name = newName ?
while (rdr.Read())
{
this._name = rdr.GetString(0);
}
DB.CloseSqlConnection(rdr, conn);
}
public static List <Band> GetAll()
{
List <Band> allBands = new List <Band> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM bands", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int bandId = rdr.GetInt32(0);
string bandName = rdr.GetString(1);
Band newBand = new Band(bandName, bandId);
allBands.Add(newBand);
}
DB.CloseSqlConnection(rdr, conn);
return(allBands);
}
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);
}
public void UpdateBand(Band OldBand, Band newBand)
{
int OldBandId = OldBand.GetId();
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE bands_venues SET band_id = @NewBandId WHERE band_id = @OldBandId AND venue_id = @VenueId;", conn);
SqlParameter venueParameter = new SqlParameter("@VenueId", this.GetId());
SqlParameter oldBandParameter = new SqlParameter("@OldBandId", OldBandId);
SqlParameter newBandParameter = new SqlParameter("@NewBandId", newBand.GetId());
cmd.Parameters.Add(venueParameter);
cmd.Parameters.Add(oldBandParameter);
cmd.Parameters.Add(newBandParameter);
SqlDataReader rdr = null;
cmd.ExecuteNonQuery();
DB.CloseSqlConnection(rdr, conn);
}
public void UpdateVenue(Venue OldVenue, Venue newVenue)
{
int OldVenueId = OldVenue.GetId();
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE bands_venues SET venue_id = @NewVenueId WHERE venue_id = @OldVenueId AND band_id = @BandId;", conn);
SqlParameter bandParameter = new SqlParameter("@BandId", this.GetId());
SqlParameter oldVenueParameter = new SqlParameter("@OldVenueId", OldVenueId);
SqlParameter newVenueParameter = new SqlParameter("@NewVenueId", newVenue.GetId());
cmd.Parameters.Add(bandParameter);
cmd.Parameters.Add(oldVenueParameter);
cmd.Parameters.Add(newVenueParameter);
SqlDataReader rdr = null;
cmd.ExecuteNonQuery();
DB.CloseSqlConnection(rdr, conn);
}