存储过程代码
ALTER PROCEDURE StoredProcedure3
@tablename varchar(200) , --表名
@strGetFields varchar(200) , --查询列名
@PageIndex int , --页码
@pageSize int, --页面大小
@strWhere varchar(100) , --查询条件
@strOrder varchar(100) , --排序列名
@intOrder bit = 0, --排序类型 1为升序
@CountAll bigint output --返回纪录总数用于计算页面数
AS
begin
declare @strSql varchar(500) --主语句
declare @strTemp varchar(100) --临时变量
declare @strOrders varchar(50) --排序语句
declare @table varchar(70)declare @SQL nvarchar(1000)
declare @R bigint
set @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName)
exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
set @CountAll= @R
if @intOrder = 0
begin
--为0是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否则为降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一页直接读出纪录
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end基于class SqlHelper 类中的方法:
/// <summary>
/// 执行一条sql语句/存储过程,得到返回的唯一值
/// 使用默认数据库连接
/// </summary>
/// <param name="cmdType">CommandType</param>
/// <param name="cmdText">sql语句/存储过程</param>
/// <param name="cmdParms">SqlCommand 的参数数组</param>
/// <returns>返回得到第一行第一列的值/存储过程返回值,object对象,如果没有记录返回null</returns>
public static object ExecuteScalar(CommandType cmdType,string cmdText, params SqlParameter[] cmdParms) 现在我在别的类中希望调用该存储过程 并获得@CountAll bigint output 的值 怎么写代码 ??
ALTER PROCEDURE StoredProcedure3
@tablename varchar(200) , --表名
@strGetFields varchar(200) , --查询列名
@PageIndex int , --页码
@pageSize int, --页面大小
@strWhere varchar(100) , --查询条件
@strOrder varchar(100) , --排序列名
@intOrder bit = 0, --排序类型 1为升序
@CountAll bigint output --返回纪录总数用于计算页面数
AS
begin
declare @strSql varchar(500) --主语句
declare @strTemp varchar(100) --临时变量
declare @strOrders varchar(50) --排序语句
declare @table varchar(70)declare @SQL nvarchar(1000)
declare @R bigint
set @SQL= N'select @R=count(*) from '+convert(nvarchar(200),@TableName)
exec SP_EXECUTESQL @SQL, N' @R BIGINT OUTPUT', @R OUTPUT
set @CountAll= @R
if @intOrder = 0
begin
--为0是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否则为降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一页直接读出纪录
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end基于class SqlHelper 类中的方法:
/// <summary>
/// 执行一条sql语句/存储过程,得到返回的唯一值
/// 使用默认数据库连接
/// </summary>
/// <param name="cmdType">CommandType</param>
/// <param name="cmdText">sql语句/存储过程</param>
/// <param name="cmdParms">SqlCommand 的参数数组</param>
/// <returns>返回得到第一行第一列的值/存储过程返回值,object对象,如果没有记录返回null</returns>
public static object ExecuteScalar(CommandType cmdType,string cmdText, params SqlParameter[] cmdParms) 现在我在别的类中希望调用该存储过程 并获得@CountAll bigint output 的值 怎么写代码 ??
{
string sp=" StoredProcedure3";
using(SqlConnection conn=Connection.GetConnection())
{
using(SqlCommand comm=new SqlCommand(sp,conn))
{
comm.Paramerters.Add("@tablename",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@strGetFields",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@PageIndex ",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@pageSize",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@strWhere",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@strOrder",SqlDataType.VarChar).Value=值;
comm.Paramerters.Add("@intOrder",SqlDataType.VarChar).Value=值;
SqlParameter out_param=new SqlParameter("@CountAll");
out_param.Direction=设置参数类型.........
要回去了没办法了,下面应该很容易了
获取参数返回值
out_param["("@CountAll"]
}
}}
通过向 方法 public static object ExecuteScalar(CommandType cmdType,string cmdText, params SqlParameter[] cmdParms)
调用 来使用这个存储过程 方法调用时候参数应该怎么写的
{
string ptr = "StoredProcedure3";
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@tablename ",SqlDbType.VarChar,200),new SqlParameter("@strGetFields ",SqlDbType.VarChar,200),
new SqlParameter("@PageIndex ",SqlDbType.Int),new SqlParameter("@pageSize ",SqlDbType.Int),
new SqlParameter("@strWhere",SqlDbType.VarChar,100),new SqlParameter("@strOrder ",SqlDbType.VarChar,100),
new SqlParameter("@intOrder ",SqlDbType.Bit), new SqlParameter("@CountAll tOrder ",SqlDbType.BigInt)};
parms[0].Value = 值;
parms[1].Value = 值;
parms[2].Value = 值;
parms[4].Value = 值;
parms[5].Value = 值;
parms[6].Value = 值;
parms[7].Value = 值;
CommandType cmdType= CommandType.StoredProcedure;
int c = sql.ExecuteTransIntExecuteScalarParams(cmdType, ptr, parms);
return c; }private static string strConnection = "Data Source=.;" + "Initial Catalog=friends;" + "Integrated Security=SSPI;";
public static int ExecuteTransIntExecuteScalarParams(CommandType cmdType, string cmdText, SqlParameter[] pParams)
{
SqlConnection sqlcon = new SqlConnection(strConnection);
SqlCommand sqlcmd = new SqlCommand();
int Result;
try
{
sqlcon.Open();
sqlcmd.Connection = sqlcon;
sqlcmd.CommandType =cmdType ;
sqlcmd.CommandText = cmdText;
for (int intCounter = 0; intCounter < pParams.GetLength(0); intCounter++)
{
sqlcmd.Parameters.Add(pParams[intCounter]);
}
Result = Convert.ToInt32(sqlcmd.ExecuteScalar());
}
catch (SqlException)
{
return -1;
}
finally
{
sqlcmd.Dispose();
sqlcon.Close();
sqlcon.Dispose();
}
return Result;
}
parms[7].Value = 值;参数是一个返回参数 值 怎么写?
parms[7].Direction = ParameterDirection.Output;
把参数类型改下
GO
/****** Object: StoredProcedure [dbo].[Pagination] Script Date: 05/09/2009 12:46:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[Pagination]
@Page int = 1, -- 当前页码
@PageSize int = 10, -- 每页记录条数(页面大小)
@Table nvarchar(500), -- 表名或视图名,甚至可以是嵌套SQL:(Select * From Tab Where ID>1000) Tab
@Field nvarchar(200) = '*', -- 返回记录集字段名,","隔开,默认是"*"
@OrderBy nvarchar(100) = 'ID ASC', -- 排序规则
@Filter nvarchar(500), -- 过滤条件
@MaxPage smallint output, -- 执行结果 -1 error, 0 false, maxpage true
@TotalRow int output, -- 记录总数 /* 2007-07-12 22:11:00 update */
@Descript varchar(100) output -- 结果描述
AS
BEGIN
Set ROWCOUNT @PageSize;Set @Descript = 'successful';
-------------------参数检测----------------
IF LEN(RTRIM(LTRIM(@Table))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'table name is empty';
Return;
EndIF LEN(RTRIM(LTRIM(@OrderBy))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'order is empty';
Return;
EndIF ISNULL(@PageSize,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page size error';
Return;
EndIF ISNULL(@Page,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page error';
Return;
End
-------------------检测结束----------------Begin Try
-- 整合SQL
Declare @SQL nvarchar(4000), @Portion nvarchar(4000),@Sql_Max nvarchar(4000); Set @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM FROM ' + @Table; Set @Portion = @Portion + (CASE WHEN LEN(@Filter) >= 1 THEN (' Where ' + @Filter + ') AS tab') ELSE (') AS tab') END); Set @SQL = 'Select TOP(' + CAST(@PageSize AS nvarchar(8)) + ') ' + @Field + ' FROM (Select ' + @Field + ',' + @Portion; Set @SQL = @SQL + ' Where tab.ROWNUM > ' + CAST((@Page-1)*@PageSize AS nvarchar(8)); -- 整合SQL
Set @Sql_Max = 'Set @Rows = (Select MAX(ROWNUM) FROM (Select' + @Portion + ')'; -- 执行SQL, 取最大页码
Execute sp_executesql @Sql_Max, N'@Rows int output', @TotalRow output; -- 执行SQL, 取当前页记录集
Execute(@SQL);
-------------------------------------------------------------------- Set @MaxPage = (CASE WHEN (@TotalRow % @PageSize)<>0 THEN (@TotalRow / @PageSize + 1) ELSE (@TotalRow / @PageSize) END);
End Try
Begin Catch
-- 捕捉错误
Set @MaxPage = -1;
Set @Descript = 'error line: ' + CAST(ERROR_LINE() AS varchar(8)) + ', error number: ' + CAST(ERROR_NUMBER() AS varchar(8)) + ', error message: ' + ERROR_MESSAGE();
Return;
End Catch;-- 执行成功
Return;
END
public static DataSet PagiNation(int PageIndex, string TableName, string Fields, string OrderBy, string Filter, ref int PageSize, ref int TotalSize)
{
SqlParameter[] parameters = new SqlParameter[9]; parameters[0] = new SqlParameter("@Page", SqlDbType.Int, 4);
parameters[1] = new SqlParameter("@PageSize", SqlDbType.Int, 4);
parameters[2] = new SqlParameter("@Table", SqlDbType.VarChar, 500);
parameters[3] = new SqlParameter("@Field", SqlDbType.VarChar, 500);
parameters[4] = new SqlParameter("@OrderBy", SqlDbType.VarChar, 500);
parameters[5] = new SqlParameter("@Filter", SqlDbType.VarChar, 500);
parameters[6] = new SqlParameter("@MaxPage", SqlDbType.Int, 4);
parameters[6].Direction = ParameterDirection.Output;
parameters[7] = new SqlParameter("@TotalRow", SqlDbType.Int, 4);
parameters[7].Direction = ParameterDirection.Output;
parameters[8] = new SqlParameter("@Descript", SqlDbType.VarChar, 1000);
parameters[8].Direction = ParameterDirection.Output; parameters[0].Value = PageIndex;
parameters[2].Value = TableName;
parameters[3].Value = Fields;
parameters[4].Value = OrderBy;
parameters[5].Value = Filter; SDERP.SQLServerDAL.SysManage.Sys_Parameters model = new SDERP.SQLServerDAL.SysManage.Sys_Parameters().GetModel("PageSize"); if (model == null)
parameters[1].Value = 20;
else
parameters[1].Value = model.Value;
DataSet ds = DbHelperSQL.RunProcedure("Pagination", parameters, ""); if (parameters[7].Value.ToString() == "")
TotalSize = 0;
else
TotalSize = (int)parameters[7].Value;
PageSize = Convert.ToInt32(parameters[1].Value); return ds;
}
{ SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}可参考petshop
参考
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
new SqlParameter("@tablename ",SqlDbType.VarChar,200),new SqlParameter("@strGetFields ",SqlDbType.VarChar,200),
new SqlParameter("@PageIndex ",SqlDbType.Int),new SqlParameter("@pageSize ",SqlDbType.Int),
new SqlPara…
SqlHelper.ExecuteScalar(CommandType.cmdType, "StoredProcedure3", parms);