http://www.cnblogs.com/taoligong/articles/2537923.html
==============================创建存储过程==================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(500) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder varchar(400) -- 排序类型
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)
==============================调用存储过程 DAL 层==================================
public static DataSet GetList(string tblName, string fldName, int p, int pageIndex, string strWhere)
{
System.Data.SqlClient.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 = tblName;
parameters[1].Value = fldName;
parameters[2].Value = p;
parameters[3].Value = pageIndex;
parameters[4].Value = 0;
parameters[5].Value = 1;
parameters[6].Value = strWhere;
return Help.DBHelpSQL.RunProcedure("UP_GetRecordByPage", parameters, "ds");
}
==============================调用存储过程 BLL 层==================================
public static System.Data.DataSet GetList(string tblName, string fldName, int p, int pageIndex, string strWhere)
{
return DAL.OutStockDAL.GetList(tblName, fldName, p, pageIndex, strWhere);
}
==============================调用存储过程 DAL 层方法1==================================
/// <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;
}
}
==============================调用存储过程 DAL 层方法2==================================
/// <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)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
==============================三层架构 UI层=================================
private void BindList(int pageIndex)
{
string tblName = "OutOrder_Main";
string fldName = "OutOrderNumber";
LabelPageIndex.Text = Convert.ToString(pageIndex);
System.Text.StringBuilder sb = new System.Text.StringBuilder(" 1=1 ");
//if (!string.IsNullOrEmpty(this.txtNumbers.Text.Trim()))
//{
// sb.Append(" and Numbers like '%" + this.txtNumbers.Text.Trim() + "%' ");
//}
DataSet ds = BLL.OutStockOrderBLL.GetList(tblName,fldName, 10, pageIndex, sb.ToString());
RepeaterList.DataSource = ds;
RepeaterList.DataBind();
this.LabelAllCount.Text = BLL.PublicClassBLL.GetObject("count(1)", sb.ToString(), tblName).ToString();
this.LabelPages.Text = Math.Ceiling(Convert.ToDouble((Convert.ToDouble(this.LabelAllCount.Text) / Convert.ToDouble(this.LabelPageSize.Text)))).ToString();
if (LabelPages.Text == "0")
{
LabelPages.Text = "1";
}
if (LabelPageIndex.Text == "1")
{
LBHome.Enabled = false;
LBUp.Enabled = false;
}
else
{
LBHome.Enabled = true;
LBUp.Enabled = true;
}
if (LabelPageIndex.Text == LabelPages.Text)
{
LBNext.Enabled = false;
LBEnd.Enabled = false;
}
else
{
LBNext.Enabled = true;
LBEnd.Enabled = true;
}
}