---

Search posts under ASP.NET MVC:

---

Insert record using transactions in ASP.NET MVC

How to work with Transactions in ASP.NET MVC?

To insert data into more than one database tables, we can take help of ViewModel and transactions. In this example, we are going to insert data into PersonalDetials and Files tables that we are keep using throughout these How to tutorials for our database operations.

VIEW MODEL CODE

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace MVCTraining5.Models
{
    public class PersonaldetailsFilesViewModel
    {
        // Columns from PersonalDetails table
        [Key]

[DatabaseGenerated(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOpt
ion.Identity)]
        public int AutoId { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public int Age { get; set; }

        public bool Active { get; set; }

        // Columns from Files table
        public string FileName { get; set; }
    }
}

Above View mode code is pretty simple, it has one primary key that is also marked as Database generated (auto increment). First 5 properties are form PersonalDetail entity (PersonalDetails database table) and last one is from Files entity (Files database tables).

CONTROLLER ACTION METHODS

public ActionResult InsertWithViewModel()
        {
            PersonaldetailsFilesViewModel model = new
PersonaldetailsFilesViewModel();
            return View(model);
        }

The first InsertWithViewModel() method simply returns the PersonaldetailsFilesViewModel view model instance to the view.

Now, right click this method and create a View corresponding to this action method.

In the above Add View dialog box, we have selected Template as Create, Model class to our ViewModel class (PersonaldetailsFilesViewModel). Clicking Add button creates a View.

VIEW CODE

@model MVCTraining5.Models.PersonaldetailsFilesViewModel

@{
    ViewBag.Title = "Insert With View Model";
}

    <h2>Insert With View Model</h2>
    <p class="label-success">@ViewBag.ResultMessage</p>

    @using (Html.BeginForm("InsertWithViewModel", "PersonalDetail", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
@Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>PersonaldetailsFilesViewModel</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.Age, htmlAttributes: new { @class =
"control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Age, new { htmlAttributes = new {
@class = "form-control" } })
@Html.ValidationMessageFor(model => model.Age, "", new { @class =
"text-danger" })
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.Active, htmlAttributes: new { @class =
"control-label col-md-2" })
            <div class="col-md-10">
                <div class="checkbox">
                    @Html.EditorFor(model => model.Active)
@Html.ValidationMessageFor(model => model.Active, "", new {
    @class = "text-danger" })
                </div>
            </div>
        </div>
        <div class="form-group">
            @Html.LabelFor(model => model.FileName, htmlAttributes: new { @class =
"control-label col-md-2" })
            <div class="col-md-10">
                <input type="file" name="FileName" class="form-control" />
                @Html.ValidationMessageFor(model => model.FileName, "", 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>
    }

Notice the Html.BeginForm helper method where we are passing our action method (InsertWithViewModel), controller name, form action method and the enctype to “multipart/form-data” so that we will be able to upload an image for the FileName field.

We have also replaced the typical @Html.EditFor of FileName property to the input type=”file”, so that for FileName field, file upload element appears instead of a normal TextBox in the browser.

Now, lets see the method that gets executed when Submit button is clicked on the above view.

CONTROLLER ACTION METHOD – POST

[HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult InsertWithViewModel(PersonaldetailsFilesViewModel model,
HttpPostedFileBase FileName)
        {
            if (!ModelState.IsValid)
            {
                return View(model);
            }

            using (var transaction = db.Database.BeginTransaction())
            {
                try
                {
                    // try to enter personal details first
                    PersonalDetail pd = new PersonalDetail()
                    {
                        Active = model.Active,
                        Age = model.Age, 
                        FirstName = model.FirstName,
                        LastName = model.LastName
                    };
                    db.PersonalDetails.Add(pd);
                    db.SaveChanges();

                    // try to enter Files detials now
                    // upload the file first
                    string path = Server.MapPath("~/UserData/");
                    //string selectedFileName =
System.IO.Path.GetFileName(FileName.FileName);

                    // get extension name of the selcted file
                    string extensionName =
System.IO.Path.GetExtension(FileName.FileName);
                   // generate a random file name and appende extension name
                    string finalFileName = DateTime.Now.Ticks.ToString() +
extensionName;
                   // now save the selected file with new name
                   FileName.SaveAs(path + finalFileName);

                   Files file = new Files()
                   {
                       Active = true,
                       FileName = finalFileName,
                       PersonalDetailsId = pd.AutoId
                   };
                   db.Files.Add(file);
                   db.SaveChanges();

                   // Oh we are here, looks like everything is fine - save all the
data permanently
                   transaction.Commit();

                   ViewBag.ResultMessage = "Record inserted into both tables
successfully !";
                }
                catch (Exception)
                {
                    // roll back all database operations, if any thing goes wrong
                    transaction.Rollback();
                    ViewBag.ResultMessage = "Error occured, records rolledback.";
                }
           }
           return View();
      }

Notice the above InsertWithViewModel controller method that accepts 1^st parameter as the PersonaldetailsFilesViewModel andn 2^nd parameter as HttpPostedFileBase (the parameter name for this must match with the name of the html file upload element in the view).

First, we are checking for the ModelState. If not valid we are returning to the View again with the model (In this case, we do not have validation in the View model attributes so no validation happens and this code doesn’t make much sense).

In this case, we have to insert one record each into PersonalDetials and Files database table and if any error occurs while inserting record into anyone of these tables, all changes made if any must be rolled back.

Let’s take an example – if there is no error while inserting record into PersonalDetail table and an error occurs while inserting record into Files table, even the PersonalDetail table record that was inserted should be rolled back. To achieve this functionality, we will use transaction.

We are beginning the transaction by calling db.Database.BeginTransaction() method and under this scope, we are using Try block and trying to insert a record into PersonalDetail and then uploading the user selected file on the server and inserting a record into Files table. If all three steps goes well, we are calling transaction.Commit() method and writing success message into ViewBag. If any error occurs, the execution process moves into catch block in which we are calling transaction.Rollback() method that undo any activity done on the database and then writing the error message in the ViewBag.