使用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是否針對這方面做了改進。
------全文完-----