ASP.NET怎么样传参数给存储过程？
create PROCEDURE [dbo].[Proc_Qubernet]这样的存储过程，在.net后台怎么传入参数？？
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@IsCount bit = 0, -- 返回记录总数, 非0 值则返回
@strWhere varchar(2000) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder 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 @IsCount != 0
set @strSQL = 'select count(' + @fldName + ') as Total from [' + @tblName + ']'
exec (@strSQL)
GO
/// <summary>这是原本的，我现在想加入一个查询参数应该怎么写？？
/// 分页存储过程
/// </summary>
/// <param name="tbName">查询表名</param>
/// <param name="keyorder">查询排序</param>
/// <param name="keymain">查询主键</param>
/// <param name="columns">查询字段</param>
/// <param name="strWhere">查询条件</param>
/// <param name="PageSize">每页数量</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RowsCount">记录总数</param>
/// <param name="PageCount">分页总数</param>
/// <returns></returns>
public static DataSet Proc_DataSet(string tbName, string keyorder, string keymain, string columns, string strWhere, int PageSize, int PageIndex, int RowsCount, int PageCount)
{
SqlParameter[] sp ={
new SqlParameter("@tbName",tbName), // -- 查询表名
new SqlParameter("@keyorder",keyorder), //-- 查询排序
new SqlParameter("@keymain",keymain), // -- 查询主键
new SqlParameter("@columns",columns), // -- 查询字段
new SqlParameter("@strWhere",strWhere), //-- 查询条件
new SqlParameter("@PageSize",PageSize), //-- 页大小
new SqlParameter("@PageIndex",PageIndex), //--当前页
new SqlParameter("@RowsCount",RowsCount), //--记录总数
new SqlParameter("@PageCount",PageCount) // ----页面总数
};
return Proc_GetDataSet("Proc_Qubernet", true, sp);
}
public static DataTable GetTestData(string tabname, int pagesize, int pageindex, string fieldname, bool IsCount)
{
int a = 0;
if (IsCount)
{
a = 1;
}
using (SqlConnection conn = new SqlConnection("server=.;Database=JFData;uid=sa;pwd=1"))
{
using (SqlDataAdapter sda = new SqlDataAdapter("exec Proc_Qubernet '" + tabname + "','" + fieldname + "'," + pagesize + "," + pageindex + ",0," + a + "", conn))
{
conn.Open();
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
