Asp.net Mvc Framework 5

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

  • 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) &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

Santosh Tiwari
Like
Like Love Haha Wow Sad Angry

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x