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

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

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

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


当前回答

你可以试试这个:

 string sql = "UPDATE Customer SET City = @City WHERE CustomerId = @CustomerId";             
 conn.Execute(sql, 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
    });
}

你可以这样做:

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

}

使用短小精悍的。它是如此简单:

插入列表:

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

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

为了解决最小化代码修改的问题,您可以使用Dapper。为了支持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 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) { }

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

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