設想
- 我正在使用 SQL Server 2017（無法更改）
- 我在 C# 控制臺和 .NET Framework 4.5 中使用 Visual Studio 2019（可以更改）
- 我使用 ADO.NET 是因為幾年前我們無法使用物體框架，因為系統被設計為使用回傳至少 100k 行的存盤程序（可能會更改）
情況
我有一個 USP，它回傳一個至少 100k 行×20 個欄位的表。我需要添加一個輸出引數以獲取 USP 本身創建的 ID。所以，情況是我需要回傳一個表和一個 ID（稱為ProcMonitorId）。我不知道這是否可能（請參閱解決方法部分）
在 SQL 級別，到目前為止似乎還不錯：
CREATE PROCEDURE [myschema].[mystore]
@ProcMonitorId BIGINT OUTPUT
AS
BEGIN
BEGIN TRANSACTION
(...)
SELECT fields FROM myTable
SELECT @ProcMonitorId = @internalVariable
SQL執行：
在存盤庫層（只有相關行，有人對示例的健康狀況感到驚訝）：
var command = new SqlCommand("myStoreProcedure", mycon);
command.CommandType = CommandType.StoredProcedure;
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = "@ProcMonitorId";
outPutParameter.SqlDbType = System.Data.SqlDbType.BigInt;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(outPutParameter);
// Open connection etc-etc that works
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
string ProcMonitorId = outPutParameter.Value.ToString();
da.Fill(dt);
一切正常，直到在 C# 級別添加輸出。它在行中回傳：
string ProcMonitorId = outPutParameter.Value.ToString();
它回傳NullReferenceException是因為Value為空（不可能），當然，不能轉換為字串。我會通過添加 a 來解決這種情況，?但如果這種情況真的發生，我需要以任何方式將其作為錯誤捕獲。主要思想是 Value 不能為空。
因為我沒有任何 ORM 映射，（我的專長不是 ADO.NET 而是物體框架）我不明白為什么為 null（不，在 SQL 層不是 null，總是回傳一個值）
題
我怎樣才能解決這個錯誤或者我怎樣才能回傳一個BIGINT引數和一個表結果？
解決方法
As I first glance I have to solve it quickly, I made a:
SELECT 1 as type, @procID as procid, null as data1, null as data2
UNION ALL
SELECT 2 as type, null as procid, data1, data2
in order to simulate a "header" and "data" rows on one single table. But I don't like this solution and is not very elegant and flexible. I've to parse the header every time.
Thanks in advance and please comment anything, tip, help, workaround, I will be glade to update my answer if more information is needed.
Also I can make my Framework to .NET Core or change to Entity Framework. That I can't change is my SQL version
Update #2
No changes in SQL - Still working as screenshot
In C# - Hangs out for ever
SqlConnection connection = new SqlConnection(ConfigurationManager.AppSettings["DbConnection"]);
connection.Open();
var command = new SqlCommand("myUSP", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = Convert.ToInt16(ConfigurationManager.AppSettings["DataBaseTimeOut"]);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlParameter r = command.Parameters.Add("@ProcMonitorId", SqlDbType.BigInt);
r.Direction = ParameterDirection.Output;
DataTable dt = new DataTable();
using (var rdr = command.ExecuteReader())
{
dt.Load(rdr);
long id = (long)r.Value;
}
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
uj5u.com熱心網友回復：
引數值在您使用結果集之后才可用，例如
var cmd0 = new SqlCommand("create or alter procedure pFoo @id int output as begin select * from sys.objects; set @id = 12; end", con);
cmd0.ExecuteNonQuery();
var cmd = new SqlCommand("pFoo", con);
cmd.CommandType = CommandType.StoredProcedure;
var p1 = cmd.Parameters.Add("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Output;
var dt = new DataTable();
using (var rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
var id = (int)p1.Value;
}
uj5u.com熱心網友回復：
您應該使用一個 Parameter 并將 Direction 屬性設定為ReturnValue，并在 sp 內部宣告一個內部變數并將其設定為您想要的值。
然后在離開 StoredProcedure 之前呼叫 RETURN 陳述句。
例如，請參閱此 SP：
ALTER PROCEDURE [GetTimeZoneGMT]
@TimeZone NVARCHAR(128)
AS
BEGIN
DECLARE @timeZoneNumber as INT = -20;
IF @TimeZone ='Pacific/Midway'
SET @timeZoneNumber = -11
ELSE IF @TimeZone ='Pacific/Niue'
SET @timeZoneNumber = -11
ELSE IF @TimeZone ='Pacific/Pago_Pago'
SET @timeZoneNumber = -11
SELECT 1 -- or whatever you need to have as result set
RETURN @timeZoneNumber;
END
存盤程序以（虛假的）SELECT 陳述句結束，但還有一個 RETURN 陳述句，其引數設定在 SP 邏輯內部。
現在從 C# 方面你可以用這種方式呼叫它（LinqPad 示例）
using (var connection = new SqlConnection("Data Source=(LOCAL);Initial Catalog=LinqPADTest;Integrated Security=True;"))
{
connection.Open();
SqlCommand cmd = new SqlCommand("GetTimeZoneGMT", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@TimeZone", SqlDbType.NVarChar).Value = "Asia/Kuala_Lumpur";
SqlParameter r = cmd.Parameters.Add("@p2", SqlDbType.BigInt);
r.Direction = ParameterDirection.ReturnValue;
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
r.Value.Dump(); // Prints -20
dt.Dump(); // Prints a row with a single column with 1 as value
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/377999.html
標籤：c# sql sql-server entity-framework ado.net
上一篇：efcore多2多映射