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