asp.net core使用mysql.data和dapper操作mysql数据库

asp.net core的生产环境一般是mysql+linux。

mysql.data是mysql基于.net的官方驱动。
github地址
https://github.com/mysql/mysql-connector-net
本人写的mysql.data示例demo
http://blog.csdn.net/Chen_Victor/article/details/53866403

dapper是.net环境下，一个高效简单的对象映射框架。
github地址
https://github.com/StackExchange/dapper-dot-net

mysql.data+dapper能够实现快速高效的搭建项目的db操作层。

---

官方介绍的性能对比：
The performance tests are broken in to 3 lists:

• POCO serialization for frameworks that support pulling static typed objects from the DB. Using raw SQL.
• Dynamic serialization for frameworks that support returning dynamic lists of objects.
• Typical framework usage. Often typical framework usage differs from the optimal usage performance wise. Often it will not involve writing SQL.

Performance of SELECT mapping over 500 iterations - POCO serialization

Method	Duration	Remarks
Hand coded (using a SqlDataReader)	47ms	Can be faster
Dapper ExecuteMapperQuery	49ms
ServiceStack.OrmLite (QueryById)	50ms
PetaPoco	52ms
BLToolkit	80ms
SubSonic CodingHorror	107ms
NHibernate SQL	104ms
Linq 2 SQL ExecuteQuery	181ms
Entity framework ExecuteStoreQuery	631ms

Performance of SELECT mapping over 500 iterations - dynamic serialization

Method	Duration	Remarks
Dapper ExecuteMapperQuery (dynamic)	48ms
Massive	52ms
Simple.Data	95ms

Performance of SELECT mapping over 500 iterations - typical usage

Method	Duration	Remarks
Linq 2 SQL CompiledQuery	81ms	Not super typical involves complex code
NHibernate HQL	118ms
Linq 2 SQL	559ms
Entity framework	859ms
SubSonic ActiveRecord.SingleOrDefault	3619ms

按照官方的说法，dapper的性能的确值得使用！

---

首先，在project.json中加入引用

"Dapper": "1.50.2",
"SapientGuardian.MySql.Data": "6.9.813",

使用示例demo：

using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MySqlDataDapperDemo
{

    /*
     建表sql和创建存储过程sql 

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Procedure structure for spGetUser
-- ----------------------------
DROP PROCEDURE IF EXISTS `spGetUser`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetUser`(in uid int)
    READS SQL DATA
BEGIN
SELECT * from person where id = uid;
END
;;
DELIMITER ;
     */

    public class Program
    {
        public static void Main(string[] args)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            MySqlConnection con = new MySqlConnection("server=127.0.0.1;database=test;uid=root;pwd=123456;charset='gbk'");

            //插入数据
            //Insert(con);

            //插入数据，集合
            //InsertCollection(con);

            //删除数据
            //Delete(con);

            //修改数据
            //Update(con);

            //查找数据
            //Search(con);

            //简单事务
            //Transaction(con);

            //简单事务，回滚
            //TransactionRoolback(con);

            //存储过程
            //StoredProcedures(con);

            //动态类型转化
            //DynamicObjects(con);

            //集合参数化
            //IEnumerableParameterize(con);

            //多对象映射
            //MultiMapping(con);

            //一次获取多个对象
            //MultipleResults(con);

            Console.WriteLine("finish");
            Console.ReadKey();
        }

        #region 一般的数据库操作
        //插入数据
        private static void Insert(MySqlConnection connection)
        {
            int count = connection.Execute($"insert into person values(null,'张三','{DateTime.Now}');");
            Console.WriteLine(count);
        }

        //插入数据，集合
        private static void InsertCollection(MySqlConnection connection)
        {
            int count = connection.Execute(@"insert into person values (null,@a, @b)", new[] { new { a = "test1", b = DateTime.Now }, new { a = "test2", b = DateTime.Now }, new { a = "test3", b = DateTime.Now } });
            Console.WriteLine(count);
        }

        //删除数据
        private static void Delete(MySqlConnection connection)
        {
            int count = connection.Execute($"delete from person where name = '张三';");
            Console.WriteLine(count);
        }

        //修改数据
        private static void Update(MySqlConnection connection)
        {
            int count = connection.Execute($"update person set name = '李四' where name = 'test1';");
            Console.WriteLine(count);
        }

        //简单事务
        private static void Transaction(MySqlConnection connection)
        {
            connection.Open();
            MySqlTransaction transaction = connection.BeginTransaction();
            int count = connection.Execute($"insert into person values(null,'王五','{DateTime.Now}');");
            transaction.Commit();
            connection.Clone();
        }

        //查找数据
        private static void Search(MySqlConnection connection)
        {
            List<User> users = connection.Query<User>("select * from person;").ToList();
            Console.WriteLine(users.Count);
            Console.WriteLine(users.First().IgnoreField == null);
        }

        //简单事务，回滚
        private static void TransactionRoolback(MySqlConnection connection)
        {
            connection.Open();
            MySqlTransaction transaction = connection.BeginTransaction();
            int count = connection.Execute($"delete from person;", transaction);
            transaction.Rollback();
            connection.Clone();
        }

        //简单存储过程
        private static void StoredProcedures(MySqlConnection connection)
        {
            var user = connection.Query<User>("spGetUser", new { uid = 10 }, commandType: CommandType.StoredProcedure).SingleOrDefault();
            Console.WriteLine(user.Name);
        }
        #endregion

        #region 其他特性

        //动态类型转化
        private static void DynamicObjects(MySqlConnection connection)
        {
            var rows = connection.Query("select * from person;");
            var id = ((int)rows.First().id);
            Console.WriteLine(id);
        }

        //集合参数化
        private static void IEnumerableParameterize(MySqlConnection connection)
        {
            //下面的写法等价于  connection.Query<int>("select * from person where Id in (@Ids1, @Ids2)", new { Ids1 = 10, Ids2 = 11 });
            var users = connection.Query<int>("select * from person where id in @Ids", new { Ids = new int[] { 10, 11 } });
            Console.WriteLine(users.Count());
        }

        //多对象映射
        private static void MultiMapping(MySqlConnection connection)
        {
            var sql =
@"select * from post p 
left join person u on u.id = p.uid 
Order by p.Id";

            var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post; });
            var item = data.First();
            Console.WriteLine(item.Owner.Id);
            Console.WriteLine(item.Id);
        }

        //一次获取多个对象
        private static void MultipleResults(MySqlConnection connection)
        {
            var sql =
@"
select * from person where id = @uid;
select * from post where id = @pid";

            using (var multi = connection.QueryMultiple(sql, new { uid = 10, pid = 1 }))
            {
                var users = multi.Read<User>().ToList();
                Console.WriteLine(users.First().Name);
                var posts = multi.Read<Post>().Single();
                Console.WriteLine(posts.Title);
            }
        }
        #endregion

    }

    //dapper将sql执行结果中字段自动映射到同名的相应的变量中
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Birthday { get; set; }
        //如果执行sql后返回的结果没有对应字段，则为null；
        public string IgnoreField { get; set; }
    }

    class Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public int uid { get; set; }
        public User Owner { get; set; }
    }

}

demo的github地址：
https://github.com/BinGithub2015/aspdotnetcore/tree/master/MySql.Data%26Dapper/MySqlDataDapperDemo