private static void GetPageDataBySelect(BaseQuery baseQuery)
{
//SET @a = 0;
//select(@a:= count(0)) as cheokcount from t_user;
//select *,@a as cheokcount from T_User limit 1,10;
var bag = (ExcutParBag_Page)(baseQuery.excutParBag);
var sql = string.Empty;
var selectTable = bag.pageTableName;
var selectColumns = bag.pageColumns;
var len = bag.commandText.ToLower().IndexOf(" from") + 1;
baseQuery.SetDynamicParameters((bag.dynamics));
BaseQuery sqlTemp = new BaseQuery();
sqlTemp.CommandText = bag.commandText;
sqlTemp.CommandType = CommandType.Text;
for (int i = 0; i < baseQuery.Parameters.Count; i++)
{
SqlParameter sqlpa = baseQuery.Parameters[i];
SqlParameter par = new SqlParameter();
par.DbType = sqlpa.DbType;
par.Direction = sqlpa.Direction;
par.ParameterName = sqlpa.ParameterName;
par.Value = sqlpa.Value;
sqlTemp.Parameters.Add(par);
}
//构建取总数的数据
StringBuilder sqlReal = new StringBuilder();
sqlReal.AppendLine("/*******查询TotalCount*******/");
//sqlReal.AppendLine("SET @totalcountcheok=0;");
sqlReal.AppendLine("select");
sqlReal.AppendLine("(@totalcountcheok:= count(0))");
sqlReal.AppendLine("from");
sqlReal.AppendLine(selectTable);
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlReal = sqlReal.AppendLine(string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0}; ", bag.whereBuiler.ToString()));
}
else
{
sqlReal.Append(";");
}
sqlReal.AppendLine("/*******SQL查询数据*******/");
sqlReal.AppendLine("");
if (!bag.isPage)
{
bag.pageIndex = 1;
bag.pageSize = 1;
}
if (bag.pageIndex == 1)
{
#region //最新改动
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, ",@totalcountcheok as CHEOKTOTALCOUNT ");
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
if (!string.IsNullOrEmpty(bag.orderBy.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.orderBy.ToString()) ? string.Empty : string.Format(" ORDER BY {0} ", bag.orderBy.ToString()));
}
sqlTemp.CommandText = sqlTemp.CommandText + " LIMIT " + "0" + "," + bag.pageSize;
#endregion
}
else
{
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, ",@totalcountcheok as CHEOKTOTALCOUNT ");
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
if (!string.IsNullOrEmpty(bag.orderBy.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.orderBy.ToString()) ? string.Empty : string.Format(" ORDER BY {0} ", bag.orderBy.ToString()));
}
sqlTemp.CommandText = sqlTemp.CommandText + " LIMIT " + (((bag.pageIndex - 1) * bag.pageSize) - 1) + "," + bag.pageSize;
}
try
{
sqlTemp.CommandText = sqlReal.ToString() + sqlTemp.CommandText;
if (!string.IsNullOrEmpty(bag._preWith))
{
sqlTemp.CommandText =
bag._preWith + @"
/**With 子句已经完毕**/
" + sqlTemp.CommandText;
}
//sqlTemp.Parameters.Add(new SqlParameter() { DbType = DbType.String, Value = "@totalcountcheok", ParameterName= "@totalcountcheok" });
baseQuery.CommandText = sqlTemp.CommandText;
baseQuery.Parameters = sqlTemp.Parameters;
}
finally
{
}
}
private static void GetPageDataByCount(BaseQuery baseQuery)
{
var bag = (ExcutParBag_Page)(baseQuery.excutParBag);
var sql = string.Empty;
var selectTable = bag.pageTableName;
var selectColumns = bag.pageColumns;
var len = bag.commandText.ToLower().IndexOf("select") + 6;
baseQuery.SetDynamicParameters((bag.dynamics));
BaseQuery sqlTemp = new BaseQuery();
sqlTemp.CommandText = bag.commandText;
sqlTemp.CommandType = CommandType.Text;
for (int i = 0; i < baseQuery.Parameters.Count; i++)
{
SqlParameter sqlpa = baseQuery.Parameters[i];
SqlParameter par = new SqlParameter();
par.DbType = sqlpa.DbType;
par.Direction = sqlpa.Direction;
par.ParameterName = sqlpa.ParameterName;
par.Value = sqlpa.Value;
sqlTemp.Parameters.Add(par);
}
if (!bag.isPage)
{
bag.pageIndex = 1;
bag.pageSize = 1;
}
if (bag.pageIndex == 1)
{
#region //最新改动
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, " TOP(@num) COUNT(0) over() CHEOKTOTALCOUNT,");
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
if (!string.IsNullOrEmpty(bag.orderBy.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.orderBy.ToString()) ? string.Empty : string.Format(" ORDER BY {0} ", bag.orderBy.ToString()));
}
#endregion
sqlTemp.Parameters.Add("@num", bag.pageSize, SqlDbType.Int, 4);
}
else
{
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, string.Format(" ROW_NUMBER() OVER(ORDER BY {0}) as [num] ,COUNT(0) over() CHEOKTOTALCOUNT, ", string.IsNullOrEmpty(bag.orderBy.ToString()) ? "(SELECT 0)" : bag.orderBy.ToString()));
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText +
(string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
sqlTemp.CommandText = "SELECT * FROM (" + sqlTemp.CommandText + ") as [tab] where [num] between @Start and @End";
sqlTemp.Parameters.Add("@Start", ((bag.pageIndex - 1) * bag.pageSize + 1), SqlDbType.Int, 4);
sqlTemp.Parameters.Add("@End", (bag.pageIndex * bag.pageSize), SqlDbType.Int, 4);
}
try
{
if (!string.IsNullOrEmpty(bag._preWith))
{
sqlTemp.CommandText =
bag._preWith + @"
--With 子句已经完毕
" + sqlTemp.CommandText;
}
baseQuery.CommandText = sqlTemp.CommandText;
baseQuery.Parameters = sqlTemp.Parameters;
}
finally
{
}
}
private static void GetPageDataBySelect(BaseQuery baseQuery)
{
var bag = (ExcutParBag_Page)(baseQuery.excutParBag);
var sql = string.Empty;
var selectTable = bag.pageTableName;
var selectColumns = bag.pageColumns;
var len = bag.commandText.ToLower().IndexOf("select") + 6;
baseQuery.SetDynamicParameters((bag.dynamics));
BaseQuery sqlTemp = new BaseQuery();
sqlTemp.CommandText = bag.commandText;
sqlTemp.CommandType = CommandType.Text;
for (int i = 0; i < baseQuery.Parameters.Count; i++)
{
SqlParameter sqlpa = baseQuery.Parameters[i];
SqlParameter par = new SqlParameter();
par.DbType = sqlpa.DbType;
par.Direction = sqlpa.Direction;
par.ParameterName = sqlpa.ParameterName;
par.Value = sqlpa.Value;
sqlTemp.Parameters.Add(par);
}
//构建取总数的数据
StringBuilder sqlReal = new StringBuilder();
sqlReal.AppendLine("------查询TotalCount----");
sqlReal.AppendLine("DECLARE @totalcountcheok INT");
sqlReal.AppendLine("select");
sqlReal.AppendLine("@totalcountcheok=count(0)");
sqlReal.AppendLine("from");
sqlReal.AppendLine(selectTable);
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlReal = sqlReal.AppendLine(string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
sqlReal.AppendLine("-----------------SQL查询数据-----------------");
sqlReal.AppendLine("");
if (!bag.isPage)
{
bag.pageIndex = 1;
bag.pageSize = 1;
}
if (bag.pageIndex == 1)
{
#region //最新改动
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, " TOP(@num) @totalcountcheok CHEOKTOTALCOUNT,");
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
if (!string.IsNullOrEmpty(bag.orderBy.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText + (string.IsNullOrWhiteSpace(bag.orderBy.ToString()) ? string.Empty : string.Format(" ORDER BY {0} ", bag.orderBy.ToString()));
}
#endregion
//sql = string.Format(@"select top(@num) {0} FROM {1} {2} order by {3}",
//string.IsNullOrWhiteSpace(selectColumns) ? "*" : selectColumns, selectTable,
//string.IsNullOrWhiteSpace(whereBuiler.ToString()) ? string.Empty : string.Format(" where {0} ", whereBuiler.ToString()), orderBy.ToString());
sqlTemp.Parameters.Add("@num", bag.pageSize, SqlDbType.Int, 4);
}
else
{
sqlTemp.CommandText = sqlTemp.CommandText.Insert(len, string.Format(" ROW_NUMBER() OVER(ORDER BY {0}) as [num] ,@totalcountcheok CHEOKTOTALCOUNT, ", string.IsNullOrEmpty(bag.orderBy.ToString()) ? "(SELECT 0)" : bag.orderBy.ToString()));
if (!string.IsNullOrEmpty(bag.whereBuiler.ToString()))
{
sqlTemp.CommandText = sqlTemp.CommandText +
(string.IsNullOrWhiteSpace(bag.whereBuiler.ToString()) ? string.Empty : string.Format(" WHERE {0} ", bag.whereBuiler.ToString()));
}
sqlTemp.CommandText = "SELECT * FROM (" + sqlTemp.CommandText + ") as [tab] where [num] between @Start and @End";
// sql = string.Format(@"SELECT * FROM ( SELECT {0},row_number() over(order by {3}) as [num] FROM {1} {2} ) as [tab] where [num] between @Start and @End", string.IsNullOrWhiteSpace(selectColumns) ? "*" : selectColumns, selectTable, string.IsNullOrWhiteSpace(whereBuiler.ToString()) ? string.Empty : string.Format(" where {0} ", whereBuiler.ToString()), orderBy.ToString());
sqlTemp.Parameters.Add("@Start", ((bag.pageIndex - 1) * bag.pageSize + 1), SqlDbType.Int, 4);
sqlTemp.Parameters.Add("@End", (bag.pageIndex * bag.pageSize), SqlDbType.Int, 4);
}
try
{
sqlTemp.CommandText = sqlReal.ToString() + sqlTemp.CommandText;
if (!string.IsNullOrEmpty(bag._preWith))
{
sqlTemp.CommandText =
bag._preWith + @"
--With 子句已经完毕
" + sqlTemp.CommandText;
}
baseQuery.CommandText = sqlTemp.CommandText;
baseQuery.Parameters = sqlTemp.Parameters;
}
finally
{
}
}