public static List <MembersViewModel> viewMembers() { List <MembersViewModel> members = new List <MembersViewModel>(); var dbCon = DBConnection.Instance(); if (dbCon.IsConnect()) { string query = "SELECT * FROM members JOIN school ON members.schoolNameId = school.id JOIN user_type ON members.userTypeId = user_type.id"; var cmd = new MySqlCommand(query, dbCon.Connection); var reader = cmd.ExecuteReader(); while (reader.Read()) { MembersViewModel member = new MembersViewModel(); member.id = reader.GetInt32(reader.GetOrdinal("Id")); member.firstName = reader.GetString(reader.GetOrdinal("firstName")); member.lastName = reader.GetString(reader.GetOrdinal("lastName")); member.picture = reader.GetString(reader.GetOrdinal("picture")); member.createDate = reader.GetDateTime(reader.GetOrdinal("createDate")); member.email = reader.GetString(reader.GetOrdinal("email")); member.password = reader.GetString(reader.GetOrdinal("password")); member.schoolNameId = reader.GetInt32(reader.GetOrdinal("schoolNameId")); member.userTypeId = reader.GetInt32(reader.GetOrdinal("userTypeId")); members.Add(member); } } dbCon.Close(); return(members); }
public static MembersViewModel getMember(string emailVar) { MembersViewModel member = new MembersViewModel(); var dbCon = DBConnection.Instance(); if (dbCon.IsConnect()) { string query = "SELECT members.*, school.id as schoolName, user_type.id as type FROM members JOIN school ON members.schoolNameId = school.id JOIN user_type ON members.userTypeId = user_type.id WHERE members.email = @email"; var cmd = new MySqlCommand(query, dbCon.Connection); cmd.Parameters.AddWithValue("@email", emailVar); var reader = cmd.ExecuteReader(); while (reader.Read()) { member.id = reader.GetInt32(reader.GetOrdinal("Id")); member.firstName = reader.GetString(reader.GetOrdinal("firstName")); member.lastName = reader.GetString(reader.GetOrdinal("lastName")); member.picture = reader.GetString(reader.GetOrdinal("picture")); member.createDate = reader.GetDateTime(reader.GetOrdinal("createDate")); member.email = reader.GetString(reader.GetOrdinal("email")); member.password = reader.GetString(reader.GetOrdinal("password")); member.schoolNameId = reader.GetInt32(reader.GetOrdinal("schoolName")); member.userTypeId = reader.GetInt32(reader.GetOrdinal("type")); } } dbCon.Close(); return(member); }