使用Dapper批量插入或更新实体, 查询实体等实例

public class GenerateMsaDocumentSignParametersDataAccess
    {
        protected string TcitClientConn = System.Configuration.ConfigurationManager.ConnectionStrings["DBTest"].ToString();
　　　　　　//单个字段取值
        public string GetDocumentSignHandleClassNameByDocId(long msaDocId)
        {
            using (var conn = new SqlConnection(TcitClientConn))
            {
                return conn.QueryFirstOrDefault<string>(@"SELECT [DocumentHandleClassName]
                                                            FROM [dbo].[DocumentHandleMappingXXX] WHERE DocumentId=@msaDocId", new { msaDocId });
            }
        }
　　　　 
　　　　//获取实体List
        public List<GrantsEntityExtend> GrantsEntityExtends(int companyId, string employeeId)
        {
            const string querySql = @" SELECT emp.FirstName , 
                                              emp.MiddleName , 
                                              emp.LastName,
                                               grants.*
                                        FROM GrantInfoxxx.Grants grants
                                            LEFT JOIN CompanyInfoxxx.Employee emp
                                                ON emp.CompanyID = grants.CompanyID
                                                   AND emp.EmployeeID = grants.EmployeeID
                                        WHERE grants.CompanyID = @companyId
                                              AND grants.EmployeeID = @employeeId;";
            using (var conn = new SqlConnection(TcitClientConn))
            {
                var result = conn.Query<GrantsEntityExtend>(querySql, new { companyId, employeeId }).ToList();
                return result;
            }
        }

　　　　　　//in的用法
        public List<VestingEntityExtend> GrantsVestingExtends(int companyId, string employeeId, string[] grantIds)
        {
            const string querySql = @"SELECT * FROM GrantInfoxxx.Vesting WHERE CompanyID=@companyId AND EmployeeID=@employeeId AND GrantID IN @grantIds";
            using (var conn = new SqlConnection(TcitClientConn))
            {
                var result = conn.Query<VestingEntityExtend>(querySql, new { companyId, employeeId, grantIds = grantIds }).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入批量实体
        /// </summary>
        /// <param name="documentSigningParameterModels">待插入集合</param>
        /// <returns>返回影响行数</returns>
        public int InsertDocumentSignatureFieldValueTable(List<DocumentSigningParameterModel> documentSigningParameterModels)
        {
            const string insertSql = @"INSERT INTO [dbo].[DocumentSigningParameter]
                                        (
                                            [DocumentSigningID],
                                            [ParameterName],
                                            [ParameterValue],
                                            [CreatedDate],
                                            [Creator],
                                            [ModifiedDate],
                                            [Modifier]
                                        )
                                        VALUES
                                        (@DocumentSigningID, @ParameterName, @ParameterValue, @CreatedDate, @Creator, @ModifiedDate, @Modifier);";
            using (var conn = new SqlConnection(TcitClientConn))
            {
                return conn.Execute(insertSql, documentSigningParameterModels);
            }
        }
    }

 

//Dapper中使用事务更新实体

public void UpdateDocumentSignatureUploadRecord(int status, Guid? errorFileContentId, Guid uploadId, List<DocumentSignatureProcessing> documentSignatureProcessings)
        {
            using (var conn = new SqlConnection(TcitClientConn))
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    const string updateSql = @"UPDATE Portal.DocumentSignatureUploadRecords
                                            SET Status =@status, ErrorFileContentID =@errorFileContentId
                                            WHERE UploadID =@uploadId; ";
                    const string updateProcessingSql = @"UPDATE Portal.DocumentSignatureProcessing
                SET Status = @Status, FailReason = @FailReason, Modifier = @Modifier, ModifiedDate = @ModifiedDate
                WHERE CompanyID = @CompanyID AND DocID = @DocID AND EmployeeID = @EmployeeID AND Status = 1 AND(BatchID IS NULL OR BatchID = ''); ";
                    try
                    {
                        conn.Execute(updateSql, new { status, errorFileContentId, uploadId }, trans);
                        conn.Execute(updateProcessingSql, documentSignatureProcessings, trans);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        NLogger.Error(ex);
                        throw;
                    }
                }
            }
        }

 将查询出的数据转为key,value形式存储：

public List<FieldValuesMappingModel> GetOptionNessInfoDic()
        {
            const string querySql = @"SELECT [CompanyID]
                                      ,[EmployeeID]
                                      ,[OptioneeID]
                                      ,[AddressLine1]
                                      ,[AddressLine2]
                                      ,[AddressLine3]
                                      ,[City]
                                      ,[State]
                                      ,[ZipCode]
                                      ,[CountryName]
                                      ,[SubsidiaryCode]
                                      ,[LocationCode]
                                      ,[TitleCode]
                                      ,[OfficerCode]
                                      ,[TaxCode]
                                      ,[BirthDate]
                                      ,[HireDate]
                                      ,[TerminationID]
                                      ,[TerminationDate]
                                      ,[UserText1]
                                  FROM [dbo].[Opt] with(nolock)
                                  where companyID=@CompanyID and EmployeeID=@EmployeeID";
            using (var conn = new SqlConnection(Conn))
            {
                using (var reader = conn.ExecuteReader(querySql, new { CompanyID = CompanyId, EmployeeID = EmployeeId }))
                {
                    var result = new List<FieldValuesMappingModel>();
                    while (reader.Read())
                    {
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var columnName = reader.GetName(i);
                            var columnValue = reader.GetValue(i);
                            result.Add(new FieldValuesMappingModel
                            {
                                KeyValue = columnValue.ToString(),
                                DisplayName = columnName,
                                KeyName = columnName
                            });
                        }
                    }
                    return result;
                }
            }
        }