Dapper

---

前言

记录学习成果，以便温故而知新

Dapper是一款轻量级ORM框架，上手容易，满足基本需求，所以记录使用过程如下

---

1.安装包

NuGet安装Dapper

2.准备数据表与实体类

t_user表

CREATE TABLE `t_user` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `Name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `Age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

User类

	/// <summary>
    /// 用户表t_user
    /// </summary>
    public class User
    {
        /// <summary>
        /// 主键、自增
        /// </summary>
        public long Id { get; set; }
        /// <summary>
        /// 姓名
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// 年龄
        /// </summary>
        public Nullable<int> Age { get; set; }

        public override string ToString()
        {
            return string.Format("User:Id=>{0},Name=>{1},Age=>{2}", Id, Name, Age);
        }
     }

3.工具类

DapperHelper类

    /// <summary>
    /// 工具类
    /// </summary>
    public class DapperHelper
    {
        /// <summary>
        /// 连接MySQL数据库
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection MySqlCon()
        {
            string mysqlConnectionStr = "server=localhost;database=test;uid=root;pwd=root;charset=utf8;port=3306";
            var connection = new MySqlConnection(mysqlConnectionStr);
            connection.Open();
            return connection;
        }

        /// <summary>
        /// insert或update或delete
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Excute(string sqlStr, object param)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                var trans = conn.BeginTransaction();//开启事务
                int result = -1;
                try
                {
                    result = conn.Execute(sqlStr, param, trans);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    trans.Rollback();//事务回滚
                    return -1;
                }
                trans.Commit();//事务提交
                return result;
            }
        }

        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public long Count(string sqlStr, object param)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                return conn.QueryFirst<long>(sqlStr, param);
            }
        }

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T QueryObject<T>(string sqlStr, object param)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                return conn.QueryFirstOrDefault<T>(sqlStr, param);
            }
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sqlStr"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<T> QueryList<T>(string sqlStr, object param)
        {
            using (IDbConnection conn = DapperHelper.MySqlCon())
            {
                return conn.Query<T>(sqlStr, param) as List<T>;
            }
        }
    }

4.调用

建立单元测试项目测试

	[TestClass]
    public class UnitTestDapperHelper
    {
        private readonly DapperHelper dapperHelper = new DapperHelper();
        [TestMethod]
        public void TestInsert()
        {
            //插入Tom
            User Tom = new User() { Name = "Tom", Age = 2 };
            int result = dapperHelper.Excute("insert into t_user(Name, Age) values(@Name, @Age)", Tom);
            Assert.AreEqual(1, result);

            //插入Jerry
            User Jerry = new User() { Name = "Jerry", Age = 1 };
            result = dapperHelper.Excute("insert into t_user(Name, Age) values(@Name, @Age)", Jerry);
            Assert.AreEqual(1, result);
        }

        [TestMethod]
        public void TestQueryList()
        {
            //查所有
            dapperHelper.QueryList<User>("select * from t_user", null).ForEach(Console.WriteLine);
        }

        [TestMethod]
        public void TestCount()
        {
            //查总条数
            long total = dapperHelper.Count("select count(Id) as total from t_user", null);
            Console.WriteLine(total);
        }

        [TestMethod]
        public void TestQueryObject()
        {
            //查询单个对象
            User user = dapperHelper.QueryObject<User>("select * from t_user where Id = @Id", new User() { Id = 1 });
            Console.WriteLine(user);
        }

        [TestMethod]
        public void TestUpdate()
        {
            //修改对象
            int result = dapperHelper.Excute("update t_user set Name = @Name, Age = @Age where Id = @Id",
                new User() { Id = 1, Name = "Tomcat", Age = 3 });
        }

        [TestMethod]
        public void TestDelete()
        {
            //删除
            int result = dapperHelper.Excute("delete from t_user where Id = @Id",
                new User() { Id = 2 });
        }
    }