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));
}