public virtual DbCommand CreateCommand(string commandText, FilterParam[] parms) { var cmd = this.DbProvider.DbProviderFactory.CreateCommand(); cmd.CommandText = commandText; if (parms != null) { foreach (var parm in parms) { DbParameter p = this.DbProvider.DbProviderFactory.CreateParameter(); p.ParameterName = this.DbProvider.ParamPrefix + parm.Name; p.Value = parm.Value; cmd.Parameters.Add(p); } } return cmd; }
/// <summary> /// SQL2005/SQL2008的分页方案,你也可以改成存储过程的方式 /// </summary> /// <param name="view"></param> /// <param name="where"> </param> /// <param name="sortname"></param> /// <param name="sortorder"></param> /// <param name="pagenumber"></param> /// <param name="pagesize"></param> /// <param name="parms"></param> /// <returns></returns> public override DataTable GetGridRows(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms) { string commandText = ""; bool pagable = pagenumber.HasValue && pagesize.HasValue; bool sortable = !sortname.IsNullOrEmpty() && !sortorder.IsNullOrEmpty(); if (pagable) { if (!sortable) { throw new Exception("必须指定一个排序条件"); } var orderby = string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC"); var orderbyREV = string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "DESC" : "ASC"); var sql = @"SELECT * FROM (SELECT {TOP} FROM {VIEW} WHERE {WHERE}) AS tmptableinner WHERE rowId between {N} and {M}"; sql = sql.Replace("{N}", ((pagenumber - 1) * pagesize + 1).ToString()); sql = sql.Replace("{M}", (pagenumber * pagesize).ToString()); sql = sql.Replace("{VIEW}", view); sql = sql.Replace("{TOP}", string.Concat("row_number() over (", orderby, ") as rowId,*")); sql = sql.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where); commandText = sql; } else { commandText = "SELECT * FROM {VIEW} WHERE {WHERE} "; if (sortable) { commandText += string.Concat(" ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC"); } commandText = commandText.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where); commandText = commandText.Replace("{VIEW}", view); } //创建command var cmd = this.CreateCommand(commandText, parms); //使用liger.Data内置的数据适配器预处理command this.DbProvider.PrepareCommand(cmd); //执行command var ds = this.DbContext.ExecuteDataSet(cmd); if (ds == null || ds.Tables.Count == 0) return null; return ds.Tables[0]; }
private string CreateFilterParam(object value,string type) { string paramName = "p" + ++paramCounter; object val = value; if (type.EqualsTo("int", true) || type.EqualsTo("digits", true)) val = DataHelper.ConvertValue(typeof(int), val); else if (type.EqualsTo("float", true) || type.EqualsTo("number", true)) val = DataHelper.ConvertValue(typeof(decimal),val); FilterParam param = new FilterParam(paramName, val); this.Parms.Add(param); return paramName; }
public virtual int GetGridTotal(string view, string where, FilterParam[] parms) { string commandText = "SELECT count(*) FROM [{0}] WHERE {1}".FormatWith(view, where.IsNullOrEmpty() ? "1=1" : where); //创建command var cmd = this.CreateCommand(commandText, parms); //使用liger.Data内置的数据适配器预处理command this.DbProvider.PrepareCommand(cmd); //执行command return (int)DbContext.ExecuteScalar(cmd); }
/// <summary> /// 默认的 N-M行 分页方案: /// 1,根据条件和排序 检索出全部前M行数据 /// 2,对这M行数据反转(排序反转) /// 3,对反转完的数据,取前 M-N+1 行 /// 4,取完数据以后,再次反转 /// 如:select * from (select top [M-N+1] * from (select top [M] * from 表 [order by id desc]) [order by id asc]) [order by id desc] /// </summary> /// <param name="view"></param> /// <param name="where"></param> /// <param name="sortname"></param> /// <param name="sortorder"></param> /// <param name="pagenumber"></param> /// <param name="pagesize"></param> /// <param name="parms"></param> /// <returns></returns> public virtual DataTable GetGridRows(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms) { string commandText = ""; bool pagable = pagenumber.HasValue && pagesize.HasValue; bool sortable = !sortname.IsNullOrEmpty() && !sortorder.IsNullOrEmpty(); if (pagable) { string sql = "SELECT TOP {2} * FROM [{0}] WHERE {1}".FormatWith(view, where.IsNullOrEmpty() ? "1=1" : where, (pagenumber.Value * pagesize.Value).ToString()); commandText = " SELECT * FROM (SELECT TOP {PAGESIZE} * FROM ({OLDSQL} {ORDER}) AS tmptableinner {REVORDER}) AS tmptableouter {ORDER} "; commandText = commandText.Replace("{PAGESIZE}", pagesize.Value.ToString()); if (sortable) { commandText = commandText.Replace("{ORDER}", string.Concat("ORDER BY " ,sortname," ", sortorder.EqualsTo("asc") ? "ASC" : "DESC")); commandText = commandText.Replace("{REVORDER}", string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "DESC" : "ASC")); } else { throw new Exception("必须指定一个排序条件"); } commandText = commandText.Replace("{OLDSQL}", sql); } else { commandText = "SELECT * FROM [{VIEW}] WHERE {WHERE} "; if (sortable) { commandText += string.Concat(" ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC"); } commandText = commandText.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where); commandText = commandText.Replace("{VIEW}", view); } //创建command var cmd = this.CreateCommand(commandText, parms); //使用liger.Data内置的数据适配器预处理command this.DbProvider.PrepareCommand(cmd); //执行command var ds = this.DbContext.ExecuteDataSet(cmd); if (ds == null || ds.Tables.Count == 0) return null; return ds.Tables[0]; }
public virtual GridData GetGridData(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms) { var rows = GetGridRows(view, where, sortname, sortorder, pagenumber, pagesize, parms); var total = GetGridTotal(view, where, parms); return new GridData() { Rows = rows, Total = total }; }