Can I use a stream to INSERT or UPDATE a row in SQL Server (C#)?

Question

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.

Solution 1

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.

Solution 2

vladimir-khozeyev points out in his answer to How to I serialize a large graph of .NET object into a SQL Server BLOB without creating a large buffer? that all you need is a Stream when using .NET Framework 4.5 SqlClient Streaming Support

SQL code:

CREATE TABLE BigFiles 
(
    [BigDataID] [int] IDENTITY(1,1) NOT NULL,
    [Data] VARBINARY(MAX) NULL
)

C# code:

using (FileStream sourceStream = new FileStream(filePath, FileMode.Open))
{
    using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE BigFiles SET Data=@Data WHERE BigDataID = @BigDataID"), _sqlConn))
    {
        cmd.Parameters.AddWithValue("@Data", sourceStream);
        cmd.Parameters.AddWithValue("@BigDataID", entryId);

        cmd.ExecuteNonQuery();
    }
}

This code works on Windows 7, but failed on Windows XP and 2003 Server.