public static List <RsoViewModel> viewRSO()
{
List <RsoViewModel> rsos = new List <RsoViewModel>();
var dbCon = DBConnection.Instance();
if (dbCon.IsConnect())
{
string query = "SELECT rso.*, school.name as schoolName, rso_type.type as type, (SELECT COUNT(Id) FROM rso_members WHERE rso_members.rso_id = rso.id) as count FROM rso JOIN school ON rso.schoolNameId = school.id JOIN rso_type ON rso.typeId = rso_type.id";
var cmd = new MySqlCommand(query, dbCon.Connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RsoViewModel rso = new RsoViewModel();
rso.id = reader.GetInt32(reader.GetOrdinal("Id"));
rso.name = reader.GetString(reader.GetOrdinal("name"));
rso.schoolNameId = reader.GetInt32(reader.GetOrdinal("schoolNameId"));
rso.typeId = reader.GetInt32(reader.GetOrdinal("typeId"));
rso.contactName = reader.GetString(reader.GetOrdinal("contactName"));
rso.contactPhone = reader.GetString(reader.GetOrdinal("contactPhone"));
rso.contactEmail = reader.GetString(reader.GetOrdinal("contactEmail"));
rso.description = reader.GetString(reader.GetOrdinal("description"));
rso.memberId = reader.GetInt32(reader.GetOrdinal("memberId"));
rso.schoolName = reader.GetString(reader.GetOrdinal("schoolName"));
rso.type = reader.GetString(reader.GetOrdinal("type"));
rso.count = reader.GetInt32(reader.GetOrdinal("count"));
rsos.Add(rso);
}
}
dbCon.Close();
return(rsos);
}
public static List <RsoViewModel> rsoTypes()
{
List <RsoViewModel> rsos = new List <RsoViewModel>();
var dbCon = DBConnection.Instance();
if (dbCon.IsConnect())
{
string query = "SELECT * FROM rso_type";
var cmd = new MySqlCommand(query, dbCon.Connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RsoViewModel rso = new RsoViewModel();
rso.id = reader.GetInt32(reader.GetOrdinal("Id"));
rso.type = reader.GetString(reader.GetOrdinal("type"));
rsos.Add(rso);
}
}
dbCon.Close();
return(rsos);
}