C# & SQL Server 存储过程 - 无法获取输出参数值，返回值有效

Question

我想获取存储过程的输出参数的值，但我一直得到+1。当通过返回获取值时，一切都很好。

我设法使用实体框架获取输出参数的值，但是，它的工作速度非常慢。我正在附加用于从存储过程中获取值的存储过程和 C# 代码。

具有输出参数的存储过程在 SQL Server 中工作正常。

有人知道为什么我的代码不起作用吗？

提前感谢您的帮助。

存储过程：

Create procedure sp_CountEachNumberOccurencesOutput
@number tinyint, 
@count_number int output
As
SET NOCOUNT ON
select count(*) 
from RawData
where Number1 = @number or Number2 = @number or Number3 = @number
select @count_number = count(*)

带有和输出参数的存储过程的 C# 代码，该参数使用实体框架并工作，但速度很慢：

using (SqlConnection cn = new SqlConnection(cnStr))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesOutput", cn))
{
var outParam = new SqlParameter();
int outputValue = 0;
outParam.SqlDbType = SqlDbType.Int;
outParam.Direction = ParameterDirection.Output;
DbContext dbContext = new DbContext(this.connectionString);
var data = dbContext.Database.SqlQuery<int>("sp_CountEachNumberOccurencesOutput @number, @count_number OUT",
new SqlParameter("number", parameter),
new SqlParameter("count_number", outputValue),
outParam);
outputValue = data.ToList()[0];
return outputValue;
}
}

带有 和输出参数的存储过程的 C# 代码不起作用：

using (SqlConnection cn = new SqlConnection(cnStr))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesOutput", cn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@number", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = parameter;
SqlParameter outputParameter = cmd.Parameters.Add("@count_number", SqlDbType.Int);
outputParameter.Direction = ParameterDirection.Output;
outputParameter.Value = 0;                   
cmd.ExecuteNonQuery();
cn.Close();
int OutputValue = (int)cmd.Parameters["@count_number"].Value;
return OutputValue;
}
}

此代码获取返回值并工作：

using (SqlConnection cn = new SqlConnection(cnStr))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand("sp_CountEachNumberOccurencesReturn", cn))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@number", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = parameter;
SqlParameter returnValue = new SqlParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnValue);
return Convert.ToInt32(cmd.ExecuteScalar());
}
}

Answer 1

不确定是否可以将 count(*( 分配给参数。它只适用于 sql 语句。

也许试试这个

Create procedure sp_CountEachNumberOccurencesOutput
@number tinyint, @count_number int output
As
SET NOCOUNT ON
select @counter_number=count(*)  from RawData
where Number1 = @number or Number2 = @number  or Number3 = @number

Answer 2

我曾经按如下方式执行此任务：

public Int32 ExecuteGenericSP(string spName, out int errNum, out string errMsg, params object[] lstParams)
{
Int32 returnValue;
/* Aqui */
SqlDatabase _sqlDatabase = new SqlDatabase(getConnectionString());
using (DbConnection con = _sqlDatabase.CreateConnection())
{
DbCommand cmd = _sqlDatabase.GetStoredProcCommand(spName);
_sqlDatabase.DiscoverParameters(cmd);
int n = 3;
errNum = 0;
errMsg = "";
cmd.Parameters["@RETURN_VALUE"].Value = 0;
cmd.Parameters["@errNum"].Value = 0;
cmd.Parameters["@errDes"].Value = "";
foreach (object o in lstParams)
{
if (cmd.Parameters[n].DbType.ToString() == "DateTime")
cmd.Parameters[n].Value = Convert.ToDateTime(o);
else
cmd.Parameters[n].Value = o.ToString();
n++;
}
returnValue = _sqlDatabase.ExecuteNonQuery(cmd);
errNum = Convert.ToInt16(_sqlDatabase.GetParameterValue(cmd, "errNum"));
errMsg = _sqlDatabase.GetParameterValue(cmd, "errDes").ToString();
}
return returnValue;
}

例如，这是一个 SP 的示例：

ALTER PROCEDURE [dbo].[AUD_Insertar]
@errNum smallint OUTPUT,
@errDes varchar(50) OUTPUT,
@id smallint,    
@message varchar(MAX),
@type char(1),
@userId varchar(20),
@modulo char(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @errNum = 0;
SET @errDes = '';
INSERT INTO log(message, type, userId, modulo) VALUES (@message, @type, @userId, @modulo)
IF (@@ROWCOUNT = 0)
BEGIN
SET @errNum = 1;
SET @errDes = 'Error while inserting';
END
END

我希望这能帮助你。 问候

艾利安