asp.net MVC using data table Example

Updated : Dec 25, 2019 in Articles

Crud operation in asp.net MVC using data table Example

In this article, we will learn about how to implement a Crud operation in asp.net MVC using data table with Example by using code first approach

Steps for implementing the data table Crud Operation

  1. Create a Database
  2. implementing model class
  3. implementing repositors
  4. creating a controller
  5. implementing view

Step1: Create a Database :

Go to SQL server create a table Name Like “Posts”

CREATE TABLE [dbo].[Posts](
	[PkPostId] [int] IDENTITY(1,1) NOT NULL,
	[FkSubCategoryId] [int] NOT NULL,
	[PostTitle] [nvarchar](max) NOT NULL,
	[PostContent] [nvarchar](max) NOT NULL,
	[PostTages] [nvarchar](max) NULL,
	[Author] [int] NULL,
	[MetaDescription] [nvarchar](max) NULL,
	[Publish] [bit] NULL,
	[CreatedDate] [datetime] NOT NULL,
	[UpdatedDate] [datetime] NULL,
	[UpdatedBy] [int] NULL,
	[DeleteFlag] [nvarchar](50) NULL,
	[Approved] [bit] NULL
	)

Step2: Implementing model class

Go to your model folder create a class name like posts and DatabaseContext enter the given below c# code

 [Table("Posts")]
    public class Posts
    {
        [Key]
        public int PkPostId { get; set; }
        [Required(ErrorMessage = "Required Subcategory Name")]
        [DisplayName("Select category")]
        public int FkSubCategoryId { get; set; }
        [Required(ErrorMessage = "Required post Title  Name")]
        public string PostTitle { get; set; }
        [AllowHtml]
        [Required(ErrorMessage = "Required post Content  Name")]
        public string PostContent { get; set; }
        public string PostTages { get; set; }
        public string MetaDescription { get; set; }
        public Boolean Publish { get; set; }
        public int Author { get; set; }
        public Boolean Approved { get; set; }
        [DataType(DataType.Date)]
        public DateTime CreatedDate { get; set; }
        [DataType(DataType.Date)]
        public Nullable<DateTime> UpdatedDate { get; set; }
        public int? UpdatedBy { get; set; }
        public string DeleteFlag { get; set; }
    }

once creating the model class then after we will create a DB context class through this we will retrieve the information from SQL server Add the following given DB context class in your model folder

 public class DatabaseContext : DbContext
    {
        public DatabaseContext() : base("DBConnection")
        {
        }       
        public DbSet<Posts> Posts { get; set; }            
    }

go to the web.config file add the connection string

 <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=YourSQlserverName; initial catalog=YourdatabaseName; user id=name; password=yourpassword;" providerName="System.Data.SqlClient" />
  </connectionStrings>

Step 3: implementing a repository

Go to your repository folder create a class name like EFPostRepository add the implemented given below EFPostRepository class

