Understanding Relational and Non-Relational Databases: A Comparative Overview

 Relational and Non-Relational Databases

In the realm of database management, two primary paradigms have emerged: Relational and Non-relational databases. Each offers distinct features, advantages, and disadvantages, catering to different use cases and requirements. Let's delve into the characteristics of each, along with their types, use cases, and conclusions.

Relational Databases:

Types: Relational databases organize data into tables consisting of rows and columns. The most popular relational database management systems (RDBMS) include MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

Advantages:

• Structured Data: Relational databases enforce a structured format, ensuring data integrity and consistency.
• ACID Compliance: Transactions in relational databases adhere to ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring data reliability.
• SQL Support: Standardized SQL (Structured Query Language) facilitates easy data manipulation and querying.
• Joins: Relational databases excel at handling complex relationships between data through JOIN operations.

Disadvantages:

• Scalability: Scaling relational databases horizontally can be complex and costly.
• Performance: High transaction volumes can impact performance, especially in complex queries.
• Schema Rigidity: Altering the database schema requires careful planning and can lead to downtime.

Use Cases:

Relational databases are ideal for applications with structured data and complex relationships, such as:

• Enterprise Applications: CRM (Customer Relationship Management), ERP (Enterprise Resource Planning), and HRM (Human Resource Management) systems.
• Financial Systems: Accounting and transaction processing applications.
• E-commerce Platforms: Product catalogs, inventory management, and order processing.

Non-relational Databases (NoSQL):

