让Dapper+SqlCE支持ntext数据类型和超过4000字符的存储
使用Dapper和SqlCE进行开发的时候，如果数据库的某字段是采用的ntext数据类型，并且在这个字段存储的数据超过了4000个字符，会报如下的错误：
Invalid parameter Size value '-1'. The value must be greater than or equal to 0.
在Google上以“dapper sqlce ntext”作为关键词搜索，可以找到如下两个解决办法：
1. Inserting a string larger then 4000 characters using Sql CE 4.0
2. Attempting to Modify Dapper to Support SQL Server CE's ntext type
这两个解决办法，要么通用性较差，要么使用比较麻烦。
通过查看Dapper的源码，发现Dapper在构造参数的动态方法中针对实体类属性为DbString的类型和数据类型为DbType.Xml进行了特别处理，我们也可以在这里入手，针对长字符串进行特别处理。
解决办法：
一、新建一个Attribute，用来标记需要特别处理的实体类属性；
[AttributeUsage(AttributeTargets.Property)] public class LongStringAttribute : Attribute { }
二、新建一个类，用来处理添加NText类型参数；
public class LongString { public static void AddParameter(IDbCommand command, string name, string value) { var param = command.CreateParameter(); param.ParameterName = name; param.Value = (object)value ?? DBNull.Value; param.DbType = DbType.String; int length = -1; if (!string.IsNullOrEmpty(value)) length = value.Length; if (length == -1 && value != null && value.Length <= 4000) { param.Size = 4000; } else { param.Size = length; } if (value != null) { if (length > 4000 && param.GetType().Name == "SqlCeParameter") { param.GetType().GetProperty("SqlDbType").SetValue(param, SqlDbType.NText, null); param.Size = length; } } command.Parameters.Add(param); } }
三、修改Dapper的源码，在SqlMapper.CreateParamInfoGenerator方法中，找到以下代码
if (prop.PropertyType == typeof(DbString)) { il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [typed-param] il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [dbstring] il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [dbstring] [command] il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [dbstring] [command] [name] il.EmitCall(OpCodes.Callvirt, typeof(DbString).GetMethod("AddParameter"), null); // stack is now [parameters] continue; } //我们的代码插入到这里 DbType dbType = LookupDbType(prop.PropertyType, prop.Name); if (dbType == DbType.Xml) { // this actually represents special handling for list types; il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [command] il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [command] [name] il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [command] [name] [typed-param] il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [command] [name] [typed-value] if (prop.PropertyType.IsValueType) { il.Emit(OpCodes.Box, prop.PropertyType); // stack is [parameters] [command] [name] [boxed-value] } il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("PackListParameters"), null); // stack is [parameters] continue; }
修改之后的代码如下：
if (prop.PropertyType == typeof(DbString)) { il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [typed-param] il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [dbstring] il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [dbstring] [command] il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [dbstring] [command] [name] il.EmitCall(OpCodes.Callvirt, typeof(DbString).GetMethod("AddParameter"), null); // stack is now [parameters] continue; } //这里插入修改的代码 Attribute lStrAttr = Attribute.GetCustomAttribute(prop, typeof(LongStringAttribute)); if (lStrAttr != null) { //special handling for long string il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [command] il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [command] [name] il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [command] [name] [typed-param] il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [command] [name] [string] il.EmitCall(OpCodes.Call, typeof(LongString).GetMethod("AddParameter"), null); continue; } DbType dbType = LookupDbType(prop.PropertyType, prop.Name); if (dbType == DbType.Xml) { // this actually represents special handling for list types; il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [command] il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [command] [name] il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [command] [name] [typed-param] il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [command] [name] [typed-value] if (prop.PropertyType.IsValueType) { il.Emit(OpCodes.Box, prop.PropertyType); // stack is [parameters] [command] [name] [boxed-value] } il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("PackListParameters"), null); // stack is [parameters] continue; }
这样就可以了，使用的时候，如果我们数据表的某字段是ntext类型，那么我们只需要在定义相应实体类的时候，给相应属性加上LongStringAttribute，Dapper就可以自动识别这个字段，插入正确的数据了。
使用代码如下：
//实体类定义 using System; using Dapper; namespace Entity { public class product { public int Id { get; set; } public int shopid { get; set; } public string type { get; set; } public string outid { get; set; } public string link { get; set; } public string title { get; set; } [LongString] public string content { get; set; } public decimal price { get; set; } public int amount { get; set; } } } //调用 product p = new product(); p.shopid = 1; p.title = "梁振英:\"占中\"者不要试探北京忍耐底线"; p.link = "http://news.163.com/14/1021/13/A936JGST0001124J.html"; string str = FileHelper.ReadTextFile("content.txt"); p.content = str; p.type = "netease"; p.outid = "A936JGST0001124J"; p.price = 123.45M; p.amount = 999; SqlCeConnection conn = new SqlCeConnection("Data Source=test.sdf"); conn.Open(); string sql = "insert into products(shopid,type,outid,link,title,content,price,amount) values(@shopid,@type,@outid,@link,@title,@content,@price,@amount)"; SqlMapper.Execute(conn, sql, p); conn.Close();
我使用的Dapper版本是博客园里@wushilonng改写的针对.NET 2.0的版本，未知最新版Dapper是否针对这方面做了改进。
------全文完-----
