Insert, Update, Delete using Models without EF in ASP.NET MVC5

In this chapter, you will learn:
1. How to Access Database and Tables using Model without Entity Framework 6?
2. How to Insert, Update and Delete from Tables without Entity Framework?

As I mentioned earlier, that Entity Framework is a very powerful tool for database management that allows you to insert, update and delete in the database easily but it is very heavy and limits developer control. Without Entity Framework, you have all the things in your hand and have complete control on your code. In this chapter, I will tell you how can you communicate with the database and Insert, Update and Delete record without using Entity Framework. However, In the next chapter, I will teach you the same thing but using Entity Framework. In the previous chapter, you learned how to add a Model and access data in Views. Let’s move to more advanced topic now.

Content of this Chapter:

1. Create or Open a Project
2. Create a Database and Table
3. Add Connection String to Web.Config file
4. Create Model for Database and Table
5. Create a Class to Handle Query
6. Create Controllers
7. Create Views
8. Run your Project

1. Create or Open a Project

I have created already a Project CompShop earlier. If you haven't created or landed first time on this page then don't worry and Create a New ASP.NET MVC 5 Project in Visual Studio now. I tried to make each chapter complete and independent so you can start right now from this chapter.

2. Create a Database and Table

Now, we will create a Database and a Table. This table will be used for storing, editing and deleting records. Either you can create database in SQL Server Management Studio or Visual Studio Server Explorer. Here, I will create database and table in Visual Studio Server Explorer. Here are the complete steps.

1. Create Database
Step 1: Open Server Explorer in Visual Studio. Go to View Server Explorer.
View - Server Explorer View - Server Explorer
Step 2: Right Click on Data Connections Create New SQL Server Database

Create New SQL Server Database
Step 3: Fill Server name as .\SQLExpress and Database name as CompShopDBwithoutEF and click OK.

SQL Server Database

b. Create Table

Step 1: Expand your Database in Server Explorer and Right Click on Table Add New Table.

Create Table
Step 2: Now design table as follows or execute following script to add table ItemList in the database.

Script
CREATE TABLE [dbo].[ItemList]
(
	[ID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(50) NULL, 
    [Category] NVARCHAR(50) NULL, 
    [Price] DECIMAL NULL
)

Design Table
Step 3: Now your Tables are created. You can see it in Server Explorer.
Table Design  

3. Add Connection String in Web.config file

Step 1: Right Click on your Database Connection in Server Explorer and Select Properties.
Database Properties
Step 2: You will find Connection String in Properties window. Copy connection string and add it in web.config file.

Connection String
Step 3: Open Root Web.config file and add connection string before </Configuration> as follow.
web.config
Code:
  <connectionStrings>
    <add name="CompShopConString" connectionString="Data Source=.\SQLExpress;Initial Catalog=CompShopDBwithoutEF;Integrated Security=True;Pooling=False"/>
  </connectionStrings>

Connection String

4. Create Model Class

Now, the next step is to creating model for ItemList Table. Here is the steps.

Step 1: Create a new ItemModel.cs class in Model. Right click on Model Add Class
Adding a class
Step 2: Add the following mapping code in it.
namespace CompShop.Models
{
    public class ItemModel
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
    }
}

5. Create an ItemDBHandler.cs Class to Handle Database Query

Here, I am going to create a class that will handle all the database related query as insert, update and delete. Model will use this class to make a connection with database and process record.

Step 1: Create an ItemDBHandler.cs class in Model folder. Right click on Model Add Class. Add the following code in ItemDBHandler.cs class.
Code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace CompShop.Models
{
    public class ItemDBHandler
    {
        private SqlConnection con;
        private void connection()
        {
            string constring = ConfigurationManager.ConnectionStrings["CompShopConString"].ToString();
            con = new SqlConnection(constring);
        }

        // 1. ********** Insert Item **********
        public bool InsertItem(ItemModel iList)
        {
            connection();
            string query = "INSERT INTO ItemList VALUES('" + iList.Name + "','" + iList.Category + "'," + iList.Price + ")";
            SqlCommand cmd = new SqlCommand(query,con);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            
            if (i >= 1)
                return true;
            else
                return false;
        }

        // 2. ********** Get All Item List **********
        public List<ItemModel> GetItemList()
        {
            connection();
            List<ItemModel> iList = new List<ItemModel>();

            string query = "SELECT * FROM ItemList";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            con.Open();
            adapter.Fill(dt);
            con.Close();

            foreach(DataRow dr in dt.Rows)
            {
                iList.Add(new ItemModel
                {
                    ID = Convert.ToInt32(dr["ID"]),
                    Name = Convert.ToString(dr["Name"]),
                    Category = Convert.ToString(dr["Category"]),
                    Price = Convert.ToDecimal(dr["Price"])
                });
            }
            return iList;
        }

        // 3. ********** Update Item Details **********
        public bool UpdateItem(ItemModel iList)
        {
            connection();
            string query = "UPDATE ItemList SET Name = '"+iList.Name+"', Category = '"+iList.Category+"', Price = "+iList.Price+" WHERE ID = "+iList.ID;
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();

            if (i >= 1)
                return true;
            else
                return false;
        }

        // 4. ********** Delete Item **********
        public bool DeleteItem(int id)
        {
            connection();
            string query = "DELETE FROM ItemList WHERE ID = " + id;
            SqlCommand cmd = new SqlCommand(query, con);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();

            if (i >= 1)
                return true;
            else
                return false;
        }
    }
}

