c# .net orm 框架 dapper.net 的应用

之前用过 entity framework 5.0 、sqlhelper 、mybatis.net 等 orm，当然 sqlhelper 不算。

感觉都是学习陈本挺高的，配置也比较复杂，尤其是 mybatis 全是 xml ，懵逼了 ~~~~~

ef 用着不错也挺灵活，就是有些数据库不支持 如mysql，网上也找过ef 接入 mysql 的方法，觉得还是比较繁重

mybatis 支持大多数的数据库，实体映射也相对方便，就是灵活性太差，加一个表需要配置 好几个 xml

继续寻找及灵活又有实体映射的 orm，最后选择了 dapper.net。用过之后你也会喜欢上它的，有以下几个特性

1.类似 ado.net 的写法，灵活拼接sql

2.有实体和数据集的映射（支持一对一、一对多实体映射）

3.学习成本超级低只有两个方法 query（）、execute（）

4.可以说是 0 配置，只要在 config 中配置连接字符串就 ok了

5.类库非常小 只有一个 sqlMapper.cs 的文件（还是喜欢vs2015以前的代码，所以我用的是 dapper.net 1.4.2，上传到我的资源中）

上代码，里面包含增、删、改、查，和事务的使用（这里测试用的是mysql数据库）

一、数据脚本

#订单表
CREATE TABLE `order_info` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单id',
  `user_id` int(11) DEFAULT NULL COMMENT '用户id',
  `order_code` char(50) DEFAULT NULL COMMENT '订单号',
  `amount` decimal(10,0) DEFAULT NULL COMMENT '订单金额',
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '下单时间',
  `goods_count` int(11) DEFAULT NULL COMMENT '商品数量',
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

#订单商品表
CREATE TABLE `order_item` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单内容id',
  `order_id` int(11) NOT NULL COMMENT '商品id',
  `goods_name` char(150) DEFAULT NULL COMMENT '商品名',
  `unit_price` decimal(11,0) DEFAULT NULL COMMENT '单价',
  `goods_count` int(11) DEFAULT NULL COMMENT '商品数量',
  `amount` decimal(11,0) DEFAULT NULL COMMENT '总金额',
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

#用户表
CREATE TABLE `user_info` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `user_name` char(50) DEFAULT NULL COMMENT '用户名',
  `upwd` char(50) DEFAULT NULL COMMENT '密码',
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改时间',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

测试用的存储过程

#带有输入输出参数的过程
DELIMITER $$
CREATE PROCEDURE `p_getUserCount`(
 _sex CHAR,
 OUT _total INT)
BEGIN
	SET @sex = _sex;
    SELECT COUNT(*) INTO _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 ;

二、实体&工具类

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; get; }
        public int user_id { set; get; }
        public string order_code { set; get; }
        public decimal amount { set; get; }
        public DateTime create_time { set; get; }
        public int goods_count { set; get; }
        /// <summary>
        /// 下单用户信息(测试一对一映射)
        /// </summary>
        public user_infoModel userInfo { set; get; }
        /// <summary>
        /// 订单商品信息(测试一对多映射)
        /// </summary>
        public List<order_itemModel>  goodsList { set; get; }
    }

	public class order_itemModel
    {
        public int item_id { set; get; }
        public int order_id { set; get; }
        public string goods_name { set; get; }
        public decimal unit_price { set; get; }
        public int goods_count { set; get; }
        public decimal amount { set; get; }
        public DateTime create_time { set; get; }
        public DateTime update_time { set; get; }
    }

	public class user_infoModel
    {
        public int user_id { set; get; }
        public string user_name { set; get; }
        public string upwd { set; get; }
        public DateTime create_time { set; get; }
        public DateTime update_time { set; get; }
        public int age { set; get; }
        public string sex { set; get; }
    }
}

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 = 
            ConfigurationManager.ConnectionStrings["test_w"].ConnectionString;
        public static readonly string Test_R = 
            ConfigurationManager.ConnectionStrings["test_r"].ConnectionString;
    }
}

三、dapper.net 测试类

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>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        public IDbConnection GetSqlConnection()
        {
            return new MySql.Data.MySqlClient.MySqlConnection(DbConfig.Test_W); ;
        }
        /// <summary>
        /// 查询记录返回实体
        /// </summary>
        [TestMethod]
        public void TestQueryT()
        {
            using (var conn = GetSqlConnection())
            {
                //订单表映射单条记录
                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);

                //订单表映射多条记录
                sql = @"SELECT * FROM order_info";
                list = conn.Query<order_infoModel>(sql, new
                {
                    order_id = 2
                }).ToList();
                Assert.IsTrue(list != null && list.Count>0);

            }
        }

        /// <summary>
        /// 一对一映射
        /// 订单实体下有个用户信息属性，通过user_id关联
        /// </summary>
        [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>
        /// 一对多映射
        /// </summary>
        [TestMethod]
        public 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;";
                //合并后的订单数据
                var orderDic = new Dictionary<int, order_infoModel>();
                var originList = conn.Query<order_infoModel, order_itemModel, order_infoModel>(
                    sql
                    , (order, goods) =>
                    {
                        //需要手动维护，一对多对象关系
                        order_infoModel ord;
                        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();
                //投影一个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="女"
            };
            var sql = @"
INSERT INTO `test`.`user_info` (
  `user_id`,
  `user_name`,
  `upwd`,
  `create_time`,
  `update_time`,
  `age`,
  `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>
        /// 执行"返回数据集"的存储过程
        /// </summary>
        [TestMethod]
        public void TestCallp_getuserinfo()
        {
            #region mysql 存储过程脚本
            /*
DELIMITER $$
CREATE
    PROCEDURE `test`.`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 //查询多个
                     }, 
                    null, 
                    true,
                    null,
                    CommandType.StoredProcedure).ToList();
                Assert.IsTrue(list!=null);
            }
        }
        
        /// <summary>
        /// 执行“带有输入输出参数”的存储过程
        /// </summary>
        [TestMethod]
        public void TestCallp_getUserCount()
        {
            #region mysql 存储过程脚本
            /*
DELIMITER $$

CREATE PROCEDURE `p_getUserCount`(
 _sex CHAR,
 OUT _total INT)
BEGIN
	SET @sex = _sex;
    SELECT COUNT(*) INTO _total FROM user_info WHERE sex=@sex;	
END$$

DELIMITER ;
             */
            #endregion
            var sql = "p_getUserCount";
            DynamicParameters pars = new DynamicParameters();
            pars.Add("_sex", "女");
            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 (IDbConnection conn = GetSqlConnection())
            {
                conn.Open();
                //开始事务
                var transaction = conn.BeginTransaction();
                try
                {
                    var order = new order_infoModel()
                    {
                        amount = 100,
                        create_time = DateTime.Now,
                        goods_count = 3,
                        order_code = "20160608001",
                        user_id = 2,
                    };
                    var item = new order_itemModel()
                    {
                        amount = 100,
                        create_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`
) 
VALUES
  (
    @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_count,
    @amount,
    @create_time,
    @update_time
  ) ;";
                    n = conn.Execute(sql, item, transaction);
                    Assert.IsTrue(n == 1);
                    if (n != 1)
                    {
                        transaction.Rollback();
                        return;
                    }
                    //提交事务
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                }
            }
        }
    }
}