示例#1
0
    /// <summary>
    /// Shows how to get a specific number of sorted records, starting from an index, based on Search Parameters.  The number of records are also retrieved.
    /// </summary>
    private void SelectSkipAndTakeDynamicWhere()
    {
        int    startRetrievalFromRecordIndex = 0;
        int    numberOfRecordsToRetrieve     = 10;
        string sortBy = "EnrollmentId";
        //string sortBy = "EnrollmentId desc";

        // search parameters, everything is nullable, only items being searched for should be filled
        // note: fields with String type uses a LIKE search, everything else uses an exact match
        // also, every field you're searching for uses the AND operator
        // e.g. int? productID = 1; string productName = "ch";
        // will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%'
        int?   enrollmentId = null;
        int?   courseName   = null;
        int?   studentName  = null;
        string comments     = null;

        // 1. select a specific number of sorted records starting from the index you specify based on Search Parameters
        List <CourseEnrollment> objCourseEnrollmentCol = CourseEnrollment.SelectSkipAndTakeDynamicWhere(enrollmentId, courseName, studentName, comments, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy);

        // to use objCourseEnrollmentCol please see the SelectAll() method examples
        // No need for Examples 1 and 2 because the Collection here is already sorted
        // Example 3:  directly bind to a GridView - for ASP.NET Web Forms
        // Example 4:  loop through all the CourseEnrollment(s).  The example above will only loop for 10 items.
    }
        /// <summary>
        /// Gets the list of data for use by the jqgrid plug-in
        /// </summary>
        public IActionResult OnGetGridDataWithFilters(string sidx, string sord, int _page, int rows, string filters)
        {
            int?   enrollmentId = null;
            int?   courseName   = null;
            int?   studentName  = null;
            string comments     = String.Empty;

            if (!String.IsNullOrEmpty(filters))
            {
                // deserialize json and get values being searched
                var jsonResult = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(filters);

                foreach (var rule in jsonResult["rules"])
                {
                    if (rule["field"].Value.ToLower() == "enrollmentid")
                    {
                        enrollmentId = Convert.ToInt32(rule["data"].Value);
                    }

                    if (rule["field"].Value.ToLower() == "coursename")
                    {
                        courseName = Convert.ToInt32(rule["data"].Value);
                    }

                    if (rule["field"].Value.ToLower() == "studentname")
                    {
                        studentName = Convert.ToInt32(rule["data"].Value);
                    }

                    if (rule["field"].Value.ToLower() == "comments")
                    {
                        comments = rule["data"].Value;
                    }
                }

                // sometimes jqgrid assigns a -1 to numeric fields when no value is assigned
                // instead of assigning a null, we'll correct this here
                if (enrollmentId == -1)
                {
                    enrollmentId = null;
                }

                if (courseName == -1)
                {
                    courseName = null;
                }

                if (studentName == -1)
                {
                    studentName = null;
                }
            }

            int totalRecords  = CourseEnrollment.GetRecordCountDynamicWhere(enrollmentId, courseName, studentName, comments);
            int startRowIndex = ((_page * rows) - rows);
            List <CourseEnrollment> objCourseEnrollmentCol = CourseEnrollment.SelectSkipAndTakeDynamicWhere(enrollmentId, courseName, studentName, comments, rows, startRowIndex, sidx + " " + sord);
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);

            if (objCourseEnrollmentCol is null)
            {
                return(new JsonResult("{ total = 0, page = 0, records = 0, rows = null }"));
            }

            var jsonData = new
            {
                total = totalPages,
                _page,
                records = totalRecords,
                rows    = (
                    from objCourseEnrollment in objCourseEnrollmentCol
                    select new
                {
                    id = objCourseEnrollment.EnrollmentId,
                    cell = new string[] {
                        objCourseEnrollment.EnrollmentId.ToString(),
                        objCourseEnrollment.CourseName.ToString(),
                        objCourseEnrollment.StudentName.ToString(),
                        objCourseEnrollment.Comments
                    }
                }).ToArray()
            };

            return(new JsonResult(jsonData));
        }