Выполнение вставок и обновлений с помощью Dapper
Я заинтересован в использовании Dapper - но из того, что я могу сказать, он поддерживает только Query и Execute. Я не вижу, что Dapper включает в себя способ вставки и обновления объектов.
Учитывая, что нашему проекту (большинству проектов?) Нужно делать вставки и обновления, какова лучшая практика для вставок и обновлений наряду с dapper?
Желательно, чтобы нам не пришлось прибегать к методу построения параметров ADO.NET и т. Д.
Лучший ответ, который я могу придумать на данный момент, это использовать LinqToSQL для вставок и обновлений. Есть ли лучший ответ?
9 ответов
Мы смотрим на создание нескольких помощников, все еще выбирая 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
Обновить
Как указано в комментариях, теперь в проекте 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>();
Выполнение CRUD-операций с использованием Dapper - простая задача. Я упомянул ниже примеры, которые должны помочь вам в операциях CRUD.
Код для C RUD:
Метод № 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);
}
Код для CR UD:
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
});
}
Код для CRU D:
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
});
sqlConnection.Close();
Используя Dapper.Contrib это так просто:
Вставить список:
public int Insert(IEnumerable<YourClass> yourClass)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Insert(yourClass) ;
}
}
Вставить один:
public int Insert(YourClass yourClass)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Insert(yourClass) ;
}
}
Обновление списка:
public bool Update(IEnumerable<YourClass> yourClass)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Update(yourClass) ;
}
}
Обновление сингла:
public bool Update(YourClass yourClass)
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
return conn.Update(yourClass) ;
}
}
Источник: https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib
Вы также можете использовать 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 для всего объекта. Для этого можно просто создать помощников, как показано ниже:
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, передав параметр TableName.
Убедитесь, что имена свойств класса совпадают с именами полей в базе данных. Только тогда вы можете передать весь объект 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),
}
Хранимая процедура + метод Dapper или оператор вставки SQL + Dapper выполняют свою работу, но они не полностью соответствуют концепции ORM, которая динамически отображает модель данных со столбцом таблицы SQL, потому что при использовании одного из двух вышеприведенных подходов вам все еще нужен жесткий код какое-то значение имени столбца в параметре хранимой процедуры или операторе вставки SQL.
Чтобы решить проблему минимизации модификации кода, вы можете использовать Dapper.Contrib для поддержки вставки SQL, вот официальное руководство , а ниже приведен пример настройки и кода.
Шаг 1
Настройте свою модель класса на С#, используя
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; }
}
Шаг 2
Настройте базу данных/таблицу SQL примерно так:
Шаг 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) { }
Вы можете попробовать это:
string sql = "UPDATE Customer SET City = @City WHERE CustomerId = @CustomerId";
conn.Execute(sql, customerEntity);
Вот простой пример с
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