c# - 将数据流式传输到 sql server 数据库而不缓冲整个数据

Question

我有一个 Table Blob，它有一个 varbinary(max) 作为列。现在我想使用文件流将数据存储到数据库中。数据可能非常大(在我的例子中是 1.5GB)所以我不想将整个数据加载到缓冲区中。

我尝试过的:

        using (FileStream fs = File.Open(@"BigData.iso", FileMode.Open))
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = @"...";
                conn.Open();
                SqlCommand command = new SqlCommand("INSERT INTO Blob Values (@0, @1)", conn);
                command.Parameters.Add(new SqlParameter("0", Guid.NewGuid()));
                var sqlb = new SqlBytes(fs);
                command.Parameters.Add(new SqlParameter("1", SqlDbType.VarBinary, -1)).Value = sqlb;
                command.ExecuteNonQuery();
            }
        }

但我得到了一个OutOfMemoryException，因为 SqlBytes 将其缓冲区初始化为数据的整个大小。

我知道 Microsoft 有一个 FILESTREAM 功能，但我不想使用它。

有什么办法可以实现吗？

Answer 1

您可以分小块读取文件并将它们附加到数据列。

您需要一个 IDENTITY可用作执行 UPDATE 语句的键的列或其他列。下面是一个使用 IDENTITY 列的示例:

创建一个表来存储数据

CREATE TABLE [dbo].[table1](
    [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Data] [varbinary](max) NULL,
)

实现 C# 插入/更新 block 中的数据

private const string C_SqlConnectionString = @"Server=SERVERNAME;Database=DBNAME;Trusted_Connection=yes;";
private const int C_FileChunkSizeBytes = 1024 * 1024; // 1 MB

private static void storeFile(string filepath)
{
    using (FileStream fs = File.Open(filepath, FileMode.Open))
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = C_SqlConnectionString;
            conn.Open();

            // Use a transaction to ensure that all parts of the file get stored to DB
            SqlCommand command = new SqlCommand("BEGIN TRAN", conn);
            command.ExecuteNonQuery();

            var pos = 0;
            byte[] fileBytes = null;
            int sqlRowId = 0;

            // Read the file in chunks
            while (pos < fs.Length)
            {
                // Read file bytes
                var bytesToRead = pos + C_FileChunkSizeBytes < fs.Length
                    ? C_FileChunkSizeBytes
                    : (int)(fs.Length - pos);

                fileBytes = new byte[bytesToRead];
                fs.Read(fileBytes, 0, bytesToRead);

                // Store bytes to a parameter
                var varbinary = new SqlParameter("0", System.Data.SqlDbType.VarBinary, -1);
                varbinary.Value = fileBytes;

                if (pos == 0)
                {
                    // If this is the first chunk, then we need to INSERT
                    // The HOLDLOCK hint will hold a lock on the table until transaction completes (or is rolled back)
                    command = new SqlCommand("INSERT INTO [dbo].[table1] WITH(HOLDLOCK) VALUES(@0)", conn);
                    command.Parameters.Add(varbinary);
                    command.ExecuteNonQuery();

                    // Get the row ID for the inserted row
                    command = new SqlCommand("SELECT @@IDENTITY", conn);
                    sqlRowId = Convert.ToInt32(command.ExecuteScalar());
                }
                else
                {
                    // Update existing row and append data
                    command = new SqlCommand("UPDATE [dbo].[table1] SET [Data] = [Data] + @0 WHERE [ID] = @1", conn);
                    command.Parameters.Add(varbinary);
                    command.Parameters.Add(new SqlParameter("1", System.Data.SqlDbType.Int)).Value = sqlRowId;
                    command.ExecuteNonQuery();
                }

                // ** Good place for a breakpoint
                pos += bytesToRead;
            }

            // Commit transaction
            command = new SqlCommand("COMMIT TRAN", conn);
            command.ExecuteNonQuery();

            conn.Close();
        }
    }
}

测试

在 C# 代码中的 while 循环底部放置一个断点，例如 pos += bytesToRead;。

在执行代码的过程中，当代码执行到断点处停止时，查看SQL中的数据:

SELECT *
        ,LEN([Data]) AS [Length]
FROM [dbo].[table1] WITH(NOLOCK)

NOLOCK 提示会让我们看到未提交事务中的数据。 LEN([Data]) 将显示每次 while 循环迭代后字段长度如何增长。