Implement Insert, Update and Delete Functionality in the WebGrid: Part 1

Introduction

This article explains how to implement insert, update, and delete functionality in a WebGrid.

This article will work on a Dynamic Database, so all the updates done will affect the database, so you can use this application directly in your MVC Project to solve the problems of showing the data in the Grid manner and can also make changes in it.

I had divided the complete application into three articles, this is the first article of this three Article Series. The next Article or Second Article can be seen Here "Implement Insert, Update and Delete Functionality in the WebGrid Part 2"

Step 1. First of all, you need to create a database in your SQL Database. The thing to remember is that you need to create one of the Columns with AutoIncrement that should also be the primary key because if your Database does not have a column with a primary key associated with it, then MVC will provide you errors when you attach it to an MVC Application and if this column is not AutoIncrement then the new data will have a problem inserting in a sequential manner.

Now, you need to attach it to your MVC Application. For that, go to the Server Explorer of your application and then right-click to "Add new Database".

Now, you need to go to the Model Folder of your application and add an ADO.NET Entity Data Model.

Step 2. On adding it will ask whether to generate from a database or to create an Empty Model, click on the "Generate From Database" and then click on the "Next" button.

Now, on the next page choose the database from which this Model should be generated.

Now if you check in the Model folder, then a class will be created, double-click on it, and you will get an SQL table like this,

Step 3. Now you need to add a class to this Model folder, for that again click on the Model folder and choose "Add a New Class".

Now open this class and write this code on your Modal Class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;
using System.Linq.Expressions;

namespace MvcApplication30.Models
{
    public class Class1
    {
        public int Student_ID { get; set; }
        public string Student_Name { get; set; }
        public string Student_Branch { get; set; }
        public string Student_City { get; set; }
        public string Student_State { get; set; }
    }

    public static class SortExtension
    {
        public static IOrderedEnumerable<TSource> OrderByWithDirection<TSource, TKey>(
            this IEnumerable<TSource> source,
            Func<TSource, TKey> keySelector,
            bool descending)
        {
            return descending ? source.OrderByDescending(keySelector)
                              : source.OrderBy(keySelector);
        }

        public static IOrderedQueryable<TSource> OrderByWithDirection<TSource, TKey>(
            this IQueryable<TSource> source,
            Expression<Func<TSource, TKey>> keySelector,
            bool descending)
        {
            return descending ? source.OrderByDescending(keySelector)
                              : source.OrderBy(keySelector);
        }
    }

    public class ModelServices : IDisposable
    {
        private readonly StudentEntities1 entities = new StudentEntities1();

        public bool SaveStudent(string name, string branch, string city, string state)
        {
            try
            {
                IT_Student stdnt = new IT_Student
                {
                    Student_Name = name,
                    Student_Branch = branch,
                    Student_City = city,
                    Student_State = state
                };
                entities.IT_Student.Add(stdnt);
                entities.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool UpdateStudent(int id, string name, string branch, string city, string state)
        {
            try
            {
                var stdnt = (from tbl in entities.IT_Student
                             where tbl.Student_Id == id
                             select tbl).FirstOrDefault();

                stdnt.Student_Name = name;
                stdnt.Student_Branch = branch;
                stdnt.Student_City = city;
                stdnt.Student_State = state;
                entities.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteStudent(int id)
        {
            try
            {
                var stdnt = (from tbl in entities.IT_Student
                             where tbl.Student_Id == id
                             select tbl).FirstOrDefault();

                entities.IT_Student.Remove(stdnt);
                entities.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }

        // For Custom Paging
        public IEnumerable<IT_Student> GetStudentPage(int pageNumber, int pageSize, string sort, bool Dir)
        {
            if (pageNumber < 1) pageNumber = 1;

            if (sort == "name")
                return entities.IT_Student.OrderByWithDirection(x => x.Student_Name, Dir)
                                          .Skip((pageNumber - 1) * pageSize)
                                          .Take(pageSize)
                                          .ToList();
            else if (sort == "state")
                return entities.IT_Student.OrderByWithDirection(x => x.Student_State, Dir)
                                          .Skip((pageNumber - 1) * pageSize)
                                          .Take(pageSize)
                                          .ToList();
            else if (sort == "city")
                return entities.IT_Student.OrderByWithDirection(x => x.Student_City, Dir)
                                          .Skip((pageNumber - 1) * pageSize)
                                          .Take(pageSize)
                                          .ToList();
            else
                return entities.IT_Student.OrderByWithDirection(x => x.Student_Id, Dir)
                                          .Skip((pageNumber - 1) * pageSize)
                                          .Take(pageSize)
                                          .ToList();
        }

        public int CountStudent()
        {
            return entities.IT_Student.Count();
        }

        public void Dispose()
        {
            entities.Dispose();
        }
    }

    public class PagedStudentModel
    {
        public int TotalRows { get; set; }
        public IEnumerable<IT_Student> It_Student { get; set; }
        public int PageSize { get; set; }
    }
}

First of all, I declared some variables in this class that are similar to the column name of my table.

Then, a class is created that will be used to sort the data in ascending or descending order.

Now the main work of this class has started, first of all, I created a function for saving the data as SaveStudent, in this function some variables are declared that will be holding the values for various data needed to be inserted in the database. In this function I created an object of my Database Table class, actually, this database class is automatically generated, you just start writing your table name and Intellisense will provide you with a class similar to your database table.

Similarly, I created the functions for update and delete, and they both will work on the ID of the data.

At the end you can see that I created a class named PagedStudentModel, this class will be used to call all these classes and functions created in the View, in this class three objects are created, one for the rows, the second for the automatically created class and the third for the page size. You can say that this class will work as a bridge between the View and the automatically generated class.

Our work on the Model class is now completed. Our next work will be to create a class for the Controller and a class for the View and then work on them. That work will be done in my future articles.