3

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.

Improve this question
11
  • 1
    Important side note: your byte[] Filebytes = ...(SelectedFilePath) is outside of the if block. Thus, its SelectedFilePath default value may mess up your logic: i.e. always reading the same file and write it again and again and again to the SQL database. You may want to consider to terminate the operation if DialogResult isn't OK. Commented Jul 18, 2017 at 2:55
  • Thanks, I see what you are saying but even with the correct SelectedFilePath the problem is still there. Commented Jul 18, 2017 at 2:59
  • Yep, it is a side note after all. ;) but it will prevent you from having another problem. Commented Jul 18, 2017 at 3:00
  • Thanks, I just hope someone finds a solution to this problem, it's four am and I've been stuck on it for the past 3 hours. Commented Jul 18, 2017 at 3:01
  • 2
    You need to use parameters to pass the values in to the query. Two reasons: Your bunch of file bytes may not gracefully concatenate with the rest of the query string and you don't want to meet [Bobby Tables](bobby-tables.com. Commented Jul 18, 2017 at 3:15

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I did that and it still stores the same code (0x53797374656D2E427974655B5D).
This is because what you are passing in is this text: '" + Filebytes + "' casted to varbinary
You should pass in variable or parameter value, not fixed text
The problem was i was not inserting the data into the table properly. Thanks!
Although the above is the accepted answer, the posted code is incorrect. The code snippet ... + Filebytes + ... will insert a fixed string "System.Byte[]" into the constructed SQL that will always store the value as 0x53797374656D2E427974655B5D. The Filebytes content needs to first be converted into a hex string, prefixed with "0x" and inserted without quotes. Something like string hex = BitConverter.ToString(Filebytes).Replace("-",""); and then "...VALUES('" + Guid.newGuid + "', 0x" + hex + ")...". (This was posted after @SilvioV. pointed out the issue in a separate post.)
0

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[]'
Improve this answer

3 Comments

This is not an answer and should be removed. (But you are correct though, the accepted answer cannot work as written. Apparently the OP found the solution, but the details were not recorded.) You can add this as a comment to the other answer if you wish.)
Excuse me, it was the first time i wrote a message here. I tried just now to put my message in the comments but I'm not allowed because I'm a newbie here.
Okay. I added a comment to the answer pointing out your finding and offering a suggested fix

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.