Question

asp.net 如何执行有返回值的存储过程 通用类

Answer 1

asp.net 执行有返回值的存储过程：
1、用ParameterDirection.ReturnValue; 返回值
ALTER PROCEDURE [dbo].[CountNumber]
AS
declare @num int
select @num=count(*) from news
return @num
GO
2、用ParameterDirection.Output; 参数返回值

ALTER PROCEDURE [dbo].[CountNumber]
@num int output
AS
select @num=count(*) from news
return @num
GO
3、调用处代码：
#region 计算出数据表的记录数

/// <summary>
/// 计算出数据表的记录数
/// </summary>
/// <returns>记录数</returns>
public string CountNumber()
{

cmd = new SqlCommand("CountNumber", GetConn());
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter i= new SqlParameter("@num", SqlDbType.Int);
//i.Direction = ParameterDirection.Output;
i.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(i);
cmd.ExecuteNonQuery();

//int strReturn = i.Value.ToString(); //返回值--方法1
string num = cmd.Parameters["@num"].Value.ToString();
//返回值--方法2

conn.Close();

return num;

}

#endregion

Answer 2

create proc Proc_Test
@x int,@y int output
As
Begin
set @y=@x+2
End

--sql语句
declare @z int
exec Proc_Test 3,@z output
select @z

--在.net调用该存储过程时
static void Main(string[] args)
{
//using System.Data.SqlClient;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "data source=192.168.6.17;initial catalog=ERPOA;user id=sa;pwd=0077";
SqlCommand cmd = new SqlCommand("Proc_Test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@x", SqlDbType.Int).Value = 6;
cmd.Parameters.Add("@y", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
int outResult = Convert.ToInt32(cmd.Parameters[1].Value);
Console.WriteLine(outResult);

Console.ReadLine();
}
参数这样设置：

Answer 3

传值的时候指明返回