(untagged)

ASP.NET Core CRUD Operations With Paging, Sorting, Searching and Export Data Option

RM Shahidul Islam Shahed

0.00/5 (No votes)

2 Jun 2019

Basic CRUD Operations With Paging, Sorting, Searching and Export Data Option

Download source - 4.1 MB

Introduction

In this article, I will illustrate how to create an ASP.NET core MVC web application using EF core. Main features of this application create a CRUD operation, faster Paging, Sorting, Searching and Export Data to CSV, PDF, Copy to Clipboard and print data as well.

After finishing this article, you will learn how to create a complete AP.NET Core web application with EF core and Generic Repository Pattern. The major advantage of this application is faster paging, sorting, filtering operation by implementing jQuery Data tables on the front end side.

Prerequisites

Visual Studio 2017
Install .NET Core 2.0.0 or above SDK
MSSQL Server 2008 or above

Technology I Used

ASP.NET Core
C#
Generic Repository Pattern
ASP.NET build in Dependency Injection
EF Core
LINQ
Razor Tag Helpers
jQuery Datatable
jQuery UI
Sweetalert 2
Bootstrap
REST API

Steps to Creating this Project

Open VS 2017 and create an ASP.NET Core web application in Visual Studio 2017:
Select MVC Project Structure from VS Template Project:
And also create an ASP.NET Core class library project for EF Core data access layer. Here, I have used the generic repository pattern for application data access.
Overall Project Structure:
Project Main UI:
Add New Data UI:
Edit Data UI:
Delete Data UI:
Export Data from Application:

Installed Nuget Packages

Go to tools from Visual Studio menu and the click NuGet Package Manager.

Install-Package Microsoft.EntityFrameworkCore -Version 2.2.0
Install-Package System.Linq.Dynamic.Core -Version 1.0.10
Install-Package CsvHelper
Install-Package jquery.datatables -Version 1.10.15
Install-Package jQuery -Version 3.4.1
Install-Package jQuery.UI.Combined -Version 1.12.1

