40_CategoriesBeforeDeleting

CRUD Operations In Asp.Net MVC 5 Using Entity Framework Database First Approach

Spread the love

Hello, today I will be discussing on how to perform CRUD (Create, Read, Update, Delete) operations on an SQL Server database table in Asp.Net MVC 5 using Entity Framework Database First Approach. When you are working with any database (like SQL Server) in asp.net, CRUD operations are the very first thing you need to know how to perform. In Asp.Net mvc, it can be done using various techniques, one of which is by using Entity Framework. And in Entity Framework, following are the approaches that we can use to interact with the database:

1. Code First: It’s called “Code First” because in this approach, we don’t create the database for the application first. Rather we do the coding first, and the code then creates the database. This approach can be used when we don’t have a database already or when we have an empty database, as the Code First approach is capable enough to create the database and its tables. So this approach can be used when the project is being created from scratch.

2. Database First: It’s called “Database First” because in this approach, we create the database for the application first and then the code interacts with this database to perform various operations on its data. So this approach can be used when we need to create a website over an existing database.

3. Model First: It’s called “Model First” because in this approach, we create the Model for the application first. So again this approach can be used when we are building a project from scratch and the database does not exist yet.

Assuming we have an existing database to use, here I will be showing you how to create an asp.net mvc 5 application to perform CRUD operations using Entity Framework database first approach. To do this, follow the following steps:

1. Create a new project by selecting New -> Project under File menu.

1_CreateNewMVCProject_CRUD_MVC_EF

2. Select Asp.Net Web Application and provide the name for the project (in our case, CRUD_MVC_EF). Also note that the default .net framework 4.5.2 is selected on top and I am using C# as the language for the project. If you want, you can use VB or any other language available that you can see in the left panel. If you want to use this code, keep it C#, since I am developing it in C# here.

2_SetProjectName_CRUD_MVC_EF

3. Select MVC template and as you can see, the MVC will be already checked and disabled in Add folders and core references section. Keep the selections like this and click OK.

3_SelectMVCTemplate_CRUD_MVC_EF

4. Then you will be prompted with a screen to configure Microsoft Azure Web App. Just click Cancel on this screen, as we don’t want to use Microsoft Azure Web App here.

4_Configure_MicrosoftAzureWebApp_CRUD_MVC_EF

5. Once you do that, you will see the following screen which says the the project creation is in progress:

5_CreatingProjectProgress_CRUD_MVC_EF

6. Once the project is created, you will see the following screen, which provides links to informative articles about various things that you can do in your Asp.Net MVC project. Do feel free to check these links.

6_Congratulations_CRUD_MVC_EF

7. Check the References folder, if Entity Framework is not installed in your project, install it from the NuGet Package Manager by going to Tools -> NuGet Package Manager -> Manage NuGet Packages For Solution. You will see the following screen:

7_NugetPackageManager

If Entity Framework is already installed in your project, you can see it in the Installed tab, else you can find it in the Browse tab. Considering you don’t have Entity Framework installed, just search for Entity framework under Browse tab, select Entity Framework (Version 6.2.0) from left, select your project on the right and click the install button. Then you can see the installation progress and soon it will be installed. Since Entity Framework is already installed in my project, you can see the Uninstall on the right side in the screenshot when I select Entity Framework.

8. Now the project is all set up and we can start coding to make it work according to our requirements. And what are our requirements? To keep things simple, I will just use a single category table that I already created in my last to last article with title – “How to display data and pictures in nested grids (datalist inside gridview) in asp.net” and I will demonstrate how we can perform CRUD operations on this Category table using asp.net MVC 5 and entity framework 6, i.e how we can add/edit/delete/display categories from this table. But before that, let’s run the project and see what we get by default in asp.net mvc 5 project. When you run the project, following is what you will see:

8_DefaultHomeScreen

And you can see the presentation logic code for this page on Index.cshtml. In case you are building the project in vb language, the file’s name will be Index.vbhtml.

Following is the default code that you will see on Index.cshtml page:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@{
    ViewBag.Title = "Home Page";
}
 
<div class="jumbotron">
    <h1>ASP.NET</h1>
    <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
    <p><a href="http://asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a></p>
</div>
 
