C#使用Dapper框架(快速上手)

---

前言

在项目用到了Dapper这个库，记录一下

Dapper 是一款轻量级的 ORM（对象关系映射）工具，适用于小型项目。相比于 Entity Framework 和 NHibernate 这些重量级的 ORM 工具，Dapper 更为简洁高效

---

一、Dapper

轻量: Dapper 只有一个文件 SqlMapper.cs，编译后大小约为 120KB
速度快: Dapper 的速度接近 IDataReader，并且在处理列表数据时比 DataTable 更快
支持多种数据库: 包括 SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL 和 SQL Server
灵活的关系映射: 支持一对一、一对多、多对多等多种关系映射
高性能: 通过 Emit 反射 IDataReader 的序列化过程，实现高效的对象生成
兼容性强: 支持 .NET Framework 2.0 至 4.5 版本

二、常用映射方式

1.自动映射（属性名与列名一致）

Dapper 默认通过实体类的 属性名 与数据库表中的 列名 进行映射
只要属性名与列名完全一致，Dapper 就会自动将查询结果映射到实体类的属性

2.使用ColumnAttribute 映射（如果名称不同）

public class User
{
    [Column("user_id")]
    public int UserId { get; set; }
    
    [Column("user_name")]
    public string UserName { get; set; } 
}

user_id为数据库中列名，实体类的属性名是 UserId

3. 手动映射（使用 SQL 别名）

var users = connection.Query<User>(
    "SELECT user_id AS UserId, user_name AS UserName  FROM Users"
).ToList();

SQL 查询使用了 AS 别名将数据库中的 user_id、user_name映射到 UserId、UserName属性上

三、基本用法

使用 NuGet 安装:
打开 Visual Studio 项目，依次点击 工具 -> NuGet 包管理器 -> 管理解决方案的 NuGet 程序包。
在搜索框中输入 Dapper，找到并点击 Dapper，勾选需要安装的项目，然后点击安装

1.插入操作

public bool AddRunningWorkOrderInfo(WorkOrderSDB04Info RunningWorkOrderInfo)
{
    try
    {
        using (IDbConnection db = new SqlConnection(SysUtil.ConnectionString))
        {
            string sql = "INSERT INTO SDB04 (Model, WorkOrderNo, PlanQuantity, PlannedIssueTime, LoadedQuantity, UnloadedQuantity, StationId,PlanRunTime) VALUES (@Model, @WorkOrderNo, @PlanQuantity, @PlannedIssueTime, @LoadedQuantity, @UnloadedQuantity, @StationId,@PlanRunTime)";
            return db.Execute(sql, RunningWorkOrderInfo) > 0;
        }
    }
    catch (Exception)
    {
        throw;
    }
}

2.删除操作

public bool DeleteTobeRunWorkOrderInfo(int sId)
  {
      try
      {
          using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
          {
              string sql = "DELETE FROM SDB03 WHERE Sid=@SId ";
              return DB.Execute(sql, new { SId = sId }) > 0;
          }
      }
      catch (Exception)
      {
          throw;
      }
  }

2.更新操作

        public bool UpdateWorkOrderStatus(EWorkOrderStatus workOrderStatus, string workOrderNo)
        {
            try
            {
                using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
                {
                    string sql = "UPDATE SDB02 SET WorkOrderStatus=@WorkOrderStatus WHERE WorkOrderNo=@WorkOrderNo";
                    return DB.Execute(sql, new { WorkOrderStatus = workOrderStatus, WorkOrderNo = workOrderNo }) > 0;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

3.查询操作

单个对象

  public WorkOrderSDB04Info GetRunningWorkOrderInfos(string workOrderNo)
  {
      try
      {
          using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
          {
              string sql = "SELECT * FROM SDB04 WHERE WorkOrderNo=@workOrderNo ";
              return DB.Query<WorkOrderSDB04Info>(sql, new { workOrderNo = workOrderNo }).ToList().FirstOrDefault();
          }
      }
      catch (Exception)
      {
          throw;
      }
  }

List集合

   public List<WorkOrderSDB03Info> GetTobeRunWorkOrderInfos(int stationId)
   {
       try
       {
           using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
           {
               string sql = "SELECT * FROM SDB03 WHERE StationId=@StationId ";
               return DB.Query<WorkOrderSDB03Info>(sql, new { StationId = stationId }).ToList();
           }
       }
       catch (Exception)
       {
           throw;
       }
   }

带过滤条件查询

public List<SBQualityInfo> GetQualityInfos(SBQualityFilterInfo filterInfo)
{
    try
    {
        using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" SELECT * FROM SDB07   WHERE 1=1 ");
            if (!string.IsNullOrEmpty(filterInfo.Model))
            {
                sb.AppendLine(" AND Model=@Model");
            }
            if (filterInfo.StationID.HasValue)
            {
                sb.AppendLine(" AND StationID=@StationID");
            }
            if (!string.IsNullOrEmpty(filterInfo.SN))
            {
                sb.AppendLine(" AND SN=@SN");
            }
            if (filterInfo.FinishTime.HasValue)
            {
                sb.AppendLine(" AND FinishTime=@FinishTime");
            }
            sb.AppendLine("  ORDER BY FinishTime DESC");
            return DB.Query<SBQualityInfo>(sb.ToString(), filterInfo).ToList();
        }
    }
    catch (Exception)
    {
        throw;
    }
}

4.事务操作

  public void RunWorkOrder(string workOrderNo, WorkOrderSDB04Info info)
  {
      if (info == null)
      {
          return;
      }
      WorkOrderSDB04Info workOrderSDB04 = this.GetRunningWorkOrderInfos(workOrderNo);
      if (workOrderSDB04!=null)
      {
          return;
      }
      try
      {
          using (IDbConnection DB = new SqlConnection(SysUtil.ConnectionString))
          {
              DB.Open();
              //开启事务
              IDbTransaction tran = DB.BeginTransaction();
              try
              {
                  // 更新 SDB02 表
                  string updateSDB02Sql = "UPDATE SDB02 SET WorkOrderStatus = 2 WHERE WorkOrderNo = @WorkOrderNo";
                  DB.Execute(updateSDB02Sql, new { WorkOrderNo = workOrderNo }, tran);
                  // 删除 SDB03 表
                  string deleteSDB03Sql = "DELETE FROM SDB03 WHERE WorkOrderNo = @WorkOrderNo";
                  DB.Execute(deleteSDB03Sql, new { WorkOrderNo = workOrderNo }, tran);
                  // 添加 SDB04 表
                  string addSDB04Sql = "INSERT INTO SDB04 (Model, WorkOrderNo, PlanQuantity, PlannedIssueTime, LoadedQuantity, UnloadedQuantity, StationId,PlanRunTime) VALUES (@Model, @WorkOrderNo, @PlanQuantity, @PlannedIssueTime, @LoadedQuantity, @UnloadedQuantity, @StationId,@PlanRunTime)";
                  DB.Execute(addSDB04Sql, info, tran);
                  // 提交事务
                  tran.Commit();
              }
              catch (Exception ex)
              {
                  // 回滚事务
                  tran.Rollback();
                  throw ex;
              }
          }
      }
      catch (Exception ex)
      {
          throw;
      }
  }