/**
* @ 初始化SQL命令，准备执行
* */
protected override bool InitSQLWithCmdText()
{
if (TableName.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性TableName的值，该值为查询的主表名称");
}
if (LeftJoin.IsNotNullOrEmpty() && TableAlias.IsNullOrEmpty())
{
throw new ArgumentNullException("当存在多表连接查询时，必须指定主表的别名，即属性TableAlias的值");
}
if (Fields.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置要查询的字段fields");
}
string alias = string.Empty;
if (TableAlias.IsNotNullOrEmpty())
{
alias = string.Format("AS {0}", TableAlias);
}
string whereString = GetCondition();
SQLCmdText = string.Format(@"SELECT {0} FROM {1} {2} {3} {4} {5} {6}", Fields.ToJoin(), TableName, alias, LeftJoin, whereString, OrderBy, GroupBy);
Succeed = true;
return(Succeed);
}
/**
* @ 初始化SQL命令，准备执行
* */
protected override bool InitSQLWithCmdText()
{
if (TableName.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性TableName的值，该值为查询的主表名称");
}
if (LeftJoin.IsNotNullOrEmpty() && TableAlias.IsNullOrEmpty())
{
throw new ArgumentNullException("当存在多表连接查询时，必须指定主表的别名，即属性TableAlias的值");
}
if (Fields.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置要查询的字段fields");
}
if (PrimaryKey.IsNullOrEmpty())
{
throw new ArgumentNullException("必须设置属性PrimaryKey的值为查询主表的主键名称");
}
if (pageSize < 1)
{
throw new ArgumentNullException("必须设置属性pageSize的值，且该值必须大于0");
}
if (OrderBy.IsNullOrEmpty())
{
throw new ArgumentNullException("必须调用SetOrderBy方法进行设置排序字段");
}
string alias = string.Empty;
string pk = string.Empty;
if (TableAlias.IsNotNullOrEmpty())
{
alias = string.Format("AS {0}", TableAlias);
pk = string.Format("{0}.{1}", TableAlias, PrimaryKey);
}
else
{
pk = PrimaryKey;
}
string whereString = GetCondition();
string tempTableName = string.Format("{0}{1}", "A", Guid.NewGuid().ToString("N"));
// 如果没有条件，对全表进行统计行数
string sysSql = string.Format(@"dbcc updateusage(0,{0}) with no_infomsgs
SELECT @DataCount =SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats
WHERE object_id = object_id('{0}')", TableName);
// 按条件查询
string mSql = string.Format(@"SELECT @DataCount=COUNT(1) FROM {0} {1} {2} {3}", TableName, alias, LeftJoin, whereString);
SQLCmdText = string.Format(@"DECLARE @DataCount int
{13}
SELECT {0},@DataCount as DataCount FROM {1} {2}
{3}
WHERE {12} IN
(
SELECT {4} FROM
(
SELECT TOP {9} {12},ROW_NUMBER() OVER({5}) AS R_NO FROM {1} {2} {3} {6} {5}
){11} WHERE R_NO BETWEEN {8} AND {10}
) {7} {5}", Fields.ToJoin(), TableName, alias, LeftJoin, PrimaryKey, OrderBy, whereString, GroupBy, pageIndex, pageSize, pageIndex * pageSize, tempTableName, pk, whereString.IsNullOrEmpty() ? sysSql : mSql);
Succeed = true;
return(Succeed);
}