Application of C #. NET ORM Framework Dapper.net

Previously used Entity Framework 5.0, SqlHelper, Mybatis.net and other Orm, of course, SqlHelper does not count.

Feeling is learning Chen Ben Very high, configuration is also more complex, especially mybatis is all XML, Meng forced ~~~~~

EF with good also quite flexible, that is, some databases do not support such as MySQL, the internet also looked for the EF access to MySQL method, I think it is more onerous

MyBatis support for most databases, entity mapping is also relatively convenient, is too flexible, add a table need to configure several XML

Continue to look for and flexible and have the entity mapping ORM, finally chose the dapper.net. You'll also like it after you use it, with the following features

1. Similar ado.net, flexible splicing of SQL

2. Mapping of Entities and datasets (supports a pair of one or one pairs of multiple entity mappings)

3. The cost of learning is extremely low only two methods query (), execute ()

4. Can be said to be 0 configuration, as long as the configuration of the connection string in config OK

5. Class Library is very small only a SqlMapper.cs file (or like vs2015 previous code, so I used the dapper.net 1.4.2, upload to my resources)

On the code, which contains the add, delete, change, check, and transaction use (here is the test used MySQL database)

One, Data script

#订单表 CREATE TABLE ' order_info ' (' order_id ' int (one) not NULL auto_increment COMMENT ' order ID ', ' user_id ' int (one) DEFAULT Null COMMENT ' User ID ', ' order_code ' char (#) default null COMMENT ' order number ', ' Amount ' decimal (10,0) default null COMMENT ' order
  Amount ', ' create_time ' timestamp (6) NULL default NULL COMMENT ' next single time ', ' goods_count ' int (one) default null COMMENT ' Quantity of merchandise ', PRIMARY KEY (' order_id ')) Engine=innodb auto_increment=6 DEFAULT charset=utf8 #订单商品表 CREATE TABLE ' order_item ' (' ite m_id ' int (one) not NULL auto_increment COMMENT ' order Content ID ', ' order_id ' int (one) not NULL COMMENT ' commodity id ', ' goods_name ' char ( Default NULL COMMENT ' commodity name ', ' Unit_price ' decimal (11,0) default null COMMENT ' unit price ', ' goods_count ' int (one) default N ull COMMENT ' Quantity of goods ', ' Amount ' decimal (11,0) default NULL COMMENT ' Total amount ', ' create_time ' timestamp (6) NULL default NULL CO Mment ' creation time ', ' Update_time ' timestamp (6) null DEFAULT null COMMENT ' modified time ', PRIMARY KEY (' item_id ') engine=innodb O_increment=8 DEFAULT CHArset=utf8 #用户表 CREATE TABLE ' user_info ' (' user_id ' int (one) not NULL auto_increment COMMENT ' user ID ', ' user_name ' char (6) Default null COMMENT ' user name ', ' Upwd ' char (#) default null COMMENT ' password ', ' create_time ' timestamp () NULL default NU LL COMMENT ' creation time ', ' Update_time ' timestamp (6) NULL default NULL COMMENT ' Modify time ', ' Age ' int (3) DEFAULT null COMMENT ' ages ' , ' Sex ' char (2) default NULL COMMENT ' sex ', PRIMARY KEY (' user_id ') engine=innodb auto_increment=5 DEFAULT Charset=ut
 F8

Stored procedures for testing

#带有输入输出参数的过程
DELIMITER $$
CREATE PROCEDURE ' P_getusercount ' (
 _sex CHAR, out
 _total INT)
BEGIN
	SET @sex = _sex;
    SELECT COUNT (*) into the _total from User_info WHERE sex= @sex;	
end$$

#返回数据集的过程
DELIMITER;
CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' p_getuserinfo ' (
		_userid INT
    )
BEGIN
		SET @uid = _ UserID;
		SET @sql = CONCAT (' select * from User_info where user_id= ', @uid);
		IF (@uid <=0) THEN
			SET  @sql = ' select * from User_info ';
		End IF;
		PREPARE strSQL from @sql; #定义预处理语句 
		EXECUTE strSQL;	
		Deallocate PREPARE strSQL;	#删除定义 
    end$$
DELIMITER;

Ii. Entities & Tools

Using System;
Using System.Collections.Generic;
Using System.Linq;

