如何运行存储过程（与参数 - 有返回值）从code的背后？ [英] How Run A Stored Procedure (with Parameters - Has A return Value) From Code Behind?

问题描述

我如何使用存储过程（带参数 - 有一个类型的返回值 INT ）从code后面

How can I use a stored procedure (with parameters - has a return value of type int) from code behind?

我的存储过程是这样的：

My stored procedure looks like this :

ALTER Procedure [dbo].[sp_Noskheh_SumOfTotalPay]
    @Co_ID int
AS
-----------------
Declare @Sum bigint
-----------------
BEGIN
    SELECT
       @Sum = SUM(TotalPay)
    FROM Noskheh
    WHERE
       (Co_ID = @Co_ID)

    RETURN @Sum
END

我想在code使用 @Sum 背后...

请你告诉我的方式做这件事？

Would you please show me a way for doing that ?

在此先感谢

问候

推荐答案

您需要设置一个的SqlConnection 和的SqlCommand 。如果您有code。与中端返回@Sum 语句，你需要做到这一点（定义类型RETURN_VALUE的一个参数）：

You need to set up a SqlConnection and a SqlCommand. If you have your code with the RETURN @Sum statement in the end, you need to do this (define a parameter of type RETURN_VALUE):

using(SqlConnection _conn = new SqlConnection(-your-connection-string-here))
using(SqlCommand _cmd = new SqlCommand("dbo.sp_Noskheh_SumOfTotalPay", _conn))
{  
   _cmd.CommandType = CommandType.StoredProcedure;

   _cmd.Parameters.Add(new SqlParameter("@CO_ID", SqlDbType.Int));
   _cmd.Parameters["@CO_ID"].Value = 5; // whatever value you want

   _cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.BigInt));
   _cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; 

   _conn.Open();
   _cmd.ExecuteNonQuery();

   Int64 result = Int64.Parse(_cmd.Parameters["@RETURN_VALUE"].Value);

   _conn.Close();
}

这将是一个容易得多，如果你将取代RETURN语句用一个简单的SELECT：

It would be a lot easier if you would replace that RETURN statement with a simple SELECT:

SELECT @Sum

在这种情况下，你可以使用简化版本我收到了 - 使用 .ExecuteScalar（）来检索单行的单个值从存储过程返回

In that case, you can use the simplified version I had before - using .ExecuteScalar() to retrieve the single value of the single row being returned from the stored proc:

using(SqlConnection _conn = new SqlConnection(-your-connection-string-here))
using(SqlCommand _cmd = new SqlCommand("dbo.sp_Noskheh_SumOfTotalPay", _conn))
{  
   _cmd.CommandType = CommandType.StoredProcedure;

   _cmd.Parameters.Add(new SqlParameter("@CO_ID", SqlDbType.Int));
   _cmd.Parameters["@CO_ID"].Value = 5; // whatever value you want

   _conn.Open();
   object result = _cmd.ExecuteScalar();
   _conn.Close();

   Int64 sum = Int64.Parse(result);
}

这应该打电话给你的存储过程，读你返回一个值，并将其转换成一种叫做 INT 变量之。

That should call your stored proc, read the single value you're returning, and converting it into an int variable called sum.

这篇关于如何运行存储过程（与参数 - 有返回值）从code的背后？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！