从存储过程中获取 C# asp.net 中的返回值（语法问题）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2342806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting a Return Value in C# asp.net from a stored procedure (syntax issue)
提问by user279521
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?
有任何想法吗？
采纳答案by AdaTheDev
To capture a RETURN VALUE (returned by SQL using the RETURN({number}) syntax) use:
要捕获返回值（由 SQL 使用 RETURN({number}) 语法返回），请使用：
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'Tadd a ReturnValue parameter at all. Just use ExecuteScalar()using your original SqlCommand setup as below:
因此，根本不要添加 ReturnValue 参数。只需使用ExecuteScalar()使用您的原始 SqlCommand 设置，如下所示：
int newId = Convert.ToInt32(cmdHeader.ExecuteScalar());
回答by STW
The direction is set on a SqlParameterobject, for your example it should be something like:
方向设置在一个SqlParameter对象上，对于您的示例，它应该是这样的：
cmdHeader.Parameters["@Postal_Code"].Direction = ParameterDirection.ReturnValue;
回答by madatanic
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;
回答by user1421730
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()return @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("","") // some empty parameters for return value}
//here we can update param with return typeSqlParameter parOut = new SqlParameter("@ReturnValue", 0);parOut.Direction = ParameterDirection.Output;param[param.Length - 1] = parOut;
//here we can update param with return typeSqlParameter 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);
