使用 Dapper 执行插入和更新

Question

我对使用 Dapper 很感兴趣 - 但据我所知它只支持查询和执行。我没有看到 Dapper 包含插入和更新对象的方法。

鉴于我们的项目（大多数项目？）需要进行插入和更新，那么与 dapper 一起进行插入和更新的最佳实践是什么？

最好我们不必求助于 ADO.NET 参数构建方法等。

此时我能想到的最佳答案是使用 LinqToSQL 进行插入和更新。有更好的答案吗？

Answer 1

我们正在考虑构建一些助手，仍在决定 API 以及是否进入核心。请参阅：https://code.google.com/archive/p /dapper-dot-net/issues/6 了解进度。

同时，您可以执行以下

val = "my value";
cnn.Execute("insert into Table(val) values (@val)", new {val});

cnn.Execute("update Table set val = @val where Id = @id", new {val, id = 1});

操作等

另请参阅我的博客文章： 那个烦人的 INSERT 问题

更新

正如评论中所指出的，现在有几个扩展可用在 <这些 IDbConnection 扩展形式的 href="https://github.com/DapperLib/Dapper.Contrib" rel="noreferrer">Dapper.Contrib 项目方法：

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();

Answer 2

使用 Dapper 执行 CRUD 操作是一项简单的任务。我提到的下面的示例应该可以帮助您进行 CRUD 操作。

CRUD 代码：

方法 #1： 当您插入来自不同实体的值时，将使用此方法。

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName,
        StateModel.State,
        CityModel.City,
        isActive,
        CreatedOn = DateTime.Now
    });
}

方法#2：当您的实体属性与 SQL 列具有相同名称时，使用此方法。因此，Dapper 作为 ORM 将实体属性与匹配的 SQL 列进行映射。

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [State], [City], [IsActive], [CreatedOn]) VALUES (@FirstName, @LastName, @State, @City, @IsActive, @CreatedOn)";

    var result = db.Execute(insertQuery, customerViewModel);
}

CRUD 代码：

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string selectQuery = @"SELECT * FROM [dbo].[Customer] WHERE FirstName = @FirstName";

    var result = db.Query(selectQuery, new
    {
        customerModel.FirstName
    });
}

CRUD 代码：

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string updateQuery = @"UPDATE [dbo].[Customer] SET IsActive = @IsActive WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(updateQuery, new
    {
        isActive,
        customerModel.FirstName,
        customerModel.LastName
    });
}

CRUD 代码：

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string deleteQuery = @"DELETE FROM [dbo].[Customer] WHERE FirstName = @FirstName AND LastName = @LastName";

    var result = db.Execute(deleteQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName
    });
}

Answer 3

你可以这样做：

sqlConnection.Open();

string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";
sqlConnection.Execute(sqlQuery,
    new
    {
        customerEntity.FirstName,
        customerEntity.LastName,
        customerEntity.Address,
        customerEntity.City
    });

Caius 添加的编辑：

请注意，没有必要以这种“在操作之前/之后立即”的方式打开/关闭连接：如果您的连接关闭，Dapper 会打开它。如果您的连接处于打开状态，Dapper 会将其保持打开状态。

如果您有许多操作要执行/您正在使用事务，请自行打开连接。如果您要做的只是打开/执行/关闭，请让 Dapper 来完成。

另外，没有必要创建匿名类型；只需使参数名称与保存数据的任何类型中的属性名称相匹配，然后传递该类型，而不是将其解压为匿名类型。

上面的代码可以这样写：

string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";

using(var sqlConnection = ...){
  sqlConnection.Execute(sqlQuery, customerEntity);

}

Answer 4

使用 Dapper.Contrib 就这么简单：

插入列表：

public int Insert(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Insert(yourClass) ;
    }
}

插入单个：

public int Insert(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Insert(yourClass) ;
    }
}

更新列表：

public bool Update(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Update(yourClass) ;
    }
}

更新单个：

public bool Update(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Update(yourClass) ;
    }
}

