public static IEnumerable <ShowsByInstrumentVM> GetShowsByInstrument(string strSearch) { List <ShowsByInstrumentVM> retval = new List <ShowsByInstrumentVM>(); // create and open a connection NpgsqlConnection conn = DatabaseConnection.GetConnection(); conn.Open(); // Define a query string query = "SELECT DISTINCT s.\"intShowID\", p.\"strInstrument\"" + " FROM pits p, shows s" + " WHERE p.\"intShowID\" = s.\"intShowID\"" + " AND p.\"strInstrument\" ILIKE '%" + strSearch + "%'"; NpgsqlCommand cmd = new NpgsqlCommand(query, conn); // Execute a query NpgsqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ShowsByInstrumentVM tmpShowByInstrument = new ShowsByInstrumentVM { IntShowID = Convert.ToInt32(dr["intShowID"]), StrInstrument = dr["strInstrument"].ToString() }; retval.Add(tmpShowByInstrument); } conn.Close(); return(retval); }
public ActionResult GetShowsByInstrument(string strSearch) { ShowsByInstrumentVM model = new ShowsByInstrumentVM() { LstShowsByInstrument = AnalyticsDAL.GetShowsByInstrument(strSearch), StrSearch = strSearch }; foreach (var showsByInstrument in model.LstShowsByInstrument) { showsByInstrument.Show = ShowsDAL.GetShow(showsByInstrument.IntShowID); } return(PartialView("AnalyticsPartials/_ShowsByInstrument", model)); }