<div class="row">
    <div class="col-md-4">
        <h2>Getting started</h2>
        <p>
            ASP.NET MVC gives you a powerful, patterns-based way to build dynamic websites that
            enables a clean separation of concerns and gives you full control over markup
            for enjoyable, agile development.
        </p>
        <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301865">Learn more &raquo;</a></p>
    </div>
    <div class="col-md-4">
        <h2>Get more libraries</h2>
        <p>NuGet is a free Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects.</p>
        <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301866">Learn more &raquo;</a></p>
    </div>
    <div class="col-md-4">
        <h2>Web Hosting</h2>
        <p>You can easily find a web hosting company that offers the right mix of features and price for your applications.</p>
        <p><a class="btn btn-default" href="http://go.microsoft.com/fwlink/?LinkId=301867">Learn more &raquo;</a></p>
    </div>
</div>

Now we don’t need to display all this on our page, so we delete everything on this page, except the first 3 lines, which sets the page title. So we are left with the following code on Index.cshtml:

1
2
3
@{
    ViewBag.Title = "Home Page";
}

And following is how the blank page looks like now:

9_BlankHomeScreen

9. Now since we need to display categories on this page, let’s first see the Category table from which we will be displaying the data on the web page. Following is the Category table structure:

9_CategoryTableStructure_CRUD_MVC_EF

10. Let’s keep the table blank this time, i.e delete any data in it, since we will be inserting data in it through our application.

11. Now open solution explorer and right click on the Models folder and add new ADO.Net entity model (which we will be using to interact with our database table), as shown below:

9_AddADONetEntityDataModel

12. Next, you will be prompted with a screen to provide the name for this model. Since we want to interact with the Categories table, let’s name this ADO.Net Entity Data Model as CategoryModel and click OK, as shown below:

10_ProvideNameADONetEntityDataModel

13. Next, we need to define what this model should contain. Since we are using the Database first approach, select “EF Designer from database” option in this screen and click next, as shown below:

11_EFDesignerFromDatabaseModel

14. Now we need to define the database connection in the next screen as shown below:

12_DefineDBConnection

15. Now click on the “New Connection” button that you can see in the last screen and you will be prompted to a new screen where you need to define the connection properties like the database server name, database name and the authentication mode, as shown below:

13_DefineConnectionProperties

In my case, I have provided the database server name as “SUNNY-PC\SQLEXPRESS“ and authentication mode as “Windows Authentication”. After this I have selected the option “Select or enter a database name” under “Connect to a database” section and I have selected the database “AspSkills” from the dropdownlist.

16. Now click on the “Test Connection” button on the bottom left and if you have provided the correct details, the database connection test should succeed and you should see “Test connection succeeded” message as shown below:

14_TestConnectionSucceeded

17. Now click OK on this “Test connection succeeded” screen and then click OK on the “Connection Properties” screen, and you will be returned back to the Entity Data Model wizard and this time, you can see the various fields (“data connection”, “Connection string” and “Save connection settings in Web.Config as”) filled based on the settings you made recently, as shown below:

15_DBConnectionFilled

Pay attention to the value entered for “Save connection settings in Web.Config as” which in our case is “AspSkillsEntities”. Let’s rename is to “db” as shown below:

16_DBConnectionRenamed

This is the name (db) that we will be using in our code to connect to the database and perform various operations on the database tables.

18. Clicking on Next will take you to the screen to select the database objects that you want to use and provide name for the Model’s namespace. Note that the Model namespace has value “AspSkillsModel”. Now just open the Tables node, then open dbo node and select the CATEGORY_TBL. Keep the remaining settings as it is as shown below:

17_DBConnectionTablesModel

Now click on the Finish button.

19. Once that is done, the edmx file for Category (named CategoryModel.edmx) will be created and opened for you. You can also find this edmx file in the Models folder. The file will contain the CATEGORY_TBL entity that we added in the last step and following is how the CategoryModel.edmx file and the solution explorer will look like now:

18_CategoryEDMX

20. Now let’s display the categories from this table on our home page. To do so, open the Index.cshtml file under the Views folder, which contains the presentation logic of our Home page, which is the following code:

1
2
3
@{
    ViewBag.Title = "Home Page";
}

