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