来源：https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib

Answer 5

您还可以将 dapper 与存储过程和通用方式一起使用，从而轻松管理所有内容。

定义您的连接：

public class Connection: IDisposable
{
    private static SqlConnectionStringBuilder ConnectionString(string dbName)
    {
        return new SqlConnectionStringBuilder
            {
                ApplicationName = "Apllication Name",
                DataSource = @"Your source",
                IntegratedSecurity = false,
                InitialCatalog = Database Name,
                Password = "Your Password",
                PersistSecurityInfo = false,
                UserID = "User Id",
                Pooling = true
            };
    }

    protected static IDbConnection LiveConnection(string dbName)
    {
        var connection = OpenConnection(ConnectionString(dbName));
        connection.Open();
        return connection;
    }

    private static IDbConnection OpenConnection(DbConnectionStringBuilder connectionString)
    {
        return new SqlConnection(connectionString.ConnectionString);
    }

    protected static bool CloseConnection(IDbConnection connection)
    {
        if (connection.State != ConnectionState.Closed)
        {
            connection.Close();
            // connection.Dispose();
        }
        return true;
    }

    private static void ClearPool()
    {
        SqlConnection.ClearAllPools();
    }

    public void Dispose()
    {
        ClearPool();
    }
}

创建一个接口来定义您实际需要的 Dapper 方法：

 public interface IDatabaseHub
    {
   long Execute<TModel>(string storedProcedureName, TModel model, string dbName);

        /// <summary>
        /// This method is used to execute the stored procedures with parameter.This is the generic version of the method.
        /// </summary>
        /// <param name="storedProcedureName">This is the type of POCO class that will be returned. For more info, refer to https://msdn.microsoft.com/en-us/library/vstudio/dd456872(v=vs.100).aspx. </param>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="model">The model object containing all the values that passes as Stored Procedure's parameter.</param>
        /// <returns>Returns how many rows have been affected.</returns>
        Task<long> ExecuteAsync<TModel>(string storedProcedureName, TModel model, string dbName);

        /// <summary>
        /// This method is used to execute the stored procedures with parameter. This is the generic version of the method.
        /// </summary>
        /// <param name="storedProcedureName">Stored Procedure's name. Expected to be a Verbatim String, e.g. @"[Schema].[Stored-Procedure-Name]"</param>
        /// <param name="parameters">Parameter required for executing Stored Procedure.</param>        
        /// <returns>Returns how many rows have been affected.</returns>         
        long Execute(string storedProcedureName, DynamicParameters parameters, string dbName);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        Task<long> ExecuteAsync(string storedProcedureName, DynamicParameters parameters, string dbName);
}