21. Now our next step is to get the data from the database table (i.e CATEGORY_TBL), that we need to display on Home page and return it to the Index view. To do this, you need to create an object of the dbcontext and return the list of categories to the Index view using this dbcontext object. We can create the dbcontext object on top of the HomeController outside of any action method, so that we can use this object in any of the HomeController’s action methods. And now we simply need to use this dbcontext class in the Index action method to return the list of categories to the view. The following code does this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                return View(objdb.CATEGORY_TBL.ToList());
            }
            return View();
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

22. Now the next step is to delete the Index.cshtml file, since we will be auto generating the Index.cshtml file using the technique which is known as scaffolding. After this, go to the HomeController file and right click on the Index action method and select “Add View”, as shown below:

19_AddView

23. Once you do this, you will be shown the “Add View” screen where you can provide the desired settings for the View that you want to generate. Let’s keep the view’s name as Index, select “List” from the template dropdown (which means that we need to display the list of the data from the model), then select CATEGORY_TBL (CRUD_MVC_EF.Models) from the Model class dropdown (which tells EF that we want to use CATEGORY_TBL model to generate our view), then select db (CRUD_MVC_EF.Models) from the Data context class dropdown, which tells EF that we want db as our data context class for this view. Leave the rest of the options as it is (i.e “Create as partial view” option unchecked and “Reference script libraries” and “Use a layout page” options checked) and leave the textbox for Layout page as blank.

Following is the “Add View” screen:
20_AddViewSettings

Now simply click on the Add button and it will automatically generate the view and open it for you which will be Index.cshtml file displaying the list of categories. You can find this file in the Home folder under Views folder. And following is how the auto generated code will look like on Index.cshtml file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@model IEnumerable<CRUD_MVC_EF.Models.CATEGORY_TBL>
 
@{
    ViewBag.Title = "Index";
}
 
<h2>Index</h2>
 
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.NAME)
        </th>
        <th></th>
    </tr>
 
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.NAME)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.ID }) |
            @Html.ActionLink("Details", "Details", new { id=item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.ID })
        </td>
    </tr>
}
 
</table>

Before I explain the code, simply run the website and you can see the list of categories (Cars and Mobiles that we have in our CATEGORY_TBL) displayed in a grid format, as shown below:

21_CategoriesListing

Coming to the explanation of the Index.cshtml code, which is pretty straight forward and explained below:

@model IEnumerable tells this view that it will be displaying list of data from the CATEGORY_TBL model.

1
<h2>Index</h2>

sets the title of the page as Index.

@Html.ActionLink(“Create New”, “Create”) is the link to Create view for creating new category.

@Html.ActionLink(“Create New”, “Create”) creates a link to the Create view,

@Html.DisplayNameFor(model => model.NAME) displays the title “Name” on the page,

@foreach (var item in Model) { } loops through the data in the model and whatever code is inside this for loop displays for n number of times, where n is the number of categories in the CATEGORY_TBL that we are getting using objdb.CATEGORY_TBL.ToList() written in the Index action method of Home controller.

@Html.DisplayFor(modelItem => item.NAME) which is inside the for loop, displays the actual data i.e. Name field data from the CATEGORY_TBL.

@Html.ActionLink(“Edit”, “Edit”, new { id=item.ID }) displays the edit link to edit the category.
@Html.ActionLink(“Details”, “Details”, new { id=item.ID }) displays the details link to view the details of any particular category.

@Html.ActionLink(“Delete”, “Delete”, new { id=item.ID }) displays the delete link to delete any particular category.

24. Now when you click on any of the blue links on this page, like “Create New”, “Edit”, “Details” or “Delete”, you will be shown a 404 resource not found screen, as shown below when I clicked “Create New” link:

22_404ResourceNotFound

This is because we have only generated the Index view, which displays the list of categories. But when you click on Create link, it goes to Create view under Home controller, which we have not yet generated and hence it’s not found. Similarly Edit, Details and Delete views are not found. So now our next step is to generate these views. And doing that is pretty simple and similar to how we created our Index view. To create the “Create” view, simply right click on the Home controller and select Add -> View as shown below:

23_AddCreateView

You will be shown the same screen, which we saw while generating View for listing categories. But this time we are generating the Create view, so set the View’s name as Create, select Create template from the template dropdown, select CATEGORY_TBL (CRUD_MVC_EF.Models) from the Models dropdown and select db (CRUD_MVC_EF.Models) from the Data context class dropdown, as shown below:

24_CreateViewProperties

