Click here to Skip to main content
14,355,397 members

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

Rate this:
3.55 (7 votes)
Please Sign up or sign in to vote.
3.55 (7 votes)
2 Jun 2019CPOL
Basic CRUD Operations With Paging, Sorting, Searching and Export Data Option

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.

Image 1

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

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

Technology I Used

  1. ASP.NET Core
  2. C#
  3. Generic Repository Pattern
  4. ASP.NET build in Dependency Injection
  5. EF Core
  6. LINQ
  7. Razor Tag Helpers
  8. jQuery Datatable
  9. jQuery UI
  10. Sweetalert 2
  11. Bootstrap
  12. REST API

Steps to Creating this Project

  1. Open VS 2017 and create an ASP.NET Core web application in Visual Studio 2017:

    Image 2

  2. Select MVC Project Structure from VS Template Project:

    Image 3

  3. 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.

    Image 4

  4. Overall Project Structure:

    Image 5

  5. Project Main UI:

    Image 6

  6. Add New Data UI:

    Image 7

  7. Edit Data UI:

    Image 8

  8. Delete Data UI:

    Image 9

  9. Export Data from Application:

    Image 10

Installed Nuget Packages

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

Image 11

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

  1. (.NET) Core Dependency Injection
  2. jQuery Data tables
  3. CsvHelper

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

RM Shahidul Islam Shahed
Program Manager
Bangladesh Bangladesh
PM, CSM®, CSD®

Comments and Discussions

 
QuestionCan u make same project without EF with ADO and stored procedures? Pin
altitus1-Sep-19 9:36
memberaltitus1-Sep-19 9:36 
AnswerRe: Can u make same project without EF with ADO and stored procedures? Pin
RM Shahidul Islam Shahed14-Sep-19 9:55
memberRM Shahidul Islam Shahed14-Sep-19 9:55 
GeneralRe: Can u make same project without EF with ADO and stored procedures? Pin
Member 1159457625-Sep-19 23:26
memberMember 1159457625-Sep-19 23:26 
Questionplease help me .I am getting Error while building the project Pin
Member 144846387-Jun-19 2:45
memberMember 144846387-Jun-19 2:45 
AnswerRe: please help me .I am getting Error while building the project Pin
RM Shahidul Islam Shahed14-Sep-19 9:50
memberRM Shahidul Islam Shahed14-Sep-19 9:50 
QuestionEdit or Delete buttons not working Pin
CurtisG6-Jun-19 11:34
professionalCurtisG6-Jun-19 11:34 
AnswerRe: Edit or Delete buttons not working Pin
RM Shahidul Islam Shahed14-Sep-19 9:52
memberRM Shahidul Islam Shahed14-Sep-19 9:52 
SuggestionSmall English Correction Pin
mldisibio3-Jun-19 9:16
membermldisibio3-Jun-19 9:16 
GeneralRe: Small English Correction Pin
RM Shahidul Islam Shahed14-Sep-19 9:53
memberRM Shahidul Islam Shahed14-Sep-19 9:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 2 Jun 2019

Stats

8.7K views
609 downloads
20 bookmarked