c#学习之-c#通过sql存储过程实现分页

c#学习之-c#通过sql存储过程实现分页

分页的重要性不言而喻，本文将相信的讲解c#通过sql进行分页的实现（三层）

数据库表如下

现在编写sql分页通用存储过程

if exists (select 1 
		   from sysobjects 
		   where [name]='proc_CommonPage' 
		   and [type]='p')
   drop proc proc_CommonPage--检查是否存在该存储过程 如果存在删除
go
create proc proc_CommonPage--创建存储过程

( @pageSize int,--分页大小（每一页多少）
  @pageIndex int,--分页索引（页数）
  @tablename varchar(50),--表名
  @prikey varchar(20),--查询关键字（以什么排序）
  @condtional varchar(2000),--查询条件
  @sort varchar(4),--排序
  @pageCount int out --总页数
  
)
as
--构建查询的sql语句
declare @sql nvarchar(1000);--查询的sql
declare @recordCount int,--数据总数
        @startIndex int,--分页的启示行  
	@endIndex int--分页的结束行

set @sql='select @recordCount=count(*) from '+@tablename+' where 1=1 ';
if	@condtional is not null and @condtional<> N''--查询条件不为空 也不为空格
begin
set @sql=@sql+@condtional
end
--print @sql
exec sp_executesql @sql,N'@recordCount int out',@recordCount out;--执行系统sp_executesql存储过程

print @recordCount
 --计算最大的分页数--
 set @pageCount = CEILING(@recordcount*1.0/@pagesize);--最大的分页数 进一法取值
 if @pageindex > @pageCount   --如果分页的索引大于最大的索引
 begin
    set @pageindex = @pageCount;
 end
 --构建分页的查询的起始行和结束行
 set @startIndex = (@pageindex-1)*@pagesize +1;--计算起始行
 set @endIndex = @pageindex *@pagesize
 --构建分页的sql
 set @sql =''
  print @pagecount
 set @sql = 'Select row_number()  over (order by '+@prikey+ ' ' +@sort+') as rowid,*  from '+@tablename+' where 1=1';
 if @condtional is not null and @condtional <> N''   --如果查询条件不为空
 begin
    set @sql = @sql+ @condtional;
 end
 set @sql = 'Select * From ('+@sql+') as tc where tc.rowid between '+convert(varchar(5),@startIndex)+' and '+convert(varchar(5),@endIndex)+'';
 exec (@sql)

 go

在这个存储过程中有2个地方需要注意

第一   sp_executesql

执行可以多次重复使用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。

(个人理解)主要可以执行一个sql语句 并且可以传出参数 用来作为下一条语句的条件 它的参数必须是Unicode字符格式

第二   row_number()

select row_number() over (order by empid asc) as rowid 像表中插入一列带有顺序的id

UI层

EmployedBll eb = new EmployedBll();        int pageSize = 6;        int pageIndex = 1;        int pageCount = 0;        string pageConditiona = "";

 List<TbEmployed> list = eb.GetListByPage(pageSize, pageIndex, pageConditiona, pageSort, out pageCount);
            this.gridControl1.DataSource = list;

BLL层

        private string pageSort = "asc";

 

public List<TbEmployed> GetListByPage(int PageSize, int pageIndex, string seletstr,string pageSort, out int pageCount)
        {
            return ed.GetListByPage(PageSize, pageIndex, seletstr,pageSort, out pageCount);
        }

 

