SQL Server 2000 通用单表或视图查询存储过程及C#调用示例

 create proc Common
@pagesize int, --页大小
@pageindex int,--第几页
@Tbname varchar(50),--表名
@TFields varchar(500),--要查询的字段
@order varchar(20),--显示顺序
@id varchar(20), --列标识名一般为ID号
@whereCondition  varchar(500),  --执行查询的条件不带  where 关键字,
@total int output,
@PageCount int output
as
begin
declare @sqlstr nvarchar(2000)
declare @MaxOrMin char(3)
declare @OpChar char(2)

if len(@order)=3
 begin
  set @MaxOrMin = 'max'
  set @OpChar = '>='
 end
if len(@order)=4
 begin
  set @MaxOrMin = 'min'
  set @OpChar = '<='
 end

set @sqlstr='select @a = count('+@id+') from  '+@Tbname+' where '+@whereCondition
exec sp_executesql @sqlstr,N'@a int output',@total output

if @total % @pagesize = 0
 begin
  set @PageCount = @total / @pagesize
 end
else
 begin
  set @PageCount = (@total / @pagesize)+1
 end
set @sqlstr='select top '+cast(@pagesize as nvarchar)+' '+@TFields+
' from '+@Tbname+'
  where '+@id+' '+@OpChar+'
 (
  select '+@MaxOrMin+' ('+@id+')
  from
  (
   select top '+cast(((@pageindex-1)*@pagesize+1) as nvarchar)+' '+@id+' from '+@Tbname+' where '+@whereCondition+' order by '+@id+' '+@order+'
  )as tblTmp
 ) and '+@whereCondition+' order by '+@id+' '+@order
exec sp_executesql @sqlstr
end

 

 

 

将下面的方法写到C#数据库操作类中将返回一个dataset，将上面的存储过程建在SQLserver2000数据库中,以下示例是asp.net中的调用

 

 //分回一个数据集并利用分页 同时可以得到记录总数及页总数用于列表页显示  一个连接一个command对象,另外还有sqldataadapter,dataset
        public static DataSet getCurrentDataSet(string ggSelectColumns, string ggtableName, string ggwhere, int ggcurpage, int ggpagesize, string ggxuName, string ggorder, out int ggpagecount, out int ggRecordCount)
        {//可以作为一个类方法取得当前数据集

            //接收传入参数列表
            int pagesize = ggpagesize;  //接收页大小
            int curpage = ggcurpage;  //当前第几页

            string where = ggwhere;   //查询的where条件
            string xuName = ggxuName;   //排序字段一般为数据表中记录编号
            string orderName = ggorder;  //用来排序的列名 一般为表记录编号,或时间
            string SelectColumns = ggSelectColumns;   //要选择的列
            string tableName = ggtableName;  //要查询的表名

            //内部使用变量
            //int pagecount;     //符合条件分页数
            //int dblRecordCount;  //符合条件记录数

            //Response.Write("<br>" + sqlstr + "<br>");

            // 打开数据库取得数据到当前记录集
            SqlConnection con = getConnection();
            SqlDataAdapter sd = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();

            DataSet ds = new DataSet();

            cmd.CommandText = "Common";
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;

            //传入参数
            SqlParameter param = new  SqlParameter();

            param.ParameterName="@pagesize";  //页大小
            param.DbType= DbType.Int32;
            param.Direction = ParameterDirection.Input;
            param.Value = pagesize;
            cmd.Parameters.Add(param);

            SqlParameter param1 = new SqlParameter();
            param1.ParameterName = "@pageindex"; //第几页
            param1.DbType = DbType.Int32;
            param1.Direction = ParameterDirection.Input;
            param1.Value = curpage;
            cmd.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.ParameterName = "@Tbname"; //表名
            param2.DbType = DbType.String;
            param2.Direction = ParameterDirection.Input;
            param2.Value = tableName;
            cmd.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter();
            param3.ParameterName = "@TFields"; //要查询的字段
            param3.DbType = DbType.String;
            param3.Direction = ParameterDirection.Input;
            param3.Value = SelectColumns;
            cmd.Parameters.Add(param3);

            SqlParameter param4 = new SqlParameter();
            param4.ParameterName = "@order"; //排序方式  只取  asc desc
            param4.DbType = DbType.String;
            param4.Direction = ParameterDirection.Input;
            param4.Value = orderName;
            cmd.Parameters.Add(param4);

            SqlParameter param5 = new SqlParameter();
            param5.ParameterName = "@id"; //列标识名一般为ID号或时间
            param5.DbType = DbType.String;
            param5.Direction = ParameterDirection.Input;
            param5.Value = xuName;
            cmd.Parameters.Add(param5);

            SqlParameter param6 = new SqlParameter();
            param6.ParameterName = "@whereCondition"; //查询条件
            param6.DbType = DbType.String;
            param6.Direction = ParameterDirection.Input;
            param6.Value = where;
            cmd.Parameters.Add(param6);

            SqlParameter param7 = new SqlParameter();
            param7.ParameterName = "@total"; //符合条件的记录总数
            param7.DbType = DbType.Int32;
            param7.Direction = ParameterDirection.Output;
            //param7.Value = ggRecordCount;
            cmd.Parameters.Add(param7);

            SqlParameter param8 = new SqlParameter();
            param8.ParameterName = "@PageCount"; //页总数
            param8.DbType = DbType.Int32;
            param8.Direction = ParameterDirection.Output;
            //param8.Value = ggpagecount;
            cmd.Parameters.Add(param8);

            sd.SelectCommand = cmd;

            sd.Fill(ds, "zbinfo");

            ggpagecount = Convert.ToInt32(cmd.Parameters["@PageCount"].Value);
            ggRecordCount = Convert.ToInt32(cmd.Parameters["@total"].Value);

            //释放资源
            con.Close();
            con.Dispose();

            cmd.Dispose();
            sd.Dispose();

            return ds;
        }