>我有一个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有一个文件流功能,但我不想使用它。
有没有办法实现这一目标?
您可以以小块的形式读取文件,并将它们附加到数据列中。
您将需要一个 IDENTITY 列或另一个可用作执行UPDATE
语句的键的列。下面是一个使用 IDENTITY
列的示例:
创建表以存储数据
CREATE TABLE [dbo].[table1](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Data] [varbinary](max) NULL,
)
实现 C# 以在区块中插入/更新数据
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
循环后如何增长。