DBHelp类sql分页（自用笔记）

        public static DataSet Query(string Sql, SqlParameter[] para, int startRecord = 0, int maxRecords = 0)
        {
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(Config.ConnStr))
            {
                conn.Open();
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    SqlTransaction tran = conn.BeginTransaction();
                    da.SelectCommand = new SqlCommand(Sql, conn, tran);
                    if (para != null)
                    {
                        da.SelectCommand.Parameters.AddRange(para);
                    }
                    try
                    {
                        da.Fill(ds, "defaultName");
                        if (maxRecords > 0)
                        {
                            da.Fill(ds, startRecord * maxRecords, maxRecords, "tableCurrentRows");
                            DataTable dt = SetPageCount(ds.Tables["defaultName"].Rows.Count, maxRecords);
                            ds.Tables.Add(dt);
                            ds.Tables.Remove("defaultName");
                        }
                        tran.Commit();
                    }
                    catch (Exception e)
                    {
                        tran.Rollback();
                        throw e;
                    }
                }
            }
            return ds;
        }

        private static DataTable SetPageCount(int rowsCount, int pageSize)
        {
            DataTable dtNew = new DataTable("pageCount");
            int pageCount = 0;
            if (rowsCount % pageSize > 0)
            {
                pageCount = rowsCount / pageSize + 1;
            }
            else
            {
                pageCount = rowsCount / pageSize;
            }

            //获取列集合,添加列  
            DataColumnCollection columns = dtNew.Columns;
            columns.Add("page", typeof(int));
            //添加一行数据  
            DataRow row = dtNew.NewRow();
            row["page"] = pageCount;
            dtNew.Rows.Add(row);
            return dtNew;
        }

　　

转载于:https://www.cnblogs.com/clear-dream/p/6365094.html