C#获取存储过程的返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/706361/
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 return value from stored procedure in C#
提问by GurdeepS
I have the following query:
我有以下查询：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output
AS
SET @Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
RETURN @b
GO
This compiles perfectly fine.
这编译得很好。
In C#, I want to execute this query and get the return value.
在 C# 中，我想执行此查询并获取返回值。
My code is as below:
我的代码如下：
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
Note: Exception handling cut to keep the code short. Everytime I get to the last line, null is returned. What's the logic error with this code?
注意：为了保持代码简短，异常处理被删减。每次到达最后一行时，都会返回 null。这段代码的逻辑错误是什么？
Thanks
谢谢
回答by Mehrdad Afshari
retval.Direction = ParameterDirection.Output;
ParameterDirection.ReturnValueshould be used for the "return value" of the procedure, not output parameters. It gets the value returned by the SQL RETURNstatement (with the parameter named @RETURN_VALUE).
ParameterDirection.ReturnValue应该用于过程的“返回值”，而不是输出参数。它获取 SQLRETURN语句返回的值（带有名为 的参数@RETURN_VALUE）。
Instead of RETURN @byou should SET @b = something
而不是RETURN @b你应该SET @b = something
By the way, return value parameter is always int, not string.
顺便说一句，返回值参数始终是int，而不是字符串。
回答by Joel Coehoorn
Mehrdad makes some good points, but the main thing I noticed is that you never run the query...
Mehrdad 提出了一些很好的观点，但我注意到的主要事情是你从不运行查询......
SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
回答by Martin Brown
You say your SQL compiles fine, but I get: Must declare the scalar variable "@Password".
你说你的 SQL 编译得很好，但我得到：必须声明标量变量“@Password”。
Also you are trying to return a varchar (@b) from your stored procedure, but SQL Server stored procedures can only return integers.
此外，您试图从存储过程返回 varchar (@b)，但 SQL Server 存储过程只能返回整数。
When you run the procedure you are going to get the error:
当您运行该过程时，您将收到以下错误：
'Conversion failed when converting the varchar value 'x' to data type int.'
'将 varchar 值 'x' 转换为数据类型 int 时转换失败。
回答by Sunny Milenov
This SP looks very strange. It does not modify what is passed to @b. And nowhere in the SP you assign anything to @b. And @Password is not defined, so this SP will not work at all.
这个SP看起来很奇怪。它不会修改传递给@b 的内容。在 SP 中没有任何地方为 @b 分配任何内容。而且@Password 没有定义，所以这个 SP 根本不起作用。
I would guess you actually want to return @Password, or to have SET @b = (SELECT...)
我猜你真的想返回@Password，或者有 SET @b = (SELECT...)
Much simpler will be if you modify your SP to (note, no OUTPUT parameter):
如果您将 SP 修改为（注意，没有 OUTPUT 参数），则会简单得多：
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Validate] @a varchar(50)
AS
SELECT TOP 1 Password FROM dbo.tblUser WHERE Login = @a
Then, your code can use cmd.ExecuteScalar, and receive the result.
然后，您的代码可以使用 cmd.ExecuteScalar，并接收结果。
回答by Alan Hymanson
I was having tons of trouble with the return value, so I ended up just selecting stuff at the end.
我在返回值方面遇到了很多麻烦，所以我最终只选择了最后的东西。
The solution was just to select the result at the end and return the query result in your functinon.
解决方案只是在最后选择结果并在您的功能中返回查询结果。
In my case I was doing an exists check:
在我的情况下，我正在做一个存在检查：
IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE @RoleName = RoleName))
SELECT 1
ELSE
SELECT 0
Then
然后
using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RoleExists";
return (int) cmd.ExecuteScalar()
}
You should be able to do the same thing with a string value instead of an int.
您应该能够使用字符串值而不是 int 来执行相同的操作。
回答by Manatherin
This is building on Joel'sand Mehrdad'sanswers: you're never binding the parameter of the retvalto the sqlcommand. You need a
这是建立在Joel和Mehrdad 的回答之上的：您永远不会将 的参数绑定retval到sqlcommand. 你需要一个
sqlcomm.Parameters.Add(retval);
and to make sure you're running the command
并确保您正在运行命令
sqlcomm.ExecuteNonQuery();
I'm also not sure why you have 2 return value strings (returnValueand retunvalue).
我也不确定为什么你有 2 个返回值字符串（returnValue和retunvalue）。
回答by Mehran Sarrafi
You have mixed up the concept of the Return Value and Output variable. 1- Output Variable:
您混淆了返回值和输出变量的概念。1- 输出变量：
Database----->:
create proc MySP
@a varchar(50),
@b varchar(50) output
AS
SET @Password =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
C# ----->:
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;//This is optional because Input is the default
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter outputval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
outputval .Direction = ParameterDirection.Output//NOT ReturnValue;
string outputvalue = sqlcomm.Parameters["@b"].Value.ToString();
回答by Sarath Avanavu
Suppose you need to pass Usernameand Passwordto Stored Procedureand know whether login is successful or not and check if any error has occurred in Stored Procedure.
假设您需要传递UsernameandPassword到Stored Procedure并知道登录是否成功并检查Stored Procedure 中是否发生了任何错误。
public bool IsLoginSuccess(string userName, string password)
{
try
{
SqlConnection SQLCon = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlConnector"].ConnectionString);
SqlCommand sqlcomm = new SqlCommand();
SQLCon.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.CommandText = "spLoginCheck"; // Stored Procedure name
sqlcomm.Parameters.AddWithValue("@Username", userName); // Input parameters
sqlcomm.Parameters.AddWithValue("@Password", password); // Input parameters
// Your output parameter in Stored Procedure
var returnParam1 = new SqlParameter
{
ParameterName = "@LoginStatus",
Direction = ParameterDirection.Output,
Size = 1
};
sqlcomm.Parameters.Add(returnParam1);
// Your output parameter in Stored Procedure
var returnParam2 = new SqlParameter
{
ParameterName = "@Error",
Direction = ParameterDirection.Output,
Size = 1000
};
sqlcomm.Parameters.Add(returnParam2);
sqlcomm.ExecuteNonQuery();
string error = (string)sqlcomm.Parameters["@Error"].Value;
string retunvalue = (string)sqlcomm.Parameters["@LoginStatus"].Value;
}
catch (Exception ex)
{
}
return false;
}
Your connection string in Web.Config
您的连接字符串 Web.Config
<connectionStrings>
<add name="SqlConnector"
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Databasename;User id=yourusername;Password=yourpassword"
providerName="System.Data.SqlClient" />
</connectionStrings>
And here is the Stored Procedurefor reference
这是存储过程供参考
CREATE PROCEDURE spLoginCheck
@Username Varchar(100),
@Password Varchar(100) ,
@LoginStatus char(1) = null output,
@Error Varchar(1000) output
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN
SET @Error = 'None'
SET @LoginStatus = ''
IF EXISTS(SELECT TOP 1 * FROM EMP_MASTER WHERE EMPNAME=@Username AND EMPPASSWORD=@Password)
BEGIN
SET @LoginStatus='Y'
END
ELSE
BEGIN
SET @LoginStatus='N'
END
END
END TRY
BEGIN CATCH
BEGIN
SET @Error = ERROR_MESSAGE()
END
END CATCH
END
GO
回答by Miles
There are two things to fix about this. First set up the stored procedure to store the value in the output ( not return ) parameter.
有两件事要解决这个问题。首先设置存储过程以将值存储在输出（而不是返回）参数中。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) output
AS
SET @b =
(SELECT Password
FROM dbo.tblUser
WHERE Login = @a)
RETURN
GO
This will but the password into @b and you will get it as a return parameter. Then to get it in your C# do this:
这会将密码输入@b，您将获得它作为返回参数。然后要在您的 C# 中获取它，请执行以下操作：
SqlConnection SqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyLocalSQLServer"].ConnectionString.ToString());
System.Data.SqlClient.SqlCommand sqlcomm = new System.Data.SqlClient.SqlCommand("Validate", SqlConn);
string returnValue = string.Empty;
try
{
SqlConn.Open();
sqlcomm.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@a", SqlDbType.VarChar, 50);
param.Direction = ParameterDirection.Input;
param.Value = Username;
sqlcomm.Parameters.Add(param);
SqlParameter retval = new SqlParameter("@b", SqlDbType.VarChar, 50);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.Parameters.Add(retval);
sqlcomm.ExecuteNonQuery();
SqlConn.Close();
string retunvalue = retval.Value.ToString();
}
回答by jbooker
When you use
当你使用
cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
you must then ensure your stored procedure has
然后您必须确保您的存储过程具有
return @RETURN_VALUE;
at the end of the stored procedure.
在存储过程的最后。
