public InstructorEditViewModel(SqlConnection Connection) { string sql = $@" SELECT c.Id, c.[name] FROM cohorts c"; using (SqlConnection conn = Connection) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; SqlDataReader reader = cmd.ExecuteReader(); List <Cohort> CurrentCohorts = new List <Cohort>(); while (reader.Read()) { Cohort cohort = new Cohort { Id = reader.GetInt32(reader.GetOrdinal("id")), Name = reader.GetString(reader.GetOrdinal("name")) }; CurrentCohorts.Add(cohort); } var selectedItems = CurrentCohorts.Select(c => new SelectListItem { Text = c.Name, Value = c.Id.ToString() }) .ToList(); Cohorts = selectedItems; reader.Close(); } } }
public void GetAllCohorts() { using (SqlConnection conn = Connection) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @"SELECT c.Id, c.Designation from Cohort c"; SqlDataReader reader = cmd.ExecuteReader(); List <Cohort> cohorts = new List <Cohort>(); while (reader.Read()) { Cohort cohort = new Cohort { Id = reader.GetInt32(reader.GetOrdinal("Id")), Designation = reader.GetString(reader.GetOrdinal("Designation")) }; cohorts.Add(cohort); } Cohorts = cohorts.Select(li => new SelectListItem { Text = li.Designation, Value = li.Id.ToString() }).ToList(); Cohorts.Insert(0, new SelectListItem { Text = "Choose cohort...", Value = "0" }); } } }
public void GetAllStuff(int id) { using (SqlConnection conn = Connection) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @"SELECT c.Id, c.Designation from Cohort c"; SqlDataReader reader = cmd.ExecuteReader(); List <Cohort> cohorts = new List <Cohort>(); while (reader.Read()) { Cohort cohort = new Cohort { Id = reader.GetInt32(reader.GetOrdinal("Id")), Designation = reader.GetString(reader.GetOrdinal("Designation")) }; cohorts.Add(cohort); } Cohorts = cohorts.Select(li => new SelectListItem { Text = li.Designation, Value = li.Id.ToString() }).ToList(); reader.Close(); } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = @" SELECT i.Id, i.FirstName, i.LastName, i.SlackHandle, i.Speciality, i.CohortId FROM Instructor i WHERE i.Id = @id "; cmd.Parameters.Add(new SqlParameter("@id", id)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Instructor.Id = reader.GetInt32(reader.GetOrdinal("Id")); Instructor.FirstName = reader.GetString(reader.GetOrdinal("FirstName")); Instructor.LastName = reader.GetString(reader.GetOrdinal("LastName")); Instructor.SlackHandle = reader.GetString(reader.GetOrdinal("SlackHandle")); Instructor.Speciality = reader.GetString(reader.GetOrdinal("Speciality")); Instructor.CohortId = reader.GetInt32(reader.GetOrdinal("CohortId")); } ; reader.Close(); } } }