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

我有以下疑问:

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

这个编译非常好。我想执行这个查询并得到返回值。我的代码如下:

  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;

注意: 异常处理切断以保持代码简短。每当我到达最后一行时，返回 null。这段代码的逻辑错误是什么？

337547 次浏览
小开 
retval.Direction = ParameterDirection.Output;

ParameterDirection.ReturnValue应该用于过程的“返回值”，而不是输出参数。它获取 SQLRETURN语句返回的值(参数名为 @RETURN_VALUE)。

你应该用 SET @b = something代替 RETURN @b

顺便说一下，返回值参数始终是 int，而不是字符串。

小开

梅赫达说得有道理，但我注意到的主要问题是你 永远不要运行查询..。

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.ReturnValue;
sqlcomm.ExecuteNonQuery(); // MISSING
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
小开

你说你的 SQL 编译很好，但是我得到: 必须声明标量变量“@Password”。

此外，您还尝试从存储过程返回 varchar (@b) ，但是 SQLServer 存储过程只能返回整数。

当你运行这个过程时，你会得到这个错误:

‘将 varchar 值‘ x’转换为数据类型 int 时，转换失败。’

小开

这个 SP 看起来很奇怪。它不修改传递给@b 的内容。在 SP 里，你没有给@b 分配任何东西。而且@Password 没有定义，所以这个 SP 根本不能工作。

我猜您实际上想返回@Password，或者使用 SET@b = (SELECT...)

如果您将 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

然后，您的代码可以使用 cmd.ExecuteScalar 并接收结果。

小开

我在返回值方面遇到了很多麻烦，所以最后我只是选择了一些东西。

解决方案只是在最后选择结果并在函数中返回查询结果。

对我来说，我是在做一个存在性检查:

IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE @RoleName = RoleName))
SELECT 1
ELSE
SELECT 0

然后

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RoleExists";
return (int) cmd.ExecuteScalar()
}

您应该能够使用字符串值而不是 int 来做同样的事情。

小开

这是建立在 乔尔的和 Mehrdad 的答案之上的: 您永远不会将 retval的参数绑定到 sqlcommand。你需要一个

sqlcomm.Parameters.Add(retval);

并确保您正在运行命令

sqlcomm.ExecuteNonQuery();

我也不知道为什么有2个返回值字符串(returnValue和 retunvalue)。

小开

您混淆了 Return Value 和 Output 变量的概念。 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();
小开

假设您需要将 Username和 Password传递给 存储过程，并且知道登录是否成功，并检查 存储过程中是否发生了错误。

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;
}

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>

这是 存储过程作为参考

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
小开

有两件事需要解决。首先设置存储过程，将值存储在 output (not return)参数中。

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

这将把密码输入到@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();
}
小开

当你使用

cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

然后必须确保存储过程具有

return @RETURN_VALUE;

在存储过程结束时。

小开

这里有很多问题:

  1. 这是不可能的。您正在尝试返回一个 varchar。存储 过程返回值只能是整数表达式 正式申报文件: Https://msdn.microsoft.com/en-us/library/ms174998.aspx.
  2. 你的 sqlcomm没有被执行，你必须打电话 为了执行你的命令。

下面是一个使用 OUTPUT 参数的解决方案:

  • WindowsServer2012
  • .NET v4.0.30319
  • C # 4.0
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Validate]
@a varchar(50),
@b varchar(50) OUTPUT
AS
BEGIN
DECLARE @b AS varchar(50) = (SELECT Password FROM dbo.tblUser WHERE Login = @a)
SELECT @b;
END

SqlConnection SqlConn = ...
var sqlcomm = new 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 output = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
ouput.Direction = ParameterDirection.Output;


sqlcomm.ExecuteNonQuery(); // This line was missing


returnValue = output.Value.ToString();


// ... the rest of code


} catch (SqlException ex) {
throw ex;
}
小开

也许这个能帮上忙。

数据库脚本:

USE [edata]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[InsertNewUser](
@neuname NVARCHAR(255),
@neupassword NVARCHAR(255),
@neuposition NVARCHAR(255)
)


AS


BEGIN


BEGIN TRY


DECLARE @check INT;


SET @check = (SELECT count(eid) FROM eusers WHERE euname = @neuname);


IF(@check = 0)


INSERT INTO  eusers(euname,eupassword,eposition)
VALUES(@neuname,@neupassword,@neuposition);


