問題描述
是否可以將 XML 文件作為參數從 C# 應用程序流式傳輸到 MS SQL 存儲過程，而無需將其加載到 C# 端的內存中?
我擁有的 XML 文件非常大，大約 600MB，包含 200 萬個條目.可以想象，這需要大量內存.
一旦 SQL 上的 SP 從這些文件中插入/更新數據庫中的值，它就不再使用它們并且可以處理.
SQL 示例:
創建程序 [dbo].[AddAllFromList](@XmlData XML)開始用@XmlData 做點什么；結尾;C# 示例
using (Stream XMLFileStream = new someFileInfo.OpenRead())使用 (SqlConnection Connection = new SqlConnection(SQLConnectionString)){var opensql = Connection.OpenAsync();SqlCommand sqlcommand = new SqlCommand("AddAllFromList", Connection){CommandType = System.Data.CommandType.StoredProcedure，命令超時 = 360};sqlcommand.Parameters.Add("@XmlData", SqlDbType.Xml).Value = XMLFileStream;嘗試{等待 sqlcommand.ExecuteNonQueryAsync()}抓住{Console.WriteLine("失敗");}這樣的事情行得通嗎?有人有成功的案例嗎?
對于如此大的負載，我建議無論如何分兩步完成.首先將其讀入臨時表，然后從那里完成其余的工作.
如果文件位于 SQL-Server 可以訪問的位置，您可以直接從 T-SQL 加載 XML.
這個問題要求上傳多個 XML(使用 CURSOR)，但是你會看到怎么走.
這個問題解決了導出到 XML 的問題，但關于文件的編碼，有幾件重要的事情需要了解.>
這個問題涵蓋了一些人們可能會陷入的陷阱.
一般情況
C# 在任何情況下都對字符串使用 unicode(2 字節編碼).這樣的字符串可以傳遞給 SQL Server 的
NVARCHAR(MAX)類型.如果 XML 格式正確，您可以將其直接傳遞給XML類型變量.SQL-Server 的編碼非常有限.很多時候，這樣的大文件都是用
utf-8存儲的.提供的鏈接提供了相關信息.SQL-Server 沒有像您一樣運行.如果它在不同的機器上運行，它將看到其他驅動器和路徑，如您所見.而且權利也可能不同.
無論是使用 C# 加載 XML 并將其傳遞給 SQL-Server 還是使用來自 T-SQL 的某些命令，數據量都將完全相同，必須從 A 傳送到 B.
如果內存很重要，您可以將其分成幾部分.
Is it possible to stream an XML file to a MS SQL Stored Procedure as a parameter from a C# application without having to load it into memory on the c# end?
The XML files i have are quite large, ~600MB with 2mil entries. Which as you can imagine, takes a bucket load of memory.
Once the SP on SQL inserts/updates values in the database from these files it has no further use for them and can be disposed.
SQL Example:
CREATE PROCEDURE [dbo].[AddAllFromList]
(
@XmlData XML
)
BEGIN
Do something with @XmlData;
END;
C# Example
using (Stream XMLFileStream = new someFileInfo.OpenRead())
using (SqlConnection Connection = new SqlConnection(SQLConnectionString))
{
var opensql = Connection.OpenAsync();
SqlCommand sqlcommand = new SqlCommand("AddAllFromList", Connection)
{
CommandType = System.Data.CommandType.StoredProcedure,
CommandTimeout = 360
};
sqlcommand.Parameters.Add("@XmlData", SqlDbType.Xml).Value = XMLFileStream;
try
{
await sqlcommand.ExecuteNonQueryAsync()
}
catch
{
Console.WriteLine("Failed");
}
Would something like this work? Does anyone have any success stories?
With such a big load I'd suggest to do this in two steps anyway. First read this into a staging table, then do the rest from there.
If the file is located where SQL-Server has access you can load the XML from T-SQL directly.
This question asks for the upload of multiple XMLs (using a CURSOR), but you will see how to go.
This question addresses the export to XML, but there are several important things to know about the file's encoding.
This question covers some traps one might get into.
In general
C# uses unicode (2-byte-encoding) in any case for strings. Such a string can be passed over to SQL Server's
NVARCHAR(MAX)type. If the XML is well-formed you can pass it directly to anXMLtyped variable.SQL-Server is very limited in encodings. Very often such big files are stored with
utf-8. The provided links give information about that.SQL-Server is not running as you. If it is running on a different machine it will see other drives and paths as you see. And the rights may be different too.
Whether you load the XML with C# and pass it over to SQL-Server or if you use some command from T-SQL will be quite the same amout of data, which must be shipped from A to B.
If memory matters you might split this in parts.
這篇關于將參數從 C# 流式傳輸到 T-SQL SP?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！