使用Dapper执行插入和更新

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

假设我们的项目(大多数项目?)需要进行插入和更新，那么与dapper一起进行插入和更新的最佳实践是什么?

我们最好不要诉诸于ADO。NET参数构建方法等。

在这一点上，我能想到的最佳答案是使用LinqToSQL进行插入和更新。还有更好的答案吗?

321503 次浏览
小开
最佳答案

我们正在考虑构建一些助手，仍然决定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});

等等

另见我的博客文章:烦人的插入问题

更新

正如注释中所指出的，现在在衣冠楚楚。Contrib < / >项目中有几个扩展可用，形式是这些IDbConnection扩展方法:

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>();
小开

你可以这样做:

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);


}
小开

使用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
});
}
小开

使用衣冠楚楚的。普通发布版就像这样简单:

插入列表:

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

小开

你可以试试这个:

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

您还可以使用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);

希望它能防止代码重复，并提供安全性;

小开

与其使用任何第三方库进行查询操作，我宁愿建议您自己编写查询。因为使用任何其他第三方包会带走使用dapper的主要优势，即编写查询的灵活性。

现在，为整个对象编写Insert或Update查询有一个问题。为此，你可以简单地创建如下所示的helper:

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);

您还可以通过传递TableName参数来修改该函数以返回整个INSERT语句。

确保Class属性名称与数据库中的字段名称匹配。然后只有你可以传递整个obj(如userObj在我们的情况下)和值将自动映射。

以同样的方式，你也可以有UPDATE查询的helper函数:

  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子句。

通过这些helper函数，你将保存以下代码行:

插入查询:

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

更新查询:

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

这似乎是几行代码的区别，但是当涉及到对具有超过10个字段的表执行插入或更新操作时，就可以感受到区别。

您可以使用操作符的名称来传递函数中的字段名称，以避免键入错误

而不是:

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

你可以这样写:

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

下面是一个关于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用于获取PostgreSQL数据库的ConnectionString

小开

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

为了解决最小化代码修改的问题，你可以使用衣冠楚楚的。普通发布版来支持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数据库/表:

enter image description here

步骤3

现在像下面这样构建你的c#代码，你需要使用这些命名空间:

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

代码:

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


UserModel objUser1 = new UserModel { UserId = "user0000001" , Name = "Jack", Sex = "Male" };
UserModel objUser2 = new UserModel { UserId = "user0000002", Name = "Marry", Sex = "female" };
UserModel objUser3 = new UserModel { UserId = "user0000003", Name = "Joe", Sex = "male" };


List<UserModel> LstUsers = new List<UserModel>();
LstUsers.Add(objUser2); LstUsers.Add(objUser3);


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(objUser1, transaction: trans);


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


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

提交答案