将字节[]转换为VARBINARY无效

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.

我正在尝试将一个文件保存到一个SQL Server数据库中，并将该文件保存在一个名为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 query和convert函数将字节[]转换为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

但是，在SQL Server数据库中，文件数据的值总是

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.

不管我选择哪个文件，FILEDATA总是那个数字。所以，如果你能告诉我为什么会发生这种情况，以及我应该做些什么来防止这种情况发生，我将非常感激。

1 个解决方案

#1

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:

截断发生在这里，你应该像这样转换到varbinary(MAX):

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

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

这里描述这种行为:二进制和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.

当数据定义或变量声明语句中未指定n时，默认长度为1。当未使用CAST函数指定n时，默认长度为30。

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

使用SQL参数而不是通过查询字符串传递数据，因为“+ Filebytes +”将在其他情况下传递。

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

要了解更多信息，请访问:https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx。

#1