如何将字节 [] 从 C# 作为字符串传递给 SQL Server 存储过程并转换为 varbinary(MAX) [英] How to pass byte[] from C# as string to SQL Server stored procedure and convert into varbinary(MAX)

问题描述

在这个项目中，有一个类封装了ADO.NET，用于ExecuteDataReader、ExecuteScalar等常见的数据访问.

In this project, there is a class which wraps ADO.NET for common data access like ExecuteDataReader, ExecuteScalar, etc..

当使用这些方法调用存储过程时，它允许您传递一个Dictionary参数(字符串键，字符串值)，然后将这些参数添加到SqlCommand 对象作为 SqlParameter.

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.

有一种情况，我们必须在数据库中保存一个文档.文档是一个byte[]，数据库中对应的列是varbinary(MAX).

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

我们已经四处寻找解决方案，但所有可用的都是使用 SqlDbType.Varbinary 的示例，这在这种情况下不是一个选项.

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.

我们最近的尝试是尝试将 byte[] 转换为二进制字符串，将其作为 nvarchar(max) 传递到存储过程中，然后使用 CONVERT(varbinary(max), @theFileBinaryString) 将其保存到 Document 表时，但是，这会保存损坏的文件.

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

推荐答案

没有在 VARBINARY(MAX) 和 byte 之间正确映射的 SqlDbType[].但实际上这没问题，因为参数化基础设施只是为你处理这个，下面的代码就可以了:

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

请参阅此处了解更多详情:什么 SqlDbType 映射到 varBinary(max)?

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

这篇关于如何将字节 [] 从 C# 作为字符串传递给 SQL Server 存储过程并转换为 varbinary(MAX)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！