How to pass byte[] from C# as string to SQL Server stored procedure and convert into varbinary(MAX)

Question

In this project, there is a class which wraps ADO.NET for common data access like ExecuteDataReader, ExecuteScalar, etc..

When using these methods to call a stored procedure, it allows you to pass a Dictionary<string, string> of parameters (string key, string value), which are then added to the SqlCommand object as SqlParameter.

There is a case where we have to save a document in the database. The document is a byte[] and the corresponding column in the database is varbinary(MAX).

We've looked around for a solution but all that is available are examples using SqlDbType.Varbinary, which is not an option in this situation.

Our latest attempt was to attempt to convert the byte[] to a binary string, pass it into the stored procedure as nvarchar(max), and then use CONVERT(varbinary(max), @theFileBinaryString) when saving it to the Document table, however, this saves a corrupt file.

C#

byte[] pDocumentBytes = ...;
string documentAsBinary = "0x" + BitConverter.ToString(pDocumentBytes).Replace("-", ""); 

SQL

@DocumentContentAsBinary nvarchar(max) -- This is "documentAsBinary" from C# above

DECLARE @DocumentContentVarbinary varbinary(max);
SET @DocumentContentVarbinary = CONVERT(varbinary(max), @DocumentContentAsBinary);

Answer 1

Assume you have this SP:

DECLARE
@Value1 ...
@Value2 ...
...
@File VARBINARY(MAX)

INSERT INTO [YourTable] (Column1, Column2, ..., File) VALUES (@Value1, @Value2, ..., @File)

Use this syntax to convert the file to byte array and directly insert byte array as varbinary data:

using System.Data.SqlClient;
using System.IO;

byte[] data;

using (FileStream fs = new FileStream(document, FileMode.Open)
{
    BinaryReader fileReader = new BinaryReader(document);
    data = fileReader.ReadBytes((int)document.Length);
    document.Close(); // don't forget to close file stream
}

using (var connection = new SqlConnection("YourConnectionStringHere"))
{
    connection.Open();
    using (var command = new SqlCommand("YourSPHere", connection)
    {
        command.CommandType = CommandType.StoredProcedure;

        // insert parameters here

        // add file parameter at the end of collection parameters
        command.Parameters.AddWithValue("@File", SqlDbType.VarBinary).Value = data;
        command.ExecuteNonQuery();
    }
    connection.Close();
}

Reference: http://www.codeproject.com/Questions/309795/How-to-insert-byte-array-into-SQL-table

I hope this solution useful.