public static List<TaskItem> QueryTasks(TaskQueryFilter filter) { var result = new List<TaskItem>(); var conStr = ConfigurationManager.ConnectionStrings["ConnBrainExtension"].ConnectionString; using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); string queryString = ""; if (filter != null && filter.StartDateMin.HasValue) { queryString = queryString + " AND StartDate >= @StartDateMin"; cmd.Parameters.AddWithValue("StartDateMin", filter.StartDateMin.Value); } if (filter != null && filter.StartDateMax.HasValue) { queryString = queryString + " AND StartDate < @StartDateMax"; cmd.Parameters.AddWithValue("StartDateMax", filter.StartDateMax.Value.AddDays(1)); } if (filter != null && filter.StatusList.Count > 0) { queryString = queryString + " AND [Status] IN (" + string.Join(",", filter.StatusList.Select(d => string.Format("'{0}'", d)).ToArray()) + ")"; } queryString = string.Format(@" SELECT [Id] ,[Name] ,[Description] ,[StartDate] ,[EndDate] ,[Status] ,[IsDelete] ,[CreateBy] ,[CreateDate] ,[UpdateBy] ,[UpdateDate] FROM [dbo].[Task] WHERE IsDelete = 0 {0} ORDER BY [StartDate] ", queryString); cmd.CommandText = queryString; using (var rd = cmd.ExecuteReader()) { if (rd != null && rd.HasRows) { while (rd.Read()) { var startDateTime = new DateTime(((DateTime)rd["StartDate"]).Ticks, DateTimeKind.Utc); var endDateTime = new DateTime(((DateTime)rd["EndDate"]).Ticks, DateTimeKind.Utc); var minDate = (new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)); result.Add(new TaskItem() { Id=rd["Id"].ToString(), Name = rd["Name"].ToString(), Description = rd["Description"].ToString(), StartDate = startDateTime, StartTime = minDate + startDateTime.TimeOfDay, EndDate = endDateTime, EndTime = minDate + endDateTime.TimeOfDay, Status = rd["Status"].ToString(), IsDelete = (bool)rd["IsDelete"], CreateBy = rd["CreateBy"].ToString(), CreateDate = (DateTime)rd["CreateDate"], UpdateBy = rd["UpdateBy"].ToString(), UpdateDate = (DateTime)rd["UpdateDate"] }); } } } } return result; }
public ActionResult QueryTasks(TaskQueryFilter filter) { var taskList = TaskManager.QueryTasks(filter); return Json(taskList); }
public static List<TaskItem> QueryTasks(TaskQueryFilter filter) { return TaskProvider.QueryTasks(filter); }