本文共 14767 字,大约阅读时间需要 49 分钟。
目录
在本文中,我将说明如何使用EF Core创建ASP.NET Core MVC Web应用程序。此应用程序的主要功能是创建CRUD操作、更快的分页、排序、搜索和导出数据到CSV、PDF、复制到剪贴板和打印数据。
完成本文后,您将学会如何使用EF Core和泛型存储库模式(Generic Repository Pattern)创建完整的AP.NET Core Web应用程序。此应用程序的主要优点是通过在前端实现jQuery Datatable来加快分页、排序和过滤操作。
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、从应用程序导出数据:
从Visual Studio菜单转到工具,然后单击NuGet包管理器。
Install-Package Microsoft.EntityFrameworkCore -Version 2.2.0Install-Package System.Linq.Dynamic.Core -Version 1.0.10Install-Package CsvHelperInstall-Package jquery.datatables -Version 1.10.15Install-Package jQuery -Version 3.4.1Install-Package jQuery.UI.Combined -Version 1.12.1
SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO 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
使用以下SQL脚本,我创建了5个运气数据,用于测试应用程序数据加载,搜索和分页性能。jQuery Datatable通过分页从服务器端非常快速地呈现数据。
truncate table PersonalInfo---SQL loop insert DECLARE @ID int =0; DECLARE @StartDate AS DATETIME = '1980-01-01' WHILE @ID < 20BEGIN 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 NullableDateOfBirth { 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; } }
"ConnectionStrings": { "MSSQLConn": "Server=DEVSTATION\\MSSQLSERVER2017;Database=DevTest;_ User ID=sa;Password=dev123456;" }
var connectionString = Configuration["ConnectionStrings:MSSQLConn"]; services.AddDbContext_ (options => options.UseSqlServer(connectionString)); services.AddTransient (); services.AddMvc().AddJsonOptions(options => { options.SerializerSettings.ContractResolver = new Newtonsoft.Json.Serialization.DefaultContractResolver(); });
$.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: 'PDF', 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 "Edit"; } }, { data: null, render: function (data, type, row) { return "Delete"; } } ], 'columnDefs': [{ 'targets': [6, 7], 'orderable': false, }], "lengthMenu": [[10, 15, 25, 50, 100, 200], [10, 15, 25, 50, 100, 200]] });});
[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 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个运气虚拟数据。搜索、过滤和分页也非常快。为了将来的工作,我将在这个项目中实现登录模块。
谢谢你宝贵的时间。我希望你完全理解并喜欢我的文章。
原文地址:
转载地址:http://hizhj.baihongyu.com/