将参数从 C# 流式传输到 T-SQL SP?

是否可以将 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 an XML typed 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.