实现接口：

     public class DatabaseHub : Connection, IDatabaseHub
        {

 /// <summary>
        /// This function is used for validating if the Stored Procedure's name is correct.
        /// </summary>
        /// <param name="storedProcedureName">Stored Procedure's name. Expected to be a Verbatim String, e.g. @"[Schema].[Stored-Procedure-Name]"</param>
        /// <returns>Returns true if name is not empty and matches naming patter, otherwise returns false.</returns>

        private static bool IsStoredProcedureNameCorrect(string storedProcedureName)
        {
            if (string.IsNullOrEmpty(storedProcedureName))
            {
                return false;
            }

            if (storedProcedureName.StartsWith("[") && storedProcedureName.EndsWith("]"))
            {
                return Regex.IsMatch(storedProcedureName,
                    @"^[\[]{1}[A-Za-z0-9_]+[\]]{1}[\.]{1}[\[]{1}[A-Za-z0-9_]+[\]]{1}$");
            }
            return Regex.IsMatch(storedProcedureName, @"^[A-Za-z0-9]+[\.]{1}[A-Za-z0-9]+$");
        }

     /// <summary>
            /// This method is used to execute the stored procedures without parameter.
            /// </summary>
            /// <param name="storedProcedureName">Stored Procedure's name. Expected to be a Verbatim String, e.g. @"[Schema].[Stored-Procedure-Name]"</param>
            /// <param name="model">The model object containing all the values that passes as Stored Procedure's parameter.</param>
            /// <typeparam name="TModel">This is the type of POCO class that will be returned. For more info, refer to https://msdn.microsoft.com/en-us/library/vstudio/dd456872(v=vs.100).aspx. </typeparam>
            /// <returns>Returns how many rows have been affected.</returns>

            public long Execute<TModel>(string storedProcedureName, TModel model, string dbName)
            {
                if (!IsStoredProcedureNameCorrect(storedProcedureName))
                {
                    return 0;
                }

                using (var connection = LiveConnection(dbName))
                {
                    try
                    {
                        return connection.Execute(
                            sql: storedProcedureName,
                            param: model,
                            commandTimeout: null,
                            commandType: CommandType.StoredProcedure
                            );

                    }
                    catch (Exception exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        CloseConnection(connection);
                    }
                }
            }

            public async Task<long> ExecuteAsync<TModel>(string storedProcedureName, TModel model, string dbName)
            {
                if (!IsStoredProcedureNameCorrect(storedProcedureName))
                {
                    return 0;
                }

                using (var connection = LiveConnection(dbName))
                {
                    try
                    {
                        return await connection.ExecuteAsync(
                            sql: storedProcedureName,
                            param: model,
                            commandTimeout: null,
                            commandType: CommandType.StoredProcedure
                            );

                    }
                    catch (Exception exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        CloseConnection(connection);
                    }
                }
            }

            /// <summary>
            /// This method is used to execute the stored procedures with parameter. This is the generic version of the method.
            /// </summary>
            /// <param name="storedProcedureName">Stored Procedure's name. Expected to be a Verbatim String, e.g. @"[Schema].[Stored-Procedure-Name]"</param>
            /// <param name="parameters">Parameter required for executing Stored Procedure.</param>        
            /// <returns>Returns how many rows have been affected.</returns>

            public long Execute(string storedProcedureName, DynamicParameters parameters, string dbName)
            {
                if (!IsStoredProcedureNameCorrect(storedProcedureName))
                {
                    return 0;
                }

                using (var connection = LiveConnection(dbName))
                {
                    try
                    {
                        return connection.Execute(
                            sql: storedProcedureName,
                            param: parameters,
                            commandTimeout: null,
                            commandType: CommandType.StoredProcedure
                            );
                    }
                    catch (Exception exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        CloseConnection(connection);
                    }
                }
            }



            public async Task<long> ExecuteAsync(string storedProcedureName, DynamicParameters parameters, string dbName)
            {
                if (!IsStoredProcedureNameCorrect(storedProcedureName))
                {
                    return 0;
                }

                using (var connection = LiveConnection(dbName))
                {
                    try
                    {
                        return await connection.ExecuteAsync(
                            sql: storedProcedureName,
                            param: parameters,
                            commandTimeout: null,
                            commandType: CommandType.StoredProcedure
                            );

                    }
                    catch (Exception exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        CloseConnection(connection);
                    }
                }
            }

    }

您现在可以根据需要从模型调用：

public class DeviceDriverModel : Base
    {
 public class DeviceDriverSaveUpdate
        {
            public string DeviceVehicleId { get; set; }
            public string DeviceId { get; set; }
            public string DriverId { get; set; }
            public string PhoneNo { get; set; }
            public bool IsActive { get; set; }
            public string UserId { get; set; }
            public string HostIP { get; set; }
        }


        public Task<long> DeviceDriver_SaveUpdate(DeviceDriverSaveUpdate obj)
        {

            return DatabaseHub.ExecuteAsync(
                    storedProcedureName: "[dbo].[sp_SaveUpdate_DeviceDriver]", model: obj, dbName: AMSDB);//Database name defined in Base Class.
        }
}

您也可以传递参数：

