让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是否针对这方面做了改进。

------全文完-----