private List <KeyValuePair <string, object> > GetFilterParam(StudyGroupFilter filter) { List <KeyValuePair <string, object> > parameters = new List <KeyValuePair <string, object> >(); if (filter == null) { return(parameters); } if (filter.Id.HasValue) { parameters.Add(new KeyValuePair <string, object>("@Id", filter.Id)); } if (filter.Name != null && filter.Name != "") { parameters.Add(new KeyValuePair <string, object>("@Name", filter.Name)); } if (filter.TeacherId.HasValue) { parameters.Add(new KeyValuePair <string, object>("@TeacherId", filter.TeacherId)); } if (filter.CourseId.HasValue) { parameters.Add(new KeyValuePair <string, object>("@CourseId", filter.CourseId)); } return(parameters); }
public IEnumerable <StudyGroup> Select(StudyGroupFilter filter) { string procedureName = "StudyGroup_Select"; using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(procedureName, connection) { CommandType = System.Data.CommandType.StoredProcedure }; var paramList = GetFilterParam(filter); foreach (var param in paramList) { SqlParameter Param = new SqlParameter { ParameterName = param.Key, Value = param.Value }; command.Parameters.Add(Param); } var result = command.ExecuteReader(); List <StudyGroup> resultList = new List <StudyGroup>(); if (result.HasRows) { while (result.Read()) { StudyGroup entity = new StudyGroup { Id = result.GetInt32(0), Name = result.GetString(1), TeacherId = result.GetInt32(2), CourseId = result.GetInt32(3) }; resultList.Add(entity); } } result.Close(); return(resultList); } }