从存储过程中获取C＃asp.net中的返回值（语法问题）

I am having issues trying to get the syntax correct for my C# 2008 asp.net code. I need to get a return value (Select @@Identity) from my stored procedure

我在尝试为我的C#2008 asp.net代码获取正确的语法时遇到问题。我需要从我的存储过程中获取返回值(选择@@ Identity)

My C# code is:

我的C#代码是:

        SqlConnection conn = new SqlConnection(strConn);
        string sql = "usp_ClientProfile_Header";
        SqlCommand cmdHeader = new SqlCommand(sql, conn);

        cmdHeader.CommandType = CommandType.StoredProcedure;

        cmdHeader.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@FName"].Value = txtFName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@LName"].Value = txtLName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@EmailAddress", SqlDbType.VarChar, 100));
        cmdHeader.Parameters["@Email"].Value = txtEMail.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@Address1", SqlDbType.VarChar, 255));
        cmdHeader.Parameters["@Address1"].Value = txtAddress1.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@City"].Value = txtCity.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@State"].Value = txtState.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.VarChar, 12));
        cmdHeader.Parameters["@Postal_Code"].Value = txtZip.Text.Trim();

The next line in my code needs to be the "ParameterDirection.ReturnValue" but I can't seem to get the syntax correct.

我的代码中的下一行需要是“ParameterDirection.ReturnValue”,但我似乎无法使语法正确。

Any ideas?

4 个解决方案

#1

To capture a RETURN VALUE (returned by SQL using the RETURN({number}) syntax) use:

要捕获RETURN VALUE(使用RETURN({number})语法由SQL返回),请使用:

cmdHeader.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

Also, you should probably be using SCOPE_IDENTITY() instead of @@IDENTITY

此外,您可能应该使用SCOPE_IDENTITY()而不是@@ IDENTITY

Edit:
So your sproc would do something like:

编辑:所以你的sproc会做类似的事情:

DECLARE @NewId INTEGER
INSERT SomeTable(FieldA) VALUES ('Something')
SELECT @NewId = SCOPE_IDENTITY()
RETURN (@NewId)

And your C# code to retrieve that value would be:

并且您检索该值的C#代码将是:

int newId = cmdHeader.Parameters[@ReturnValue].value;

Edit 2:
Ok, the original question confused the issue as the "return value" is a different thing to what you're actually doing which is returning a single column resultset.

编辑2:好的,原始问题混淆了问题,因为“返回值”与您实际执行的操作不同,后者返回单个列结果集。

So, instead DON'T add a ReturnValue parameter at all. Just use ExecuteScalar() using your original SqlCommand setup as below:

因此,请勿添加ReturnValue参数。只需使用原始SqlCommand设置执行ExecuteScalar(),如下所示:

int newId = Convert.ToInt32(cmdHeader.ExecuteScalar());

#2

SqlParameter parameterReturnValue = new SqlParameter("ReturnValue", SqlDbType.Int, 4);
parameterReturnValue.Direction = ParameterDirection.ReturnValue;
cmdHeader.Parameters.Add(parameterReturnValue);

//Execute your command
cmdHeader.ExecuteNonQuery();

//Get the return value
int returnvalue = (int)myCommand.Parameters["ReturnValue"].Value;

#3

The direction is set on a SqlParameter object, for your example it should be something like:

方向是在SqlParameter对象上设置的,对于您的示例,它应该类似于:

cmdHeader.Parameters["@Postal_Code"].Direction = ParameterDirection.ReturnValue;

#4

SQL Stored Procedure:

SQL存储过程:

Assume procedure returns some id/ identity as shown below

假设过程返回一些id / identity,如下所示

SET @ReturnValue= Scope_identity() return @ReturnValue

SET @ ReturnValue = Scope_identity()返回@ReturnValue

in C# we can

在C#我们可以

SqlParameter[] param = null; param = new SqlParameter[] { new SqlParameter("@idStudent",idStudent), new SqlParameter("","") // some empty parameters for return value }

SqlParameter [] param = null; param = new SqlParameter [] {new SqlParameter(“@ idStudent”,idStudent),new SqlParameter(“”,“”)//返回值的一些空参数}

//here we can update param with return type SqlParameter parOut = new SqlParameter("@ReturnValue", 0); parOut.Direction = ParameterDirection.Output; param[param.Length - 1] = parOut;

//这里我们可以用返回类型更新param SqlParameter parOut = new SqlParameter(“@ ReturnValue”,0); parOut.Direction = ParameterDirection.Output; param [param.Length - 1] = parOut;

DataSet dsResult = con.executeSelectStoredProc("SP_NAME", param);

DataSet dsResult = con.executeSelectStoredProc(“SP_NAME”,param);

#1