C# 从asp.net中的存储过程获取返回值

Get Return Value from Stored procedure in asp.net

提问by Ahmad Abbasi

i have a stored procedure

我有一个存储过程

ALTER PROC TESTLOGIN
    @UserName varchar(50),
    @password varchar(50)
As
Begin
    declare @return int;

    set @return  = (SELECT COUNT(*) 
                    FROM CPUser 
                    WHERE UserName = @UserName 
                    AND Password = @password);

    return @return;
End

and in c#

在 C# 中

SqlConnection con = db.con;
SqlCommand cmd = new SqlCommand("TESTLOGIN", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parm = new SqlParameter("@return", SqlDbType.Int);
parm.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(parm);
cmd.Parameters.Add(new SqlParameter("@UserName", txtUserName.Text.ToString().Trim()));
cmd.Parameters.Add(new SqlParameter("@password", txtPassword.Text.ToString().Trim()));

cmd.ExecuteNonQuery();
con.Close();

int id = Convert.ToInt32(parm.Value);

but it always return 0. Please help me to solve this problem

但它总是返回 0。请帮我解决这个问题

采纳答案by Shafeeq Koorimannil

You need a parameter with Direction set to ParameterDirection.ReturnValuein code but no need to add an extra parameter in SP. Try this

您需要ParameterDirection.ReturnValue在代码中将参数设置为方向，但无需在 SP 中添加额外的参数。尝试这个

  SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
  returnParameter.Direction = ParameterDirection.ReturnValue;
  cmd.ExecuteNonQuery();

  int id = (int) returnParameter.Value;

回答by Tushar Cse

you can try this.Add the parameter as output direction and after executing the query get the output parameter value.

你可以试试这个。将参数添加为输出方向，执行查询后获取输出参数值。

  SqlParameter parmOUT = new SqlParameter("@return", SqlDbType.Int);
  parmOUT.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(parmOUT);
  cmd.ExecuteNonQuery();
  int returnVALUE = (int)cmd.Parameters["@return"].Value;

回答by Abhimanyu

Do it this way (make necessary changes in code)..

这样做（在代码中进行必要的更改）。

            SqlConnection con = new SqlConnection(GetConnectionString());
            con.Open();
            SqlCommand cmd = new SqlCommand("CheckUser", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("username", username.Text);
            SqlParameter p2 = new SqlParameter("password", password.Text);
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            SqlDataReader rd = cmd.ExecuteReader();
            if(rd.HasRows)
            {
                //do the things
            }
            else
            {
                lblinfo.Text = "abc";
            }

回答by user1990587

Procedure never returns a value.You have to use a output parameter in store procedure.

过程从不返回值。您必须在存储过程中使用输出参数。

ALTER PROC TESTLOGIN
@UserName   varchar(50),
@password   varchar(50)
@retvalue int output
 as
 Begin
    declare @return     int 
    set @return  = (Select COUNT(*) 
    FROM    CPUser  
    WHERE   UserName = @UserName AND Password = @password)

   set @retvalue=@return
  End

Then you have to add a sqlparameter from c# whose parameter direction is out. Hope this make sense.

然后你必须从c#中添加一个参数方向为out的sqlparameter。希望这是有道理的。

回答by Slick

2 things.

2件事。

• The query has to complete on sql server before the return value is sent.

• The results have to be captured and then finish executing before the return value gets to the object.

• 在发送返回值之前，查询必须在 sql server 上完成。

• 结果必须被捕获，然后在返回值到达对象之前完成执行。

In English, finish the work and then retrieve the value.

在英语中，完成工作，然后检索值。

this will not work:

这将不起作用：

                cmm.ExecuteReader();
                int i = (int) cmm.Parameters["@RETURN_VALUE"].Value;

This will work:

这将起作用：

                        SqlDataReader reader = cmm.ExecuteReader();
                        reader.Close();

                        foreach (SqlParameter prm in cmd.Parameters)
                        {
                           Debug.WriteLine("");
                           Debug.WriteLine("Name " + prm.ParameterName);
                           Debug.WriteLine("Type " + prm.SqlDbType.ToString());
                           Debug.WriteLine("Size " + prm.Size.ToString());
                           Debug.WriteLine("Direction " + prm.Direction.ToString());
                           Debug.WriteLine("Value " + prm.Value);

                        }

if you are not sure check the value of the parameter before during and after the results have been processed by the reader.

如果您不确定在读取器处理结果之前和之后检查参数值。

回答by Saleeq Mohammed

If you want to to know how to return a value from stored procedure to Visual Basic.NET. Please read this tutorial: How to return a value from stored procedure

如果您想知道如何将存储过程中的值返回到 Visual Basic.NET。请阅读本教程：如何从存储过程返回值

I used the following stored procedure to return the value.

我使用以下存储过程返回值。

CREATE PROCEDURE usp_get_count

AS
BEGIN
 DECLARE @VALUE int;

 SET @VALUE=(SELECT COUNT(*) FROM tblCar);

 RETURN @VALUE;

END
GO