Types: Non-relational databases come in various forms, including document-oriented (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores (e.g., Apache Cassandra), and graph databases (e.g., Neo4j).

Advantages:

• Schema Flexibility: NoSQL databases offer schema flexibility, allowing for agile development and accommodating evolving data models.
• Scalability: Non-relational databases excel at horizontal scalability, making them suitable for distributed systems and handling massive volumes of data.
• Performance: NoSQL databases are optimized for specific use cases, providing superior performance for certain workloads.
• Flexible Data Models: Document-oriented databases, in particular, store data in JSON-like documents, enabling nested structures and easier representation of complex data.

Disadvantages:

• Lack of ACID Compliance: Many NoSQL databases sacrifice strict ACID compliance for performance and scalability.
• Limited Querying Capabilities: NoSQL databases may lack the robust querying capabilities offered by SQL in relational databases.
• Data Consistency Challenges: Maintaining consistency in distributed environments can be challenging, leading to eventual consistency models.

Use Cases:

NoSQL databases are suitable for applications requiring flexibility, scalability, and performance, such as:

• Big Data and Analytics: Storing and analyzing large volumes of unstructured or semi-structured data.
• Real-time Analytics: IoT (Internet of Things) platforms and streaming data processing.
• Content Management Systems: Managing diverse content types and user-generated content.
• Social Networks: Storing social graphs and user interactions.

Conclusion:

Choosing between relational and non-relational databases depends on the specific requirements of the application. Relational databases excel in structured data environments with complex relationships and transactions, while non-relational databases offer flexibility, scalability, and performance for diverse data types and distributed systems. Ultimately, the decision should consider factors such as data structure, consistency requirements, scalability needs, and performance expectations to determine the most suitable database solution.

How to insert multiple records using an editable grid in Appian

Hi, Appian Developers! I hope you all are doing well and safe. Today we are going to learn to insert multiple records for customer in a database table named tbl_customer using an editable grid in Appian. I would clear in the starting, you must be certified with Appian Associate Developer, so it will be easy for you all. First I will tell you the whole requirements and what we are going to implement here.

Requirement:

Let’s consider, that we have a tbl_customer in the Appian cloud database. And this table has the following fields as

·      customerID
·      customerName
·      emailID
·      phoneNumber
·      profilePicture
·      description
·      createdBy
·      createdDate
·      isActive

You can see the below screenshot for how does your table looks like.

Now, we want to insert multiple customer details in one go instead of adding them one by one. It means we will add the multiple customer data from an interface using an editable grid in Appian.

e.g., Customer1, Customer2, Customer3, and Customer4 will have their own CustomerID, CustomerName, emailID, and so on. By implementing this, we are not required to add the n number of customer details one by one. So, without wasting our time, let’s switch to Appian Designer Console. As I am using the free community edition of Appian, I have my own Appian Designer console. You can also request your instance by clicking on this link https://community.appian.com/

 

I am creating a new application named “Practice for Appian” as below screenshot.

As I have checked the box in yellow highlighted in the above screenshot.

It will create some default folders and groups for me. This feature is available in only Appian version 21.4 release or later. Now click on the save button and you will see your application is created with default objects with folders and groups as below.

First, we will create our Data Store for our application which helps to establish a connection between our database and application. To create the Data Store, please click on the new button and select Data Store as below screenshot.

You must enter your datastore name and description as below.

Now click on Create button and choose your security for datastore object and click on the Save button as below.

Note: Once your CDT is created, do not forget to verify and publish your Data Store.

As we have already created our database table, so now we will create our CDT. To create a CDT, please click on the New button and select Data Type as below screenshot.

We will create our CDT using "Create from a database table or view" and select your Data Source name and table as below.

Now you must enter your CDT name and select your Data Store which we have created as above. Please see the below screenshot for your reference.

Once your CDT is created, now we will create a Constant for our entity as below.

Both Datastore and CDT have been created, now we will create our interface using an editable grid. To create an Interface, please click on the New button and select Interface as below.

Please enter your Interface name below.

Now click on Create button, and you will see the below window.

Now switch to expression mode and paste the below code into your Interface.

	a!formLayout(
	label: "Add Multiple Cutomer Data",
	contents: {
	a!gridLayout(
	totalCount: count(ri!items),
	headerCells: {
	a!gridLayoutHeaderCell(label: "Customer Name"),
	a!gridLayoutHeaderCell(label: "Email ID"),
	a!gridLayoutHeaderCell(label: "Phone No."),
	a!gridLayoutHeaderCell(label: "Profile Picture"),
	a!gridLayoutHeaderCell(label: "Description"),
	/* For the "Remove" column */
	a!gridLayoutHeaderCell(label: "")
	},
	/* Only needed when some columns need to be narrow */
	columnConfigs: {
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 2),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "ICON")
	},
	rows: a!forEach(
	items: ri!items,
	expression: a!gridRowLayout(
	id: fv!index,
	contents: {
	a!textField(
	label: "customer name " & fv!index,
	value: fv!item.customername,
	saveInto: fv!item.customername,
	required: true
	),
	a!textField(
	label: "email " & fv!index,
	value: fv!item.emailid,
	saveInto: fv!item.emailid,
	characterLimit: 50,
	required: true,
	requiredMessage: "Please enter purchaser email id!",
	validations: if(
	or(
	isnull(trim(fv!item.emailid)),
	len(trim(fv!item.emailid)) > 255,
	length(split(trim(fv!item.emailid), " ")) > 1,
	count(split(fv!item.emailid, "@")) <> 2
	),
	"Please enter valid email id",
	with(
	local!localPart: split(trim(fv!item.emailid), "@")[1],
	local!domainPart: split(trim(fv!item.emailid), "@")[2],
	if(
	or(
	length(split(local!domainPart, ".")) < 2,
	contains(split(local!localPart, "."), ""),
	contains(split(local!domainPart, "."), ""),
	not(
	isnull(
	stripwith(
	lower(local!domainPart),
	"abcdefghijklmnopqrstuvwxyz1234567890-."
	)
	)
	),
	not(
	isnull(
	stripwith(
	lower(local!localPart),
	"abcdefghijklmnopqrstuvwxyz1234567890-._+'&%"
	)
	)
	)
	),
	"Please enter valid email id",
	""
	)
	)
	)
	),
	a!textField(
	label: "phone number " & fv!index,
	value: fv!item.phonenumber,
	saveInto: fv!item.phonenumber,
	refreshAfter: "UNFOCUS",
	characterLimit: 10,
	required: true,
	requiredMessage: "Please enter purchaser mobile number!",
	validations: {
	if(len(fv!item.phonenumber) = 10, {}, ""),
	if(
	like(
	fv!item.phonenumber,
	"[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
	),
	"",
	"Please enter 10 digits mobile numbers only"
	)
	}
	),
	a!fileUploadField(
	target: cons!PFA_PROFILE_PICTURE_FOLDER,
	maxSelections: 1,
	value: fv!item.profilepicture,
	saveInto: fv!item.profilepicture,
	required: true,
	requiredMessage: "profile picture is required",
	validations: {
	a!localVariables(
	local!invalidExtensions: difference(
	upper(fv!files.extension),
	{ "PNG", "JPG" }
	),
	if(
	length(local!invalidExtensions) > 0,
	"Attachments must be images. Remove: " & index(
	fv!files,
	"name",
	wherecontains(
	local!invalidExtensions,
	upper(fv!files.extension)
	),
	{}
	),
	""
	)
	),
	if(
	or(fv!files.size > 300000),
	"Attachments may not exceed 300KB. Remove: " & index(
	fv!files,
	"name",
	where(fv!files.size > 300000),
	{}
	),
	""
	)
	},
	buttonStyle: "SECONDARY",
	buttonSize: "STANDARD"
	),
	a!textField(
	value: fv!item.description,
	saveInto: fv!item.description,
	characterLimit: 100,
	required: true,
	validationGroup: "main"
	),
	/* For the Removal Column*/
	a!richTextDisplayField(
	value: a!richTextIcon(
	icon: "close",
	altText: "delete " & fv!index,
	caption: "Remove " & fv!item.customername,
	link: a!dynamicLink(
	value: fv!index,
	saveInto: {
	a!save(ri!items, remove(ri!items, save!value))
	}
	),
	linkStyle: "STANDALONE",
	color: "NEGATIVE"
	)
	)
	}
	)
	),
	addRowlink: a!dynamicLink(
	label: "Add Customer",
	value: 'type!{urn:com:appian:types:}PFA_Customer'(
	createdby: "Admin",
	createddate: now(),
	isactive: "Active"
	),
	saveInto: {
	a!save(ri!items, append(ri!items, save!value))
	}
	),
	rowHeader: 1
	)
	},
	buttons: a!buttonLayout(
	primaryButtons: a!buttonWidget(
	label: "Submit",
	saveInto: a!writeToDataStoreEntity(
	dataStoreEntity: cons!PFA_DSE_CUSTOMER,
	valueToStore: ri!items
	),
	submit: true
	)
	)
	)

view raw AddMultipleCustomerData hosted with ❤ by GitHub

Once you pasted the above code, you will see what your interface looks like as below screenshot.

Wow, Congratulations!!! Our interface is ready to insert the multiple customer data into our database table tbl_Customer.

Let's add a few customer details to the form and click on Submit button as below.

Congratulation...!!! 💓💓💓

Successfully, we have inserted our 5 customer details into the database table. As you can see from the below screenshot for our table record.

Note:

I have created two constants, one is to save the customer profile picture, the constant type is a folder, and the second is for CDT, the constant type is the Data Store Entity. And Please add one rule input as items to your interface.

I have used a!writeToDataStoreEntity function on Submit button in the interface to save the customer data into the database table.

