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
- Create a Database
- implementing model class
- implementing repositors
- creating a controller
- 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) && 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 && obj.PostContent != null && 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

- Data annotations and validations in MVC with example - February 8, 2020
- Crud operation in asp.net MVC using data tableExample - December 25, 2019