public class EFPostRepository
    {
        DatabaseContext context = new DatabaseContext();

        public IEnumerable<Posts> LastPost
        {
            get
            {
                context.Database.Log = s => Debug.WriteLine(s);
                return context.Posts.OrderByDescending(c => c.CreatedDate).Take(20);
            }
        }

        public List<LoadPosts> GetAllpages()
        {
            var result = context.Database.SqlQuery<LoadPosts>("LoadPosts").ToList();
            return result;
        }
        
        public IEnumerable<Posts> PostIEnum
        {
            get
            {
                return context.Posts;
            }
        }

        public IQueryable<Posts> PostList
        {
            get
            {
                context.Database.Log = s => Debug.WriteLine(s);
                return context.Posts.AsQueryable();
            }
        }

        public Posts Delete(int? Id)
        {
            Posts dbEntry = context.Posts.Find(Id);
            if (dbEntry != null)
            {
                context.Posts.Remove(dbEntry);
                context.SaveChanges();
            }
            return dbEntry;
        }
    
        public dynamic Details(int? Id)
        {
            Posts dbEntry = context.Posts.Find(Id);
            return dbEntry;
        }
        public async Task SaveAsync(Posts page)
        {
            if (page.PkPostId == 0)
            {
                var _page = new Posts();//To get Post ID From AddPost to use it for Details
                _page.PostTitle = page.PostTitle;
                _page.PostContent = page.PostContent;
                _page.MetaDescription = page.MetaDescription;
                _page.CreatedDate = DateTime.Now;
                _page.Author = 0;//will came form httpcontext
                _page.UpdatedDate = page.UpdatedDate;
                _page.UpdatedBy = page.UpdatedBy;
                _page.PostTages = page.PostTages;
                _page.FkSubCategoryId = page.FkSubCategoryId;
                _page.DeleteFlag = "N";
                _page.Approved = false;
                _page.Publish = false;
                context.Posts.Add(_page);
                await context.SaveChangesAsync();
                page.PkPostId = _page.PkPostId;
            }
            else
            {
                Posts dbEntry = context.Posts.Find(page.PkPostId);
                if (dbEntry != null)
                {
                    dbEntry.PkPostId = page.PkPostId;
                    dbEntry.PostTitle = page.PostTitle;
                    dbEntry.MetaDescription = page.MetaDescription;
                    dbEntry.PostContent = page.PostContent;
                    dbEntry.CreatedDate = page.CreatedDate;
                    dbEntry.UpdatedBy = page.UpdatedBy;
                    dbEntry.UpdatedDate = DateTime.Now;
                    dbEntry.PostTages = page.PostTages;
                    dbEntry.DeleteFlag = page.DeleteFlag;
                    dbEntry.Author = page.Author;
                    dbEntry.Publish = page.Publish;
                    dbEntry.Approved = page.Approved;
                    dbEntry.FkSubCategoryId = page.FkSubCategoryId;
                    await context.SaveChangesAsync();
                }
            }
        }
    }

Step 4: creating a controller

Create Postcontroller in your controller folder