Click on the Add button to generate this Create view, which will automatically create a new file in the HomeController named “Create.cshtml” and following is the code of this autogenerated file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
@model CRUD_MVC_EF.Models.CATEGORY_TBL
 
 
@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
 
    <div class="form-horizontal">
         @*<h4>CATEGORY_TBL</h4> Notice that I commented the default title and provided “Add Category” as the page title*@
<h4>Add Category</h4>
<hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.NAME, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.NAME, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.NAME, "", 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")
}

The file simply creates an html layout for the Create Category page, where @Html.LabelFor is to display a label for Name, @Html.EditorFor is to display a textbox for Name, @Html.ValidationMessageFor is for validation for Name, then there’s a submit button with its text set to “Create” and then there’s “Back to List” link (@Html.ActionLink) which redirects back to the Categories listing page, which is the Index view (that is set in the action name property of this ActionLink).

25. Now we will simply call this Create view in the Home controller within a new action method named “Create”, and you can see the complete updated code of the HomeController below (Notice the new Action method named “Create”):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                return View(objdb.CATEGORY_TBL.ToList()); 
//Get the list of categories and return it to the view for displaying
            }
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

Now when you click on the Create link on the home page, you will be redirected to the create page (i.e Create action under Home controller), as shown below:

25_AddCategoryPage

26. Now when you enter a category name in the textbox and click on the Create button, obviously the category is not added in the database, because we haven’t written the code to insert categories into the database yet, and that’s what we will be doing now.

But before we write the code to insert categories into database, we need to know where to write this code. All the action methods in the HomeController (including the Create action method) are HTTPGet action methods, which means they are invoked with an HTTPGet request i.e. when the url to that action method in that particular controller is accessed, i.e. it’s more like what happens in page_load event in asp.net. But now we need to invoke a create action method when the create button is clicked and that’s done by creating another action method with same name (“Create”) and setting it as an HTTPPost method by writing the HttpPost attribute on top of this action method. And since we can’t have two functions with same name and parameters, and also since we need to pass the data while adding a category, we need to pass an object of FormCollection as parameter to this HttpPost Create method, whose purpose is to get the data from the posted form. And then we can use all these posted values from the FormCollection object and insert them into database using LINQ (Lambda expression). And the last thing is to redirect to the Categories listing page after the Category is added by calling the Index action method of the HomeController. Following is the complete code of HomeController after writing the Create action method with HttpPost attribute.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                return View(objdb.CATEGORY_TBL.ToList()); 
                //Get the list of categories and return it to the view for displaying
            }
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Create(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Create object (objcategory) of CATEGORY_TBL class
            objcategory.NAME = fc["name"]; //Assign the name of category that we entered in texbox to the Name property of objcategory
            objdb.CATEGORY_TBL.Add(objcategory); //Add this category in the CATEGORY_TBL
            objdb.SaveChanges(); //Save the changes made to the data in the CATEGORY_TBL
            return RedirectToAction("Index","Home");// Redirects to Index action method of HomeController
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

Now when you click on add button after entering the name of the new category, the category will be added into the database and you will be redirected back to the Home page (Index action method of the Home Controller), where you can see the newly added category. For example when I enter a new category, let’s say “Bikes” and click the Create button as shown below:

26_CreateCategoryBikes

The Bikes category will be added into the database in the CATEGORY_TBL and I will be redirected back to the Index action method of the Home Controller, where I can see the newly added category (i.e Bikes) in the list of categories, as shown below:

27_ListCategoriesWithNewlyAddedCategory

27. Now if you notice that the newly added categories are showing at the bottom. Generally this is not the case we want. We may either want to display the categories with newly added categories on top or we may want to display the categories alphabetically. For both the scenarios, all I need to do is modify the lambda expression which is fetching the categories from the CATEGORY_TBL

objdb.CATEGORY_TBL.ToList() – Returns the list of categories as its present in the CATEGORY_TBL

objdb.CATEGORY_TBL.ToList().OrderByDescending(m => m.ID) – Returns the list of categories ordered by ID descending, which means the latest added categories will be shown on top.

objdb.CATEGORY_TBL.ToList().OrderBy(m=>m.NAME) – Returns the list of categories ordered by the Category name, which means the list of categories in this case will be displayed sorted alphabetically.

I am using alphabetically sorting case and keeping the code for other 2 cases commented, so here’s the code of HomeController:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                //return View(objdb.CATEGORY_TBL.ToList());
                //Get the list of categories as it's in the database table and return it to the view for displaying
 
                //return View(objdb.CATEGORY_TBL.ToList().OrderByDescending(m => m.ID));
                //Get the list of categories ordered by id descending (i.e latest added categories on top) and return it to the view for displaying
 
                return View(objdb.CATEGORY_TBL.ToList().OrderBy(m=>m.NAME));
                //Get the list of categories ordered alphabetically by Name and return it to the view for displaying
            }
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        [HttpPost]
        //public ActionResult Create(FormCollection fc)
        public ActionResult Create(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Create object (objcategory) of CATEGORY_TBL class
            objcategory.NAME = fc["name"]; //Assign the name of category that we entered in texbox to the Name property of objcategory
            objdb.CATEGORY_TBL.Add(objcategory); //Add this category in the CATEGORY_TBL
            objdb.SaveChanges(); //Save the changes made to the data in the CATEGORY_TBL
            return RedirectToAction("Index","Home");// Redirects to Index action method of HomeController
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

And as expected, the home page will now display all the categories alphabetically sorted in ascending order, as shown below:

28_ListCategoriesAlphabeticallySorted

28. Now our next step is to work on the Edit, Details and Delete functionality in order to edit a particular category, view details of a particular category or delete a particular category.
Let’s work on the details functionality first. What we want to do here is that if we click on the details link in front of any category, it should display the details of the selected category and since the category table has only name information in it, we display want to display the name of the category in the category details page.

To do this, we need to create Details action method in the Home controller, which will take one parameter that will be the category id, and we need to get the category details based on this id and pass these details to the “Details” view. The following code does this:

1
2
3
4
public ActionResult Details(int id)
{            
      return return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault());
}