Please see my below objects for the application.

I hope, this blog will help you to understand the use of an editable grid within the interface.

Thank you so much for your time.

Please comment if you have any issues regarding the same.

Happy Coding! ✌💓

Razor Page CRUD in ASP.NET Core 3.1 with jQuery AJAX | Visual Studio 2019 for Mac | CRUD on Single Page | MySQL

In this tutorial, we will learn a clean and simple way to implement Razor Page CRUD in Asp.Net Core with jQuery Ajax and Bootstrap Modal. This is the Entity Management Set of CRUD operations which doesn't reload the  Pages, we can call it Single Page Application.

Table of Contents:

1. Scope
2. The Architecture
3. Getting Started with Razor Page CRUD in ASP.NET Core
• Setting up the Project
• Setting up the Core Layer
• Setting up the Infrastructure Layer
• Setting up the Asp.Net Core Project
• Rendering Razor Partial View to String

Scope 

There are the things we will be implementing in our CRUD Application. This will be an Employee Management System.

• Data Annotations for Validations
• jQuery Validations and Unobstructive Validations - Client Side Validations
• Bootstrap Modal
• Dyanamic Loading of Partial Views via Ajax Calls
• Repository Pattern with Unit of. Work
• Onion Architecture Solutions
• jQuery Datatables

The Architecture

To keep things simple and clean we will be using onion architecture with inverted dependencies.

We will create three layers in this project repectively Web, Core and Infrastructure.

Getting Started with Razor Page CRUD in Asp.Net Core 3.1

Let's create the required projects, here I am creating the Web project first using Visual Studio 2019

for Mac.

Setting up the Projects

Setting up the Core Layer

Let's add a new project withing the same solution and name it Core. This will be created as .Net Core Library 3.1

Let's add two folders Entities and Interfaces in the Core Project. And under the Entities folder, create a new class and name it Employee

	public class Employee
	{
	[Key]
	public int Id { get; set; }
	[Required]
	[MinLength(2)]
	public string FirstName { get; set; }
	public string LastName { get; set; }
	[Required]
	[EmailAddress]
	public string Email { get; set; }
	[Required]
	public int Mobile { get; set; }
	public string Company { get; set; }
	}

view raw Employee.cs hosted with ❤ by GitHub

To follow the Repository pattern, we will add a new interface and name it IGenericRepositoryAsync under the Interfaces folder in Core Proejct Layer.

	public interface IGenericRepositoryAsync<T> where T : class
	{
	Task<T> GetByIdAsync(int id);
	Task<IReadOnlyList<T>> GetAllAsync();
	Task<T> AddAsync(T entity);
	Task UpdateAsync(T entity);
	Task DeleteAsync(T entity);
	}

view raw IGenericRepositoryAsync.cs hosted with ❤ by GitHub

So we have a generic repository. now to use this generic interface for a specific entity like an employee along with extra methods, we will add a new interface and name it IEmployeeRepositoryAsync

	public interface IEmployeeRepositoryAsync : IGenericRepositoryAsync<Employee>
	{
	}

view raw IEmployeeRepositoryAsync.cs hosted with ❤ by GitHub

Let's add the unit of work

	public interface IUnitOfWork : IDisposable
	{
	Task<int> Commit();
	}

view raw IUnitOfWork.cs hosted with ❤ by GitHub

We have completed everything for the Core Project Layer, now let's move to create Infrastructure Project Layer as we did for the Core Project layer.

Setting up the Infrastructure Project Layer

Let's install the below-required packages for Infrastructure Layer

	Install-Package Microsoft.EntityFrameworkCore
	Install-Package Microsoft.EntityFrameworkCore.Tools
	Install-Package MySql.Data
	Install-Package MySql.Data.EntityFrameworkCore
	Install-Package Microsoft.EntityFrameworkCore.Design
	Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design

view raw Packages hosted with ❤ by GitHub

Now that you have Entity Framework Core installed at the Infrastructure Layer, let’s add the ApplicationDbContext in-order to access the database. (We will be setting the connection string to the database later in this article at the Web Layer.)

Create a new Folder in the Infrastructure Layer and name it Data. Here add a new Class and name it ApplicationDbContext.cs

	public class ApplicationDbContext : DbContext
	{
	public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
	{
	}
	public DbSet<Employee> Employees { get; set; }
	}

view raw ApplicationDbContext.cs hosted with ❤ by GitHub

Add a new folder in the Infrastructure layer and name it Repositories. Here add a new class, GenericRepositoryAsync.cs

	public class GenericRepositoryAsync<T> : IGenericRepositoryAsync<T> where T : class
	{
	private readonly ApplicationDbContext _dbContext;
	public GenericRepositoryAsync(ApplicationDbContext dbContext)
	{
	_dbContext = dbContext;
	}
	public virtual async Task<T> GetByIdAsync(int id)
	{
	return await _dbContext.Set<T>().FindAsync(id);
	}
	public async Task<T> AddAsync(T entity)
	{
	await _dbContext.Set<T>().AddAsync(entity);
	return entity;
	}
	public Task UpdateAsync(T entity)
	{
	_dbContext.Entry(entity).State = EntityState.Modified;
	return Task.CompletedTask;
	}
	public Task DeleteAsync(T entity)
	{
	_dbContext.Set<T>().Remove(entity);
	return Task.CompletedTask;
	}
	public async Task<IReadOnlyList<T>> GetAllAsync()
	{
	return await _dbContext.Set<T>().ToListAsync();
	}
	}

view raw GenericRepositoryAsync.cs hosted with ❤ by GitHub

