Performing Inserts and Updates with Dapper

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

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

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

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

}