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

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

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

Edit added by Caius:

Note that it s not necessary to open/close the connection in this "immediately before/after the operation" way: if your connection is closed, Dapper opens it. If your connection is open, Dapper leaves it open.

Open the connection yourself if you e.g. have many operations to perform/you re using a transaction. Leave Dapper to do it if all you ll do is open/execute/close.

Also, it s unnecessary to make an anonymous type; just make your parameters names match your property names in whatever type holds your data, and pass that type rather than unpacking it to an anonymous type.

The code above can be written thus:

string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";

using(var sqlConnection = ...){
  sqlConnection.Execute(sqlQuery, customerEntity);

}

Using Dapper.Contrib it is as simple as this:

Insert list:

public int Insert(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Insert(yourClass) ;
    }
}

Insert single:

public int Insert(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Insert(yourClass) ;
    }
}

Update list:

public bool Update(IEnumerable<YourClass> yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Update(yourClass) ;
    }
}

Update single:

public bool Update(YourClass yourClass)
{
    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        return conn.Update(yourClass) ;
    }
}

Source: https://github.com/StackExchange/Dapper/tree/master/Dapper.Contrib

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

Implement the interface:

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

    }

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

        }

Now call from your controllers:

var queryData = new DeviceDriverModel().DeviceInfo_Save(obj);

Hope it s prevent your code repetition and provide security;

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

Now, by simply passing the name of the columns to insert, the whole query will be created automatically, like below:

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

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 < string > fields) {
   StringBuilder updateQueryBuilder = new StringBuilder();

   foreach(string columnName in fields) {
    updateQueryBuilder.AppendFormat("{0}=@{0}, ", columnName);
   }
   return updateQueryBuilder.ToString().TrimEnd( , ,    );
  }

And use it like:

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

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:

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

For Update Query:

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

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 < string > columns = new List < string > {
 "UserName",
 "City"
}

You can write:

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

Stored procedure + Dapper method or SQL insert statement + Dapper do the work, but it do not perfectly fulfill the concept of ORM which dynamic mapping data model with SQL table column, because if using one of the above 2 approaches, you still need hard code some column name value in your stored procedure parameter or SQL insert statement.

To solve the concern of minimize code modification, you can use Dapper.Contrib to support SQL insert, here is the official guide and below was the sample setup and code

Step 1

Set up your class model in C#, by using Dapper.Contrib.Extensions : [Table] attribute will point to the desired table name in your SQL box, [ExplicitKey] attribute will tell Dapper this model properties is a primary key in your SQL table.

[Table("MySQLTableName")]
public class UserModel
{
    [ExplicitKey]
    public string UserId { get; set; }
    public string Name { get; set; }
    public string Sex { get; set; }
}

Step 2

Setup you SQL database/table something like this:

Step 3

Now build your C# code as something like below, you need to use these namespaces:

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

Code:

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) { }

You can try this:

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

Here is a simple example with Repository Pattern :

public interface IUserRepository
{
    Task<bool> CreateUser(User user);
    Task<bool> UpdateUser(User user);
}

And in 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;
        }
    }

Note : NpgsqlConnection used for getting the ConnectionString of PostgreSQL database