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

来源：互联网发布：深入理解linux内核pdf 编辑：程序博客网时间：2024/05/01 13:49

之前用过 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)BEGINSET @sex = _sex;    SELECT COUNT(*) INTO _total FROM user_info WHERE sex=@sex;END$$#返回数据集的过程DELIMITER ;CREATE DEFINER=`root`@`localhost` PROCEDURE `p_getUserInfo`(_userid INT    )BEGINSET @uid = _userid;SET @sql = CONCAT('select * from user_info where user_id=',@uid );IF(@uid<=0) THENSET  @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    )    BEGINSET @uid = _userid;SET @sql = CONCAT('select * from user_info where user_id=',@uid );IF(@uid<=0) THENSET  @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)BEGINSET @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();                }            }        }    }}

1 0