public class PostController : Controller
    {
        EFPostRepository objpost = new EFPostRepository();
        // GET: Post
        public ActionResult Index()
        {
            ViewBag.actionName = this.ControllerContext.RouteData.Values["action"].ToString();
            ViewBag.controller = this.ControllerContext.RouteData.Values["controller"].ToString();
            return View();
        }

        public ActionResult GetPost(int id)
        {           
            var page = objpost.Details(id);
            var jsonOb2 = JsonConvert.SerializeObject(page);
            return Json(jsonOb2, JsonRequestBehavior.AllowGet);
        }
        public ActionResult CreatePost()
        {
            ViewBag.actionName = this.ControllerContext.RouteData.Values["action"].ToString();
            ViewBag.controller = this.ControllerContext.RouteData.Values["controller"].ToString();
            return View();
        }
         public ActionResult LoadData()
        {
            try
            {
                //Creating instance of DatabaseContext class
                using (DatabaseContext _context = new DatabaseContext())
                {
                    var draw = Request.Form.GetValues("draw").FirstOrDefault();
                    var start = Request.Form.GetValues("start").FirstOrDefault();
                    var length = Request.Form.GetValues("length").FirstOrDefault();
                    var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
                    var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
                    var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();


                    //Paging Size (10,20,50,100)  
                    int pageSize = length != null ? Convert.ToInt32(length) : 0;
                    int skip = start != null ? Convert.ToInt32(start) : 0;
                    int recordsTotal = 0;

                    // Getting all post data  
                    var Posts = (from tempPosts in _context.Posts
                                 select tempPosts);

                    //IEnumerable<Posts> Posts = null;
                   
                       // Posts = objpost.GetAllpages().ToList();
                    
                    //Sorting  
                    if (!(string.IsNullOrEmpty(sortColumn) &amp;&amp; string.IsNullOrEmpty(sortColumnDir)))
                    {
                        Posts = Posts.OrderBy(x => x.PkPostId);
                    }

                    //Search  
                    if (!string.IsNullOrEmpty(searchValue))
                    {

                        Posts = Posts.Where(m => m.PostTitle == searchValue);
                    }

                    //total number of rows count   
                    recordsTotal = Posts.Count();
                    //Paging   
                    var data = Posts.Skip(skip).Take(pageSize).ToList();
                    //Returning Json Data  
                    return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });
                }
            }
            catch (Exception ex)
            {
                throw;
            }

        }

        [HttpGet]
        public ActionResult Edit(int? ID)
        {
            try
            {
                
                    ViewBag.actionName = this.ControllerContext.RouteData.Values["action"].ToString();
                    ViewBag.controller = this.ControllerContext.RouteData.Values["controller"].ToString();
                    using (DatabaseContext _context = new DatabaseContext())
                    {
                        var Posts = (from posts in _context.Posts
                                     where posts.PkPostId == ID
                                     select posts).FirstOrDefault();
                        var SubCategory = _context.GetSubCategories.ToList();
                        ViewBag.SubCategory = new SelectList(SubCategory, "FkCategoryId", "SubCategoryName"); // do not need .ToList()
                        return View(Posts);
                    }
              
                return View();
            }
            catch (Exception)
            {
                throw;
            }
        }
      
        [HttpPost]
        public JsonResult DeletePost(int? ID)
        {
            using (DatabaseContext _context = new DatabaseContext())
            {
                var post = _context.Posts.Find(ID);
                if (ID == null)
                    return Json(data: "Not Deleted", behavior: JsonRequestBehavior.AllowGet);
                _context.Posts.Remove(post);
                _context.SaveChanges();

                return Json(data: "Deleted", behavior: JsonRequestBehavior.AllowGet);
            }
        }

       
        [HttpPost]
        [ValidateInput(false)]
        public async Task<ActionResult> CreatePost(Posts obj)
        {
            if (ModelState.IsValid)
            {
                if (obj.PostTitle != null &amp;&amp; obj.PostContent != null &amp;&amp; obj.FkSubCategoryId != 0)
                {
                    await objpost.SaveAsync(obj);
                    return Redirect("/Post/Index");
                }
                else
                {
                    return Content("content,title,Category Required");
                }
            }
            else
            {
                return View(obj);
            }
        }
       
       
    }

Step 5: implementing view

Load existing post-Data Index.cshtml before going to implementing Crud operation we want to require the data table cdn’s https://datatables.net/

<link href="~/Content/dataTables.bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery.dataTables.min.js"></script>
@{
    ViewBag.Title = "Index";
}

<div class="kt-breadcrumb">
    <nav class="breadcrumb">
        <a class="breadcrumb-item" href="index.html">@ViewBag.controller</a>
        <span class="breadcrumb-item active">@ViewBag.actionName</span>
    </nav>
</div>

<div class="main-page">
    <div id="top" class="">
        <a class="handle ui-slideouttab-handle-rounded btn-warning"><i class="fa fa-cog" aria-hidden="true"></i>filters</a>
        <div class="dropdown-menus" id="DownloadFilterContent1">
        </div>
    </div>
    <a class="btn btn-success btn-xs" href="/Post/CreatePost"><i class="fa fa-plus" aria-hidden="true"></i>Add</a>

    <div class="clearfix"><br /></div>
    <table id="demoGrid" class="table table-striped table-bordered dt-responsive nowrap blueTable" width="100%" cellspacing="0">
        <thead>
            <tr>
                <th data-id="0">PkPostId</th>
                <th data-id="1">subCat</th>
                <th data-id="2">PostTitle</th>
                <th data-id="3">Tages</th>
                <th data-id="4">Author</th>
                <th data-id="5">MetaDescription</th>
                <th data-id="6">Publish</th>
                <th data-id="7">CreatedDate</th>
                <th data-id="8">UpdatedDate</th>
                <th data-id="9">UpdatedBy</th>
                <th data-id="10">DeleteFlag</th>
            </tr>
        </thead>
    </table>