These are the very basic ADO.NET code and you can understand it easily. If you are having a problem in ADO.NET then you can take a quick look or learn complete ADO.NET here.

 

6. Create a Controller : ItemController.cs

Step 1: Right Click on Controllers Add Controller
Adding a Controller
Step 2: Create MVC 5 Controller - Empty and click Add button.
Adding a Empty Controller
Step 3: Give controller name : ItemController and click Add.
Step 4: Now Add following code in ItemController.cs
using System.Collections.Generic;
using System.Web.Mvc;
using CompShop.Models;

namespace CompShop.Controllers
{
    public class ItemController : Controller
    {
        // 1. *********** Display All Item List in Index Page ***********
        public ActionResult Index()
        {
            ViewBag.ItemList = "Computer Shop Item List Page";
            ItemDBHandler IHandler = new ItemDBHandler();
            ModelState.Clear();
            return View(IHandler.GetItemList());
        }

        // 2. *********** Add New Item ***********
        [HttpGet]
        public ActionResult Create()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Create(ItemModel iList)
        {
           // try
            //{
                if(ModelState.IsValid)
                {
                    ItemDBHandler ItemHandler = new ItemDBHandler();
                    if(ItemHandler.InsertItem(iList))
                    {
                        ViewBag.Message = "Item Added Successfully";
                        ModelState.Clear();
                    }
                }
                return View();
           /* }
            catch { return View();  }*/
        }

        // 3. *********** Update Item Details ***********
        [HttpGet]
        public ActionResult Edit(int id)
        {
            ItemDBHandler ItemHandler = new ItemDBHandler();
            return View(ItemHandler.GetItemList().Find(itemmodel => itemmodel.ID == id));
        }
        [HttpPost]
        public ActionResult Edit(int id, ItemModel iList)
        {
            try
            {
                ItemDBHandler ItemHandler = new ItemDBHandler();
                ItemHandler.UpdateItem(iList);
                return RedirectToAction("Index");
            }
            catch { return View(); }
        }

        // 4. *********** Delete Item Details ***********
        public ActionResult Delete(int id)
        {
            try
            {
                ItemDBHandler ItemHandler = new ItemDBHandler();
                if(ItemHandler.DeleteItem(id))
                {
                    ViewBag.AlertMsg = "Item Deleted Successfully";
                }
                return RedirectToAction("Index");
            }
            catch { return View(); }
        }

        public ActionResult Details(int id)
        {
            ItemDBHandler ItemHandler = new ItemDBHandler();
            return View(ItemHandler.GetItemList().Find(itemmodel => itemmodel.ID == id));
        }
    }
}

Let’s take a moment to understand this code

Honestly, nothing special in this code. I have used Model class ItemModel to access data or store data. ItemModel class uses ItemDBHandler class for processing the database related job. I have written all the database related code in ItemDBHandler.cs class.

7. Add View Pages from Controller’s Action Method.

Now, this time is to adding view pages for all the action methods from ItemController. It is very easy, just look how.

Step 1: Go to ItemController and Right click on Index() Action Method then select Add View…
Index Action Method
Step 2: Add View Dialog box will appear. Fill details as in this picture and click to Add button.
Step 3: Using the same process Add View for Create(), Edit(), Delete() and Details() action method.
Create()
Create Action Method
Edit()
Edit Action Method
Delete()
Delete Action Method
Details()
Details Action Method

8. Run your Project

Press F5 to Run your project and navigate to following link http://localhost:1233/Item/Index/

Output Image
Index Page
Create Page
Item List Page
Details Page

Summary:

In this chapter, you learned how can you use Model for Inserting, Updating and Deleting record from database table. You must make a Database Handler class along with Model because Model depends on this handler class. It is known as best practices and when you develop your project systematically. It would be easier to understand your code later. When Controllers needs any information from the database it looks into model and model call DB Handler class and gather information and then return value to the controller. I think this chapter is complete explanatory and you have understood it clearly.

This chapter uses clean ADO.NET Code to connect to Database. In the next chapter, you will make this same project again but this time I will introduce you to Entity Framework 6. You will see that how Entity Framework makes Database Part easier. In the next chapter, you will learn Insert, Update and Delete in ASP.NET MVC 5 using Entity Framework 6.

More Similar Articles

Insert Update Delete In ASP.NET MVC 5 Without Entity Framework [Using Stored Procedure]

More Articles

 

Share your thought