c# - 将大型 xml 文件插入 xml 列的最佳方法(在远程 SQL Server 上)

Question

假设我有一个这样的表:

CREATE TABLE [dbo].[TBL_XML]
(
   [XmlFileID]       [BIGINT] IDENTITY (1, 1) NOT NULL,
   [FileName]        [NVARCHAR](500) NULL,   
   [XmlData]         [XML] NULL,
   [DateCreated]     [DATETIME] NOT NULL,
)

我目前填表的方法是这样的:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = @"INSERT INTO [dbo].[TBL_XML] 
                                    ( [XmlData] , 
                                    [FileName] , 
                                    [DateCreated]
                                    ) 
                        VALUES (@XMLData, @FileName, GETDATE())";

    using (var xmlReader = new XmlTextReader(new FileStream(item.XmlFileName, FileMode.Open)))
    {
        cmd.Parameters.Add("@FileName", SqlDbType.NVarChar, 500).Value = System.IO.Path.GetFileName(item.XmlFileName);
        cmd.Parameters.Add(
        new SqlParameter("@XMLData", SqlDbType.Xml)
        {
            Value = new SqlXml(xmlReader)
        });

        SetConnectionParameters(cmd);

        cmd.ExecuteScalar());
    }
}

但这不适用于非常大的 XML，因为整个文件都加载到内存中并且我得到 OutOfMemory 异常。

从与服务器不同的计算机上运行的 .net 应用程序将大型(>100MB)XML 文件插入 XMLData 列的最佳方法是什么？

批量插入是不可能的，因为 SQL 服务器将无法访问我的 XML 文件。

Answer 1

下面是一种仅使用 .NET 对其进行分块的潜在方法。我没有尝试执行此操作，但它应该可以工作。

    public static ChunkedXmlInsert(XmlItem item)
    {
        int bufferSize = 65536;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            CreateTempTable(connection);

            int position = 0;
            using (StreamReader textStream = File.OpenText(item.XmlFileName))
            {
                char[] buffer = new char[bufferSize];
                int length = textStream.Read(buffer, position, buffer.Length);
                long id = InsertFirstBlock(connection, new string(buffer, 0, length));

                while (textStream.EndOfStream == false)
                {
                    position += length;
                    length = textStream.Read(buffer, position, buffer.Length);
                    AppendBlock(connection, id, new string(buffer, 0, length));
                }
            }

            CopyRecordFromTemp(connection, id);
        }
    }

    private static void CreateTempTable(SqlConnection connection)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"CREATE TABLE #TBL_XML (
                                                              [XmlFileID] [BIGINT] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
                                                              [FileName] [NVARCHAR](500) NULL,
                                                              [XmlData] [NVARCHAR(MAX)] NULL,
                                                              [DateCreated] [DATETIME] NOT NULL
                                                          )";
            command.ExecuteNonQuery();
        }
    }

    private static long InsertFirstBlock(SqlConnection connection, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO #TBL_XML
                                                        ( [XmlData] , 
                                                          [FileName] , 
                                                          [DateCreated]
                                                        ) 
                                        VALUES (@XMLData, @FileName, GETDATE()); SELECT SCOPE_IDENTITY()";

            command.Parameters.AddWithValue("@FileName", System.IO.Path.GetFileName(item.XmlFileName));
            command.Parameters.AddWithValue("@XmlData", text);
            return (long)command.ExecuteScalar();
        }
    }

    private static void AppendBlock(SqlConnection connection, long id, string text)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE #TBL_XML
                                            SET XmlData = XmlData + @xmlData
                                    WHERE XmlFileID = @XmlFileID";

            command.Parameters.AddWithValue("@XmlData", text);
            command.Parameters.AddWithValue("@XmlFileID", id);
            command.ExecuteNonQuery();
        }
    }

    private static long CopyRecordFromTemp(SqlConnection connection, long id)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO [dbo].[TBL_XML] ([XmlData], [FileName], [DateCreated])
                                    SELECT CONVERT(xml, [XmlData]), [FileName], [DateCreated]
                                    FROM #TBL_XML
                                    WHERE XmlFileID = @XmlFileID; SELECT SCOPE_IDENTITY()";
            return (long)command.ExecuteScalar();
        }
    }