Jqgrid在C#下的应用,使用LINQ查询结果集

本文展示了如何在C#环境下使用Jqgrid与LINQ进行数据查询和展示。前端通过引用必要的Jqgrid样式和脚本文件,设置表格列和交互功能;后端通过AXSH文件处理HTTP请求,利用LINQ查询数据库并返回JSON数据,供Jqgrid展示分页和过滤后的结果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

<link href="~/Styles/jquery-ui-1.9.0.custom.min.css" rel="stylesheet" type="text/css" />
<link href="~/Styles/ui.jqgrid.css" rel="stylesheet" type="text/css" />

 

 <script src="../Scripts/jquery-ui-1.9.0.custom.min.js" type="text/javascript"></script>
 <script src="../Scripts/grid.locale-cn.js" type="text/javascript"></script>
 <script src="../Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>

这些文件时Jqgrid必须要引用的文件,引用先后顺序不能错.

前端代码如下:

    $("#Jqgrid").jqGrid({
                url: '/ISAPI/FlexGrid.ashx',
                datatype: "JSON",
                colNames: ['编号', '项目编号', '创建日期', '类型', '名称', '备注', '金额','pid','操作'],

                colModel: [
                             { name: 'FinanceID', index: 'FinanceID', width: 150, align: "center", editable: true, hidden: true, editrules: { edithidden: true} },
                             { name: 'ProjectID', index: 'ProjectID', width: 100, align: "center", editable: true, hidden: true, editrules: { edithidden: true} },
                             { name: 'CreateDate', index: 'CreateDate', width: 150, align: "center",search:false},
                             { name: 'tbFinanceType', index: 'tbFinanceType', width: 100, align: "center", sortable: false, searchoptions: { sopt: ["eq", "ne","cn","nc"]},search:false}, //其中eq、ne、。。。为JQGRID查询的时候所需要使用的。
                             { name: 'Name', index: 'Name', width: 200, align: "center", sortable: false, searchoptions: { sopt: ["eq", "ne", "cn", "nc"]} },
                             { name: 'Descritpion', index: 'Descritpion', width: 200, align: "center", sortable: false, searchoptions: { sopt: ["eq", "ne", "cn", "nc"]} },
                             { name: 'Money', index: 'Money', width: 120, align: "center", searchoptions: { sopt: ["lt", "le", "gt", "ge", "cn", "nc"]} },
                             { name: 'pid', index: 'pid', width: 150, align: "center", editable: true, hidden: true, editrules: { edithidden: true} },
                             { name: 'Operate', index: 'Operate', width: 120, align: "center", sortable: false, search: false },
                         ],
                rowNum: 5, //每页显示行数
                rowList: [5, 10, 20],  //每页可显示多少条数据
                viewrecords: true,
                sortname: 'CreateDate',
                pager: "#pager", 
                caption: "收入",
                sortorder: "desc",
                hidegrid: false,
                gridComplete: function () {
                    var ids = jQuery("#Jqgrid").jqGrid('getDataIDs');
                    for (var i = 0; i < ids.length; i++) {
                        var rowData = $("#Jqgrid").jqGrid("getRowData", ids[i]);
                        if (rowData) {
                            operate = "<a href='Edit.aspx?pid=" + rowData["pid"] + " 'style='color:#f60'>编辑</a>";
                            jQuery("#Jqgrid").jqGrid('setRowData', ids[i], { Operate: operate });
                        }
                    }
                }
            }).navGrid("#pager", { edit: false, add: false, del: false, search: true });

 

需要在显示页面加入

 <table id="Jqgrid" class="InfoTable" cellpadding="0" cellspacing="0" width="100%">
            </table>
            <div id="pager" class="scroll" style="text-align: center;">
                <!--用于分页的层-->
          </div>

后台为.axsh文件处理数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MojoFinance.Dal;
using System.Text;
using System.Data;
using System.ComponentModel;
using System.Reflection;
using System.Collections;
using MojoFinance.Constants;
using System.Web.Script.Serialization;
using ProjectTask.Common;
using System.Web.SessionState;
using MojoFinance.Portal.Helper;

