1、请求字符串
{
"take": 20,
"skip": 0,
"page": 1,
"pageSize": 20,
"filter": {
"logic": "and",
"filters": [
{
"field": "CompanyName",
"operator": "contains",
"value": "C"
},
{
"logic": "or",
"filters": [
{
"value": "AddressTypeName1",
"operator": "eq",
"field": "AddressTypeName"
},
{
"value": "AddressTypeName2",
"operator": "eq",
"field": "AddressTypeName"
}
]
},
{
"logic": "or",
"filters": [
{
"value": "UseDepartmentName2",
"operator": "eq",
"field": "UseDepartmentName"
},
{
"value": "UseDepartmentName3",
"operator": "eq",
"field": "UseDepartmentName"
}
]
}
]
}
}
2、sql语句
public ActionResult GetPageList()
{
string sqlWhere = " 1=1 ";
var queryStr = Server.UrlDecode(Request.QueryString.ToString());
var query = queryStr.Substring(queryStr.IndexOf("{"), queryStr.LastIndexOf("}") - queryStr.IndexOf("{") + 1);
sqlWhere += GetAllColumnFuzzyQuery("");
GridRequest jsonSerialize = (GridRequest)JsonConvert.DeserializeObject(queryStr, typeof(GridRequest));
if (jsonSerialize.filter != null && jsonSerialize.filter.filters.Count > 0)
{
sqlWhere += GetWhereString(jsonSerialize.filter);
}
List<Customers> list = new List<Customers>();
for (int i = 1; i <= 60; i++)
{
var model = new Customers
{
CustomerID = "Firefox-icon",
ContactName = "ContactName" + i.ToString(),
ContactTitle = "ContactTitle" + i.ToString(),
CompanyName = "CompanyName" + i.ToString(),
Country = "Country" + i.ToString(),
UseDepartmentName = "UseDepartmentName" + i.ToString(),
SourceName = "SourceName" + i.ToString(),
AddressTypeName = "AddressTypeName" + i.ToString(),
};
list.Add(model);
}
return Json(new
{
total = list.Count,
result = list.ToList(),
}, JsonRequestBehavior.AllowGet);
}
private string GetWhereString(WebKendo.ViewModel.Filter filter)
{
string sqlWhere = string.Empty;
if (filter.logic.ToLower() == "and")
{
#region
foreach (var item in filter.filters)
{
var jsonString = Convert.ToString(item);
FilterCollection singleQuery = (FilterCollection)JsonConvert.DeserializeObject(jsonString, typeof(FilterCollection));
if (singleQuery.field != null && singleQuery.@operator != null && singleQuery.value != null)
{
#region 列查询,文本输"单条件"查询
if (singleQuery != null)
{
string column = singleQuery.field.ToLower();
switch (column)
{
case "contacttitle":
sqlWhere += SetOperateType(singleQuery, "a.contacttitle");
break;
case "companyname":
sqlWhere += SetOperateType(singleQuery, "a.companyname");
break;
default:
sqlWhere += SetOperateType(singleQuery);
break;
}
}
#endregion
}
else
{
ViewModel.Filter multipleQuery = (ViewModel.Filter)JsonConvert.DeserializeObject(jsonString, typeof(ViewModel.Filter));
#region 列查询,复选框“多条件”查询
if (multipleQuery != null && multipleQuery.filters.Count > 0)
{
string multipleValue = string.Empty;
foreach (var multiple in multipleQuery.filters)
{
string currentString = Convert.ToString(multiple);
FilterCollection currentQuery = (FilterCollection)JsonConvert.DeserializeObject(currentString, typeof(FilterCollection));
switch (currentQuery.field.ToLower())
{
case "sourcename":
multipleValue += SetMultipleColumns(currentQuery, "a.sourcename");
break;
case "usedepartmentname":
multipleValue += SetMultipleColumns(currentQuery, "a.usedepartmentname");
break;
case "addresstypename":
multipleValue += SetMultipleColumns(currentQuery, "a.addresstypename");
break;
default:
break;
}
}
sqlWhere += " AND (1=0 " + multipleValue + ")";
}
#endregion
}
}
#endregion
}
else if (filter.logic.ToLower() == "or")
{
string multipleValue = string.Empty;
#region 列查询,复选框“多条件”查询
foreach (var item in filter.filters)
{
var jsonString = Convert.ToString(item);
FilterCollection multiple = (FilterCollection)JsonConvert.DeserializeObject(jsonString, typeof(FilterCollection));
switch (multiple.field.ToLower())
{
case "sourcename":
multipleValue += SetMultipleColumns(multiple, "a.sourcename");
break;
case "usedepartmentname":
multipleValue += SetMultipleColumns(multiple, "a.usedepartmentname");
break;
case "addresstypename":
multipleValue += SetMultipleColumns(multiple, "a.addresstypename");
break;
default:
break;
}
}
sqlWhere += " AND (1=0 " + multipleValue + ")";
#endregion
}
else
{ }
return sqlWhere;
}
3、html
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
@*<base href="http://demos.telerik.com/kendo-ui/grid/index">*@
<style>
html {
font-size: 14px;
font-family: Arial, Helvetica, sans-serif;
}
</style>
<title></title>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2017.2.621/styles/kendo.common-material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2017.2.621/styles/kendo.material.min.css" />
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2017.2.621/styles/kendo.material.mobile.min.css" />
<script src="https://kendo.cdn.telerik.com/2017.2.621/js/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2017.2.621/js/kendo.all.min.js"></script>
</head>
<body>
<div id="example">
<div id="grid"></div>
<script>
$(document).ready(function () {
$("#grid").kendoGrid({
dataSource: {
//type: "odata",
type: "json",
transport: {
//方式1
//read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Customers"
//read: "http://localhost:1930/Home/GetPageList",
//方式2
read: {
url: "/Home/GetPageList",
},
parameterMap: function (options) {
return encodeURI(JSON.stringify(options));
}
},
//schema: {
// data: "result",
// total: "total"
//},
batch: true,
schema: {
model: {
id: "",
},
data: function (response) {
//return response.result;
return response.result2;
},
total: function (response) {
//return response.total;
return response.total2;
}
},
pageSize: 20,
serverFiltering: true,
serverPaging: true,
serverSorting: true,
},
height: 550,
resizable: true,
sortable: true,
reorderable: true,
columnMenu: true,
dataBound: function () {
},
batch: true,
editable: "inline",
filterable: {
extra: false,
operators: {
string: {
contains: "包含",
doesnotcontain: "不包含",
eq: "等于",
neq: "不等于",
startswith: "开头为",
endswith: "结尾为"
}
}
},
pageable: {
refresh: true,
pageSizes: true,
buttonCount: 5,
pageSizes: [20, 50, 100]
},
columns: [
{
template: "<div class='customer-photo' style='background-image: url(/Images/#:data.CustomerID#.png);'></div>" +
"<div class='customer-name'>#: ContactName #</div>",
field: "ContactName", title: "联系人", width: 150
},
{ field: "ContactTitle", title: "职位名称", width: 150 },
{ field: "CompanyName", title: "公司名称", width: 150 },
{ field: "Country", title: "国家", width: 150 },
{
field: "UseDepartmentName", title: "使用部门", width: 150,
filterable: {
multi: true,
messages: { checkAll: "全部", filter: "过滤", clear: "清除" },
dataSource: {
transport: {
read: {
url: "/Home/GetDepartByUserIdColumnFilter?userID=1&t=" + Math.random(),
dataType: "json",
}
}
}
}
},
{
field: "SourceName", title: "来源", width: 150,
filterable: {
multi: true,
messages: { checkAll: "全部", filter: "过滤", clear: "清除" },
dataSource: [{ "SourceName": "购入" }, { "SourceName": "自建" }, { "SourceName": "租赁" }, { "SourceName": "捐赠" }, { "SourceName": "其他" }],
}
},
{
field: "AddressTypeName", title: "区域", width: 150,
filterable: {
multi: true,
messages: { checkAll: "全部", filter: "过滤", clear: "清除" },
dataSource: dsGetAllAddressType
}
},
]
});
});
var dsGetAllAddressType = new kendo.data.DataSource({
type: "json",
transport: {
read: {
url: "/Home/GetAllAddressType?t=" + Math.random(),
dataType: "json",
cache: true
}
}
});
</script>
</div>
<style type="text/css">
.customer-photo {
display: inline-block;
width: 32px;
height: 32px;
border-radius: 50%;
background-size: 32px 35px;
background-position: center center;
vertical-align: middle;
line-height: 32px;
box-shadow: inset 0 0 1px #999, inset 0 0 10px rgba(0,0,0,.2);
margin-left: 5px;
}
.customer-name {
display: inline-block;
vertical-align: middle;
line-height: 32px;
padding-left: 3px;
}
</style>
</body>
</html>
4、Controller
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using Test.Entity;
using WebKendo.CoreLibrary;
using WebKendo.ViewModel;
namespace WebKendo.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
public ActionResult About()
{
ViewBag.Message = "Your application description page.";
return View();
}
public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";
return View();
}
public ActionResult KendoIndex()
{
ViewBag.Message = "Your kendo page.";
return View();
}
public ActionResult Kendo()
{
ViewBag.Message = "Your kendo page.";
return View();
}
public ActionResult GetPageList()
{
string sqlWhere = " 1=1 ";
var queryStr = Server.UrlDecode(Request.QueryString.ToString());
var query = queryStr.Substring(queryStr.IndexOf("{"), queryStr.LastIndexOf("}") - queryStr.IndexOf("{") + 1);
sqlWhere += GetAllColumnFuzzyQuery("");
GridRequest jsonSerialize = (GridRequest)JsonConvert.DeserializeObject(queryStr, typeof(GridRequest));
if (jsonSerialize.filter != null && jsonSerialize.filter.filters.Count > 0)
{
sqlWhere += GetWhereString(jsonSerialize.filter);
}
List<Customers> list = new List<Customers>();
for (int i = 1; i <= 60; i++)
{
var model = new Customers
{
CustomerID = "Firefox-icon",
ContactName = "ContactName" + i.ToString(),
ContactTitle = "ContactTitle" + i.ToString(),
CompanyName = "CompanyName" + i.ToString(),
Country = "Country" + i.ToString(),
UseDepartmentName = "UseDepartmentName" + i.ToString(),
SourceName = "SourceName" + i.ToString(),
AddressTypeName = "AddressTypeName" + i.ToString(),
};
list.Add(model);
}
List<Customers> list2 = new List<Customers>();
for (int i = 101; i <= 200; i++)
{
var model = new Customers
{
CustomerID = "Firefox-icon",
ContactName = "ContactName" + i.ToString(),
ContactTitle = "ContactTitle" + i.ToString(),
CompanyName = "CompanyName" + i.ToString(),
Country = "Country" + i.ToString(),
UseDepartmentName = "UseDepartmentName" + i.ToString(),
SourceName = "SourceName" + i.ToString(),
AddressTypeName = "AddressTypeName" + i.ToString(),
};
list2.Add(model);
}
//return Json(list, JsonRequestBehavior.AllowGet);
return Json(new
{
total = list.Count,
result = list.ToList(),
total2 = list2.Count,
result2 = list2.ToList(),
}, JsonRequestBehavior.AllowGet);
}
public ActionResult GetAllAddressType()
{
List<AddressType> list = new List<AddressType>();
for (int i = 1; i <= 10; i++)
{
var model = new AddressType
{
Id = "Id" + i.ToString(),
Name = "Name" + i.ToString(),
Code = "Code" + i.ToString(),
AddressTypeName = "AddressTypeName" + i.ToString(),
OutAddressTypeName = "OutAddressTypeName" + i.ToString(),
CheckAddressType = "CheckAddressType" + i.ToString(),
InAddressTypeName = "InAddressTypeName" + i.ToString(),
UseRevertAddressTypeName = "UseRevertAddressTypeName" + i.ToString(),
};
list.Add(model);
}
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult GetDepartByUserIdColumnFilter()
{
List<Depart> list = new List<Depart>();
for (int i = 1; i <= 10; i++)
{
var model = new Depart
{
UseDepartmentName = "UseDepartmentName" + i.ToString(),
UseDepartName = "UseDepartName" + i.ToString(),
OutUseDepartName = "OutUseDepartName" + i.ToString(),
InUseDepartName = "InUseDepartName" + i.ToString(),
UseDepartment = "UseDepartment" + i.ToString(),
CheckUseDepartment = "CheckUseDepartment" + i.ToString(),
UseRevertUseDepartmentName = "UseRevertUseDepartmentName" + i.ToString(),
};
list.Add(model);
}
return Json(list, JsonRequestBehavior.AllowGet);
}
private string GetWhereString(WebKendo.ViewModel.Filter filter)
{
string sqlWhere = string.Empty;
if (filter.logic.ToLower() == "and")
{
#region
foreach (var item in filter.filters)
{
var jsonString = Convert.ToString(item);
FilterCollection singleQuery = (FilterCollection)JsonConvert.DeserializeObject(jsonString, typeof(FilterCollection));
if (singleQuery.field != null && singleQuery.@operator != null && singleQuery.value != null)
{
#region 列查询,文本输"单条件"查询
if (singleQuery != null)
{
string column = singleQuery.field.ToLower();
switch (column)
{
case "contacttitle":
sqlWhere += SetOperateType(singleQuery, "a.contacttitle");
break;
case "companyname":
sqlWhere += SetOperateType(singleQuery, "a.companyname");
break;
default:
sqlWhere += SetOperateType(singleQuery);
break;
}
}
#endregion
}
else
{
ViewModel.Filter multipleQuery = (ViewModel.Filter)JsonConvert.DeserializeObject(jsonString, typeof(ViewModel.Filter));
#region 列查询,复选框“多条件”查询
if (multipleQuery != null && multipleQuery.filters.Count > 0)
{
string multipleValue = string.Empty;
foreach (var multiple in multipleQuery.filters)
{
string currentString = Convert.ToString(multiple);
FilterCollection currentQuery = (FilterCollection)JsonConvert.DeserializeObject(currentString, typeof(FilterCollection));
switch (currentQuery.field.ToLower())
{
case "sourcename":
multipleValue += SetMultipleColumns(currentQuery, "a.sourcename");
break;
case "usedepartmentname":
multipleValue += SetMultipleColumns(currentQuery, "a.usedepartmentname");
break;
case "addresstypename":
multipleValue += SetMultipleColumns(currentQuery, "a.addresstypename");
break;
default:
break;
}
}
sqlWhere += " AND (1=0 " + multipleValue + ")";
}
#endregion
}
}
#endregion
}
else if (filter.logic.ToLower() == "or")
{
string multipleValue = string.Empty;
#region 列查询,复选框“多条件”查询
foreach (var item in filter.filters)
{
var jsonString = Convert.ToString(item);
FilterCollection multiple = (FilterCollection)JsonConvert.DeserializeObject(jsonString, typeof(FilterCollection));
switch (multiple.field.ToLower())
{
case "sourcename":
multipleValue += SetMultipleColumns(multiple, "a.sourcename");
break;
case "usedepartmentname":
multipleValue += SetMultipleColumns(multiple, "a.usedepartmentname");
break;
case "addresstypename":
multipleValue += SetMultipleColumns(multiple, "a.addresstypename");
break;
default:
break;
}
}
sqlWhere += " AND (1=0 " + multipleValue + ")";
#endregion
}
else
{ }
return sqlWhere;
}
/// <summary>
/// 设置判断类型:包含、不包含、等于、不等于
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private string SetOperateType(FilterCollection filter)
{
string sqlWhere = string.Empty;
///判断类型:包含、不包含、等于、不等于
string operate = filter.@operator;
switch (operate)
{
case "eq":
sqlWhere += string.Format(" AND ({0} = '{1}' OR {0} IS NULL )", filter.field, filter.value);
break;
case "neq":
sqlWhere += string.Format(" AND {0} != '{1}'", filter.field, filter.value);
break;
case "startswith":
sqlWhere += string.Format(" AND {0} LIKE '{1}%'", filter.field, filter.value);
break;
case "contains":
sqlWhere += string.Format(" AND {0} LIKE '%{1}%'", filter.field, filter.value);
break;
case "doesnotcontain":
sqlWhere += string.Format(" AND {0} not LIKE '%{1}%'", filter.field, filter.value);
break;
case "endswith":
sqlWhere += string.Format(" AND {0} LIKE '%{1}'", filter.field, filter.value);
break;
}
return sqlWhere;
}
/// <summary>
/// 设置判断类型:包含、不包含、等于、不等于
/// </summary>
/// <param name="filter"></param>
/// <param name="column"></param>
/// <returns></returns>
private string SetOperateType(FilterCollection filter, string column)
{
string sqlWhere = string.Empty;
///判断类型:包含、不包含、等于、不等于
string operate = filter.@operator;
switch (operate)
{
case "eq":
sqlWhere += string.Format(" AND ({0} = '{1}' OR {0} IS NULL )", column, filter.value);
break;
case "neq":
sqlWhere += string.Format(" AND {0} != '{1}'", column, filter.value);
break;
case "startswith":
sqlWhere += string.Format(" AND {0} LIKE '{1}%'", column, filter.value);
break;
case "contains":
sqlWhere += string.Format(" AND {0} LIKE '%{1}%'", column, filter.value);
break;
case "doesnotcontain":
sqlWhere += string.Format(" AND {0} NOT LIKE '%{1}%'", column, filter.value);
break;
case "endswith":
sqlWhere += string.Format(" AND {0} LIKE '%{1}'", column, filter.value);
break;
}
return sqlWhere;
}
/// <summary>
/// 设置判断类型:包含、不包含、等于、不等于
/// </summary>
/// <param name="column"></param>
/// <param name="operate"></param>
/// <param name="value"></param>
/// <returns></returns>
private string SetOperateType(string column, string operate, string value)
{
string sqlWhere = string.Empty;
///判断类型:包含、不包含、等于、不等于
switch (operate)
{
case "eq":
sqlWhere += string.Format(" AND ({0} = '{1}' OR {0} IS NULL )", column, value);
break;
case "neq":
sqlWhere += string.Format(" AND {0} != '{1}'", column, value);
break;
case "startswith":
sqlWhere += string.Format(" AND {0} LIKE '{1}%'", column, value);
break;
case "contains":
sqlWhere += string.Format(" AND {0} LIKE '%{1}%'", column, value);
break;
case "doesnotcontain":
sqlWhere += string.Format(" AND {0} NOT LIKE '%{1}%'", column, value);
break;
case "endswith":
sqlWhere += string.Format(" AND {0} LIKE '%{1}'", column, value);
break;
}
return sqlWhere;
}
/// <summary>
///
/// </summary>
/// <param name="filter"></param>
/// <param name="columnName"></param>
/// <returns></returns>
private string SetMultipleColumns(FilterCollection filter,string columnName)
{
var sqlWhere = "";
switch (filter.@operator)
{
case "eq":
sqlWhere += string.Format(" OR {0} = '{1}'", columnName, filter.value);
break;
}
return sqlWhere;
}
/// <summary>
///
/// </summary>
/// <param name="columnName"></param>
/// <param name="operate"></param>
/// <param name="value"></param>
/// <returns></returns>
private string SetMultipleColumns(string columnName, string operate, string value)
{
var sqlWhere = "";
switch (operate)
{
case "eq":
sqlWhere += string.Format(" OR {0} = '{1}'", columnName, value);
break;
}
return sqlWhere;
}
/// <summary>
/// 某个表中所有列的模糊查询
/// </summary>
/// <param name="search"></param>
/// <returns></returns>
private string GetAllColumnFuzzyQuery(string title)
{
StringBuilder sqlWhere = new StringBuilder();
if (!string.IsNullOrEmpty(title))
{
sqlWhere.Append(" AND (");
sqlWhere.Append(string.Format("Barcode LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("Specs LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("SNNo LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("vc.Name LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("vd.Name LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("voc.Name LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("vat.Name LIKE '%{0}%' OR ", title));
sqlWhere.Append(string.Format("a.Name LIKE '%{0}%'", title));
sqlWhere.Append(") ");
}
return sqlWhere.ToString();
}
}
}