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