</div>
<div class="modelpop">

</div>

<script>
    $(document).ready(function () {
        $("#demoGrid").DataTable({

            "processing": true, // for show progress bar
            "serverSide": true, // for process server side
            "filter": true, // this is for disable filter (search box)
            "orderMulti": false, // for disable multiple column at once
            "pageLength": 10,

            "ajax": {
                "url":'@Url.Action("LoadData", "Post")', //"/Post/LoadData",
                "type": "POST",
                "datatype": "json"
            },

            "columnDefs":
                [{
                    "targets": [0],
                    "visible": false,
                    "searchable": false
                },
                {
                    "targets": [6],
                    "visible": false,
                    "searchable": false
                },
                {
                    "targets": [7],
                    "visible": false,
                    "searchable": false
                },
                {
                    "targets": [2],
                    "searchable": false,
                    "width": "20%"
                },
                {
                    "targets": [5],
                    "searchable": false,
                    "orderable": false
                },
                {
                    "targets": [6],
                    "searchable": false,
                    "orderable": false
                },
                {
                    "targets": [3],
                    "searchable": false,
                    "orderable": false
                }],

            "columns": [
                { "data": "PkPostId", "name": "PkPostId", "data-id": "0" },
                { "data": "SubCategoryName", "name": "Category", "data-id": "1" },

                {
                    "render": function (data, type, full, meta) {
                        return '<span>' + full.PostTitle + '</span><br/><a class="" href="/Post/Edit/' + full.PkPostId + '">Edit</a> | <a data-toggle="modal" data-target="#myModal" href="#" onclick=QuickEdit(' + full.PkPostId + '); >Quick Edit</a> | <a href="#" onclick=Reviewpage(' + full.PkPostId + '); >View</a> | <a href="#" onclick=DeleteData(' + full.PkPostId + '); >delete</a>';
                    }
                },

                { "data": "PostTages", "name": "Post Tages", "data-id": "3" },
                {
                    "render": function (data, type, full, meta) {
                        return '<a href="/Users/Details/' + full.UserId + '">' + full.Author + '</a>';
                    }
                },
                { "data": "MetaDescription", "name": "Description", "data-id": "5" },
                { "data": "Publish", "name": "Publish", "data-id": "6" },
                { "data": "CreatedDate", "name": "CreatedDate" },
                { "data": "UpdatedDate", "name": "UpdatedDate" },
                { "data": "UpdatedBy", "name": "UpdatedBy" },
                { "data": "DeleteFlag", "name": "Delete Flag", "width": "10px" },
            ]
        });
    });
</script>

<script>

        function DeleteData(CustomerID) {
            if (confirm("Are you sure you want to delete ...?")) {
                Delete(CustomerID);
            }
            else {
                return false;
            }
        }

        function Delete(PostId) {
            var url = '@Url.Content("~/")' + "Post/DeletePost";
            $.post(url, { ID: PostId }, function (data) {
                if (data == "Deleted") {
                    alert("Deleted page !");
                    oTable = $('#demoGrid').DataTable();
                    oTable.draw();
                }
                else {
                    alert("Something Went Wrong!");
                }
            });
    };   
           
</script>

Creating post view

Createpost.cshtml

@model CommunityLearningTutorials.Models.Posts