SingleOrDefault is used when we are fetching a single record from the database.
And when you run this code now, you get the error that the view for Details does not exist, as shown below:

29_CategoryDetailsViewNotExist

This is because we have not created the view for the details action method yet and when we click on the Details link, it’s unable to find the view that needs to be displayed here. So obviously our next step now is to create this view. To do this, simply right click on the Details action method on the HomeController and select Add View, and we will be presented with the following screen:

31_AddCategoryDetailsView

Simply fill the details by putting “Details” as the view name, then select “Details” template from the list of template options in the dropdown, then select the CATEGORY_TBL (CRUD_MVC_EF.Models) from the Model class dropdown, then select db (CRUD_MVC_EF.Models) from the Data context class dropdown, leave the checkbox selections as it is, leave the textbox for layout page blank and click on the Add button.

Once we do that, the Details view (Details.cshtml) will be created and opened for you. You can find this view (Details.cshtml file) in the Home folder inside the Views folder. Following is the code on Details.cshtml file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@model CRUD_MVC_EF.Models.CATEGORY_TBL
 
@{
    ViewBag.Title = "Details";
}
 
<h2>Details</h2>
 
<div>
@*<h4>CATEGORY_TBL</h4>*@
    <h4>Category Details</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.NAME)
        </dt>
 
        <dd>
            @Html.DisplayFor(model => model.NAME)
        </dd>
 
    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.ID }) |
    @Html.ActionLink("Back to List", "Index")
</p>

@model CRUD_MVC_EF.Models.CATEGORY_TBL tells that this view is strongly typed to CRUD_MVC_EF.Models.CATEGORY_TBL model. ViewBag.Title sets the browser’s title.

1
<h2>Details</h2>

displays the page’s title as “Details” on top of the page. @Html.DisplayNameFor(model => model.NAME) displays label for NAME field
@Html.DisplayFor(model => model.NAME) displays the value of the NAME field.
@Html.ActionLink(“Edit”, “Edit”, new { id = Model.ID }) displays an Edit link with ID field passed to id, telling which category it should edit upon being clicked.
@Html.ActionLink(“Back to List”, “Index”) is simply a link back to the home page (i.e. the Index action method of the Home controller)

Now, when you open the categories listing page again and click on the “Details” link in front of any category (let’s say you click Details link in front of Cars), you will be redirected to the details page, displaying the Details of the selected Car category (since in our case, CATEGORY_TBL has only one field NAME apart from the primary key field ID, the NAME field will be displayed), as shown below:

