考虑到我们的项目(大多数项目?)需要进行插入和更新操作,那么在使用 dapper 的同时进行插入和更新的最佳实践是什么?
最好不要采用 ADO.NET 参数构建等方法。
目前我能想到的最佳答案是使用 LinqToSQL 进行插入和更新。还有更好的答案吗?
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问题
更新
如评论中所指出,现在在Dapper.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>();
set
пјҢеҰӮ cnn.Execute("update Table SET val = @val where Id = @id", new {val, id = 1});
жҲ–иҖ…иҝҷжҳҜDapperзү№е®ҡзҡ„з”Ёжі•пјҹжҲ‘жҳҜDapperзҡ„ж–°жүӢпјҢжӯЈеңЁеҜ»жүҫдёҖдёӘжӣҙж–°зҡ„зӨәдҫӢ :) - JP HellemonsInsert
扩展返回的 long
标识符是什么吗?我的表的主键是非数字字符串(VARCHAR),实际上,在调用 Insert 后,我想知道的唯一事情是行是否成功插入或是否发生错误,我该如何知道呢? - Sasino使用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列具有相同的名称时,使用此方法。因此,作为ORM的Dapper将实体属性映射到相应的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
});
}
你可以这样做:
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);
}
using
语句,这样即使发生异常连接也会被关闭。 - Tim SchmeltersqlConnection.Execute(sqlQuery, customerEntity);
- Thomas LevesquesqlConnection.Execute(sqlQuery, customerEntity);
- Shankar@parameter
参数名与C#属性名相匹配,然后可以把任何SQL语句放到Execute中。 - Caius Jard使用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
conn.Update(yourClass)
中,如果某些属性为空,那么将更新字段为NULL?不起作用。将字段更新为NULL。不是部分更新
。 - Kiquenet您也可以使用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();
}
}
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 的主要优势,即可以灵活地编写查询语句。
现在,为整个对象编写插入或更新查询存在问题。为此,可以简单地创建如下的帮助程序:
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);
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),
}
Dapper.Contrib.Extensions
:
[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; }
}
第二步
设置你的SQL数据库/表,类似于以下方式:
第三步
现在按照以下示例构建您的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) { }
string sql = "UPDATE Customer SET City = @City WHERE CustomerId = @CustomerId";
conn.Execute(sql, customerEntity);
仓储模式
相关的简单示例: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
。
conn.Execute("INSERT Person VALUES(@Name, @Age)", person);
- Caius Jard