@{
    ViewBag.Title = "CreatePage";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://cdn.ckeditor.com/4.13.1/standard-all/ckeditor.js"></script>

<div class="kt-breadcrumb">
    <nav class="breadcrumb">
        <a class="breadcrumb-item" href="index.html">@ViewBag.controller</a>
        <span class="breadcrumb-item active">@ViewBag.actionName</span>
    </nav>
</div>
<div class="main-page">
    <div class="col-sm-12" style="padding: 5px;">

        @using (Html.BeginForm("CreatePost", "Post", FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
        {
            <div class="col-sm-9">
                @Html.AntiForgeryToken()

                <div class="form-horizontal">
                    @Html.ValidationSummary(true, "", new { @class = "text-danger" })

                    <div class="">
                        <div class="">
                            @Html.EditorFor(model => model.PostTitle, new { htmlAttributes = new { @class = "form-control", @placeholder = "Enter Post Title" } })
                            @Html.ValidationMessageFor(model => model.PostTitle, "", new { @class = "text-danger" })
                        </div>
                    </div>

                    <div class="">
                        @Html.TextAreaFor(model => model.PostContent, new { @id = "editor1", @name = "editor1" })
                        @Html.ValidationMessageFor(model => model.PostContent, "", new { @class = "text-danger" })
                    </div>



                    <div class="">
                        <label>Publish</label>
                        <div class="checkbox">
                            @Html.EditorFor(model => model.Publish, new { @class = "in-putData" })
                            @Html.ValidationMessageFor(model => model.Publish, "", new { @class = "text-danger" })
                        </div>
                    </div>
                    <div class="Seo-Action">
                        <h3 style="color: #ff8d1e;">Seo Section</h3>
                        <div class="">
                            <label>Tags</label>
                            <div class="">
                                @Html.EditorFor(model => model.PostTages, new { htmlAttributes = new { @class = "tagsinput", @id = "form-tags-1" } })
                                @Html.ValidationMessageFor(model => model.PostTages, "", new { @class = "text-danger" })
                            </div>
                        </div>

                        <div class="">
                            <label>Meta Descritpion</label>
                            <div class="">
                                @Html.TextAreaFor(model => model.MetaDescription, new { htmlAttributes = new { @class = "form-control meta-des", @placeholder = "Enter Description" } })
                                @Html.ValidationMessageFor(model => model.MetaDescription, "", new { @class = "text-danger" })
                            </div>
                        </div>
                    </div>
                </div>
                <div>
                    @Html.ActionLink("Back to List", "Index")
                </div>
            </div>

            <div class="col-sm-3">
                <div class="">
                    <div class="">
                        <input type="submit" value="Priview" class="btn btn-success btn-sm" />
                        <input type="submit" value="Publish" class="btn btn-primary btn-sm" />

                    </div>
                </div>
                <div class="clearfix"><br /></div>
                <div class="">
                    <div class="">
                        <select class="form-control" name="FkSubCategoryId" id="FkSubCategoryId" required="required" aria-required="true"></select>
                        @Html.ValidationMessageFor(model => model.FkSubCategoryId, "", new { @class = "text-danger" })
                    </div>
                </div>
            </div>
        }
    </div>
</div>

Editpost.cshtml

@model CommunityLearningTutorials.Models.Posts

@{
    ViewBag.Title = "Edit";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://cdn.ckeditor.com/4.13.1/standard-all/ckeditor.js"></script>

<div class="kt-breadcrumb">
    <nav class="breadcrumb">
        <a class="breadcrumb-item" href="index.html">@ViewBag.controller  <i class="fa fa-long-arrow-right" aria-hidden="true"></i></a>
        <span class="breadcrumb-item active">@ViewBag.actionName</span>
    </nav>
</div>
<div class="main-page">
    <div class="col-sm-12" style="padding: 5px;">

        @using (Html.BeginForm("CreatePost", "Post", FormMethod.Post, new { @class = "form-horizontal", role = "form" }))
        {
            <div class="col-sm-9">
                <a class="btn btn-info btn-xs" href="/Pages/CreatePage"><i class="fa fa-plus" aria-hidden="true"></i>Add New</a>
                @Html.AntiForgeryToken()
                <div class="form-horizontal">
                    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                    <div class="">
                        <div class="">
                            @Html.HiddenFor(model => model.PkPostId, new { htmlAttributes = new { @class = "form-control" } })
                            @Html.ValidationMessageFor(model => model.PkPostId, "", new { @class = "text-danger" })
                        </div>
                    </div>
                    <div class="">
                        <div class="">
                            @Html.EditorFor(model => model.PostTitle, new { htmlAttributes = new { @class = "form-control", @placeholder = "Enter Page Title" } })
                            @Html.ValidationMessageFor(model => model.PostTitle, "", new { @class = "text-danger" })
                        </div>
                    </div>

                    <div class="">
                        <div class="">
                            @Html.TextAreaFor(model => model.PostContent, new { @id = "editor1", @name = "editor1" })
                            @Html.ValidationMessageFor(model => model.PostContent, "", new { @class = "text-danger" })
                        </div>
                    </div>
                    <div class="">
                        <div class="">
                            @Html.HiddenFor(model => model.Author, new { htmlAttributes = new { @class = "form-control", @placeholder = "Enter Author Title" } })
                            @Html.ValidationMessageFor(model => model.Author, "", new { @class = "text-danger" })
                        </div>
                    </div>

                    <div class="">
                        <label>Publish</label>
                        <div class="">
                            <div class="checkbox">
                                @Html.EditorFor(model => model.Publish, new { @class = "in-putData" })
                                @Html.ValidationMessageFor(model => model.Publish, "", new { @class = "text-danger" })
                            </div>
                        </div>
                    </div>
                    <div class="form-group">

                        <div class="">
                            @Html.HiddenFor(model => model.CreatedDate, new { htmlAttributes = new { @class = "form-control" } })
                            @Html.ValidationMessageFor(model => model.CreatedDate, "", new { @class = "text-danger" })
                        </div>
                    </div>
                    <label>Flag</label>
                    <div class="">
                        <div class="">
                            @Html.EditorFor(model => model.DeleteFlag, new { htmlAttributes = new { @class = "form-control" } })
                            @Html.ValidationMessageFor(model => model.DeleteFlag, "", new { @class = "text-danger" })
                        </div>
                    </div>

                    <div class="Seo-Action">
                        <h3 style="color: #ff8d1e;">Seo Section</h3>
                        <div class="">
                            <label>Tags</label>
                            <div class="">
                                @Html.EditorFor(model => model.PostTages, new { htmlAttributes = new { @class = "tagsinput", @id = "form-tags-1" } })
                                @Html.ValidationMessageFor(model => model.PostTages, "", new { @class = "text-danger" })
                            </div>
                        </div>

                        <div class="">
                            <label>Meta Descritpion</label>
                            <div class="">
                                @Html.TextAreaFor(model => model.MetaDescription, new { htmlAttributes = new { @class = "form-control meta-des", @placeholder = "Enter Description" } })
                                @Html.ValidationMessageFor(model => model.MetaDescription, "", new { @class = "text-danger" })
                            </div>
                        </div>
                    </div>


                </div>
                <div>
                    @Html.ActionLink("List", "Index", null, new { @class = "btn btn-dark btn-xs" })
                </div>
            </div>

            <div class="col-sm-3">
                <div class="">
                    <div class="">

                        <input type="submit" value="Priview" class=" btn btn-primary btn-xs" />
                        <input type="submit" value="Update" class="btn btn-success btn-xs" />

                    </div>
                </div>
                <div class="clearfix"><br /></div>
                <div class="">
                    <label>Category Type</label>
                    <div class="">
                        @Html.DropDownListFor(m => m.FkSubCategoryId, (SelectList)ViewBag.SubCategory, "Please select", new { @class = "form-control" })
                        @Html.ValidationMessageFor(model => model.FkSubCategoryId, "", new { @class = "text-danger" })
                    </div>
                    <div class="">
                        <a data-toggle="modal" data-target="#myModals" class="btn btn-success btn-xs" href=""><i class="fa fa-plus" aria-hidden="true"></i>Add</a>
                    </div>
                </div>
            </div>
        }
    </div>
</div>

try to run your application thanks note: some CSS and js files are not available here

Curd operation in asp.net MVC using data table  Example
Curd operation in asp.net MVC using data table Example