DECLARE @lastid INT;


SET @lastid = @@IDENTITY;


RETURN @lastid;




END TRY




BEGIN CATCH


SELECT ERROR_LINE() as errline,
ERROR_MESSAGE() as errmessage,
ERROR_SEVERITY() as errsevirity


END CATCH


END

应用程序配置文件:

<?xml version="1.0" encoding="utf-8"?>
<configuration>


<appSettings>
<add key="conStr" value="Data Source=User\SQLEXPRESS;Initial Catalog=edata;Integrated Security=True"/>
</appSettings>
</configuration>

数据访问层(DAL) :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class DAL
{
public static SqlConnection conn;


static DAL()
{




conn = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());
conn.Open();




}




}
}

业务逻辑层(BLL) :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DAL;
namespace BLL
{
public static class BLL
{




public static int InsertUser(string lastid, params SqlParameter[] coll)
{


int lastInserted = 0;


try
{




SqlCommand comm = new SqlCommand();


comm.Connection = DAL.DAL.conn;




foreach (var param in coll)
{


comm.Parameters.Add(param);


}


SqlParameter lastID = new SqlParameter();
lastID.ParameterName = lastid;
lastID.SqlDbType = SqlDbType.Int;
lastID.Direction = ParameterDirection.ReturnValue;


comm.Parameters.Add(lastID);


comm.CommandType = CommandType.StoredProcedure;


comm.CommandText = "InsertNewUser";


comm.ExecuteNonQuery();


lastInserted = (int)comm.Parameters[lastid].Value;


}


catch (SqlException ex)
{




}


finally {


if (DAL.DAL.conn.State != ConnectionState.Closed) {


DAL.DAL.conn.Close();
}


}


return lastInserted;


}


}
}

实施方法:

BLL.BLL.InsertUser("@lastid",new SqlParameter("neuname","Ded"),
new SqlParameter("neupassword","Moro$ilka"),
new SqlParameter("neuposition","Moroz")
);
小开

当不使用 select 语句从存储过程返回值时。 我们需要使用“ Parameter terDirection.return nValue”和“ ExecuteScalar”命令来获取值。

CREATE PROCEDURE IsEmailExists
@Email NVARCHAR(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


-- Insert statements for procedure here
IF EXISTS(SELECT Email FROM Users where Email = @Email)
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END

在 C # 中

GetOutputParaByCommand("IsEmailExists")


public int GetOutputParaByCommand(string Command)
{
object identity = 0;
try
{
mobj_SqlCommand.CommandText = Command;
SqlParameter SQP = new SqlParameter("returnVal", SqlDbType.Int);
SQP.Direction = ParameterDirection.ReturnValue;
mobj_SqlCommand.Parameters.Add(SQP);
mobj_SqlCommand.Connection = mobj_SqlConnection;
mobj_SqlCommand.ExecuteScalar();
identity = Convert.ToInt32(SQP.Value);
CloseConnection();
}
catch (Exception ex)
{


CloseConnection();
}
return Convert.ToInt32(identity);
}

我们使用上面的 c # 函数得到 SP“ IsEmailExists”的返回值。

小开

此代码行返回从 SQLServer 返回的 StoredProcess 值

cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();

查询值的执行将从 SP 返回

id = (int)cmd.Parameters["@id"].Value;
小开

您试图获取的值不是返回值，而是输出参数。您需要将参数方向更改为 Output。

SqlParameter retval = sqlcomm.Parameters.Add("@b", SqlDbType.VarChar);
retval.Direction = ParameterDirection.Output;
command.ExecuteNonquery();
string retunvalue = (string)sqlcomm.Parameters["@b"].Value;
小开

对于.net core 3.0和 dapper:

如果存储过程返回:

select ID, FILE_NAME from dbo.FileStorage where ID = (select max(ID) from dbo.FileStorage);

然后在 c # 中:

 var data = (_dbConnection.Query<FileUploadQueryResponse>
("dbo.insertFile", whateverParameters, commandType: CommandType.StoredProcedure)).ToList();
var storedFileName = data[0].FILE_NAME;
var id = data[0].ID;

正如您所看到的，您可以定义一个简单的类来帮助从 dapper 的默认返回结构(我发现不可能使用它)检索实际值:

public class FileUploadQueryResponse
{
public string ID { get; set; }
public string FILE_NAME { get; set; }
}

提交答案