As you can see from the above, almost all the CRUD operations ever needed for any entity is covered. T could be any class and you already have an entire class that could Perform Create, Read, Update and Delete Operations on the T Entity. But also see that we are not saving anything directly to the database via the Repository Implementation. Rather we will have a separate class that is responsible for Committing changes to the database. You should have heard about Unit Of Work, yeah?

Create another new class and name it UnitOfWork.cs in the Infrastructure layer.

	public class UnitOfWork : IUnitOfWork
	{
	private readonly ApplicationDbContext _dbContext;
	private bool disposed;
	public UnitOfWork(ApplicationDbContext dbContext)
	{
	_dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
	}
	public async Task<int> Commit()
	{
	return await _dbContext.SaveChangesAsync();
	}
	public void Dispose()
	{
	Dispose(true);
	GC.SuppressFinalize(this);
	}
	protected virtual void Dispose(bool disposing)
	{
	if (!disposed)
	{
	if (disposing)
	{
	_dbContext.Dispose();
	}
	}
	disposed = true;
	}
	}

view raw UnitOfWork.cs hosted with ❤ by GitHub

Let's add the last implementation which is responsible for performing the CRUD operations specific to Employee Entity.

	public class EmployeeRepositoryAsync : GenericRepositoryAsync<Employee>, IEmployeeRepositoryAsync
	{
	private readonly DbSet<Employee> _employee;
	public EmployeeRepositoryAsync(ApplicationDbContext dbContext) : base(dbContext)
	{
	_employee = dbContext.Set<Employee>();
	}
	}

view raw EmployeeRepositoryAsync.cs hosted with ❤ by GitHub

Setting up the Asp.Net Core Project

Let's install the below-required packages for the Web project layer.

	Install-Package Microsoft.EntityFrameworkCore
	Install-Package Microsoft.EntityFrameworkCore.Tools
	Install-Package MySql.Data
	Install-Package MySql.Data.EntityFrameworkCore
	Install-Package Microsoft.EntityFrameworkCore.Design
	Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design

view raw Packages hosted with ❤ by GitHub

With that done, open up the Startup.cs. Here we will have to add the services/dependencies to our ASP.NET Core Container. Navigate to the ConfigureServices method and add the following. Note that you would have to fix the reference warnings that may occur.

	public void ConfigureServices(IServiceCollection services)
	{
	services.AddDbContext<ApplicationDbContext>(options =>
	options.UseMySQL(
	Configuration.GetConnectionString("DefaultConnection"), b => b.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));
	#region Repositories
	services.AddTransient(typeof(IGenericRepositoryAsync<>), typeof(GenericRepositoryAsync<>));
	services.AddTransient<IEmployeeRepositoryAsync, EmployeeRepositoryAsync>();
	services.AddTransient<IUnitOfWork, UnitOfWork>();
	#endregion
	services.AddRazorPages();
	}

view raw Startup.cs hosted with ❤ by GitHub

We should add the connectionString in our appsettings.json file... So let's move to add ConnectionStrings

	{
	"Logging": {
	"LogLevel": {
	"Default": "Information",
	"Microsoft": "Warning",
	"Microsoft.Hosting.Lifetime": "Information"
	}
	},
	"AllowedHosts": "*",
	"ConnectionStrings": {
	"DefaultConnection": "Server=localhost;Database=RazorCRUD;Uid=root;Pwd=root@123;"
	}
	}

view raw appsettings.json hosted with ❤ by GitHub

Now we need to add the migrations. if you want to know how to add Migrations so click here to Add Migrations

Let's install the jQuery Datatables into our Web project and add the reference for DataTables in _Layout.cshtml file as below.

	<head>
	<meta charset="utf-8" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0" />
	<title>@ViewData["Title"] - Web</title>
	<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" />
	<link rel="stylesheet" href="~/css/site.css" />
	<link href="~/lib/datatables/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
	</head>
	//Just add the below scripts right above the @RenderSection("Scripts", required: false)
	<script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>
	<script src="~/lib/datatables/js/dataTables.bootstrap4.min.js"></script>
	<script src="~/lib/jquery-validation/dist/jquery.validate.js"></script>
	<script src="~/lib/jquery-validation-unobtrusive/jquery.validate.unobtrusive.js"></script>

view raw _Layout.cshtml hosted with ❤ by GitHub

Since we are already here, let’s also add the skeleton of the Bootstrap Modal that we are going to use further in the article. Above the footer tag, add the HTML for the Modal. Note that the Modal Body is empty. This is because we will be filling it dynamically via jQuery AJAX with Razor Partial Views.

	//Add the below code just above the <footer> tag
	<div class="modal fade" tabindex="-1" role="dialog" data-backdrop="static" data-keyboard="false" id="form-modal">
	<div class="modal-dialog modal-lg" role="document">
	<div class="modal-content">
	<div class="modal-header">
	<h5 class="modal-title"></h5>
	<button type="button" class="close" data-dismiss="modal" aria-label="Close">
	<span aria-hidden="true">×</span>
	</button>
	</div>
	<div class="modal-body">
	</div>
	</div>
	</div>
	</div>

view raw _Layout.cshtml hosted with ❤ by GitHub

There is already a site.js file under the wwwroot/js folder. So let's add the below code. Here we'll add the 3 functions which will be required for CRUD operations and it's the important part of our project which allows us not to reload the pages.

GetAll() - Loads all the Employees from the database to the jQuery DataTables.

CreateOrEdit() - This functions do the Create or Edit an employee.

