Dapper和varchars [英] Dapper and varchars
问题描述
我在 Dapper .NET项目主页中找到了以下评论。
I found the following comment on the Dapper .NET project home page.
Dapper支持varchar参数，如果要使用参数在varchar列上执行where子句，请确保以这种方式传递它：
Dapper supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:
Query<Thing>("select * from Thing where Name = @Name", new {Name =
new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });
在Sql Server上，查询unicode和查询非unicode时的ansi
On Sql Server it is crucial to use the unicode when querying unicode and ansi when querying non unicode
我正在评估Dapper与遗留数据库（SQL Server 2008）一起使用，其中包含许多存储过程
I'm evaluating Dapper for use with a legacy database (SQL Server 2008), with lots of stored procedures with varchar parameters, and I'm a little confused by this restriction.
使用手工制作的ADO.NET代码，对于上面的查询，我将使用以下内容：
With hand-crafted ADO.NET code, I'd use the following for the above query:
new SqlParameter("@Name", "abcde")
，不指定是否为unicode，也不指定长度。
without specifying whether it's unicode or not, nor the length.
-
为什么我需要Dapper使用这种冗长的DbString语法，并指定列长度IsFixedLength和IsAnsi？
Why do I need this verbose DbString syntax with Dapper, specifying the column length, IsFixedLength and IsAnsi?
为什么IsFixedLength = varchar列为true（我
Why IsFixedLength = true for a varchar column (I'd expect it to be true for a char or nchar column)?
是否必须对存储过程参数使用像这样的DbString？
Do I have to use DbString like this for stored procedure parameters?
我期望Dapper使我的DAL代码更简洁，但这似乎使varchar参数更加冗长。
I was expecting Dapper to make my DAL code more concise, but this seems to be making it more verbose for varchar parameters.
更新
我进行了进一步研究，以了解为什么Dapper会有这种varchar限制，但我没有出现在手工编写的代码中，通常会在其中创建输入参数，如下所示：
I've researched a bit further, to try to understand why Dapper would have this varchar restriction, which I don't appear to have in my hand-crafted code, where I would normally create an input parameter as follows:
var parameter = factory.CreateParameter(); // Factory is a DbProviderFactory
parameter.Name = ...;
parameter.Value = ...;
，通常离开提供者来推断 DbType 使用它自己的规则，除非我特别想强迫它。
and usually leave the provider to infer the DbType using its own rules, unless I specifically want to coerce it.
看着Dapper的 DynamicParameters 类，它具有方法 AddParameters 会按如下方式创建参数：
Looking at Dapper's DynamicParameters class, it has a method AddParameters which creates parameters as follows:
var dbType = param.DbType; // Get dbType and value
var val = param.Value; // from
...
// Coerce dbType to a non-null value if val is not null !!!!!
if (dbType == null && val != null) dbType = SqlMapper.LookupDbType(val.GetType(),name);
...
var p = command.CreateParameter();
...
if (dbType != null)
{
p.DbType = dbType.Value;
}
即它将 IDataParameter.DbType 强制转换为使用自己的算法查找的值，而不是让提供程序使用自己的规则。
I.e. it explicitly coerces IDataParameter.DbType to a value it looks up with its own algorithm, rather than leaving the provider to use its own rules.
这是否有充分的理由？对于我来说，这似乎是错误的，尤其是考虑到有关Dapper对varchar参数的支持的评论。
Is there a good reason for this? It seems wrong for me, particularly in the light of the comment about Dapper's support for varchar parameters.
推荐答案
您需要在d＃中为Dapper定义CHAR（30）字段作为DbString，并设置长度（30）和ansi（true）值，以防止Dapper假定字符串为text / blob类型。否则，您可能会收到错误：非法尝试转换文本/字节Blob类型。
You would need to define a CHAR(30) field as a DbString in c# for Dapper and also set the length (30) and ansi (true) values to prevent Dapper from assuming the string was a text/blob type. Otherwise you will likely receive the error: "Illegal attempt to convert Text/Byte blob type".
我在使用ODBC连接到Informix之前遇到了此错误，直到定义了我的参数作为DbString（）并设置长度和ansi值。
I was getting this error using ODBC to connect to Informix until I defined my param as a DbString() and set the length and ansi values.
这篇关于Dapper和varchars的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
