带有 byte[] 字段的 DataTable 作为存储过程的参数 [英] DataTable with a byte[] field as parameter to a stored procedure
问题描述
我一直在重复使用这种使用 DataTable 作为存储过程参数的方法,并且效果很好.这是简化的工作代码:
I've been reusing this method of using a DataTable as a parameter to a stored procedure and it's been working great. This is the simplified working code:
using (dbEntities dbe = new dbEntities())
{
var dt = new dataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Message");
dt.Columns.Add("CreatedOn", typeof(DateTime));
foreach (var row in randomDataSource)
{
dt.Rows.Add(
row.id,
row.message,
DateTime.Now
);
}
var tableType = new SqlParameter("tableType", SqlDbType.Structured);
tableType.Value = dt;
tableType.TypeName = "[dbo].[RandomTableType]";
dbe.ExecuteStoreCommand(
"EXEC [dbo].[SaveTable] @tableType",
new object[] { tableType }
);
}
当我要添加的字段是二进制类型时,就会出现问题.即:
The problem arises when the field I want to add is of a binary type. i.e.:
dt.Columns.Add("BinaryMessage", typeof(byte[]));
顺便说一下,数据库中对应的列是varbinary(MAX)
.当我尝试运行此程序时,出现此错误:
The corresponding column in the database is varbinary(MAX)
by the way. When I try to run this, I get this error:
从数据类型 nvarchar(max) 到 varbinary(max) 的隐式转换是不允许.使用 CONVERT 函数运行此查询.
Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.
我如何修改我必须做的工作?
How do I modify what I have to make this work?
推荐答案
.NET 中二进制字符串的表示是 SqlBinary
结构.
The representation for a binary string in .NET is the SqlBinary
structure.
你想像这样添加你的列:
You want to add your column like this:
dt.Columns.Add("BinaryMessage", typeof(SqlBinary));
SqlBinary
类有一个 显式转换为字节数组和隐式转换一个字节数组,因此从字节数组到列的值是一个简单的赋值问题,而从列中获取字节数组需要显式转换.
The SqlBinary
class has an explicit conversion to a byte array and an implicit conversion from a byte array, so the value from a byte array to the column is a simple matter of assignment, while getting a byte array from the column requires an explicit cast.
这篇关于带有 byte[] 字段的 DataTable 作为存储过程的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!