Introduction
In this article, I will illustrate how to create an APS.NET core MVC web application using EF core. The 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
01. Visual Studio 2017
02. Install .NET Core 2.0.0 or above SDK
03. MSSQL Server 2008 or above
Technology I Used
01. ASP.NET Core
02. C#
03. Generic Repository Pattern
04. ASP.NET build in Dependency Injection
05. EF Core
06. LINQ
07. Razor Tag Helpers
08. jQuery Datatable
09. jQuery UI
10. Sweetalert 2
11. Bootstrap
12. REST API
Steps to Creating this Project
01. Open VS 2017 and Create an ASP.NET Core web application in Visual Studio 2017
03. 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.
04. Overall Project Structure
Install-Package Microsoft.EntityFrameworkCore -Version 2.2.0
Install-Package System.Linq.Dynamic.Core -Version 1.0.10
Install-Package CsvHelperInstall-Package jquery.datatables -Version 1.10.15
Install-Package jQuery -Version 3.4.1
Install-Package jQuery.UI.Combined -Version 1.12.1MSSQL 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 fastly 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)
ENDCreate 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 a 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 understand and enjoyed my article. For further more query please email me at teamechno360@gmail.com
No comments:
Post a Comment