Dapper是什么？

　　Dapper是一款.Net平台简单（Simple）的对象映射库，并且Dapper拥有着“微型ORM之王”的称号。就速度而言与手写ADO.NET SqlDateReader相同。ORM是Object Rela­tion­al Mapper的缩写，代表着数据库与编程语言之间的映射。简单来说就是使用Entity Framework、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀，那么Dapper会是不错的选择。(之前我也写过一篇关于sqlsugar轻量级的博客，大家可以对照一下)

Dapper基本用法

通过NuGet安装：Dapper包，如果要进行mysql连接还需要安装Mysql.Data包

如果进行SQL链接则安装System.Data.SqlClient包

通用方法类：

<connectionStrings>
    <add name="MysqlServer" connectionString="Database=用数据库名称;Data Source=IP;Port=端口;
User Id=用户名;Password=密码;Charset=utf8mb4;TreatTinyAsBoolean=false;" />
 //Mysql
    <add name="LinqConnection" connectionString="Data Source=IP;Initial Catalog=数据库名称;
User ID=用户名;Password=密码"  providerName="System.Data.SqlClient"/>     //sql
</connectionStrings>

Mysql连接方法：  
public class DapperService
        {
            public static MySqlConnection MySqlConnection()
            {
                string mysqlConnectionStr 
= ConfigurationManager.ConnectionStrings["MysqlServer"].ToString();
                var connection = new MySqlConnection(mysqlConnectionStr);
                connection.Open();
                return connection;
            }
        }
sql连接方法：
 public class DapperService
        {
            public static SqlConnection MySqlConnection()
            {
                string mysqlConnectionStr 
=ConfigurationManager.ConnectionStrings["LinqConnection"].ToString();
                var connection = new SqlConnection(mysqlConnectionStr);
               // var connection = new MySqlConnection(mysqlConnectionStr);
                connection.Open();
                return connection;
            }
        }

新增方法（单体、批量）：

public ActionResult GetDapper(CarModel carModel)
        {
            try
            {
                // string sqlconn = WebConfigurationManager.AppSettings["sqlConn"];
                // FormCollection只能接收post请求传递的参数
                using (IDbConnection conn = DapperService.MySqlConnection())
                {
                    int result 
= conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values (@UserTel,@UserName,@UserPwd)", carModel);
                }
                return Json("success");
            }
            catch(Exception e)
            {
                return Json("failed");
            }
        }

public ActionResult GetDapper(List<CarModel> carModel)
        {
            try
            {
                // string sqlconn = WebConfigurationManager.AppSettings["sqlConn"];
                // FormCollection只能接收post请求传递的参数
                using (IDbConnection conn = DapperService.MySqlConnection())
                {
                    int result = conn.Execute("insert into UserInfo(UserTel,UserName,UserPwd) values(@UserTel,@UserName,@UserPwd)", carModel);
                }
                return Json("success");
            }
            catch(Exception e)
            {
                return Json("failed");
            }
        }

删除方法（单体、批量）：

public static int Delete(CarModel carModel)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        return conn.Execute("delete from UserInfo where id=@ID", carModel);
    }
}

public static int Delete(List<CarModel> carModel)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        return conn.Execute("delete from UserInfo where id=@ID", carModel);
    }
}

更新方法（单体、批量）

public static int Update(CarModel carModel)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
    }
}

public static int Update(List<CarModel> carModel)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        return conn.Execute("update UserInfo set name=@name where id=@ID", carModel);
    }
}

查询：

/// <summary>
/// 无参查询所有数据
/// </summary>
/// <returns></returns>
public static List<CarModel> Query()
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
    return conn.Query<CarModel>("select * from UserInfo ").ToList();
    }
}

/// <summary>
/// 查询指定数据
/// </summary>
/// <param name="person"></param>
/// <returns></returns>
public static Person Query(CarModel carModel)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        return conn.Query<CarModel>("select * from UserInfo where id=@ID",carModel).SingleOrDefault();
}
}

Dapper的复杂操作

/// <summary>
/// In操作
/// </summary>
public static List<CarModel> QueryIn()
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        var sql = "select * from UserInfo where id in @ids";
        //参数类型是Array的时候，dappper会自动将其转化
        return conn.Query<CarModel>(sql, new { ids = new int[2] { 1, 2 }, }).ToList();
    }
}

public static List<CarModel> QueryIn(int[] ids)
{
    using (IDbConnection conn = DapperService.MySqlConnection())
    {
        var sql = "select * from UserInfo where id in @ids";
        //参数类型是Array的时候，dappper会自动将其转化
        return conn.Query<CarModel>(sql, new { ids }).ToList();
    }
}

多语句操作

   public ActionResult QueryMultiple()
        {
            try
            {
                // string sqlconn = WebConfigurationManager.AppSettings["sqlConn"];
                // FormCollection只能接收post请求传递的参数
                using (IDbConnection conn = DapperService.MySqlConnection())
                {
                    var sql=  "select * from Person; select * from UserInfo";
                    var multiReader = conn.QueryMultiple(sql);
                    var personList = multiReader.Read<Person>();
                    var bookList = multiReader.Read<CarModel>();
                    multiReader.Dispose();
                }
                return Json("success");
            }
            catch(Exception e)
            {
                return Json("failed");
            }
        }