Performing Inserts and Updates with Dapper

Question

I am interested in using Dapper - but from what I can tell it only supports Query and Execute   I do not see that Dapper includes a way of Inserting and Updating objects   Given that our project  most projects   need to do inserts and updates  what is the best practice for doing Inserts and Updates alongside dapper   Preferably we would not have to resort to the ADO NET method of parameter building  etc   The best answer I can come up with at this point is to use LinqToSQL for inserts and updates   Is there a better answer

User · Answer

Using Dapper Contrib it is as simple as this   Insert list   public int Insert IEnumerable lt YourClass gt  yourClass        using  SqlConnection conn   new SqlConnection ConnectionString                 conn Open            return conn Insert yourClass              Insert single   public int Insert YourClass yourClass        using  SqlConnection conn   new SqlConnection ConnectionString                 conn Open            return conn Insert yourClass              Update list   public bool Update IEnumerable lt YourClass gt  yourClass        using  SqlConnection conn   new SqlConnection ConnectionString                 conn Open            return conn Update yourClass              Update single   public bool Update YourClass yourClass        using  SqlConnection conn   new SqlConnection ConnectionString                 conn Open            return conn Update yourClass              Source  https   github com StackExchange Dapper tree master Dapper Contrib

User · Answer

you can do it in such way   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

User · Answer

You can also use dapper with a stored procedure and generic way by which everything easily manageable   Define your connection   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              Create an interface to define Dapper methods those you actually need    public interface IDatabaseHub          long Execute lt TModel gt  string storedProcedureName  TModel model  string dbName                 lt summary gt              This method is used to execute the stored procedures with parameter This is the generic version of the method               lt  summary gt               lt param name  storedProcedureName  gt 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   lt  param gt               lt typeparam name  TModel  gt  lt  typeparam gt               lt param name  model  gt The model object containing all the values that passes as Stored Procedure s parameter  lt  param gt               lt returns gt Returns how many rows have been affected  lt  returns gt          Task lt long gt  ExecuteAsync lt TModel gt  string storedProcedureName  TModel model  string dbName                 lt summary gt              This method is used to execute the stored procedures with parameter  This is the generic version of the method               lt  summary gt               lt param name  storedProcedureName  gt Stored Procedure s name  Expected to be a Verbatim String  e g     Schema   Stored-Procedure-Name   lt  param gt               lt param name  parameters  gt Parameter required for executing Stored Procedure  lt  param gt                       lt returns gt Returns how many rows have been affected  lt  returns gt                   long Execute string storedProcedureName  DynamicParameters parameters  string dbName                 lt summary gt                            lt  summary gt               lt param name  storedProcedureName  gt  lt  param gt               lt param name  parameters  gt  lt  param gt               lt returns gt  lt  returns gt          Task lt long gt  ExecuteAsync string storedProcedureName  DynamicParameters parameters  string dbName       Implement the interface        public class DatabaseHub   Connection  IDatabaseHub                  lt summary gt              This function is used for validating if the Stored Procedure s name is correct               lt  summary gt               lt param name  storedProcedureName  gt Stored Procedure s name  Expected to be a Verbatim String  e g     Schema   Stored-Procedure-Name   lt  param gt               lt returns gt Returns true if name is not empty and matches naming patter  otherwise returns false  lt  returns gt           private static bool IsStoredProcedureNameCorrect string storedProcedureName                        if  string IsNullOrEmpty storedProcedureName                                 return false                             if  storedProcedureName StartsWith       amp  amp  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                            lt summary gt                  This method is used to execute the stored procedures without parameter                   lt  summary gt                   lt param name  storedProcedureName  gt Stored Procedure s name  Expected to be a Verbatim String  e g     Schema   Stored-Procedure-Name   lt  param gt                   lt param name  model  gt The model object containing all the values that passes as Stored Procedure s parameter  lt  param gt                   lt typeparam name  TModel  gt 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   lt  typeparam gt                   lt returns gt Returns how many rows have been affected  lt  returns gt               public long Execute lt TModel gt  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 lt long gt  ExecuteAsync lt TModel gt  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                                                                           lt summary gt                  This method is used to execute the stored procedures with parameter  This is the generic version of the method                   lt  summary gt                   lt param name  storedProcedureName  gt Stored Procedure s name  Expected to be a Verbatim String  e g     Schema   Stored-Procedure-Name   lt  param gt                   lt param name  parameters  gt Parameter required for executing Stored Procedure  lt  param gt                           lt returns gt Returns how many rows have been affected  lt  returns gt               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 lt long gt  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                                                                  You can now call from model as your need   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 lt long gt  DeviceDriver SaveUpdate DeviceDriverSaveUpdate obj                         return DatabaseHub ExecuteAsync                      storedProcedureName    dbo   sp SaveUpdate DeviceDriver    model  obj  dbName  AMSDB    Database name defined in Base Class                You can also passed parameters as well   public Task lt long gt  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                Now call from your controllers   var queryData   new DeviceDriverModel   DeviceInfo Save obj     Hope it s prevent your code repetition and provide security

