关于这个网上有很多,但都不是非常完整,许多看的一头雾水。我也是根据前辈的很多资料进行了总结,做一个完整的介绍,希望大家共同学习。
首先是存储过程:
- ALTER PROCEDURE [dbo].[GetRecordFromPage]
- @tblName varchar(255), -- 表名
- @fldName varchar(255), -- 字段名
- @PageSize int = 10, -- 页尺寸
- @PageIndex int = 1, -- 页码
- @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
- @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
- @strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)
- AS
- declare @strSQL varchar(6000) -- 主语句
- declare @strTmp varchar(1000) -- 临时变量
- declare @strOrder varchar(500) -- 排序类型
- declare @str varchar(500) --字符串临时变量
- if @OrderType != 0
- begin
- set @strTmp = '<(select min'
- set @strOrder = ' order by [' + @fldName + '] desc'
- end
- else
- begin
- set @strTmp = '>(select max'
- set @strOrder = ' order by [' + @fldName +'] asc'
- end
- set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
- + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
- + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
- + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
- + @strOrder
- if @strWhere != ''
- set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
- + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
- + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
- + @fldName + '] from [' + @tblName + '] where (' + @strWhere + ') '
- + @strOrder + ') as tblTmp) and (' + @strWhere + ') ' + @strOrder
- if @PageIndex = 1
- begin
- set @strTmp = ''
- if @strWhere != ''
- set @strTmp = ' where (' + @strWhere + ') '
- set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
- + @tblName + ']' + @strTmp + ' ' + @strOrder
- end
- if @IsReCount != 0
- set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where (' + @strWhere + ') '
- exec (@strSQL)
如果是需要建立的那么就将ALTER修改为CREATE即可;
下面就介绍如何使用,我采用三层架构的方式:
首先是WEB:
- /// <summary>
- /// 查询按钮
- /// </summary>
- /// <param name="sender"></param>
- /// <param name="e"></param>
- protected void btnSearch_Click(object sender, EventArgs e)
- {
- SZBG.BLL.HS.OrdinaryHSInquires_BL bll = new SZBG.BLL.HS.OrdinaryHSInquires_BL();
- this.AspNetPager.RecordCount = bll.GetCount(this.txtStr.Text);
- this.BindData();
- }
这个是按钮的事件
- /// <summary>
- /// 绑定列表
- /// </summary>
- private void BindData()
- {
- SZBG.BLL.HS.OrdinaryHSInquires_BL bll = new SZBG.BLL.HS.OrdinaryHSInquires_BL();
- DataSet ds = bll.GetList(this.AspNetPager.PageSize, this.AspNetPager.CurrentPageIndex, this.txtStr.Text, "1");
- this.HSGridView.DataSource = ds;
- this.HSGridView.DataBind();
- }
这个是绑定到GridView的函数;
BLL层:
- /// <summary>
- /// 分页获得数据列表
- /// </summary>
- /// <param name="PageSize">每页数量</param>
- /// <param name="PageIndex">当前页索引</param>
- /// <param name="strWhere">查询字符串</param>
- /// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
- /// <returns></returns>
- public DataSet GetList(int PageSize, int PageIndex, string strWhere, string OrderType)
- {
- return dal.GetList(PageSize, PageIndex, strWhere, OrderType);
- }
DAL层:
- /// <summary>
- /// 分页获取数据列表
- /// </summary>
- /// <param name="PageSize">每页数量</param>
- /// <param name="PageIndex">当前页索引</param>
- /// <param name="strWhere">查询字符串</param>
- /// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
- /// <returns></returns>
- public DataSet GetList(int PageSize, int PageIndex, string str, string OrderType)
- {
- string keyStr = "";
- if (str != "")
- {
- keyStr = "Name like '%" + str + "%'";
- }
- SqlParameter[] parameters = {
- new SqlParameter("@tblName", SqlDbType.VarChar, 255),
- new SqlParameter("@fldName", SqlDbType.VarChar, 255),
- new SqlParameter("@PageSize", SqlDbType.Int),
- new SqlParameter("@PageIndex", SqlDbType.Int),
- new SqlParameter("@IsReCount", SqlDbType.Bit),
- new SqlParameter("@OrderType", SqlDbType.Bit),
- new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
- };
- parameters[0].Value = "HS_Code";
- parameters[1].Value = "Name";
- parameters[2].Value = PageSize;
- parameters[3].Value = PageIndex;
- parameters[4].Value = 0;
- parameters[5].Value = int.Parse(OrderType);
- parameters[6].Value = keyStr;
- return DBHelper.RunProcedure("GetRecordFromPage", parameters, "ds");
- }
其中keyStr = "Name like '%" + str + "%'"; 就是传入模糊查询的参数了
最后就是DBHelper了:
- /// <summary>
- /// 执行存储过程
- /// </summary>
- /// <param name="storedProcName">存储过程名</param>
- /// <param name="parameters">存储过程参数</param>
- /// <param name="tableName">DataSet结果中的表名</param>
- /// <returns>DataSet</returns>
- public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
- {
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- DataSet dataSet = new DataSet();
- connection.Open();
- SqlDataAdapter sqlDA = new SqlDataAdapter();
- sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
- sqlDA.Fill(dataSet, tableName);
- connection.Close();
- return dataSet;
- }
- }
- /// <summary>
- /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
- /// </summary>
- /// <param name="connection">数据库连接</param>
- /// <param name="storedProcName">存储过程名</param>
- /// <param name="parameters">存储过程参数</param>
- /// <returns>SqlCommand</returns>
- private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
- {
- SqlCommand command = new SqlCommand(storedProcName, connection);
- command.CommandType = CommandType.StoredProcedure;
- foreach (SqlParameter parameter in parameters)
- {
- if (parameter != null)
- {
- if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
- (parameter.Value == null))
- {
- parameter.Value = DBNull.Value;
- }
- command.Parameters.Add(parameter);
- }
- }
- return command;
- }
这样大体能实现功能了。
希望对大家有帮助。
转载于:https://blog.51cto.com/zhangyusong/999751