Delete() - It deletes an employee from the database.

	$(document).ready(function () {
	jQueryModalGet = (url, title) => {
	try {
	$.ajax({
	type: 'GET',
	url: url,
	contentType: false,
	processData: false,
	success: function (res) {
	$('#form-modal .modal-body').html(res.html);
	$('#form-modal .modal-title').html(title);
	$('#form-modal').modal('show');
	},
	error: function (err) {
	console.log(err)
	}
	})
	//to prevent default form submit event
	return false;
	} catch (ex) {
	console.log(ex)
	}
	}
	jQueryModalPost = form => {
	try {
	$.ajax({
	type: 'POST',
	url: form.action,
	data: new FormData(form),
	contentType: false,
	processData: false,
	success: function (res) {
	if (res.isValid) {
	$('#viewAll').html(res.html)
	$('#form-modal').modal('hide');
	}
	},
	error: function (err) {
	console.log(err)
	}
	})
	return false;
	} catch (ex) {
	console.log(ex)
	}
	}
	jQueryModalDelete = form => {
	if (confirm('Are you sure to delete this record ?')) {
	try {
	$.ajax({
	type: 'POST',
	url: form.action,
	data: new FormData(form),
	contentType: false,
	processData: false,
	success: function (res) {
	$('#viewAll').html(res.html);
	},
	error: function (err) {
	console.log(err)
	}
	})
	} catch (ex) {
	console.log(ex)
	}
	}
	//prevent default form submit event
	return false;
	}
	});

view raw site.js hosted with ❤ by GitHub

Now the missing pieces to the puzzle are as follows:

1. Razor Partial View to Hold the jQuery Datatable.

2. Razor Partial View to Add / Edit Customer.

3. A way to convert Razor Views to String, so that jQuery AJAX can fetch this string and simply overwrite the existing div / DOM object on the HTML. (This was particularly tough to figure out.)

4. And finally, our C# Handler methods that will return the partial views as requested by the AJAX calls.

Let’s start with the _ViewAll.cshtml. This will hold the HTML table definition and also the script needed to activate jQuery Datatable. Under the Pages folder in the Web Project, Create a new Razor View (Empty) and name it _ViewAll.cshtml.

	@using Core.Entities
	@model IEnumerable<Employee>
	<table class="table table-bordered" id="employeTable">
	<thead>
	<tr>
	<th>FirstName</th>
	<th>LastName</th>
	<th>Email</th>
	<th>PhoneNumber</th>
	<th>Company</th>
	<th>Actions</th>
	</tr>
	</thead>
	<tbody>
	@if (Model.Count() != 0)
	{
	@foreach (var employee in Model)
	{
	<tr>
	<td>@employee.FirstName</td>
	<td>@employee.LastName</td>
	<td>@employee.Email</td>
	<td>@employee.Mobile</td>
	<td>@employee.Company</td>
	<td text-right">
	<a onclick="jQueryModalGet('?handler=CreateOrEdit&id=@employee.Id','Edit Customer')" class="btn btn-info text-white"> Edit</a>
	<form method="post" asp-page="Index" asp-route-id="@employee.Id" asp-page-handler="Delete" onsubmit="return jQueryModalDelete(this)" class="d-inline">
	<button type="submit" class="btn btn-danger text-white"> Delete</button>
	</form>
	</td>
	</tr>
	}
	}
	</tbody>
	</table>
	<script>$(document).ready(function () {
	$("#employeTable").DataTable();
	});</script>

view raw _ViewAll.cshtml hosted with ❤ by GitHub

Next, Let’s create the Form, CreateOEdit that will have all the fields we require. This is again a straight forward piece of code snippet. Create a new Razor Page View (Empty) and name it _CreateOrEdit.cshtml and add in the following.

	@using Core.Entities
	@model Employee
	<form id="create-form" method="post" asp-page="Index" asp-route-id="@Model.Id" asp-page-handler="CreateOrEdit" onsubmit="return jQueryModalPost(this);">
	<div class="form-group row">
	<label class="col-md-3 col-form-label">First Name</label>
	<div class="col-md-9">
	<input type="text" autocomplete="off" asp-for="FirstName" name="FirstName" class="form-control">
	<span asp-validation-for="FirstName" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-md-3 col-form-label">Last Name</label>
	<div class="col-md-9">
	<input type="text" autocomplete="off" asp-for="LastName" name="LastName" class="form-control">
	<span asp-validation-for="LastName" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-md-3 col-form-label">Email</label>
	<div class="col-md-9">
	<input type="email" autocomplete="off" asp-for="Email" name="Email" class="form-control">
	<span asp-validation-for="Email" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-md-3 col-form-label">Mobile</label>
	<div class="col-md-9">
	<input type="number" autocomplete="off" asp-for="Mobile" name="Mobile" class="form-control" />
	<span asp-validation-for="Mobile" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group row">
	<label class="col-md-3 col-form-label">Company</label>
	<div class="col-md-9">
	<input type="text" autocomplete="off" asp-for="Company" name="Company" class="form-control" />
	<span asp-validation-for="Company" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group row">
	<div class="col-md-3">
	</div>
	</div>
	<div class="form-group justify-content-between">
	<button type="button" class="btn btn-secondary close-button" data-dismiss="modal">Cancel</button>
	<button type="submit" class="btn btn-primary save-button">Save</button>
	</div>
	</form>
	<script type="text/javascript" language=javascript>$.validator.unobtrusive.parse(document);</script>

view raw CreateOrEdit.cshtml hosted with ❤ by GitHub

