当我在存储过程中使用单个插入命令返回插入行的主键时,C# 代码工作正常。
但是,当尝试在 2 个表中插入一行并获取主键时,C# 代码会引发以下错误"Table2PK"。
MSSQL 存储过程脚本:
CREATE PROCEDURE [dbo].[usp_WriteBackStatus]
@QtyProduced decimal,
@QuantityToIssue decimal,
@Table1PK nvarchar OUTPUT,
@Table2PK nvarchar OUTPUT
AS
BEGIN
INSERT INTO Table1 (QuantityProduced)
OUTPUT inserted.Table1PK
VALUES (@QtyProduced)
INSERT INTO Table2 (QuantityToIssue)
OUTPUT inserted.Table2PK
VALUES (@QuantityToIssue)
END
GO
C# 代码:
using (var sqlConnection = new SqlConnection (mConnectionStringSrc)) {
sqlConnection.Open ();
using (var sqlCommand = sqlConnection.CreateCommand ()) {
sqlCommand.Parameters.Clear ();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "usp_WriteBackStatus";
sqlCommand.Parameters.Add (new SqlParameter (QtyProduced, 10));
sqlCommand.Parameters.Add (new SqlParameter (QuantityToIssue, 5));
SqlParameter outParam = new SqlParameter ("@Table1PK", SqlDbType.NVarChar, 100);
outParam.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add (outParam);
outParam = new SqlParameter ("@Table2PK", SqlDbType.NVarChar, 100);
outParam.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add (outParam);
using (var sqlDataReader = sqlCommand.ExecuteReader ()) {
while (sqlDataReader.Read ()) {
var reportedID1 = sqlDataReader["Table1PK"].ToString ();
var reportedID2 = sqlDataReader["Table2PK"].ToString (); // ERROR "Table2PK" IS THROWN HERE!!!
Console.WriteLine ($"{reportedID1} {reportedID2}");
}
}
}
}
正如其他 SO 答案中所建议的那样,我尝试使用表变量来存储输出并设置输出变量,但我收到以下错误 C# 代码。
将表达式转换为数据类型 nvarchar 的算术溢出错误。(在 .执行读取器(( 行(
使用表变量时使用的脚本:
DECLARE @OutputData1 table (Table1ID nvarchar);
DECLARE @OutputData2 table (Table2ID nvarchar);
....
OUTPUT inserted.Table1PK INTO @OutputData1 (Table1ID)
OUTPUT inserted.Table2PK INTO @OutputData2 (Table2ID)
....
SELECT @Table1PK = Table1ID, @Table2PK = Table2ID FROM @OutputData1, @OutputData2;
... END ...
SQL Server 中的输出子句返回一个表。
这意味着存储过程实际上返回两个表,每个表都有一条记录。IDataReader
接口(因此,SqlDataReader
实现它(具有一个名为NextResult()
的方法,用于将 DataReader 从当前结果集推进到下一个结果集 - 因此您应该在 c# 代码中执行以下操作:
string reportedID1 = null, reportedID2 = null;
if(sqlDataReader.Read ()) {
reportedID1 = sqlDataReader["Table1PK"].ToString ();
if(sqlDataReader.NextResult())
{
if(sqlDataReader.Read ()) {
reportedID2 = sqlDataReader["Table2PK"].ToString ();
}
}
}
// you probably want to check that they are not both null...
Console.WriteLine ($"{reportedID1} {reportedID2}");