/// <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(); } }