目录
介绍
在本文中，我将说明如何使用EF Core创建ASP.NET Core MVC Web应用程序。此应用程序的主要功能是创建CRUD操作、更快的分页、排序、搜索和导出数据到CSV、PDF、复制到剪贴板和打印数据。
完成本文后，您将学会如何使用EF Core和泛型存储库模式(Generic Repository Pattern)创建完整的AP.NET Core Web应用程序。此应用程序的主要优点是通过在前端实现jQuery Datatable来加快分页、排序和过滤操作。
先决条件
- Visual Studio 2017
- 安装.NET Core 2.0.0或更高版本的SDK
- MSSQL Server 2008或更高版本
我使用的技术
- ASP.NET Core
- C＃
- 泛型存储库模式(Generic Repository Pattern)
- ASP.NET内置依赖注入
- EF Core
- LINQ
- Razor标签助手
- jQuery Datatable
- jQuery UI
- Sweetalert 2
- Bootstrap
- REST API
创建此项目的步骤
1、打开VS 2017并在Visual Studio 2017中创建ASP.NET Core Web应用程序：
2、从VS模板项目中选择MVC项目结构：
3、并为EF Core数据访问层创建ASP.NET Core类库项目。在这里，我使用泛型存储库模式(Generic Repository Pattern)进行应用程序数据访问。
4、整体项目结构：
5、项目主UI：
6、添加新数据的UI：
7、编辑数据的UI：
8、删除数据的UI：
9、从应用程序导出数据：
已安装的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.1MSSQL表脚本
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 Datatable通过分页从服务器端非常快速地呈现数据。
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 Datatable实现的。应用程序在几秒钟内成功加载了5个运气虚拟数据。搜索、过滤和分页也非常快。为了将来的工作，我将在这个项目中实现登录模块。
谢谢你宝贵的时间。我希望你完全理解并喜欢我的文章。
参考
原文地址：https://www.codeproject.com/Articles/5061500/ASP-NET-Core-CRUD-Operations-With-Paging-Sorting-S
1658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
