Dapper使用详解 - 栀子味的四月

Dapper使用详解

Dapper是什么?

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

Dapper基本用法

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

通用方法类:

<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");
            }
        }
暂无评论

节省时间,也就是使一个人的有限的生命,更加有效地延长了人的生命。



发表评论