public Venue AddNewVenue(String venueName, int cityId) { int tempInt = 0; Venue venueAdded = new Venue(); DataSet tempDS = new DataSet(); try { string venueInsert = string.Format(@"INSERT INTO [Venues] (VenueName, Capacity, CityId, Description) VALUES ('{0}', 1000, {1}, '') Select @@Identity", venueName, cityId); using (var venueDataAdapter = new SqlDataAdapter(new SqlCommand(venueInsert, new SqlConnection(constructVenuesConnectString())))) { venueDataAdapter.Fill(tempDS); // capture integer value of newly added venue if (tempDS.Tables.Count > 0 && tempDS.Tables[0].Rows.Count > 0 && tempDS.Tables[0].Rows[0][0] != DBNull.Value) Int32.TryParse(tempDS.Tables[0].Rows[0][0].ToString(), out tempInt); // if venue was added, continue, else, return if (tempInt > 0) venueAdded.VenueId = tempInt; else return venueAdded; } string seatSectionInsert = string.Format(@"INSERT INTO [SeatSection] (SeatCount, VenueId, Description) VALUES (100, {0}, '') Select @@Identity", venueAdded.VenueId); //Add SeatSections for this venue using (var seatDataAdapter = new SqlDataAdapter(new SqlCommand(seatSectionInsert, new SqlConnection(constructVenuesConnectString())))) for (int i = 1; i <= 10; i++) seatDataAdapter.Fill(tempDS); } catch { venueAdded = null; } LogAction("Added new venue - " + venueName); return venueAdded; }
private Venue populateSingleVenueFromDbReader(SqlDataReader dbReader) { var venueCountry = new Country { CountryName = dbReader.GetString(dbReader.GetOrdinal(@"countryName")) }; var venueState = new State { StateName = dbReader.GetString(dbReader.GetOrdinal(@"stateName")), Description = (dbReader.IsDBNull(dbReader.GetOrdinal(@"stateDescription"))) ? "": dbReader.GetString(dbReader.GetOrdinal(@"stateDescription")), Country = venueCountry }; var venueCity = new City { CityName = dbReader.GetString(dbReader.GetOrdinal(@"cityName")), CityId = dbReader.GetInt32(dbReader.GetOrdinal("cityId")), Description = (dbReader.IsDBNull(dbReader.GetOrdinal(@"cityDescription"))) ? "" : dbReader.GetString(dbReader.GetOrdinal(@"cityDescription")), State = venueState }; var venueToReturn = new Venue { VenueName = dbReader.GetString(dbReader.GetOrdinal(@"venueName")), Capacity = dbReader.GetInt32(dbReader.GetOrdinal(@"venueCapacity")), Description = (dbReader.IsDBNull(dbReader.GetOrdinal(@"venueDescription"))) ? "" : dbReader.GetString(dbReader.GetOrdinal(@"venueDescription")), VenueCity = venueCity, VenueId = dbReader.GetInt32(dbReader.GetOrdinal(@"venueId")) }; return venueToReturn; }