Can I use a stream to INSERT or UPDATE a row in SQL Server (C#)?
.net, c#, sql-server, stream
Solution
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);
Use this:
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);
Of course, don't forget to dispose the `MemoryStream` and all these other `IDisposable` instances after the command has been executed.
Edit: 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.
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.
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.
Problem
Suppose I have a VarBinary[MAX] column, can I insert or update into that column using a type derived from System.IO.Stream? How? 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. Possible? (from c#... Without writing T-SQL ?) EDIT I've seen code like this: ``` 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.