DAL层

 

 public List<TbEmployed> GetListByPage(int pageSize, int pageIndex, string seletcondtional,string pageSort,out int pagecount)
        {

                #region MyRegion
                SqlParameter parIndex = new SqlParameter("@pageIndex", SqlDbType.Int);
                parIndex.Value = pageIndex;
                SqlParameter parSize = new SqlParameter("@PageSize", SqlDbType.Int);
                parSize.Value = pageSize;
                SqlParameter parTname = new SqlParameter("@tablename", SqlDbType.VarChar);
                parTname.Value = "tb_Employed";
                SqlParameter parPrikey = new SqlParameter("@prikey", SqlDbType.VarChar);
                parPrikey.Value = "empid";
                SqlParameter parSort = new SqlParameter("@pageSort", SqlDbType.VarChar);
                parSort.Value = pageSort;SqlParameter parCondtional = new SqlParameter("@condtional", SqlDbType.VarChar);
                parCondtional.Value =seletcondtional;
                SqlParameter parCount = new SqlParameter("@pagecount", SqlDbType.Int);
                parCount.Direction = ParameterDirection.Output;//传出参数      Direction    
                SqlParameter[] pars = { parSize, parIndex, parTname, parPrikey, parCondtional,parSort, parCount };
                string sql = " exec proc_CommonPage @pageSize,@pageIndex,@tablename,@prikey,@condtional,@pageSort,

		@pagecount out";
                DataTable dt = _sqlhelper.GetDataSet(sql, pars).Tables[0];
            
                //  DataTable dt = _sqlhelper.GetDataTable(sql, pars);
                pagecount = (int)parCount.Value;//把结构赋值给传出参数Pagecount   
                List<TbEmployed> list = GetListModel(dt); 
                #endregion

                #region MyRegion
            //string tablename = "tb_Employed";
            //string prikey = "empid";
            //string condtional = "";
            //DataTable dt =
            //    _sqlhelper.GetDataSet(
            //        @"exec proc_CommonPage @pageIndex,@PageSize,@tablename,@prikey,@condtional,@pageCount out",
            //        new SqlParameter("@pageIndex", pageIndex),
            //        new SqlParameter("@PageSize", PageSize),
            //        new SqlParameter("@tablename", tablename),
            //        new SqlParameter("@prikey", prikey),
            //        new SqlParameter("@condtional", condtional),
            //        new SqlParameter("@pageCount", pageCount)).Tables[0];
            //pageCount = (int)parCount.Value;//把结构赋值给传出参数Pagecount
            //List<TbEmployed> list = GetListModel(dt);  
            #endregion
                return list;

<pre class="csharp" name="code"> private List<TbEmployed> GetListModel(DataTable dt)
        {
            List<TbEmployed> list = new List<TbEmployed>();
            foreach (DataRow dr in  dt.Rows)
            {
                TbEmployed te = new TbEmployed();
                te.empid = (int) dr["empid"];
                te.empname = dr["empname"].ToString();
                te.roleid = (int) dr["roleid"];
                te.empsex = dr["empsex"].ToString();
                te.empage = (int)dr["empage"];
                te.emplogin = dr["emplogin"].ToString();
                te.empwd = dr["empwd"].ToString();            
                list.Add(te);
            }
            return list;
        }

AbstractSqlHelper

  public SqlCommand GetCommand(string sql, params SqlParameter[] pars)
        {
            GetConnection();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;         
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            return cmd;
        
        }

 public void GetConnection()
        {    //private static string conStr = ConfigurationManager.ConnectionStrings["sqlstr"].ConnectionString;
             string strCon = ConfigurationManager.ConnectionStrings["sqlstr"].ConnectionString;
            conn = new SqlConnection(strCon);
            conn.Open();
            if (conn.State!=ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }

 

 public SqlCommand GetCommand(string sql, params SqlParameter[] pars)
        {
            GetConnection();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;         
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
            return cmd;
        
        }

 

SqlHelper

 public override DataSet GetDataSet(string sql, params SqlParameter[] parameters)
        {
            SqlCommand cmd = GetCommand(sql, parameters);          
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            sda.Fill(dataset);
            return dataset;  
        }

 

以上是实习分页的全部代码

model省略了的

表名 关键字也可以通过参数传入

代码间调用就是普通的调用 数据连接的打开在抽象类中

连接字符串在配置文件中

以winfrom 为教材

希望大家批评指教