我可以使用流在 SQL Server (C#) 中插入或更新行吗? [英] Can I use a stream to INSERT or UPDATE a row in SQL Server (C#)?

问题描述

假设我有一个 VarBinary[MAX] 列，我可以使用派生自 System.IO.Stream 的类型插入或更新到该列中吗?如何?

Suppose I have a VarBinary[MAX] column, can I insert or update into that column using a type derived from System.IO.Stream? How?

我认为我可以使用 SqlDataReader 从这样的列中获取一个 只读 流，调用 GetSqlBytes() 在阅读器上，获取 SqlBytes 实例，然后引用 Stream 属性.

I think that I can obtain a read-only stream from such a column using a SqlDataReader, calling GetSqlBytes() on the reader, getting the SqlBytes instance, and then referencing the Stream property on that.

我想要的是相反的 - 我想要一个用于更新或插入的流.

What I want is the converse - I want a stream for update or insert.

可能吗?(来自 c#... 不写 T-SQL ?)

Possible? (from c#... Without writing T-SQL ?)

编辑

我见过这样的代码:

    System.Data.SqlClient.SqlCommand _SqlCommand
        = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

    // Convert image to memory stream
    System.IO.MemoryStream _MemoryStream = new System.IO.MemoryStream();
    _Image.Save(_MemoryStream, _ImageFormat);

    // Add image as SQL parameter
    System.Data.SqlClient.SqlParameter _SqlParameter 
        = new System.Data.SqlClient.SqlParameter("@" + _ImageFieldName, SqlDbType.Image);

    _SqlParameter.Value = _MemoryStream.ToArray();
    _SqlCommand.Parameters.Add(_SqlParameter);

    // Executes a Transact-SQL statement against the connection 
    // and returns the number of rows affected.
    _SqlRetVal = _SqlCommand.ExecuteNonQuery();

    // Dispose command
    _SqlCommand.Dispose();
    _SqlCommand = null;  

...但我不想使用数组来指定值.这适用于小数据大小，但不适用于大小变大.我想写入流.

...but I don't want to use an array to specify the value. That works for small data sizes, but not as sizes get larger. I want to write into a stream.

推荐答案

您应该能够将 SqlBytes 的实例作为参数传递给 SqlCommandcode>varbinary 是必需的.同一个 SqlBytes 类有一个 构造函数重载包装一个 Stream.因此，只需从流中创建一个 SqlBytes 实例，然后将其作为参数值传入.

You should be able to pass an instance of SqlBytes as a parameter to a SqlCommand wherever a varbinary is needed. That same SqlBytes class has a constructor overload that wraps a Stream. So simply create a SqlBytes instance from the stream, then pass that in as the parameter value.

换句话说，将其放入您修改后的代码中，而不是这样:

In other words, fitting that into your revised code, instead of this:

MemoryStream _MemoryStream = new System.IO.MemoryStream();
_Image.Save(_MemoryStream, _ImageFormat);
SqlParameter _SqlParameter = new 
    SqlParameter("@" + _ImageFieldName, SqlDbType.Image);
_SqlParameter.Value = _MemoryStream.ToArray();
_SqlCommand.Parameters.Add(_SqlParameter);

使用这个:

MemoryStream _MemoryStream = new System.IO.MemoryStream();
_Image.Save(_MemoryStream, _ImageFormat);
_MemoryStream.Position = 0;  // I *think* you need this
SqlParameter _SqlParameter = new 
    SqlParameter("@" + _ImageFieldName, SqlDbType.VarBinary);
_SqlParameter.Value = new SqlBytes(_MemoryStream);
_SqlCommand.Parameters.Add(_SqlParameter);

当然，不要忘记在命令执行后处理 MemoryStream 和所有其他 IDisposable 实例.

Of course, don't forget to dispose the MemoryStream and all these other IDisposable instances after the command has been executed.

好的，我刚刚看到您编辑的底部，这意味着数据非常大，您不希望它最终出现在内存中，这实际上并不能解决该问题.问题是，如果值那么大，首先将其存储在 varbinary 列中是个坏主意.

OK, I just saw the bottom of your edit, which is implying that the data is extremely large and you don't want it to end up in memory, and this won't actually solve that problem. Thing is, if the value is that big, it's a bad idea to be storing it in a varbinary column in the first place.

如果您使用的是 SQL Server 2008，则可以(并且应该！)使用 FILESTREAM 代替.这实际上确实支持 ADO.NET 中的真正"流式传输 通过 SqlFileStream 类.

If you're using SQL Server 2008, you can (and should!) use FILESTREAM instead. This actually does support "true" streaming in ADO.NET through the SqlFileStream class.

如果您不能使用 FILESTREAM 存储，那么恐怕您将不得不在某个时间点处理内存中的数据，这几乎就是 ADO 的方式.NET 工作.

If you can't use FILESTREAM storage, then I'm afraid you're going to have to deal with the data being in memory at some point in time, that's pretty much how ADO.NET works.

这篇关于我可以使用流在 SQL Server (C#) 中插入或更新行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！