关于dapper 使用的部分技巧

1.使用存储过程：

  public ResultMsg InsertOne(UserInfo model)
        {
            DynamicParameters p = new DynamicParameters();
            p.Add("@UserName", model.UserName);
            p.Add("@PassWord",model.PassWord,System.Data.DbType.String);
            p.Add("@RealName", model.RealName, System.Data.DbType.String);
            p.Add("@sex", model.Sex, System.Data.DbType.String);
            p.Add("@TeamId", model.TeamId, System.Data.DbType.Int32);
            p.Add("@Tel", model.Tel, System.Data.DbType.String);
            p.Add("@UserNo", model.UserNo, System.Data.DbType.String);
            p.Add("@UserType", model.UserType, System.Data.DbType.Int32);
            p.Add("@Address", model.Address, System.Data.DbType.String);
            p.Add("@IsAdmin", model.IsAdmin, System.Data.DbType.Int32);
            p.Add("@BothDay", model.BothDay, System.Data.DbType.DateTime);
            p.Add("@errId",0,System.Data.DbType.Int32,System.Data.ParameterDirection.Output);//设定返回值
            DapperDbHelper.Execute("InsertUserInfo",p,null,null,System.Data.CommandType.StoredProcedure);
            msg.ErrId = p.Get<Int32>("errId");//获取返回值
            return msg;

         }

2.使用事务（这是个假事务）：

 public ResultMsg UpdateList(VesselTableModel model)
        {
            ResultMsg msg = new ResultMsg();
            int modelId = model.ID;
            foreach (VesselDetailTableModel oneModel in model.ListDetail)
            {
                oneModel.Pid = model.ID;
            }
            using (IDbConnection dbConnection = DapperConnFactory.Instance.GetOpenConn())
            {
                IDbTransaction transaction = dbConnection.BeginTransaction();//创建事务
                try
                {
                    string sql = @"UPDATE [VesselTable] SET 
    				[JobsNumber] = @JobsNumber,
	  				[ReallyWeight] = @ReallyWeight,
	  				[PayingUnits] = @PayingUnits,
	  				[ContractNumber] = @ContractNumber,
	  				[WorkTime] = @WorkTime,
	  				[BillingPersonnel] = @BillingPersonnel,
	  				[Reviewers] = @Reviewers,
	  				[billableHours] = @billableHours,
	  				[CustomerName] = @CustomerName,
	  				[CostsPaid] = @CostsPaid,
	  				[zhongyi] = @zhongyi,
	  				[zhonger] = @zhonger,
	  				[zhongsan] = @zhongsan,
	  				[zhongsi] = @zhongsi,
	  				[zhongwu] = @zhongwu,
	  				[zhongliu] = @zhongliu,
                     [zhongqi]=@zhongqi,
	  				[ReviewType] = @ReviewType,
	  				[VesselName] = @VesselName 
				WHERE [ID] = @ID;
                SELECT SCOPE_IDENTITY();";//返回最后一条操作Id

                    msg.ReturnInt = DapperDbHelper.ExecuteScalar<int>(sql, model);
                    msg.Success = true;
                    if (msg.Success )
                    {
                        string sql1 = $"DELETE FROM VesselDetailTable where Pid='{model.ID}'";
                        msg.ReturnInt = DapperDbHelper.Execute(sql1);
                        if (msg.Success)
                        {
                            string sql2 =@"INSERT [VesselDetailTable](
    				[Pid],
  				[CargoName],
  				[ModusOperandi],
  				[Charge],
  				[BillingNumber],
  				[Price],
  				[Remarks],
  				[Amount],
  				[CarNumber],
  				[StatisticsNumber],
  				[OperatingDate],
  				[OperatingUser]  ,
                [PieceCount]
    			) VALUES(
    				@PID,
  				@CargoName,
  				@ModusOperandi,
  				@Charge,
  				@BillingNumber,
  				@Price,
  				@Remarks,
  				@Amount,
  				@CarNumber,
  				@StatisticsNumber,
  				@OperatingDate,
  				@OperatingUser,
                @PieceCount
    			); SELECT SCOPE_IDENTITY();";
                            msg.ReturnInt = DapperDbHelper.Execute(sql2, model.ListDetail);
                            msg.Success = true;
                        }                      
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    msg.Success = false;
                    throw;
                }
            }
            return msg;
        }

3.单独返回处理后的数据：

  msg.ReturnInt = DapperDbHelper.ExecuteScalar<int>(sql, model);//返回一条int数据

4.单独参数的传入：

  DapperDbHelper.QueryFirst<JobsTableModel>(sql, new { ID = ID });
    多个参数传入：
    DapperDbHelper.QueryFirst<staffinfo>(sql, new
                {
                    username = string.Format("{0}", username),
                    userpwd = string.Format("{0}", userpwd)
                });

5.数据库存储过程返回表格：

public ResultMsg InsertOne(UserInfo model)
        {
            DynamicParameters p = new DynamicParameters();
            p.Add("@UserName", model.UserName);
            p.Add("@PassWord",model.PassWord,System.Data.DbType.String);
            p.Add("@RealName", model.RealName, System.Data.DbType.String);
            p.Add("@sex", model.Sex, System.Data.DbType.String);
            p.Add("@TeamId", model.TeamId, System.Data.DbType.Int32);
            p.Add("@Tel", model.Tel, System.Data.DbType.String);
            p.Add("@UserNo", model.UserNo, System.Data.DbType.String);
            p.Add("@UserType", model.UserType, System.Data.DbType.Int32);
            p.Add("@Address", model.Address, System.Data.DbType.String);
            p.Add("@IsAdmin", model.IsAdmin, System.Data.DbType.Int32);
            p.Add("@BothDay", model.BothDay, System.Data.DbType.DateTime);
            p.Add("@errId",0,System.Data.DbType.Int32,System.Data.ParameterDirection.Output);//设定返回值
            DapperDbHelper.qurey<返回的类型>("InsertUserInfo",p,null,null,System.Data.CommandType.StoredProcedure); //发返回来回阿里
            msg.ErrId = p.Get<Int32>("errId");//获取返回值
            return msg;

         }

6.返回多条语句

/// <summary>
        /// 4条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="param">参数值（可选）</param>
        public static void QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, out IList<TFirst> tfList, out IList<TSecond> tsList, out IList<TThird> ttList, out IList<TFour> tfourList, object param = null)
        {
            using (IDbConnection con = DapperConnFactory.Instance.GetOpenConn())
            {
                var multi = con.QueryMultiple(sql, param);
                tfList = new List<TFirst>();
                tsList = new List<TSecond>();
                ttList = new List<TThird>();
                tfourList = new List<TFour>();
                if (!multi.IsConsumed)
                {
                    tfList = multi.Read<TFirst>().ToList();
                    tsList = multi.Read<TSecond>().ToList();
                    ttList = multi.Read<TThird>().ToList();
                    tfourList = multi.Read<TFour>().ToList();
                }
            }
        } 

数据导入方式

string sqlStr = @"select Id,Title,Author from Article where Id = @Id
                      select * from QQModel where Name = @Name
                      select * from SeoTKD where Status = @Status";
                      

即可