Now we required the partial views, so let's add the following code in our existing Index.cshtml file

	@page
	@model IndexModel
	@{
	ViewData["Title"] = "Home page";
	}
	<div class="text-center">
	<h1 class="display-4">Razor CRUD</h1>
	<p>Learn about <a href="https://code-town.blogspot.com" target="_blank">building Web apps with ASP.NET Core</a>.</p>
	</div>
	<div class="card">
	<div class="col-sm-12" style="padding:20px">
	<a onclick="jQueryModalGet('?handler=CreateOrEdit','Create Employee')" class="btn bg-success">
	Create
	</a>
	<a id="reload" class="btn bg-warning">
	Reload
	</a>
	</div>
	<div id="viewAll" class="card-body table-responsive"></div>
	</div>
	@section Scripts
	{
	<script>$(document).ready(function () {
	$('#viewAll').load('?handler=ViewAllPartial');
	});
	$(function () {
	$('#reload').on('click', function () {
	$('#viewAll').load('?handler=ViewAllPartial');
	});
	});</script>
	}

view raw Index.cshtml hosted with ❤ by GitHub

Rendering Razor Partial View to String

Now, the question is, We have Razor Partial Views and AJAX, how do you convert Razor Partial Views to Strings / HTML. The answer is to create a service that can do so. In the Web Project add a new Folder and name it Services. And in it, create a new class and name it RazorRenderService.cs

	public interface IRazorRenderService
	{
	Task<string> ToStringAsync<T>(string viewName, T model);
	}
	public class RazorRenderService : IRazorRenderService
	{
	private readonly IRazorViewEngine _razorViewEngine;
	private readonly ITempDataProvider _tempDataProvider;
	private readonly IServiceProvider _serviceProvider;
	private readonly IHttpContextAccessor _httpContext;
	private readonly IActionContextAccessor _actionContext;
	private readonly IRazorPageActivator _activator;
	public RazorRenderService(IRazorViewEngine razorViewEngine,
	ITempDataProvider tempDataProvider,
	IServiceProvider serviceProvider,
	IHttpContextAccessor httpContext,
	IRazorPageActivator activator,
	IActionContextAccessor actionContext)
	{
	_razorViewEngine = razorViewEngine;
	_tempDataProvider = tempDataProvider;
	_serviceProvider = serviceProvider;
	_httpContext = httpContext;
	_actionContext = actionContext;
	_activator = activator;
	}
	public async Task<string> ToStringAsync<T>(string pageName, T model)
	{
	var actionContext =
	new ActionContext(
	_httpContext.HttpContext,
	_httpContext.HttpContext.GetRouteData(),
	_actionContext.ActionContext.ActionDescriptor
	);
	using (var sw = new StringWriter())
	{
	var result = _razorViewEngine.FindPage(actionContext, pageName);
	if (result.Page == null)
	{
	throw new ArgumentNullException($"The page {pageName} cannot be found.");
	}
	var view = new RazorView(_razorViewEngine,
	_activator,
	new List<IRazorPage>(),
	result.Page,
	HtmlEncoder.Default,
	new DiagnosticListener("RazorRenderService"));
	var viewContext = new ViewContext(
	actionContext,
	view,
	new ViewDataDictionary<T>(new EmptyModelMetadataProvider(), new ModelStateDictionary())
	{
	Model = model
	},
	new TempDataDictionary(
	_httpContext.HttpContext,
	_tempDataProvider
	),
	sw,
	new HtmlHelperOptions()
	);
	var page = (result.Page);
	page.ViewContext = viewContext;
	_activator.Activate(page, viewContext);
	await page.ExecuteAsync();
	return sw.ToString();
	}
	}
	private IRazorPage FindPage(ActionContext actionContext, string pageName)
	{
	var getPageResult = _razorViewEngine.GetPage(executingFilePath: null, pagePath: pageName);
	if (getPageResult.Page != null)
	{
	return getPageResult.Page;
	}
	var findPageResult = _razorViewEngine.FindPage(actionContext, pageName);
	if (findPageResult.Page != null)
	{
	return findPageResult.Page;
	}
	var searchedLocations = getPageResult.SearchedLocations.Concat(findPageResult.SearchedLocations);
	var errorMessage = string.Join(
	Environment.NewLine,
	new[] { $"Unable to find page '{pageName}'. The following locations were searched:" }.Concat(searchedLocations));
	throw new InvalidOperationException(errorMessage);
	}
	}

view raw RazorRenderService.cs hosted with ❤ by GitHub

Let's add the below services into the Startup.cs as in ConfigureServices method.

	services.AddHttpContextAccessor();
	services.AddTransient<IActionContextAccessor, ActionContextAccessor>();
	services.AddScoped<IRazorRenderService, RazorRenderService>();

view raw startup.cs hosted with ❤ by GitHub

