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

Question

Suppose I have a VarBinary [MAX] column, is it possible to insert or update this column using a type obtained from System.IO.Stream? How?

I think I can get a read-only stream from such a column using SqlDataReader, by calling GetSqlBytes () on the reader, getting an instance of SqlBytes, and then referring to the Stream property .

What I want is the opposite - I want a stream to update or insert.

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

---

EDIT

I saw a 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 do not want to use an array to indicate the value. This works for small data sizes, but not as the size grows. I want to write to the stream.

Answer 1

SqlBytes SqlCommand , varbinary. SqlBytes , Stream. SqlBytes , .

, :

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 .

---

: , , , , , , . , , varbinary, .

SQL Server 2008, ( !) FILESTREAM. "" ADO.NET SqlFileStream.

FILESTREAM, , , , ADO.NET.

Answer 2

vladimir-khozeyev .NET BLOB SQL Server ?, , , Stream .NET Framework 4.5 SqlClient

SQL:

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

#:

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();
    }
}

Windows 7, Windows XP 2003 Server.