namespace MojoFinance.Portal.ISAPI
{
    /// <summary>
    /// FlexGrid 的摘要说明
    /// </summary>
    public class FlexGrid : IHttpHandler, IRequiresSessionState
    {

        public void ProcessRequest(HttpContext context)
        {
            Guid ProjectID = Guid.Empty;
            string sField = string.Empty;
            if (HttpContext.Current.Request.Params["searchField"] != null)
            {
                sField = HttpContext.Current.Request.Params["searchField"].ToString();
            }
            string sOper = string.Empty;
            if (HttpContext.Current.Request.Params["searchOper"] != null)
            {
                sOper = HttpContext.Current.Request.Params["searchOper"].ToString();
            }
            string sValue = string.Empty;
            if (HttpContext.Current.Request.Params["searchString"] != null)
            {
                sValue = HttpContext.Current.Request.Params["searchString"].ToString();
            }
            string sPage = HttpContext.Current.Request.Params["page"].ToString();  //当前请求第几页
            int iPage = 0;
            if (!string.IsNullOrEmpty(sPage))
            {
                iPage = int.Parse(sPage);
            }
            string sLimit = HttpContext.Current.Request.Params["rows"].ToString(); //grid需要显示几行
            int iLimit = 0;
            if (!string.IsNullOrEmpty(sLimit))
            {
                iLimit = int.Parse(sLimit);
            }
            string sSidx = HttpContext.Current.Request.Params["sidx"].ToString();  //按什么排序
            string sSord = HttpContext.Current.Request.Params["sord"].ToString();  //排序方式('desc'/'asc')
            if (string.IsNullOrEmpty(sSidx))
            {
                sSidx = "CreateDate";
            }
            int TotalPage = 0;
            if (context.Session["ProjectID"] != null)
            {
                if (Guid.TryParse(context.Session["ProjectID"].ToString(), out ProjectID))
                {
                    MojoFinanceRecordEntities Dbcontext = new MojoFinanceRecordEntities();
                    IEnumerable<FinanceDailyData> Finance = Dbcontext.tbFinanceDailyData.Where(p => p.ProjectID.Equals(ProjectID) && p.IsInCome)
                                                                        .Select(pp => new FinanceDailyData
                                                                        {
                                                                            FinanceID = pp.ID,
                                                                            ProjectID = pp.ProjectID,
                                                                            CreateDate = pp.CreateDate,
                                                                            tbFinanceType = pp.tbFinanceType.Name,
                                                                            Name = pp.Name,
                                                                            Descritpion = pp.Descritpion,
                                                                            Money = pp.Money
                                                                        });
                    TotalPage = Finance.Count() / iLimit + 1;
                    if (sSord.Equals("asc"))
                    {
                        Finance = Finance.OrderBy(p => JsonHandle.GetValue(p, sSidx)).Skip((iPage - 1) * iLimit).Take(iLimit);
                    }
                    else
                    {
                        Finance = Finance.OrderByDescending(p => JsonHandle.GetValue(p, sSidx)).Skip((iPage - 1) * iLimit).Take(iLimit);
                    }
                    if (Finance.Count() > 0)
                    {
                        #region 查询条件过滤
                        if (!string.IsNullOrEmpty(sOper))
                        {
                            decimal sResult = 0M;
                            if (sOper.Equals("eq"))
                            {
                                Finance = Finance.Where(p => JsonHandle.GetValue(p, sField).Equals(sValue));
                            }
                            else if (sOper.Equals("ne"))
                            {
                                Finance = Finance.Where(p => !JsonHandle.GetValue(p, sField).Equals(sValue));
                            }
                            else if (sOper.Equals("lt"))
                            {
                                if (decimal.TryParse(sValue, out sResult))
                                {
                                    Finance = Finance.Where(p => (decimal)JsonHandle.GetValue(p, sField) < sResult);
                                }
                            }
                            else if (sOper.Equals("le"))
                            {
                                if (decimal.TryParse(sValue, out sResult))
                                {
                                    Finance = Finance.Where(p => (decimal)JsonHandle.GetValue(p, sField) <= sResult);
                                }
                            }
                            else if (sOper.Equals("gt"))
                            {
                                if (decimal.TryParse(sValue, out sResult))
                                {
                                    Finance = Finance.Where(p => (decimal)JsonHandle.GetValue(p, sField) > sResult);
                                }
                            }
                            else if (sOper.Equals("ge"))
                            {
                                if (decimal.TryParse(sValue, out sResult))
                                {
                                    Finance = Finance.Where(p => (decimal)JsonHandle.GetValue(p, sField) >= sResult);
                                }
                            }
                            else if (sOper.Equals("cn"))
                            {
                                Finance = Finance.Where(p => JsonHandle.GetValue(p, sField).ToString().Contains(sValue));
                            }
                            else if (sOper.Equals("nc"))
                            {
                                Finance = Finance.Where(p => !JsonHandle.GetValue(p, sField).ToString().Contains(sValue));
                            }
                        }
                        #endregion 条件查询过滤
                        List<FinanceDailyData> NewFinance = null;
                        if (Finance.Count()>0)
                        {
                            NewFinance = Finance.ToList();
                            foreach (FinanceDailyData item in NewFinance)
                            {
                                Hashtable ht = new Hashtable();
                                ht.Add(CommonConstants.QueryPageUrl, "~" + "/Finance/List.aspx");
                                ht.Add("ID", item.FinanceID);
                                ht.Add("ProjectID", item.ProjectID);
                                item.pid = HashTableConvert.HashtableToString(ht);
                            }
                        }
                        DataTable dt = NewFinance.ToDataTable();
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            string jsonData = JsonHandle.GetJson(dt, iPage, TotalPage);
                            context.Response.Write(jsonData);
                        }
                    }
                }
            }
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
    #region Linq数据集转换DataTable
    public static class ConvertToDataTable
    {
        public static DataTable ToDataTable(this IEnumerable list)
        {
            DataTable dt = new DataTable();
            bool schemaIsBuild = false;
            PropertyInfo[] props = null;
            foreach (object item in list)
            {
                if (!schemaIsBuild)
                {
                    props = item.GetType().GetProperties();
                    foreach (var pi in props)
                    {
                        dt.Columns.Add(new DataColumn(pi.Name, pi.PropertyType));
                    }
                    schemaIsBuild = true;
                }

                var row = dt.NewRow();
                foreach (var pi in props)
                {
                    row[pi.Name] = pi.GetValue(item, null);
                }
                dt.Rows.Add(row);
            }
            dt.AcceptChanges();
            return dt;
        }
    }
    #endregion Linq数据集转换DataTable
}

格式化Json数据类为

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Reflection;

namespace MojoFinance.Constants
{
    public class JsonHandle
    {
        public JsonHandle() { }
        public static string GetJson(DataTable dt,int page,int TotalPage)
        {
            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("{\"page\":" + page + ",\"total\":" + TotalPage + ",\"records\":" + dt.Rows.Count + ",\"rows\"");
            jsonBuilder.Append(":[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                jsonBuilder.Append("{\"Name\":"+'"'+ dt.Rows[i]["Name"].ToString() +'"' +",\"cell\":[");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(dt.Rows[i][j].ToString());
                    jsonBuilder.Append("\",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("]},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("]");
            jsonBuilder.Append("}");
            return jsonBuilder.ToString();
        }
        #region Linq动态查询
        public static object GetValue<T>(T model, string field)
        {
            if (field.Split('.').Length == 1)
                return model.GetType().GetProperty(field).GetValue(model, null);

            int index = field.IndexOf('.');
            string f1 = field.Substring(0, index);
            string f2 = field.Substring(index + 1);
            object obj = model.GetType().GetProperty(f1).GetValue(model, null);
            return GetValue(obj, f2);
        }
        #endregion Linq动态查询
    }
}

效果图如下

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值