/// <summary>
/// 执行分页查询（DataTable分页）
/// </summary>
/// <param name="PageSize">每页大小</param>
/// <param name="pageNumber">页码</param>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="parameters">参数（可空）</param>
/// <param name="IsUseTransaction">是否使用事务</param>
/// <returns></returns>
public PagingData ExecutePageByDataTable(int PageSize, int pageNumber, string sql, SqlParameter[] parameters, bool IsUseTransaction)
{
SqlCommand _SqlCommand = CreateSqlCommand();
try
{
_SqlCommand.CommandText = sql;
_SqlCommand.CommandType = CommandType.Text;
_SqlCommand.Parameters.Clear();
if (parameters != null && parameters.Length > 0)
{
_SqlCommand.Parameters.AddRange(parameters);
}
using (DataTable dt = new DataTable())
{
SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
if (IsUseTransaction)
{
SqlTransaction tran = _SqlCommand.Connection.BeginTransaction();
sda.SelectCommand.Transaction = tran;
try
{
sda.Fill(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new Exception(ex.Message);
}
finally
{
tran.Dispose();
}
}
else
{
sda.Fill(dt);
}
sda.Dispose();
//---分页------------------------------------------------------------
PagingData pd = new PagingData();
pd.pagenumber = pageNumber;
pd.pagesize = PageSize;
pd.total = dt.Rows.Count;
if (pageNumber < 1)
{
pageNumber = 1;
}
/*
* int minIndex = PageSize * (pageNumber - 1) + 1;
* int maxIndex = PageSize * pageNumber;
*/
int minIndex = PageSize * (pageNumber - 1); //从零开始
int maxIndex = PageSize * pageNumber - 1; //1210
int rowMaxIndex = (pd.total - 1) >= maxIndex ? maxIndex : (pd.total - 1);
DataTable pageTable = dt.Clone();
for (int i = minIndex; i <= rowMaxIndex; i++)
{
DataRow pageRow = pageTable.NewRow();
DataRow dRow = dt.Rows[i];
foreach (DataColumn column in dt.Columns)
{
pageRow[column.ColumnName] = dRow[column.ColumnName];
}
//pageRow["RowIndex"] = i + 1;
pageTable.Rows.Add(pageRow);
}
pd.rows = pageTable;
return(pd);
}
}
catch (Exception e)
{
throw e;
}
finally {
_SqlCommand.Connection.Close();
_SqlCommand.Dispose();
}
}
/// <summary>
/// 执行分页查询
/// </summary>
/// <param name="PageSize">每页大小</param>
/// <param name="pageNumber">页码</param>
/// <param name="OrderBy">排序参数。比如：“bar_sn desc,CreateTime asc”、“bar_sn asc”、“bar_sn”。</param>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="parameters">参数（可空）</param>
/// <param name="IsUseTransaction">是否使用事务</param>
/// <returns></returns>
public PagingData ExecutePaging(int PageSize, int pageNumber, string OrderBy, string sql, SqlParameter[] parameters, bool IsUseTransaction)
{
SqlCommand _SqlCommand = CreateSqlCommand();
try
{
if (pageNumber < 1)
{
pageNumber = 1;
}
if (string.IsNullOrEmpty(OrderBy))
{
throw new Exception("排序参数OrderBy不能为空。");
}
int min = PageSize * (pageNumber - 1) + 1;
int max = PageSize * pageNumber; // PageSize * (pageNumber - 1) + PageSize;
string _sql = string.Format("select ROW_NUMBER() over ( order by {0} ) RowNumber,* from ( {1} ) {2} ", OrderBy, sql, "G_" + Guid.NewGuid().ToString("N"));
_sql = string.Format("select count(*) from ({0}) {1}; ", _sql, "G_" + Guid.NewGuid().ToString("N")) /*总数据条数*/
+ string.Format(@"select {3}.* from( {0} ) {3} where {3}.RowNumber between {1} and {2} ;", _sql, min, max, "G_" + Guid.NewGuid().ToString("N"));
_SqlCommand.CommandText = _sql;
_SqlCommand.CommandType = CommandType.Text;
_SqlCommand.Parameters.Clear();
if (parameters != null && parameters.Length > 0)
{
_SqlCommand.Parameters.AddRange(parameters);
}
using (DataSet ds = new DataSet())
{
SqlDataAdapter sda = new SqlDataAdapter(_SqlCommand);
if (IsUseTransaction)
{
SqlTransaction tran = _SqlCommand.Connection.BeginTransaction();
sda.SelectCommand.Transaction = tran;
try
{
sda.Fill(ds);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw new Exception(ex.Message);
}
finally
{
tran.Dispose();
}
}
else
{
sda.Fill(ds);
}
sda.Dispose();
PagingData pd = new PagingData();
pd.pagenumber = pageNumber;
pd.pagesize = PageSize;
pd.total = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
pd.rows = ds.Tables[1];
return(pd);
}
}
catch (Exception e)
{
throw e;
}
finally {
_SqlCommand.Connection.Close();
_SqlCommand.Dispose();
}
}
