Dapper Getting Started With Inserting and Updating Data

When working with databases, you will often need to insert and update records. When it comes to writing insert or update statements in Dapper, the simple and easy way is to use the Execute method, which allows you to execute a raw SQL statement.

The Execute extension method in Dapper enables you to execute a command one or multiple times and return the number of affected rows.

This method is usually used to execute:

Insert

Inserting in Dapper a single new record is easy. All we need to do is write an INSERT statement with parameters for each column that we want to set. To insert data into the database, the CustomerController contains two Create action methods.

The first method is for the GET process which will run when we call the method.
The second method is for the POST process which will run when we submit the entry to the server.

In Controller Create action for POST, let's add the following code to insert new customer records to the Customers table.

// GET: Customer/Create
public ActionResult Create()
{
    return View();
}

// POST: Customer/Create
[HttpPost]
public ActionResult Create(Customer customer)
{
    try
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString))
        {
            string sqlQuery = "Insert Into Customers (FirstName, LastName, Email) Values(@FirstName, @LastName, @Email)";

            int rowsAffected = db.Execute(sqlQuery, customer);
        }

        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

Let's create a view by right-clicking on the Create action method in CustomerController and select the Add View. On the Add Scaffold dialog, choose MVC 5 View and click on the Add button.

On the Add View dialog, the View name will appear by default.
Select the template from the Template dropdown list for which you want to create a view, e.g. Create.
Choose your model class, e.g. Customer

Click the Add button and it will add the following code for you.

@model DapperDemoApp.Models.Customer

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Customer</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Try it online

Update

Updating in Dapper an existing entity is similar to inserting. All we need is a SQL statement containing an UPDATE statement that sets the appropriate columns. We also want to make sure we include a WHERE clause limiting the update only to the row with the specified Id.

To update data in the database, the CustomerController contains two Edit action methods.

The first method for the GET process which will run when we call the method.
The second method for the POST process will run when we submit the entry to the server.

In Controller Edit actions, let's add the following code to update the existing customer record into the Customers table.

// GET: Customer/Edit/5
public ActionResult Edit(int id)
{
    Customer customer = new Customer();
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString))
    {
        customer = db.Query<Customer>("Select * From Customers WHERE CustomerID =" + id, new { id }).SingleOrDefault();
    }
    return View(customer);
}

// POST: Customer/Edit/5
[HttpPost]
public ActionResult Edit(int id, Customer customer)
{
    try
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString))
        {
            string sqlQuery = "UPDATE Customers set FirstName='" + customer.FirstName + 
                "',LastName='" + customer.LastName + 
                "',Email='" + customer.Email + 
                "' WHERE CustomerID=" + customer.CustomerID;

            int rowsAffected = db.Execute(sqlQuery);
        }

        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

Let's create a view by right-clicking on the Edit action method in CustomerController and select Add View. On Add Scaffold dialog, choose MVC 5 View and click on the Add button.

On the Add View dialog, the View name will appear by default.
Select the template from the Template dropdown list for which you want to create a view, e.g. Edit.
Choose your model class, e.g. Customer

Click the Add button and it will add the following code for you.

@model DapperDemoApp.Models.Customer

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Customer</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.CustomerID)

        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Try it online

Delete

Deleting in Dapper an entity is the easiest of all because it only requires a unique Id to identify the entity being deleted. The SQL statement is a simple DELETE with a WHERE clause on the Id column.

To delete a record from the database, the CustomerController contains two Delete action methods.

The first method is for the GET process which will run when we call the method.
The second method is for the POST process which will run when we submit the delete request to the server.

In Controller Delete actions, let's add the following code to delete the existing customer record from the Customers table.

// GET: Customer/Delete/5
public ActionResult Delete(int id)
{
    Customer customer = new Customer();
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString))
    {
        customer = db.Query<Customer>("Select * From Customers WHERE CustomerID =" + id, new { id }).SingleOrDefault();
    }
    return View(customer);
}

// POST: Customer/Delete/5
[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
    try
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString))
        {
            string sqlQuery = "Delete From Customers WHERE CustomerID = " + id;

            int rowsAffected = db.Execute(sqlQuery);
        }

        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

Let's create a view by right-clicking on the Delete action method in CustomerController and select Add View. On Add Scaffold dialog, choose MVC 5 View and click on the Add button.

On the Add View dialog, the View name will appear by default.
Select the template from the Template dropdown list for which you want to create a view, e.g. Delete.
Choose your model class, e.g. Customer

Click the Add button and it will add the following code for you.

@model DapperDemoApp.Models.Customer

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Customer</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.FirstName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.FirstName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.LastName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.LastName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Email)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Email)
        </dd>

    </dl>

    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>