Using System.Text;
            Namespace Testdapperdotnet.models {public class Order_infomodel {public Order_infomodel () {
        Goodslist = new list<order_itemmodel> ();
        public int order_id {set;
        public int user_id {set; get;}
        public string Order_code {set;
        Public decimal Amount {set;
        Public DateTime Create_time {set;
        public int Goods_count {set; get;}  <summary>///the next single user information (test one-to-one mapping)///</summary> public User_infomodel userInfo {set; Get ///<summary>///Order product information (test One-to-many mappings)///</summary> public List<order_itemmo
    Del> goodslist {set; get;}
        public class Order_itemmodel {public int item_id {set;
        public int order_id {set; get;} public string Goods_name {set; Get
        Public decimal Unit_price {set;
        public int Goods_count {set; get;}
        Public decimal Amount {set;
        Public DateTime Create_time {set;
    Public DateTime Update_time {set;
        public class User_infomodel {public int user_id {set;
        public string user_name {set;
        public string Upwd {set;
        Public DateTime Create_time {set;
        Public DateTime Update_time {set;
        public int Age {set;
    public string Sex {set;
} using System;
Using System.Collections.Generic;
Using System.Configuration;
Using System.Linq;

Using System.Text; Namespace Testdapperdotnet {public class Dbconfig {public static readonly string test_w = C onfigurationmanager.connectionstrings["Test_w"].
        ConnectionString; public static readonly String test_r = Configurationmanager.connectionstrings["Test_r"].
    ConnectionString; }
}

Third, dapper.net test class

Using System;
Using System.Collections.Generic;
Using System.Data;
Using System.Linq;
Using Dapper;
Using Microsoft.VisualStudio.TestTools.UnitTesting;

Using Testdapperdotnet.models; namespace Testdapperdotnet {[TestClass] public class Testdapper {///<summary>///get Connected connect string///</summary>///<returns></returns> public idbconnection getsqlconnectio
        N () {return new MySql.Data.MySqlClient.MySqlConnection (dbconfig.test_w);; ///<summary>///Query Records return entity///</summary> [TestMethod] public void T
                Estqueryt () {using (var conn = getsqlconnection ()) {//Order table mapping single record
                var sql = @ "SELECT * from Order_info WHERE order_id= @order_id;"; var list = conn. query<order_infomodel> (SQL, new {order_id = 2}).
 ToList ();               var itme = list.
                FirstOrDefault ();

                Assert.istrue (itme.order_id = 2);
                Order table mapping multiple Records sql = @ "SELECT * from Order_info"; List = conn. query<order_infomodel> (SQL, new {order_id = 2}).
                ToList (); Assert.istrue (list!= null && list.

            COUNT&GT;0); }///<summary>///one-to-one mapping///There is a user information attribute under the order entity, user_id associated///</summary&
        Gt
            [TestMethod] public void Testonetoone () {using (IDbConnection conn = Getsqlconnection ()) 
                {var sql = @ "Select a.*,b.* from Order_info a JOIN user_info b on a. ' user_id ' =b.user_id;"; var list = conn.  Query<order_infomodel, User_infomodel, order_infomodel> (SQL, (Order, user) => {OrdeR.userinfo = user;
                    return order;
                    }, NULL, NULL, True, "order_id" , NULL, NULL).

                ToList (); List.
                ForEach (x => {assert.istrue (x.userinfo.user_id = = x.user_id);
                });
            Assert.istrue (TRUE); }///<summary>///one-to-many mappings///</summary> [TestMethod] Publi
                c void Testonetomore () {using (IDbConnection conn = Getsqlconnection ()) {
                var sql = @ "Select a.*,b.* from Order_info a JOIN order_item b on a.order_id=b.order_id;";
                The merged order data var orderdic = new Dictionary<int, order_infomodel> (); var originlist = conn.
 Query<order_infomodel, Order_itemmodel, order_infomodel> (                   SQL, (Order, goods) => {//Require manual maintenance, a
                        Order_infomodel ord of multi-object relationship;
                            if (!orderdic.trygetvalue (order.order_id, out Ord)) {ord = order;
                        Orderdic.add (order.order_id, order);
                        } ord.goodsList.Add (goods);
                    return order;
                    }, NULL, NULL, True, "order_id" , NULL, NULL).
                ToList (); Project a list var list = Orderdic.select (x => x.value).

                ToList (); List.
                        ForEach (x => {x.goodslist.foreach) (o => {
               Assert.istrue (o.order_id = = x.order_id);     });
                });
            Assert.istrue (TRUE); 
            } [TestMethod] public void Testinsertone () {var user = new User_infomodel ()
                {user_name= "tester", upwd= "123", Create_time=datetime.now,
            Update_time=datetime.now, age=99, sex= "female"}; var sql = @ ' INSERT into ' test '. ' User_info ' (' user_id ', ' user_name ', ' upwd ', ' create_time ', ' update_time ', ' a 
  GE ', ' Sex ' VALUES (@user_id, @user_name, @upwd, @create_time, @update_time, @age, @sex
            ) ;"; using (IDbConnection conn = Getsqlconnection ()) {var n = conn.
                Execute (sql, user);
            Assert.istrue (n = 1);
        }///<summary>///execute the stored procedure for the return dataset///</summary> [TestMethod] Public VOID Testcallp_getuserinfo () {#region MySQL stored procedure script/* DELIMITER $$ CREATE PROCEDURE ' te
		St '. ' P_getuserinfo ' (_userid INT) BEGIN SET @uid = _userid;
		SET @sql = CONCAT (' select * from User_info where user_id= ', @uid);
		IF (@uid <=0) THEN SET @sql = ' select * from User_info ';
		End IF;	
		PREPARE strSQL from @sql; #定义预处理语句 EXECUTE strSQL;	Deallocate PREPARE strSQL;
             #删除定义 end$$ DELIMITER;
            * * #endregion var sql = "P_getuserinfo"; using (IDbConnection conn = Getsqlconnection ()) {var list = conn. query<user_infomodel> (SQL, new {_userid = 0//Query
                    multiple}, NULL, true, NULL, CommandType.StoredProcedure).
                ToList ();
            Assert.istrue (List!=null);
        
}
        }        <summary>///Execute stored procedures with input and output parameters///</summary> [TestMethod] Public void Testcallp_getusercount () {#region MySQL stored procedure script/* DELIMITER $$ CREATE PROCEDURE ' p
    _getusercount ' (_sex CHAR, out _total INT) BEGIN SET @sex = _sex;	
SELECT COUNT (*) into the _total from User_info WHERE sex= @sex;
             end$$ DELIMITER;
            * * #endregion var sql = "P_getusercount";
            Dynamicparameters pars = new Dynamicparameters (); Pars.
            ADD ("_sex", "female"); Pars.
            ADD ("_total", "" ", Dbtype.int32, ParameterDirection.Output); using (IDbConnection conn = Getsqlconnection ()) {var n = conn. Execute (SQL, pars, NULL, NULL, commandtype .
                StoredProcedure); var total = pars.
                Get<int32> ("_total");
 Assert.istrue (Total > 0);           } [TestMethod] public void testtransaction () {using (idbconnecti On conn = Getsqlconnection ()) {Conn.
                Open (); Start transaction var transaction = conn.
                BeginTransaction ();
                        try {var order = new Order_infomodel () {
                        Amount = Create_time = DateTime.Now, Goods_count = 3,
                    Order_code = "20160608001", user_id = 2,}; var item = new Order_itemmodel () {amount = m, creat
                        E_time = DateTime.Now, Goods_count = 3, Goods_name = "001", order_id = 2, Unit_price = 99.99M, Update_time = DateTime.Now}; var sql = @ "INSERT into ' order_info ' (' user_id ', ' order_code ', ' Amount ', ' create_time ', ' goods_count ') VALUE
                    S (@user_id, @order_code, @amount, @create_time, @goods_count); "; int n = conn.
                    Execute (SQL, order, transaction);
                    Assert.istrue (n = 1); if (n!= 1) {transaction.
                        Rollback ();
                    Return
  sql = @ ' INSERT into ' order_item ' (' order_id ', ' goods_name ', ' unit_price ', ' Goods_count ', ' Amount ', ' create_time ', ' Update_time ' VALUES (@order_id, @goods_name, @unit_price, @goods_coun
                    T, @amount, @create_time, @update_time); "; n = conn.
                    Execute (SQL, item, transaction);
                    Assert.istrue (n = 1);
                    if (n!= 1){transaction.
                        Rollback ();
                    Return
                //Submit transaction transaction.commit (); The catch (Exception ex) {//exception occurred, transaction rollback TRANSAC tion.
                Rollback ();
 }
            }
        }
    }
}