MSSQL Table Script

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[PersonalInfo](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [FirstName] [nvarchar](max) NULL,
 [LastName] [nvarchar](max) NULL,
 [DateOfBirth] [datetime2](7) NULL,
 [City] [nvarchar](max) NULL,
 [Country] [nvarchar](max) NULL,
 [MobileNo] [nvarchar](max) NULL,
 [NID] [nvarchar](max) NULL,
 [Email] [nvarchar](max) NULL,
 [CreatedDate] [datetime2](7) NULL,
 [LastModifiedDate] [datetime2](7) NULL,
 [CreationUser] [nvarchar](max) NULL,
 [LastUpdateUser] [nvarchar](max) NULL,
 [Status] [tinyint] NOT NULL,
 CONSTRAINT [PK_PersonalInfo] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO

Create MOC Data in MSSQL Database

Using the following SQL script, I have created 5 luck data for testing application data load, search and pagination performance. jQuery data tables render data very fast from the server side by paging.

truncate table PersonalInfo
---SQL loop insert 
DECLARE @ID int =0; 
DECLARE @StartDate AS DATETIME = '1980-01-01' 
WHILE @ID < 20
BEGIN 
insert into PersonalInfo values('First Name ' + _
CAST(@ID AS nvarchar),'Last Name ' + CAST(@ID AS VARCHAR),dateadd(day,1, @StartDate), 
'City ' + CAST(@ID AS VARCHAR),'Country ' + CAST(@ID AS VARCHAR),_
 ABS(CAST(NEWID() AS binary(12)) % 1000) + 5555, 
ABS(CAST(NEWID() AS binary(12)) % 1000) + 99998888,'email' + _
    CAST(@ID AS nvarchar) +'@gmail.com',
GETDATE(),null,'Admin' + CAST(@ID AS VARCHAR),null,1) 
SET @ID = @ID + 1; 
set @StartDate=dateadd(day,1, @StartDate) 
END

Create a Model Class

public class PersonalInfo 
    {
        public long ID { get; set; }
        [Required(ErrorMessage = "First Name is required.")]
        [DisplayName("First Name")]
        public string FirstName { get; set; }
        [Required(ErrorMessage = "Last Name is required.")]
        [DisplayName("Last Name")]
        public string LastName { get; set; }
        [DisplayName("Date Of Birth")]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        public Nullable<DateTime> DateOfBirth { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        [DisplayName("Mobile No")]
        public string MobileNo { get; set; }
        public string NID { get; set; }
        [EmailAddress]
        public string Email { get; set; }
        public DateTime? CreatedDate { get; set; }
        public DateTime? LastModifiedDate { get; set; }
        public string CreationUser { get; set; }
        public string LastUpdateUser { get; set; }
        public byte Status { get; set; }
    }

DB Connection: appsettings.json

"ConnectionStrings": {
    "MSSQLConn": "Server=DEVSTATION\\MSSQLSERVER2017;Database=DevTest;_
     User ID=sa;Password=dev123456;"
  }

Startup.cs

var connectionString = Configuration["ConnectionStrings:MSSQLConn"];
            services.AddDbContext<DataBaseContext>_
            (options => options.UseSqlServer(connectionString));
 
            services.AddTransient<IPersonalInfoRepository, PersonalInfoRepository>();
 
            services.AddMvc().AddJsonOptions(options =>
            {
                options.SerializerSettings.ContractResolver
                = new Newtonsoft.Json.Serialization.DefaultContractResolver();
            });

Create Data: Ajax Request

$.ajax({
        type: "POST",
        url: "/PersonalInfo/Create",
        data: myformdata,
        success: function (result) {
            $("#PersonalInfoFormModel").modal("hide");
 
            Swal.fire({
                title: "Alert!",
                text: result,
                type: "Success"
            }).then(function () {
                $('#tblPersonalInfo').DataTable().ajax.reload();
            });
        },
        error: function (errormessage) {
            alert(errormessage.responseText);
        }
    });

Data Tables: JavaScript Code

File location in the project:

~Sln.jQueryDatatables\jQueryDatatables\wwwroot\js\PersonalInfo\PersonalInfo_Datatable.js

$(document).ready(function () {
    document.title = 'PersonalInfo DataTable';
    $("#tblPersonalInfo").DataTable({
        paging: true,
        select: true,
        "order": [[0, "desc"]],
        dom: 'Bfrtip',
 
        buttons: [
            'pageLength',
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    {
                        extend: 'pdfHtml5',
                        customize: function (doc) {
                            //doc.content[1].margin = [100, 0, 100, 0];
                            //Remove the title created by datatTables
                            doc.content.splice(0, 1);
                            //Create a date string that we use in the footer. 
                            //Format is dd-mm-yyyy
                            var now = new Date();
                            var jsDate = now.getDate() + '-' + 
                                (now.getMonth() + 1) + '-' + now.getFullYear();
 
                            doc.pageMargins = [20, 60, 20, 30];
                            // Set the font size fot the entire document
                            doc.defaultStyle.fontSize = 7;
                            // Set the fontsize for the table header
                            doc.styles.tableHeader.fontSize = 10; 
 
                            doc['header'] = (function () {
                                return {
                                    columns: [
                                        {
                                            alignment: 'left',  //center
                                            italics: true,
                                            text: 'Personal Info',
                                            fontSize: 18,
                                            margin: [0, 0]
                                        }
                                    ],
                                    margin: 20
                                }
                            });
 
                            // Create a footer object with 2 columns
                            doc['footer'] = (function (page, pages) {
                                return {
                                    columns: [
                                        {
                                            alignment: 'left',
                                            text: ['Created on: ', 
                                                  { text: jsDate.toString() }]
                                        },
                                        {
                                            alignment: 'right',
                                            text: ['page ', { text: page.toString() }, ' 
                                                    of ', { text: pages.toString() }]
                                        }
                                    ],
                                    margin: 5
                                }
                            });
                            // Change dataTable layout (Table styling)
                            // To use predefined layouts uncomment the line below 
                            // and comment the custom lines below
                            // doc.content[0].layout = 'lightHorizontalLines'; // noBorders,
                                                                          // headerLineOnly
                            var objLayout = {};
                            objLayout['hLineWidth'] = function (i) { return .5; };
                            objLayout['vLineWidth'] = function (i) { return .5; };
                            objLayout['hLineColor'] = function (i) { return '#aaa'; };
                            objLayout['vLineColor'] = function (i) { return '#aaa'; };
                            objLayout['paddingLeft'] = function (i) { return 4; };
                            objLayout['paddingRight'] = function (i) { return 4; };
                            doc.content[0].layout = objLayout;
                        }, 
 
                        orientation: 'portrait', // landscape
                        pageSize: 'A4',
                        pageMargins: [0, 0, 0, 0], // try #1 setting margins
                        margin: [0, 0, 0, 0], // try #2 setting margins
                        text: '<u>PDF</u>',
                        key: { // press E for export PDF
                            key: 'e',
                            altKey: false
                        },
                        exportOptions: {
                            columns: [0, 1, 2, 3, 4, 5], //column id visible in PDF
                            modifier: {
                                // DataTables core
                                order: 'index',   // 'current', 'applied', 'index',  'original'
                                page: 'all',      // 'all',     'current'
                                search: 'none'    // 'none',    'applied', 'removed'
                            }
                        }
                    },
                    'copyHtml5',
                    'excelHtml5',
                    'csvHtml5',
                    {
                        extend: 'print',
                        exportOptions: {
                            columns: [0, 1, 2, 3, 4, 5],
                            page: 'all'
                        }
                    }
                ]
            }
        ],
 
        "processing": true,
        "serverSide": true,
        "filter": true, //Search Box
        "orderMulti": false,
        "stateSave": true,
 
        "ajax": {
            "url": "/PersonalInfo/GetDataTabelData",
            "type": "POST",
            "datatype": "json"
        },
 
        "columns": [
            { "data": "ID", "name": "ID", "autoWidth": true },
            { "data": "FirstName", "name": "FirstName", "autoWidth": true },
            {
                "data": "DateOfBirth",
                "name": "DateOfBirth",
                "autoWidth": true,
                "render": function (data) {
                    var date = new Date(data);
                    var month = date.getMonth() + 1;
                    return (month.length > 1 ? month : "0" + month) + "/" + 
                                   date.getDate() + "/" + date.getFullYear();
                }
            },
            { "data": "City", "name": "City", "autoWidth": true },
            { "data": "Country", "name": "Country", "autoWidth": true },
            { "data": "MobileNo", "name": "MobileNo", "autoWidth": true },
            {
                data: null, render: function (data, type, row) {
                    return "<a href='#' class='btn btn-info btn-sm' 
                        onclick=AddEditPersonalInfo('" + row.ID + "');>Edit</a>";
                }
            },
            {
                data: null, render: function (data, type, row) {
                    return "<a href='#' class='btn btn-danger btn-sm' 
                      onclick=DeletePersonalInfo('" + row.ID + "'); >Delete</a>";
                }
            }
        ],
 
        'columnDefs': [{
            'targets': [6, 7],
            'orderable': false,
        }],
        "lengthMenu": [[10, 15, 25, 50, 100, 200], [10, 15, 25, 50, 100, 200]]
    });
});

Data Tables: C# Code

[HttpPost]
        public IActionResult GetDataTabelData()
        {
            try
            {
                var draw = HttpContext.Request.Form["draw"].FirstOrDefault();
                var start = Request.Form["start"].FirstOrDefault();
                var length = Request.Form["length"].FirstOrDefault();
 
                var sortColumn = Request.Form["columns[" + 
                       Request.Form["order[0][column]"].FirstOrDefault() + "]
                                   [name]"].FirstOrDefault();
                var sortColumnAscDesc = Request.Form["order[0][dir]"].FirstOrDefault();
                var searchValue = Request.Form["search[value]"].FirstOrDefault();
 
                int pageSize = length != null ? Convert.ToInt32(length) : 0;
                int skip = start != null ? Convert.ToInt32(start) : 0;
                int resultTotal = 0;
 
                var personalInfoData = (from tblObj in _personalInfoRepository.GetAll() 
                                        select tblObj);
 
                //Sorting
                if (!(string.IsNullOrEmpty(sortColumn) && 
                           string.IsNullOrEmpty(sortColumnAscDesc)))
                {
                    personalInfoData = _personalInfoRepository.GetAll().
                                  OrderBy(sortColumn + " " + sortColumnAscDesc);
                }
 
                //Search
                if (!string.IsNullOrEmpty(searchValue))
                {
                    personalInfoData = personalInfoData.Where
                                 (t => t.FirstName.Contains(searchValue)
                    || t.LastName.Contains(searchValue)
                    || t.City.Contains(searchValue)
                    || t.Country.Contains(searchValue)
                    || t.MobileNo.Contains(searchValue));
                }
 
                resultTotal = personalInfoData.Count();
                var result = personalInfoData.Skip(skip).Take(pageSize).ToList();
                return Json(new { draw = draw, recordsFiltered = resultTotal, 
                                   recordsTotal = resultTotal, data = result });
 
            }
            catch (Exception ex)
            {
                throw ex;
            } 
        }

Export All Data to CSV

For export, all data I have used CSV helper. From Nuget library, just install CsvHelper by following command in PMC:
Install-Package CsvHelper.

public FileStreamResult ExportAllDatatoCSV()
        {
            var personalInfoData = (from tblObj in 
                  _personalInfoRepository.GetAll() select tblObj).Take(100);
            var result = Common.WriteCsvToMemory(personalInfoData);
            var memoryStream = new MemoryStream(result);
            return new FileStreamResult(memoryStream, "text/csv") 
                      { FileDownloadName = "Personal_Info_Data.csv" };
        }

Conclusion

This is a very basic CRUD application using ASP.NET .NET Core but advance in data operation. The application performs faster data loading operation, which is implemented by jQuery data tables. Application successfully loaded 5 luck dummy data with paging within a few seconds. Searching, filtering, and paging are pretty fast as well. For future work, I will implement the login module in this project.

Thanks for your valuable time. I hope you fully understood and enjoyed my article.

References

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here