public static IEnumerable <MembersByVoicePartVM> GetMembersByVoicePart(string strVoicePart) { List <MembersByVoicePartVM> retval = new List <MembersByVoicePartVM>(); // create and open a connection NpgsqlConnection conn = DatabaseConnection.GetConnection(); conn.Open(); // Define a query string query = "SELECT m.\"strName\", COUNT(m.\"strName\")" + " FROM members m, casts c" + " WHERE m.\"intMemberID\" = c.\"intMemberID\"" + " AND c.\"strVoicePart\" = '" + strVoicePart + "'" + " GROUP BY m.\"strName\""; NpgsqlCommand cmd = new NpgsqlCommand(query, conn); // Execute a query NpgsqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { MembersByVoicePartVM tmpMemberByVoicePart = new MembersByVoicePartVM { StrName = dr["strName"].ToString(), IntCount = Convert.ToInt32(dr["count"]) }; retval.Add(tmpMemberByVoicePart); } conn.Close(); return(retval); }
public ActionResult GetMemberByVoicePart(string strVoicePart) { MembersByVoicePartVM model = new MembersByVoicePartVM() { LstAllVoiceParts = AnalyticsDAL.GetPossibleVoiceParts(), LstMembersByVoicePart = AnalyticsDAL.GetMembersByVoicePart(strVoicePart), StrVoicePart = strVoicePart }; return(PartialView("AnalyticsPartials/_MembersByVoicePart", model)); }