Performing Inserts and Updates with Dapper

Question

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

sqlConnection.Close();

Using Dapper.Contrib it is as simple as this:

Insert list:

public int Insert(IEnumerable<YourClass> 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<YourClass> 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