using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web.Mvc; namespace formula { public class QueryBuilderBinder : IModelBinder { public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext) { var qb = (QueryBuilder)(bindingContext.Model ?? new QueryBuilder()); var dict = controllerContext.HttpContext.Request.Params; var quickQueryList = !string.IsNullOrEmpty(dict["quickQueryData"]) ? JsonHelper.ToList(dict["quickQueryData"]) : new List<Dictionary<string, object>>(); var queryList = !string.IsNullOrEmpty(dict["queryData"]) ? JsonHelper.ToList(dict["queryData"]) : new List<Dictionary<string, object>>(); foreach (var dic in quickQueryList) { var val = dic["value"].ToString(); if (val == "") continue; qb.add(dic["field"].ToString(), dic["method"].ToString(), val, true); } foreach (var dic in queryList) { var val = dic["value"].ToString(); if (val == "") continue; qb.add(dic["field"].ToString(), dic["method"].ToString(), val, false); } qb.page = !string.IsNullOrEmpty(dict["page"]) ? int.Parse(dict["page"].ToString()) : 1; qb.rows = !string.IsNullOrEmpty(dict["rows"]) ? int.Parse(dict["rows"].ToString()) : 10; qb.sort = !string.IsNullOrEmpty(dict["sort"]) ? dict["page"].ToString() : "id"; qb.order = !string.IsNullOrEmpty(dict["order"]) ? dict["order"].ToString() : "desc"; return qb; } } }
4、数据库查询帮助类SqlHelper：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.Web; namespace formula { public class SqlHelper { #region 构造函数 public SqlHelper(string connName) { if (System.Configuration.ConfigurationManager.ConnectionStrings[connName] == null) throw new BuessinessException(string.Format("配置文件中不包含数据库连接字符串：{0}", connName)); this.connName = connName; this.connString = System.Configuration.ConfigurationManager.ConnectionStrings[connName].ConnectionString; } public string connName { get; private set; } public string connString { get; private set; } public string dbName { get { SqlConnection conn = new SqlConnection(connString); return conn.Database; } } #endregion #region 基本方法 public object ExecuteScalar(string cmdText) { using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); SqlCommand cmd = new SqlCommand(cmdText, conn); return cmd.ExecuteScalar(); } } public DataTable ExecuteDataTable(string cmdText) { using (SqlConnection conn = new SqlConnection(connString)) { DataTable dt = new DataTable(); SqlDataAdapter apt = new SqlDataAdapter(cmdText, conn); apt.Fill(dt); return dt; } } public DataTable ExecuteDataTable(string cmdText, int start, int len) { using (SqlConnection conn = new SqlConnection(connString)) { DataTable dt = new DataTable(); SqlDataAdapter apt = new SqlDataAdapter(cmdText, conn); apt.Fill(start, len, dt); return dt; } } public string ExecuteNonQuery(string cmdText) { using (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); SqlCommand cmd = new SqlCommand(cmdText, conn); return cmd.ExecuteNonQuery().ToString(); } } #endregion #region 支持查询对象 public DataTable ExecuteDataTable(string sql, SearchCondition cnd, string orderBy) { string sqlWhere = " where 1=1" + GetUrlFilterSqlWhere(sql) + cnd.getWhereString(false); sql = string.Format("select {0} from ({1}) sourceTable {2} {3}", cnd.fields, sql, sqlWhere, orderBy); DataTable dt = this.ExecuteDataTable(sql); return dt; } public Dictionary<string, object> ExecuteGridData(string sql, QueryBuilder qb) { string sqlWhere = " where 1=1" + GetUrlFilterSqlWhere(sql) + qb.getWhereString(false); qb.total = (int)this.ExecuteScalar(string.Format("select count(1) from ({0}) sourceTable {1}", sql, sqlWhere)); sql = string.Format("select {0} from ({1}) sourceTable {2} {3}", qb.fields, sql, sqlWhere, qb.getOrderByString()); DataTable dt = ExecuteDataTable(sql, (qb.page - 1) * qb.rows, qb.rows); Dictionary<string, object> dic = new Dictionary<string, object>(); dic.Add("total", qb.total); dic.Add("rows", dt); return dic; } #endregion #region 私有方法 private string GetUrlFilterSqlWhere(string sql) { sql = string.Format("select * from({0}) as dt1 where 1=2", sql); var dtField = ExecuteDataTable(sql); StringBuilder sb = new StringBuilder(); foreach (string key in HttpContext.Current.Request.QueryString.Keys) { if (string.IsNullOrEmpty(key) || key.ToLower() == "id") continue; if (dtField.Columns.Contains(key)) { string value = HttpContext.Current.Server.UrlDecode(HttpContext.Current.Request[key]); value = value.Replace(",", "','"); sb.AppendFormat(" and {0} in ('{1}')", key, value); } } return sb.ToString(); } #endregion } }
5、用于取代返回值JsonResult的NewtonJsonResult：