32_DetailsCategory

29. Let’s work on the edit functionality now. What we want to do here is that if we click on the edit link in front of any category, we should be able to edit that selected category.

To do this, we need to create Edit action method in the Home controller, which will again take one parameter that will be the category id, and we need to get the category details based on this id and pass this details to the “Edit” view so that we can view the details of the category which we want to edit. The following code does this:

1
2
3
4
public ActionResult Edit(int id)
{            
      return return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault()); //Gets the category data based on the id passed and returns the data to the view
}

Now when we will click on the Edit link in front of any category, we will get the error that the view “Edit” does not exist, as shown below, because we have not created the view yet.

33_EditCategory_ViewNotExist

30. So now we need to create the view for this “Edit” action method of Home Controller. Simply right click on the Edit action method of the Home Controller and click Add View, as shown below:

34_EditCategoryAddView

We will be presented with a screen to define the various properties of this view as shown below:

35_EditViewProperties

Simply fill the details by putting “Edit” as the view name, then select “Edit” template from the list of template options in the dropdown, then select the CATEGORY_TBL (CRUD_MVC_EF.Models) from the Model class dropdown, then select db (CRUD_MVC_EF.Models) from the Data context class dropdown, leave the checkbox selections as it is, leave the textbox for layout page blank and click on the Add button.

Once we do that, the Edit view (Edit.cshtml) will be created and opened for you. You can find this view (Edit.cshtml file) in the Home folder inside the Views folder.

Before explaining the code in Edit.cshtml file, let’s see how this page will look. And following is how the Edit page you will see when clicking on the edit link:

36_EditCategory

Following is the code on Edit.cshtml file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
@model CRUD_MVC_EF.Models.CATEGORY_TBL
 
@{
    ViewBag.Title = "Edit";
}
 
<h2>Edit</h2>
 
 
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
 
    <div class="form-horizontal">
        <h4>Edit Category </h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.ID)
 
        <div class="form-group">
            @Html.LabelFor(model => model.NAME, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.NAME, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.NAME, "", 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")
}

@model CRUD_MVC_EF.Models.CATEGORY_TBL tells that this view is strongly typed to CRUD_MVC_EF.Models.CATEGORY_TBL model. ViewBag.Title sets the browser’s title.

1
<h2>Details</h2>

displays the page’s title as “Edit” on top of the page.
@using (Html.BeginForm()) – defines the beginning of the form and the form spans from the following opening curly braces “{“ till the matching closing curly braces “}”.

The next line @Html.AntiForgeryToken() – helps protect your application against cross-site request forgery.

After that you see html code that defines the layout of your Edit category page. In this html, you can see:

I have changed

1
<h4>Edit</h4>

to

1
<h4>Edit Category </h4>

just for the sake of making the page title more meaningful.

@Html.ValidationSummary – which shows the validation messages in summary format, when the submitted data is not complying with all the validations placed on the page.
“text-danger” is the css class applied on it to display the message in red color.

@Html.HiddenFor(model => model.ID) – refers to the primary key field “ID”. Since we don’t want to display this field on the page usually, it is coded using @Html.HiddenFor instead of @Html.DisplayFor

@Html.LabelFor(model => model.NAME, htmlAttributes: new { @class = “control-label col-md-2” }) – displays a label for the Name column of the CATEGORY_TBL

@Html.EditorFor(model => model.NAME, new { htmlAttributes = new { @class = “form-control” } }) – displays a textbox for the Name column of the CATEGORY_TBL

@Html.ValidationMessageFor(model => model.NAME, “”, new { @class = “text-danger” }) – displays a validation message for the Name column of the CATEGORY_TBL

1
<input type="submit" value="Save" class="btn btn-default" />

– This is the submit button, which when clicked calls the Edit action method of the Home controller.

@Html.ActionLink(“Back to List”, “Index”) – This is a link which links back to the home page (i.e the Index action method of the Home Controller) and “Back to List” is the text of this link.

The following code references the jqueryval bundle with virtual path

1
2
3
4
“~/bundles/jqueryval”
@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

And you can open the BundleConfig.cs file in App_Start folder where the bundles are registered under RegisterBundles function. This file tells which bundle should include what all jquery files. For example the following line tells that “~/bundles/jquery” should include all the jquery-{version}.js files located at “~/Scripts/” location.

