Allows Dapper+sqlce to support ntext data types and more than 4000 characters of storage

When developing with Dapper and sqlce, if a field in the database is the ntext data type used, and the data stored in this field exceeds 4,000 characters, the following error is reported:

Invalid parameter Size value '-1 '. The value must is greater than or equal to 0.

In Google on the "dapper SqlCe ntext" as a keyword search, you can find the following two solutions:

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

These two solutions are either less versatile or cumbersome to use.

By looking at the source code of the dapper, it is found that dapper in the dynamic method of constructing parameters for the type and data type of the entity class attribute dbstring is special processing for dbtype.xml, we can also start here, for the long string for special processing.

Workaround:

First, create a new attribute to mark the entity class attributes that require special handling;

    [AttributeUsage (Attributetargets.property)]    publicclass  Longstringattribute:attribute    {    }

Second, create a new class to handle the addition of ntext type parameters;

     Public classlongstring { Public Static voidAddparameter (IDbCommand command,stringNamestringvalue) {            varparam =command.            CreateParameter (); Param. ParameterName=name; Param. Value= (Object) value??DBNull.Value; Param. DbType=dbtype.string; intLength =-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); }    }

Third, modify the source of the dapper, in the Sqlmapper.createparaminfogenerator method, find the following code

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; }                //our code is inserted hereDbType 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; }

The code after the modification is as follows:

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; }                //Insert the modified code hereAttribute lstrattr = Attribute.GetCustomAttribute (prop,typeof(Longstringattribute)); if(Lstrattr! =NULL)                {                    //Special handling for long stringIl. 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; }

So that, when used, if our data table of a field is the ntext type, then we only need to define the corresponding entity class, the corresponding attribute with Longstringattribute,dapper can automatically recognize the field, insert the correct data.

Use the following code:

//entity class definitionusingSystem;usingDapper;namespaceentity{ Public classProduct { Public intId {Get;Set; }  Public intShopid {Get;Set; }  Public stringType {Get;Set; }  Public stringOutid {Get;Set; }  Public stringLink {Get;Set; }  Public stringTitle {Get;Set; } [Longstring] Public stringContent {Get;Set; }  Public decimalPrice {Get;Set; }  Public intAmount {Get;Set; } }}//calledProduct P=Newproduct (); P.shopid=1; P.title="Leung Chun-ying: \ "occupy china \" Do not tempt Beijing to endure the bottom line"; P.link="http://news.163.com/14/1021/13/A936JGST0001124J.html"; stringstr = Filehelper.readtextfile ("Content.txt"); P.content=str; P.type="NetEase"; P.outid="a936jgst0001124j"; P.price=123.45M; P.amount=999; SqlCeConnection Conn=NewSqlCeConnection ("Data source=test.sdf"); Conn.            Open (); stringsql ="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 ();

I use the dapper version of the blog Park @wushilonng rewritten for. NET 2.0 version, unknown whether the latest version of Dapper for this aspect has been improved.

------Complete the full text-----

Allows Dapper+sqlce to support ntext data types and more than 4000 characters of storage