CODEWITHSUNDEEP
c#sqlsql-server
TescoExpress
TescoExpress

Reputation: 55

Converting bytes[] to VARBINARY is not working

I am trying to save a file into a SQL Server database and the column that the file will be saved in is of datatype VARBINARY.

The way I am currently doing this is by getting the file path and turning the file into a byte array.

string SelectedFilePath = "" ;
OpenFileDialog choofdlog = new OpenFileDialog();

if (choofdlog.ShowDialog() == DialogResult.OK)
{
    SelectedFilePath = choofdlog.FileName;      
}

byte[] Filebytes = File.ReadAllBytes(SelectedFilePath);

Then, I insert the bytes into the database using an insert query and the convert function to convert the byte[] to varbinary:

INSERT * INTO TblFile([FILEID], [FILEDATA])  
VALUES('" + Guid.newGuid + "', CONVERT(VARBINARY, '" + Filebytes + "'));

However, in the SQL Server database, the value of the FILEDATA is always 

0x53797374656D2E427974655B5D

And it doesn't matter which file I select the FILEDATA will always be that number. So if you could tell me why this is happening and what I should do to prevent it I would very much appreciate that.

Upvotes: 3

Views: 8578

Answers (2)

Silvio V.
Silvio V.

Reputation: 1

I can't understand how it can work because if you create a string concatening the byte[] Filebytes variable without a conversion you can get always something like this:

Console.WriteLine("'" + Filebytes + "'");

 'System.Byte[]'

Upvotes: 0

sepupic
sepupic

Reputation: 8687

Here is an error:

INSERT * INTO TblFile([FILEID], [FILEDATA])  
VALUES('" + Guid.newGuid + "', CONVERT(VARBINARY, '" + Filebytes + "'));

The truncation occurs here, you should cast to varbinary(MAX) like this:

INSERT * INTO TblFile([FILEID], [FILEDATA])  
VALUES('" + Guid.newGuid + "', CONVERT(VARBINARY(MAX), '" + Filebytes + "'));

This behaviour is described here: binary and varbinary (Transact-SQL)

Remarks

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Instead of passing the data through the Query string, use SQL parameters as '" + Filebytes + "' will be passed in otherwise.

SqlParameter FileDataUploadParameter = Cmd.Parameters.Add("@FileData", SqlDbType.VarBinary);
FileDataUploadParameter.Value = FileToUpload;

For more information, go to: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

Upvotes: 4

Related Questions