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
        // -1 means max
        command.Parameters.AddWithValue("@File", SqlDbType.VarBinary, -1).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.

Answer 2

There isn't a SqlDbType that maps properly between VARBINARY(MAX) and byte[]. But actually that is OK, because the parameterisation infrastructure just handles this for you, the following code will just work:

var binary = new bytes[1];
var command = new SqlCommand("INSERT INTO [MyTable]([BinaryColumn]) VALUES (@binary)");
command.Parameters.AddWithValue("@binary", binary);
command.ExecuteNonQuery();

See here for more details: What SqlDbType maps to varBinary(max)?

Answer 3

Update your query to use paramaters and pass the byte array as a paramater directly to the table

eg SQL:

insert into table values (@data);

C#

SqlComman com = new SqlCommand("insert into table values (@data);",Database Connection);
com.Paramaters.Add(new SqlParameter("@Data" Byte array));
com.executeNonQuery();