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

score 4 · Answer 1 · edited Sep 15 '22 at 12:24

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.

Thanks for your reply. The SP we are calling, accepts a user-defined table parameter which only accepts the document content as nvarchar(max). We need to find the correct way to pass this to the SP as nvarchar(max) so it can be converted to varbinary(max) within the SP. — thiag0, May 27 '16 at 13:42

score 2 · Answer 2 · edited May 23 '17 at 12:24

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)?

edited May 23 '17 at 12:24

Community

1
1

answered May 27 '16 at 01:38

satnhak

9,407
5
63
81

Thanks for your reply. Unfortunately this will not work in this scenario as we have to pass the document content as nvarchar(max) and then convert it within the SP. – thiag0 May 27 '16 at 13:41
But why? Converting to an NVARCHAR and then converting back makes no sense at all. The parameters to your stored procedure are just plain wrong and need changing. – satnhak May 30 '16 at 05:24
`SqlDbType.VarBinary`?? – Charlieface Sep 15 '22 at 12:25

score 1 · Answer 3 · answered May 26 '16 at 22:50

1

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

answered May 26 '16 at 22:50

Shon

486
4
9

Thanks for your reply. Unfortunately we don't have full control of the SqlCommand object and have to pass in the file as nvarchar(max). – thiag0 May 27 '16 at 13:43

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

3 Answers3

Linked