User · Answer

Performing CRUD operations using Dapper is an easy task  I have mentioned the below examples that should help you in CRUD operations   Code for CRUD   Method  1  This method is used when you are inserting values from different entities   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             Method  2  This method is used when your entity properties have the same names as the SQL columns  So  Dapper being an ORM maps entity properties with the matching SQL columns   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       Code for 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             Code for 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             Code for 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

User · Answer

You can try this    string sql    UPDATE Customer SET City    City WHERE CustomerId    CustomerId                 conn Execute sql  customerEntity

User · Answer

Instead of using any 3rd party library for query operations  I would rather suggest writing queries on your own  Because using any other 3rd party packages would take away the main advantage of using dapper i e  flexibility to write queries  Now  there is a problem with writing Insert or Update query for the entire object  For this  one can simply create helpers like below  InsertQueryBuilder   public static string InsertQueryBuilder IEnumerable  lt  string  gt  fields        StringBuilder columns   new StringBuilder      StringBuilder values   new StringBuilder        foreach string columnName in fields       columns Append    quot  columnName    quot       values Append    quot   columnName    quot           string insertQuery      quot    columns ToString   TrimEnd             VALUES    values ToString   TrimEnd              quot      return insertQuery      Now  by simply passing the name of the columns to insert  the whole query will be created automatically  like below  List  lt  string  gt  columns   new List  lt  string  gt      quot UserName quot     quot City quot      QueryBuilder is the class having the InsertQueryBuilder   string insertQueryValues   QueryBuilderUtil InsertQueryBuilder columns    string insertQuery      quot INSERT INTO UserDetails  insertQueryValues  RETURNING UserId quot    Guid insertedId   await  connection ExecuteScalarAsync  lt  Guid  gt   insertQuery  userObj    You can also modify the function to return the entire INSERT statement by passing the TableName parameter  Make sure that the Class property names match with the field names in the database  Then only you can pass the entire obj  like userObj in our case  and values will be mapped automatically  In the same way  you can have the helper function for UPDATE query as well    public static string UpdateQueryBuilder List  lt  string  gt  fields       StringBuilder updateQueryBuilder   new StringBuilder        foreach string columnName in fields        updateQueryBuilder AppendFormat  quot  0    0    quot   columnName           return updateQueryBuilder ToString   TrimEnd                 And use it like  List  lt  string  gt  columns   new List  lt  string  gt      quot UserName quot     quot City quot      QueryBuilder is the class having the UpdateQueryBuilder   string updateQueryValues   QueryBuilderUtil UpdateQueryBuilder columns    string updateQuery      quot UPDATE UserDetails SET  updateQueryValues  WHERE UserId  UserId quot    await  connection ExecuteAsync updateQuery  userObj    Though in these helper functions also  you need to pass the name of the fields you want to insert or update but at least you have full control over the query and can also include different WHERE clauses as and when required  Through this helper functions  you will save the following lines of code  For Insert Query      quot INSERT INTO UserDetails  UserName City  VALUES   UserName  City  RETURNING UserId quot    For Update Query    quot UPDATE UserDetails SET UserName  UserName  City  City WHERE UserId  UserId quot    There seems to be a difference of few lines of code  but when it comes to performing insert or update operation with a table having more than 10 fields  one can feel the difference  You can use the nameof operator to pass the field name in the function to avoid typos Instead of  List  lt  string  gt  columns   new List  lt  string  gt      quot UserName quot     quot City quot     You can write  List  lt  string  gt  columns   new List  lt  string  gt    nameof UserEntity UserName   nameof UserEntity City

User · Answer

We are looking at building a few helpers  still deciding on APIs and if this goes in core or not  See  https   code google com archive p dapper-dot-net issues 6 for progress    In the mean time you can do the following   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      etcetera   See also my blog post  That annoying INSERT problem  Update  As pointed out in the comments  there are now several extensions available in the Dapper Contrib project in the form of these IDbConnection extension methods   T Get lt T gt  id   IEnumerable lt T gt  GetAll lt T gt     int Insert lt T gt  T obj   int Insert lt T gt  Enumerable lt T gt  list   bool Update lt T gt  T obj   bool Update lt T gt  Enumerable lt T gt  list   bool Delete lt T gt  T obj   bool Delete lt T gt  Enumerable lt T gt  list   bool DeleteAll lt T gt

[c#] Performing Inserts and Updates with Dapper

Examples related to c#

Examples related to orm

Examples related to dapper