public Task<long> DeleteFuelPriceEntryByID(string FuelPriceId, string UserId)
        {


            var parameters = new DynamicParameters();
            parameters.Add(name: "@FuelPriceId", value: FuelPriceId, dbType: DbType.Int32, direction: ParameterDirection.Input);
            parameters.Add(name: "@UserId", value: UserId, dbType: DbType.String, direction: ParameterDirection.Input);

            return DatabaseHub.ExecuteAsync(
                    storedProcedureName: @"[dbo].[sp_Delete_FuelPriceEntryByID]", parameters: parameters, dbName: AMSDB);

        }

现在从您的控制器调用：

var queryData = new DeviceDriverModel().DeviceInfo_Save(obj);

希望它可以防止您的代码重复并提供安全性；

Answer 6

我宁愿建议您自己编写查询，而不是使用任何第三方库进行查询操作。因为使用任何其他第 3 方包都会剥夺使用简洁的主要优势，即编写查询的灵活性。

现在，为整个对象编写插入或更新查询时存在问题。为此，可以简单地创建如下帮助程序：

InsertQueryBuilder：

 public static string InsertQueryBuilder(IEnumerable < string > fields) {


  StringBuilder columns = new StringBuilder();
  StringBuilder values = new StringBuilder();


  foreach(string columnName in fields) {
   columns.Append($ "{columnName}, ");
   values.Append($ "@{columnName}, ");

  }
  string insertQuery = $ "({ columns.ToString().TrimEnd(',', ' ')}) VALUES ({ values.ToString().TrimEnd(',', ' ')}) ";

  return insertQuery;
 }

现在，只需传递要插入的列的名称，就会自动创建整个查询，如下所示：

List < string > columns = new List < string > {
 "UserName",
 "City"
}
//QueryBuilder is the class having the InsertQueryBuilder()
string insertQueryValues = QueryBuilderUtil.InsertQueryBuilder(columns);

string insertQuery = $ "INSERT INTO UserDetails {insertQueryValues} RETURNING UserId";

Guid insertedId = await _connection.ExecuteScalarAsync < Guid > (insertQuery, userObj);

您还可以修改该函数以返回整个 INSERT 语句 ：传递表名参数。

确保类属性名称与数据库中的字段名称匹配。然后，只有您可以传递整个 obj（如我们示例中的 userObj），并且值将自动映射。

以同样的方式，您也可以拥有 UPDATE 查询的辅助函数：

  public static string UpdateQueryBuilder(List < string > fields) {
   StringBuilder updateQueryBuilder = new StringBuilder();

   foreach(string columnName in fields) {
    updateQueryBuilder.AppendFormat("{0}=@{0}, ", columnName);
   }
   return updateQueryBuilder.ToString().TrimEnd(',', ' ');
  }

并像这样使用它：

List < string > columns = new List < string > {
 "UserName",
 "City"
}
//QueryBuilder is the class having the UpdateQueryBuilder()
string updateQueryValues = QueryBuilderUtil.UpdateQueryBuilder(columns);

string updateQuery =  $"UPDATE UserDetails SET {updateQueryValues} WHERE UserId=@UserId";

await _connection.ExecuteAsync(updateQuery, userObj);

虽然在这些辅助函数中，您也需要传递要插入或更新的字段的名称，但至少您有完整的控制查询，还可以根据需要包含不同的 WHERE 子句。

通过这个辅助函数，您将节省以下几行代码：

对于插入查询：

 $ "INSERT INTO UserDetails (UserName,City) VALUES (@UserName,@City) RETURNING UserId";

对于更新查询：

$"UPDATE UserDetails SET UserName=@UserName, City=@City WHERE UserId=@UserId";

似乎有几行代码的区别，但是当涉及到对具有多于一行的表执行插入或更新操作时10个领域，一目了然。

您可以使用 nameof 运算符在函数中传递字段名称以避免拼写错误

而不是：

List < string > columns = new List < string > {
 "UserName",
 "City"
}

您可以编写：

List < string > columns = new List < string > {
nameof(UserEntity.UserName),
nameof(UserEntity.City),
}

