使用分页、排序、搜索和数据导出选项的ASP.NET Core CRUD操作

RM Shahidul Islam Shahed

3.93/5 (8投票s)

2019年6月2日

CPOL

2分钟阅读

32000

1974

具有分页、排序、搜索和数据导出选项的基本CRUD操作

下载源代码 - 4.1 MB

引言

在本文中，我将演示如何使用EF Core创建一个ASP.NET Core MVC Web应用程序。此应用程序的主要功能是创建CRUD操作，实现更快的分页、排序、搜索以及将数据导出为CSV、PDF、复制到剪贴板和打印数据。

阅读完本文后，您将学习如何使用EF Core和泛型仓储模式创建一个完整的AP.NET Core Web应用程序。此应用程序的主要优点是通过在前端实现jQuery DataTables来实现更快的分页、排序和过滤操作。

必备组件

Visual Studio 2017
安装.NET Core 2.0.0或更高版本的SDK
MSSQL Server 2008或更高版本

我使用的技术

ASP.NET Core
C#
泛型仓储模式
ASP.NET内置的依赖注入
EF Core
LINQ
Razor标签助手
jQuery Datatable
jQuery UI
Sweetalert 2
Bootstrap
REST API

创建此项目的步骤

打开VS 2017并在Visual Studio 2017中创建一个ASP.NET Core Web应用程序
从VS模板项目中选择MVC项目结构
并为EF Core数据访问层创建一个ASP.NET Core类库项目。在这里，我使用了泛型仓储模式来访问应用程序数据。
整体项目结构
项目主UI
添加新数据UI
编辑数据UI
删除数据UI
从应用程序导出数据

已安装的Nuget包

从Visual Studio菜单中转到工具，然后单击NuGet包管理器。

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表脚本

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

在MSSQL数据库中创建MOC数据

使用以下SQL脚本，我创建了5条幸运数据用于测试应用程序数据加载、搜索和分页性能。jQuery DataTables通过分页从服务器端非常快速地呈现数据。

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

创建模型类

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; }
    }

数据库连接：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();
            });

创建数据：Ajax请求

$.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);
        }
    });

数据表：JavaScript代码

项目中的文件位置

~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]]
    });
});

数据表：C#代码

[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;
            } 
        }

将所有数据导出到CSV

为了导出所有数据，我使用了CSV Helper。从Nuget库中，只需使用PMC中的以下命令安装`CsvHelper`
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" };
        }

结论

这是一个使用ASP.NET .NET Core的非常基本的CRUD应用程序，但在数据操作方面比较高级。该应用程序执行更快的加载数据操作，这是由jQuery DataTables实现的。该应用程序在几秒钟内成功加载了5条幸运的虚拟数据以及分页。搜索、过滤和分页也相当快。未来的工作中，我将在该项目中实现登录模块。

感谢您宝贵的时间。我希望您完全理解并喜欢我的文章。