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
};
}