Answer 7

存储过程 + Dapper 方法或 SQL insert 语句 + Dapper 可以完成这项工作，但它并不能完美实现 ORM 的概念，即动态映射数据模型与 SQL 表列，因为如果使用上述两种方法之一，您仍然需要硬编码存储过程参数或 SQL 插入语句中的某些列名称值。

为了解决尽量减少代码修改的问题，可以使用 Dapper.Contrib 来支持SQL 插入，这里是官方指南，下面是示例设置和代码

第 1 步

使用 Dapper.Contrib.Extensions 在 C# 中设置类模型：
[Table] 属性将指向 SQL 框中所需的表名称，[ExplicitKey] 属性将告诉 Dapper 该模型属性是 SQL 表中的主键。

[Table("MySQLTableName")]
public class UserModel
{
   [ExplicitKey]
   public string UserId { get; set; }
   public string Name { get; set; }
   public string Sex { get; set; }
}

第 2 步

设置 SQL 数据库/表，如下所示：

第 3 步

现在构建您的 C# 代码，如下所示，您需要使用这些命名空间：

using Dapper.Contrib.Extensions;
using System.Data;

代码：

string connectionString =
    "Server=localhost;Database=SampleSQL_DB;Integrated Security=True";

UserModel user1 = new UserModel { UserId = "user0000001", 
                                     Name = "Jack", Sex = "Male" };
UserModel user2 = new UserModel { UserId = "user0000002", 
                                     Name = "Marry", Sex = "female" };
UserModel user3 = new UserModel { UserId = "user0000003", 
                                     Name = "Joe", Sex = "male" };

List<UserModel> LstUsers = new List<UserModel>();
LstUsers.Add(user2); LstUsers.Add(user3);

try
{
   using (IDbConnection connection = 
          new System.Data.SqlClient.SqlConnection(connectionString))
   {
      connection.Open();

      using (var trans = connection.BeginTransaction())
      {
        try
        {
           //  insert single record with custom data model
           connection.Insert(user1, transaction: trans);

           // insert multiple records with List<Type>
           connection.Insert(LstUsers, transaction: trans);

           // Only save to SQL database if all required SQL 
           // operations completed successfully 
           trans.Commit();
        }
        catch (Exception e)
        {
           // If one of the SQL operation fail , 
           // roll back the whole transaction
           trans.Rollback();
        }
     }
  }
}
catch (Exception e) { }

Answer 8

你可以试试这个：

 string sql = "UPDATE Customer SET City = @City WHERE CustomerId = @CustomerId";             
 conn.Execute(sql, customerEntity);

Answer 9

这是一个使用Repository Pattern的简单示例：

public interface IUserRepository
{
    Task<bool> CreateUser(User user);
    Task<bool> UpdateUser(User user);
}

在UserRepository中：

public class UserRepository: IUserRepository
    {
        private readonly IConfiguration _configuration;

        public UserRepository(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        public async Task<bool> CreateUser(User user)
        {
            using var connection = new NpgsqlConnection(_configuration.GetValue<string>("DatabaseSettings:ConnectionString"));

            var affected =
                await connection.ExecuteAsync
                    ("INSERT INTO User (Name, Email, Mobile) VALUES (@Name, @Email, @Mobile)",
                            new { Name= user.Name, Email= user.Email, Mobile = user.Mobile});

            if (affected == 0)
                return false;

            return true;
        }

        public async Task<bool> UpdateUser(User user)
        {
            using var connection = new NpgsqlConnection(_configuration.GetValue<string>("DatabaseSettings:ConnectionString"));

            var affected = await connection.ExecuteAsync
                    ("UPDATE User SET Name=@Name, Email= @Email, Mobile = @Mobile WHERE Id = @Id",
                            new { Name= user.Name, Email= user.Email, Mobile  = user.Mobile , Id = user.Id });

            if (affected == 0)
                return false;

            return true;
        }
    }

注意：NpgsqlConnection用于获取ConnectionString PostgreSQL数据库