1
2
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));

If you open BundleConfig.cs file, following is the complete code you see:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
using System.Web;
using System.Web.Optimization;
 
namespace CRUD_MVC_EF
{
    public class BundleConfig
    {
        // For more information on bundling, visit http://go.microsoft.com/fwlink/?LinkId=301862
        public static void RegisterBundles(BundleCollection bundles)
        {
            bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                        "~/Scripts/jquery-{version}.js"));
 
            bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                        "~/Scripts/jquery.validate*"));
 
            // Use the development version of Modernizr to develop with and learn from. Then, when you're
            // ready for production, use the build tool at http://modernizr.com to pick only the tests you need.
            bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                        "~/Scripts/modernizr-*"));
 
            bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                      "~/Scripts/bootstrap.js",
                      "~/Scripts/respond.js"));
 
            bundles.Add(new StyleBundle("~/Content/css").Include(
                      "~/Content/bootstrap.css",
                      "~/Content/site.css"));
        }
    }
}

Notice that we are able to see the details on the Edit page, but when we click on “Save” button, it doesn’t update the category. That’s because we haven’t written the code to update the category, which is what we will be doing next.

31. Just like we created the “Create” action method in the Home controller with HttpPost attribute in order to add categories, now we need to create the “Edit” action method in the Home controller with HttpPost attribute and write the code to update the category. The meaning of each line of code is written in front of it in comment i.e. after “//” symbol. Following is the complete code of HomeController after writing the edit action method code with HttpPost attribute:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                //return View(objdb.CATEGORY_TBL.ToList());
                //Gets the list of categories as it's in the database table and returns it to the view for displaying
 
                //return View(objdb.CATEGORY_TBL.ToList().OrderByDescending(m => m.ID));
                //Gets the list of categories ordered by id descending (i.e latest added categories on top) and return it to the view for displaying
 
                return View(objdb.CATEGORY_TBL.ToList().OrderBy(m=>m.NAME));
                //Gets the list of categories ordered alphabetically by Name and returns it to the view for displaying
            }
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Create(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Create object (objcategory) of CATEGORY_TBL class
            objcategory.NAME = fc["name"]; //Assign the name of category that we entered in texbox to the Name property of objcategory
            objdb.CATEGORY_TBL.Add(objcategory); //Add this category in the CATEGORY_TBL
            objdb.SaveChanges(); //Save the changes made to the data in the CATEGORY_TBL
            return RedirectToAction("Index","Home");// Redirects to Index action method of HomeController
        }
 
        public ActionResult Details(int id)
        {
            return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault()); //Gets the category data based on the id passed and returns the data to the view
        }
 
        public ActionResult Edit(int id)
        {
            return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault()); //Gets the category data based on the id passed and returns the data to the view
        }
 
        [HttpPost]
        public ActionResult Edit(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Creates objcategory as object of the CATEGORY_TBL
            objcategory.ID= Convert.ToInt32(fc["id"]); //Passes the id from FormCollection to ID property of objcategory object
            objcategory.NAME = fc["name"]; //Passes the name from FormCollection to NAME property of objcategory object
            objdb.CATEGORY_TBL.Add(objcategory); //Passes the objcategory object to CATEGORY_TBL
            objdb.Entry(objcategory).State = EntityState.Modified; //Informs that we need to perform an update operation here and not insert
            objdb.SaveChanges(); //Saves the changes made into the database
            return RedirectToAction("Index", "Home"); //Redirects to the home page, where we can see the list of categories including the category we edited
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

After writing this much code, my Edit functionality will start working. And to check it, open the home page, click on edit link in front of Bikes, you will be redirected to edit page with Bikes prefilled in the form. Now let’s change the name of “Bikes” category to “Motor Bikes” and click Save button , as shown below:

37_EditCategory_Bikes_MotorBikes

As you click on the save button, you will be redirected back to the categories listing page, where you can see that instead of “Bikes”, the list displays “Motor Bikes”, as shown below, which is exactly what we wanted to achieve here.

38_CategoryUpdated_Bikes_MotorBikes

32. Now that we are done with the Edit and Details functionality for the categories, our next step is to do the Delete functionality for the categories, and that’s what we will be doing here now. If you click on the “Delete” button in front of any category, you can see “The resource cannot be found.” message, as shown below:

39_DeleteNotFound

This is because, as you can see from the url, it is searching for the Delete action method in the Home controller, which we have not been created yet. So our next step is to create the “Delete” action method in the HomeController and write the code to perform the deletion of the category. After the deletion activity, we want to redirect back to the Home page, to see the remaining list of categories. Following is the code of the HomeController after writing the “Delete” action method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_MVC_EF.Models;
 
using System.Data.Entity;
 
namespace CRUD_MVC_EF.Controllers
{
    public class HomeController : Controller
    {
        db objdb = new db();
        public ActionResult Index()
        {
            if (ModelState.IsValid)
            {
                //return View(objdb.CATEGORY_TBL.ToList());
                //Get the list of categories as it's in the database table and return it to the view for displaying
 
                //return View(objdb.CATEGORY_TBL.ToList().OrderByDescending(m => m.ID));
                //Get the list of categories ordered by id descending (i.e latest added categories on top) and return it to the view for displaying
 
                return View(objdb.CATEGORY_TBL.ToList().OrderBy(m=>m.NAME));
                //Get the list of categories ordered alphabetically by Name and return it to the view for displaying
            }
            return View();
        }
 
        public ActionResult Create()
        {
            return View();
        }
 
        [HttpPost]
        public ActionResult Create(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Create object (objcategory) of CATEGORY_TBL class
            objcategory.NAME = fc["name"]; //Assign the name of category that we entered in texbox to the Name property of objcategory
            objdb.CATEGORY_TBL.Add(objcategory); //Add this category in the CATEGORY_TBL
            objdb.SaveChanges(); //Save the changes made to the data in the CATEGORY_TBL
            return RedirectToAction("Index","Home");// Redirects to Index action method of HomeController
        }
 
        public ActionResult Details(int id)
        {
            return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault()); //Gets the category data based on the id passed and returns the data to the view
        }
 
        public ActionResult Edit(int id)
        {
            return View(objdb.CATEGORY_TBL.Where(m => m.ID == id).SingleOrDefault()); //Gets the category data based on the id passed and returns the data to the view
        }
 
        [HttpPost]
        public ActionResult Edit(FormCollection fc)
        {
            CATEGORY_TBL objcategory = new CATEGORY_TBL(); //Creates objcategory as object of the CATEGORY_TBL
            objcategory.ID= Convert.ToInt32(fc["id"]); //Passes the id from FormCollection to ID property of objcategory object
            objcategory.NAME = fc["name"]; //Passes the name from FormCollection to NAME property of objcategory object
            objdb.CATEGORY_TBL.Add(objcategory); //Passes the objcategory object to CATEGORY_TBL
            objdb.Entry(objcategory).State = EntityState.Modified; //Informs that we need to perform an update operation here and not insert
            objdb.SaveChanges(); //Saves the changes made into the database
            return RedirectToAction("Index", "Home"); //Redirects to the home page, where we can see the list of categories including the category we edited
        }
 
        public ActionResult Delete(Int32 Id)
        {
            var category = objdb.CATEGORY_TBL.Where(m => m.ID == Id).FirstOrDefault(); //Gets the category that needs to be deleted
            if(category !=null) //Checks that the category is not null
            {
                objdb.Entry(category).State = EntityState.Deleted; //Removes this category from the CATEGORY_TBL
                objdb.SaveChanges();//Saves the changes made to the data in the CATEGORY_TBL
            }
            return RedirectToAction("Index", "Home");// Redirects to Index action method of HomeController
        }
 
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";
 
            return View();
        }
 
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";
 
            return View();
        }
    }
}

After writing this much code, your delete functionality should start working. As you can see in the screenshot below, we have 3 categories right now, Cars, Mobiles and Motor Bikes:

40_CategoriesBeforeDeleting

Now when I click on the Delete button in front of “Motor Bikes” category, that particular category is deleted as expected and can’t be seen in the list of categories anymore, as shown below:

41_CategoriesAfterDeleting

So here we have learned how to perform CRUD operations in asp.net MVC 5 using entity framework on a single database table. Since CRUD operations are very common requirement in web applications, we will be working on it a bit more. In the next article I will be explaining how to work with multiple database tables in asp.net mvc 5 and perform CRUD operations on them using entity framework database first approach.


Leave a Reply

Your email address will not be published. Required fields are marked *