This is the final part of the puzzle. The actual Index.cs. Open it up and add the following.

	public class IndexModel : PageModel
	{
	private readonly IEmployeeRepositoryAsync _employee;
	private readonly IUnitOfWork _unitOfWork;
	private readonly IRazorRenderService _renderService;
	private readonly ILogger<IndexModel> _logger;
	public IndexModel(ILogger<IndexModel> logger, IEmployeeRepositoryAsync employee, IUnitOfWork unitOfWork, IRazorRenderService renderService)
	{
	_logger = logger;
	_employee = employee;
	_unitOfWork = unitOfWork;
	_renderService = renderService;
	}
	public IEnumerable<Employee> Employees { get; set; }
	public void OnGet()
	{
	}
	public async Task<PartialViewResult> OnGetViewAllPartial()
	{
	Employees = await _employee.GetAllAsync();
	return new PartialViewResult
	{
	ViewName = "_ViewAll",
	ViewData = new ViewDataDictionary<IEnumerable<Employee>>(ViewData, Employees)
	};
	}
	public async Task<JsonResult> OnGetCreateOrEditAsync(int id = 0)
	{
	if (id == 0)
	return new JsonResult(new { isValid = true, html = await _renderService.ToStringAsync("_CreateOrEdit", new Employee()) });
	else
	{
	var thisEmployee = await _employee.GetByIdAsync(id);
	return new JsonResult(new { isValid = true, html = await _renderService.ToStringAsync("_CreateOrEdit", thisEmployee) });
	}
	}
	public async Task<JsonResult> OnPostCreateOrEditAsync(int id, Employee employee)
	{
	if (ModelState.IsValid)
	{
	if (id == 0)
	{
	await _employee.AddAsync(employee);
	await _unitOfWork.Commit();
	}
	else
	{
	await _employee.UpdateAsync(employee);
	await _unitOfWork.Commit();
	}
	Employees = await _employee.GetAllAsync();
	var html = await _renderService.ToStringAsync("_ViewAll", Employees);
	return new JsonResult(new { isValid = true, html = html });
	}
	else
	{
	var html = await _renderService.ToStringAsync("_CreateOrEdit", employee);
	return new JsonResult(new { isValid = false, html = html });
	}
	}
	public async Task<JsonResult> OnPostDeleteAsync(int id)
	{
	var employee = await _employee.GetByIdAsync(id);
	await _employee.DeleteAsync(employee);
	await _unitOfWork.Commit();
	Employees = await _employee.GetAllAsync();
	var html = await _renderService.ToStringAsync("_ViewAll", Employees);
	return new JsonResult(new { isValid = true, html = html });
	}
	}

view raw Index.cshtml.cs hosted with ❤ by GitHub

Now you can build your all projects and run the application... :)

 

Please comment if you face any issue in code.

You can download the Source Code from GitHub

Read my previous blog on ASP.NET Core 3.1 MVC CRUD Operations

Below is a small video of my running application...

ASP.NET Core 3.1 MVC | EntityFrameworkCore | Visual Studio 2019 for Mac | CRUD Operations | MySQL

Today we will create an asp.net core MVC application using EntityFramework and MySQL Database with Visual Studio 2019 for Mac.

This is a simple CRUD operation for an Employe using ASP.NET Core 3.1 and EntityFrameworkCore

Prerequisites:

1. Visual Studio 2019 IDE for Mac
2. Make sure you are running the latest .Net core 3.1
3. Install the latest DOTNET and EF CLI using this command: dotnet tool install --global dotnet ef
4. MySQL database should be installed on your Mac

Getting Started:

         Open the Visual Studio 2019 for Mac and Create a New Project as below

    

     Please select the .Net Core 3.1  as below

        I created a new project with the Name, "DotNetCore" and below is the Solution structure which

        is created by VS2019.

        Let's install the few mandatory nuget packages as below

                        1. Microsoft.EntityFrameworkCore

                        2. Microsoft.EntityFrameworkCore.Design

                        3. Microsoft.EntityFrameworkCore.Tools

                        4. MySql.Data

                        5. MySql.Data.EntityFrameworkCore

        Now let's add a New Class name "DotNetCoreDbContext" under the Models folder by right-clicking

        and select Add New Class.

        Now let's write the code for this class.

        We have to inherit our class from the DbContext class by using Microsoft.EntityFrameworkCore

        

	using System;
	using Microsoft.EntityFrameworkCore;
	namespace DotNetCore.Models
	{
	public class DotNetCoreDbContext : DbContext
	{
	public DotNetCoreDbContext(DbContextOptions<DotNetCoreDbContext> options): base(options)
	{
	}
	}
	}

view raw DotNetCoreDbContext.cs hosted with ❤ by GitHub

        Now let's add a New Class name "Employee" under the Models folder.

        

        

	using System;
	using System.ComponentModel;
	using System.ComponentModel.DataAnnotations;
	namespace DotNetCore.Models
	{
	public class Employee
	{
	[Key]
	public int EmployeeID { get; set; }
	[Required(ErrorMessage ="Please enter the full name!")]
	[DisplayName("Full Name")]
	public string FullName { get; set; }
	[Required(ErrorMessage = "Please enter the employee code!")]
	[DisplayName("Employee Code")]
	public string EmpCode { get; set; }
	[Required(ErrorMessage = "Please enter the position!")]
	[DisplayName("Position")]
	public string Position { get; set; }
	[Required(ErrorMessage = "Please enter the location!")]
	[DisplayName("Location")]
	public string Location { get; set; }
	}
	}

view raw Employee.cs hosted with ❤ by GitHub

        Now add our class into DotNetCoreDbContext as below

        

	using System;
	using Microsoft.EntityFrameworkCore;
	namespace DotNetCore.Models
	{
	public class DotNetCoreDbContext : DbContext
	{
	public DotNetCoreDbContext(DbContextOptions<DotNetCoreDbContext> options): base(options)
	{
	}
	public DbSet<Employee> Employees { get; set; }
	}
	}

view raw DotNetCoreDbContext.cs hosted with ❤ by GitHub

        Now we need to write code for ConnectionStrings in appsettings.json file

        and modify the Startup.cs file as below

        

	{
	"Logging": {
	"LogLevel": {
	"Default": "Information",
	"Microsoft": "Warning",
	"Microsoft.Hosting.Lifetime": "Information"
	}
	},
	"AllowedHosts": "*",
	"ConnectionStrings": {
	"DefaultConnection": "Server=localhost;Database=dotnetcore;Uid=root;Pwd=root@123;"
	}
	}

view raw appsettings.json hosted with ❤ by GitHub

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Threading.Tasks;
	using DotNetCore.Models;
	using Microsoft.AspNetCore.Builder;
	using Microsoft.AspNetCore.Hosting;
	using Microsoft.AspNetCore.HttpsPolicy;
	using Microsoft.Extensions.Configuration;
	using Microsoft.Extensions.DependencyInjection;
	using Microsoft.Extensions.Hosting;
	using Microsoft.EntityFrameworkCore;
	namespace DotNetCore
	{
	public class Startup
	{
	public Startup(IConfiguration configuration)
	{
	Configuration = configuration;
	}
	public IConfiguration Configuration { get; }
	// This method gets called by the runtime. Use this method to add services to the container.
	public void ConfigureServices(IServiceCollection services)
	{
	services.AddControllersWithViews();
	services.AddDbContext<DotNetCoreDbContext>(options => options.UseMySQL(Configuration.GetConnectionString("DefaultConnection")));
	}
	// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
	public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
	{
	if (env.IsDevelopment())
	{
	app.UseDeveloperExceptionPage();
	}
	else
	{
	app.UseExceptionHandler("/Home/Error");
	// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
	app.UseHsts();
	}
	app.UseHttpsRedirection();
	app.UseStaticFiles();
	app.UseRouting();
	app.UseAuthorization();
	app.UseEndpoints(endpoints =>
	{
	endpoints.MapControllerRoute(
	name: "default",
	pattern: "{controller=Employee}/{action=Index}/{id?}");
	});
	}
	}
	}

view raw Startup.cs hosted with ❤ by GitHub

        Next step to add the Migration to create the Database in MySql

        Run below command w.r.t your project folder path on Terminal

        

        To Add-Migration:

        dotnet ef migrations add InitialCreate

        To Update-Database

        dotnet ef database update

        To Remove-Migration:

        dotnet ef migrations remove

        After adding the Migration, we can see our database has been created into the MySql database

        We can see our created database dotnetcore including the Employees table.

           Now let's add the controller name "EmployeeController" using Scaffolding as below

        

            

        We will use the same action for Create and Edit an Employee.

        Let's write the code for AddOrEdit action into EmployeeController
        

	// GET: Employee/AddOrEdit
	public IActionResult AddOrEdit(int Id=0)
	{
	if(Id==0)
	return View(new Employee());
	else
	return View(_context.Employees.Find(Id));
	}

view raw EmployeeController.cs hosted with ❤ by GitHub

        Now let's add the view page for AddOrEdit action under the Views/Employee folder

        

	@model DotNetCore.Models.Employee
	@{
	ViewData["Title"] = "Create";
	}
	<h4>Employee Form</h4>
	<hr />
	<div class="row">
	<div class="col-md-4">
	<form asp-action="AddOrEdit">
	<div asp-validation-summary="ModelOnly" class="text-danger"></div>
	<input type="hidden" asp-for="EmployeeID" />
	<div class="form-group">
	<label asp-for="FullName" class="control-label"></label>
	<input asp-for="FullName" class="form-control" />
	<span asp-validation-for="FullName" class="text-danger"></span>
	</div>
	<div class="row">
	<div class="form-group col-md-6">
	<label asp-for="EmpCode" class="control-label"></label>
	<input asp-for="EmpCode" class="form-control" />
	<span asp-validation-for="EmpCode" class="text-danger"></span>
	</div>
	<div class="form-group col-md-6">
	<label asp-for="Position" class="control-label"></label>
	<input asp-for="Position" class="form-control" />
	<span asp-validation-for="Position" class="text-danger"></span>
	</div>
	</div>
	<div class="form-group">
	<label asp-for="Location" class="control-label"></label>
	<input asp-for="Location" class="form-control" />
	<span asp-validation-for="Location" class="text-danger"></span>
	</div>
	<div class="form-row">
	<div class="form-group col-md-6">
	<input type="submit" value="Submit" class="btn btn-primary btn-block" />
	</div>
	<div class="form-group col-md-6">
	<a asp-action="Index" class="btn btn-secondary btn-block"><i class="fa fa-table"></i> Back to List</a>
	</div>
	</div>
	</form>
	</div>
	</div>
	@section Scripts {
	@{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
	}

view raw AddOrEdit.cshtml hosted with ❤ by GitHub

        We have written the code to create and edit an employee, added the view page also.

        Now let's build our solution and run the application.

        Let's create 3-4 employees by entering the required fields as below

        Let's write some action methods to Index, Details, and Delete an employee as well

	// GET: Employee
	public async Task<IActionResult> Index()
	{
	return View(await _context.Employees.ToListAsync());
	}

view raw EmployeeController.cs hosted with ❤ by GitHub

	// GET: Employee/Details/5
	public async Task<IActionResult> Details(int? id)
	{
	if (id == null)
	{
	return NotFound();
	}
	var employee = await _context.Employees
	.FirstOrDefaultAsync(m => m.EmployeeID == id);
	if (employee == null)
	{
	return NotFound();
	}
	return View(employee);
	}

view raw EmployeeController.cs hosted with ❤ by GitHub

	// GET: Employee/Delete/5
	public async Task<IActionResult> Delete(int? id)
	{
	var employee = await _context.Employees.FindAsync(id);
	_context.Employees.Remove(employee);
	await _context.SaveChangesAsync();
	return RedirectToAction(nameof(Index));
	}

view raw EmployeeController.cs hosted with ❤ by GitHub

        Below are the screenshots for Index, Details, and Delete action methods

        https://localhost:5001/Employee/Index

        https://localhost:5001/Employee/Details/1

        Now let's delete the Harshit Kumar record... it will show an alert popup message as below

        Harshit Kumar record has been deleted from the database as we can see the below

    

        

           Below is the Employee table screenshot

        I hope it will help you :)

        

        Please comment if you face any issue regarding the same...

        You can download the source code from GitHub by click on the below link

         Click to download the Source Code

Below is the video of my running application: