CRUD Operations with SqlModelMapper
Last Updated: December 2023
This tutorial uses an example to walk through the steps of creating, in Visual Studio, an ASP.NET Core web application that uses the SqlModelMapper model from SnapObjects as its data manipulation component.
In this tutorial, you learn how to:
- Create a Class Library Project
- Add the SnapObjects Packages
- Add a Database Context
- Add a SqlModelMapper Model
- Add a Service
- Implement CRUD Operations
- Create a Web Application Project
- Register the Database Context
- Register the Service
- Add a Razor Page
- Perform CRUD Operations Using the Razor Page
Prerequisites
- Visual Studio 2019
- .NET Core SDK 3.1.405
- Sample database setup
- Download the database backup file from https://github.com/Appeon/.NET-Project-Example-Database. (This specific version of the DB is required for this tutorial)
- Install SQL Server Express or SQL Server if it is not installed.
- Restore database using the downloaded database backup file.
Create a Class Library Project
Start Visual Studio 2019 and select Create a new project from the Start page. Or, from the File menu, select New and then Project....
In the New Project dialog box, enter Class in the search box, and choose C# from the language list. After you apply the filter, select Class Library (.NET Core). Name the project "Appeon.SnapObjectsDemo.Service", name the solution "SnapObjects-Asp.NetCore-Example" and click Create.
Add the SnapObjects Packages
Right-click the Appeon.SnapObjectsDemo.Service project and select Manage NuGet packages....
On the popup window, search for "SnapObjects" in Browse menu, and install the packages SnapObjects.Data, SnapObjects.Data.AspNetCore, and SnapObjects.Data.SqlServer.
Add a Database Context
The database context is the class that manages database connections and transactions. This class is created by deriving from the SnapObjects.Data.DataContext class.
Right-click the "Appeon.SnapObjectsDemo.Service" project and select Add > New Folder. and name it DataContext, then right-click on the DataContext folder and click Add > Class....
In the item template list select Class and name the class AppeonDataContext.cs and click Add.
Add the following code to reference the packages.
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;
Add the following code to initialize the constructor.
namespace Appeon.SnapObjectsDemo.Service.DataContext
{
public class AppeonDataContext : SqlServerDataContext
{
public AppeonDataContext(string connectionString)
: this(new SqlServerDataContextOptions<AppeonDataContext>(connectionString))
{
}
public AppeonDataContext(IDataContextOptions<AppeonDataContext> options)
: base(options)
{
}
public AppeonDataContext(IDataContextOptions options)
: base(options)
{
}
}
}
Add a SqlModelMapper Model
SqlModelMapper handles complicated business logic in a more efficient way with significantly less coding. It can build models with data from multiple tables, support nested level of relationships, and provide implicit transaction management for business entities (i.e. a top-level object and all of its children).
Right-click on the "Appeon.SnapObjectsDemo.Service" project. Click Add > New Folder. Name it Models. Then right-click on the Models folder and click Add > Class....
On the popup window select Class and name it SalesOrder.cs and click Add.
A SqlModelMapper model class shall consist of model attributes and model properties. The model attributes applied on the class (such as Top, SqlParameter, Table, SqlWhere) specify the database tables the model class will be mapped to, as well as its mapping query conditions; the model properties map to columns from the database tables. The created model can be used by SqlModelMapper to do CRUD operations towards the database. Refer to API document for more details about the attributes used in the model.
The sample code below adds a SqlModelMapper model named "SalesOrder". It retrieves data from the SalesOrderHeader table, and contains columns such as Order ID, Revision Number, etc.
using SnapObjects.Data;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel;
namespace Appeon.SnapObjectsDemo.Service.Models
{
[Top(1000)]
[SqlParameter("custId", typeof(int))]
[SqlParameter("startOrderDate", typeof(DateTime))]
[SqlParameter("endOrderDate", typeof(DateTime))]
[Table("SalesOrderHeader", Schema = "Sales")]
[SqlWhere("(CustomerId = :custId Or :custId = 0) " +
" and (Orderdate Between :startOrderDate and :endOrderDate)")]
public class SalesOrder
{
[Key]
[Identity]
[DisplayName("Order ID")]
public int SalesOrderID { get; set; }
[Required]
[DisplayName("Revision Number")]
public byte RevisionNumber { get; set; }
[Required]
[DisplayName("Order Date")]
[DataType(DataType.Date)]
public DateTime? OrderDate { get; set; }
[Required]
[DisplayName("Due Date")]
[DataType(DataType.Date)]
public DateTime? DueDate { get; set; }
[DisplayName("Ship Date")]
[DataType(DataType.Date)]
public DateTime? ShipDate { get; set; }
[Required]
public byte? Status { get; set; }
[Required]
[DisplayName("Online Order Flag")]
public Boolean OnlineOrderFlag { get; set; }
[DisplayName("Order Number")]
[SqlCompute("(isnull(N'SO' + CONVERT([nvarchar](23), [SalesOrderID]), N'*** ERROR ***'))")]
public string SalesOrderNumber { get; set; }
[DisplayName("PO Number")]
public string PurchaseOrderNumber { get; set; }
[DisplayName("Account Number")]
public string AccountNumber { get; set; }
[Required]
[DisplayName("Customer")]
public int CustomerID { get; set; }
[DisplayName("Sales Person")]
public int? SalesPersonID { get; set; }
[DisplayName("Territory ID")]
public int? TerritoryID { get; set; }
[Required]
[DisplayName("Bill To Address")]
public int? BillToAddressID { get; set; }
[Required]
[DisplayName("Ship To Address")]
public int? ShipToAddressID { get; set; }
[Required]
[DisplayName("Ship Method")]
public int? ShipMethodID { get; set; }
[DisplayName("Credit Card ID")]
public int? CreditCardID { get; set; }
[DisplayName("Card Approval Code")]
public string CreditCardApprovalCode { get; set; }
[DisplayName("Currency Rate")]
public int? CurrencyRateID { get; set; }
[Required]
[DisplayName("Subtotal")]
[DataType(DataType.Currency)]
public decimal? SubTotal { get; set; }
[Required]
[DisplayName("Tax Amount")]
[DataType(DataType.Currency)]
public decimal? TaxAmt { get; set; }
[Required]
[DataType(DataType.Currency)]
public decimal? Freight { get; set; }
[DisplayName("Total Due")]
[DataType(DataType.Currency)]
[PropertySave(SaveStrategy.ReadAfterSave)]
public decimal? TotalDue { get; set; }
public string Comment { get; set; }
[DisplayName("Modified Date")]
[DataType(DataType.Date)]
[SqlDefaultValue("(getdate())")]
public DateTime? ModifiedDate { get; set; }
}
}
We can also add a Page class which packages the pagination information.
Right-click on the Models folder and click on Add > Class.... Name it Page.cs and click OK.
using System.Collections.Generic;
namespace Appeon.SnapObjectsDemo.Service.Models
{
public class Page<TModel>
{
/// <summary>
/// current page
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// total num
/// </summary>
public int TotalItems { get; set; }
/// <summary>
/// page size
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// result items
/// </summary>
public IList<TModel> Items { get; set; }
}
}
Add a Service
Define an Interface
First, add an interface. The interface contains only the declaration of the methods, properties and events, but not the implementation.
Right-click on the "Appeon.SnapObjectsDemo.Service" project. Then click Add > New Folder. Name it Services. Now add a sub-folder to it and name it Impl. Next, right-click on the Services folder and click on Add > New Item....
On the popup window select Interface and name it ISalesOrderService.cs and click Add.
Add the declaration of the methods we will implement on the service.
using Appeon.SnapObjectsDemo.Service.Models;
using System.Collections.Generic;
namespace Appeon.SnapObjectsDemo.Service.Services
{
public interface ISalesOrderService
{
IList<SalesOrder> Retrieve(bool includeEmbedded, params object[] parameters);
SalesOrder LoadByKey(bool includeEmbedded, params object[] parameters);
Page<SalesOrder> LoadByPage(int pageIndex, int pageSize, params object[] parameters);
int Create(SalesOrder salesOrder);
int Update(SalesOrder salesOrder);
int DeleteByKey(params object[] parameters);
}
}
Implement the Interface
To implement the interface, add a class that inherits from the interface and implements all the methods from it.
Right-click on the Service > Impl sub-folder and click on Add > Class.... Name it SalesOrderService.cs and click Add. (Notice the difference between the name of the interface "ISalesOrderService.cs" and the name of the service "SalesOrderService.cs")
Update SalesOrderService.cs to implement the ISalesOrderService interface, and add a constructor to receive the data context:
using System;
using Appeon.SnapObjectsDemo.Service.DataContext;
using Appeon.SnapObjectsDemo.Service.Models;
using SnapObjects.Data;
using System.Collections.Generic;
namespace Appeon.SnapObjectsDemo.Service.Services.Impl
{
public class SalesOrderService : ISalesOrderService
{
protected readonly AppeonDataContext _context;
public SalesOrderService(AppeonDataContext context)
{
_context = context;
}
public int Create(SalesOrder salesOrder)
{
throw new NotImplementedException();
}
public int Update(SalesOrder salesOrder)
{
throw new NotImplementedException();
}
public int DeleteByKey(params object[] parameters)
{
throw new NotImplementedException();
}
public IList<SalesOrder> Retrieve(bool includeEmbedded, params object[] parameters)
{
throw new NotImplementedException();
}
public SalesOrder LoadByKey(bool includeEmbedded, params object[] parameters)
{
throw new NotImplementedException();
}
public Page<SalesOrder> LoadByPage(int pageIndex, int pageSize, params object[] parameters)
{
throw new NotImplementedException();
}
}
}
Implement CRUD Operations
This section provides examples of CRUD operations with SqlModelMapper. Note that SqlModelMapper supports both explicit and implicit transactions. The implicit transaction in SqlModelMapper takes a SaveChanges() as the commit method, and inherently includes all the CUD operations on the SqlModelMapper model until the commit method is executed.
For more information on SqlModelMapper APIs, refer to SnapObjects .NET API Reference.
Retrieve using SqlModelMapper
To retrieve data using the SqlModelMapper we can use any one of the methods we declared in the Interface (Retrieve, LoadByPage, LoadByKey). We will show you how to call the LoadByPage and LoadByKey methods.
LoadByPage() method in the Service
Retrieve the data by page according to the SQL query defined in the SalesOrder model. Convert the result into a List and return it to the client. Modify the LoadByPage() method as follows:
public Page<SalesOrder> LoadByPage(int pageIndex, int pageSize, params object[] parameters)
{
int currentIndex = (pageIndex - 1) * pageSize;
IList<SalesOrder> items = null;
Page<SalesOrder> page = new Page<SalesOrder>();
page.PageSize = pageSize;
page.PageIndex = pageIndex;
items = _context.SqlModelMapper.LoadByPage<SalesOrder>(
currentIndex, pageSize, parameters).ToList();
int totalItems = _context.SqlModelMapper.Count<SalesOrder>(parameters);
page.TotalItems = totalItems;
page.Items = items;
return page;
}
LoadByKey() method in the Service
Retrieve the data according to the SQL query defined in the SalesOrder model while loading only the row that matches its key column. Convert the result into a SalesOrder model and return it to the page. Modify the LoadByKey() method as follows:
public SalesOrder LoadByKey(bool includeEmbedded, params object[] parameters)
{
SalesOrder model = _context.SqlModelMapper.LoadByKey<SalesOrder>(parameters)
.FirstOrDefault();
return model;
}
Create/Insert a new row using the SqlModelMapper
To insert data into the database using the SqlModelMapper we will use the Create() method declared in the Interface.
Create() method in the Service
Call an instance of SqlModelMapper, then use the TrackCreate method of the SqlModelMapper to track the table insert operation. The data will be inserted into the database table through the cached SalesOrder model. Finally use the SaveChanges method of the SqlModelMapper to save the changes to the database. Calling the SaveChanges method of SqlModelMapper causes a SQL INSERT statement to be generated using the cached data of the SalesOrder object and the mapping information defined in the SalesOrder class, and then executes the SQL INSERT. If no errors occurred, SqlModelMapper automatically commits the changes to the database or rolls back the operation if otherwise. In this method we are only adding one row of data. Modify the Create() method as follows:
public int Create(SalesOrder salesOrder)
{
return _context.SqlModelMapper.TrackCreate<SalesOrder>(salesOrder)
.SaveChanges()
.InsertedCount;
}
Update changes using the SqlModelMapper
To update data into the database using the SqlModelMapper we will use the Update() method declared in the Interface.
Update() method in the Service
Call an instance of SqlModelMapper, then use the TrackUpdate method of the SqlModelMapper to track the table update operation. The data will be updated into the database table through the cached SalesOrdermodel. Finally use the SaveChanges method of the SqlModelMapper to save the changes to the database. Calling the SaveChanges method of SqlModelMapper causes a SQL UPDATE statement to be generated using the cached data of the SalesOrder object and the mapping information defined in the SalesOrder class, and then executes the SQL UPDATE. If no errors occurred, SqlModelMapper automatically commits the changes to the database or rolls back the operation if otherwise. In this method we are only updating one row of data. Modify the Update() method of the service as follows:
public int Update(SalesOrder salesOrder)
{
var oldSalesOrder = this.LoadByKey(true, salesOrder.SalesOrderID);
return _context.SqlModelMapper.TrackUpdate(oldSalesOrder, salesOrder)
.SaveChanges()
.ModifiedCount;
}
Delete data using the SqlModelMapper
To delete data from database using the SqlModelMapper we will use the Delete() method declared in the Interface.
Delete() method in the Service
Call an instance of SqlModelMapper, then use the TrackDeleteByKey method of the SqlModelMapper to track the table delete operation. The data will be deleted from the database table through the cached SalesOrder model. Finally use the SaveChanges method of the SqlModelMapper to save the changes to the database. Calling the SaveChanges method of SqlModelMapper causes a SQL DELETE statement to be generated using the cached data of the SalesOrder object and the mapping information defined in the SalesOrder class, and then executes the SQL DELETE. If no errors occurred, SqlModelMapper automatically commits the changes to the database or rolls back the operation if otherwise. Modify the Delete() method of the service as follows:
public int DeleteByKey(params object[] parameters)
{
return _context.SqlModelMapper.TrackDeleteByKey<SalesOrder>(parameters)
.SaveChanges()
.DeletedCount;
}
Create a Web Application Project
Right-click the "SnapObjects-Asp.NetCore-Example" solution and select Add > New Project....
In the New Project dialog box, choose C# from the language list, then select ASP.NET Core Web Application. Name the project "Appeon.SnapObjectsDemo.Web" and click Create.
Select the .NET Core and ASP.NET Core 3.1, then select Web Application as project template and click Create.
After you finish this step, you must add Class Library Project dependencies. To do so, right-click the Dependencies in "Appeon.SnapObjectsDemo.Web" project and select Add Project Reference.... On the popup window select Projects, check the “Appeon.SnapObjectsDemo.Service” project and click OK. Please note that the two projects must target the same version of Framework. (e.g., .NET Core 3.1)
Register the Database Context
In ASP.NET Core, services such as the DataContext must be registered with the dependency injection (DI) container. The container provides the service to the controllers.
Add the following using statements to Startup.cs:
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;
using Appeon.SnapObjectsDemo.Service.DataContext;
Add the following code to the ConfigureServices() method in Startup.cs according to the comments:
// Uncomment the following line to connect to the SQL server database.
// Note: Replace "ContextName" with the configured context name; replace "key" with the database connection name that exists in appsettings.json. The sample code is as follows:
services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(Configuration["ConnectionStrings:AdventureWorks2012"]));
After registering the DataContext we need to add the Connection string so that the DataContext can actually know what database to connect to. In order to do that, modify the appsettings.json file as follows:
{
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"AdventureWorks2012": "Data Source=localhost,1433;Initial Catalog=AdventureWorks2012;Integrated Security=False;User ID=sa;Password=myPassword;Pooling=True;Min Pool Size=0;Max Pool Size=100;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite"
}
}
Modify the Data Source to reflect the IP Address and port of your database server. You also need to modify the User ID and Password with the corresponding data of your server. Note that this database is restored from the database backup file at the beginning of the document.
Register the Service
In ASP.NET Core, services such as the SalesOrderService must be registered with the dependency injection (DI) container. The container provides the service to the controllers.
Update Startup.cs. Add the following using statements:
using Appeon.SnapObjectsDemo.Service.Services;
using Appeon.SnapObjectsDemo.Service.Services.Impl;
and add the following code to the ConfigureServices() method in Startup.cs according to the comments:
// The service needs to be registered in the ConfigureServices method of the Startup class.
services.AddScoped<ISalesOrderService, SalesOrderService>();
Add Razor Page
In "Appeon.SnapObjectsDemo.Web" project, right-click on the Pages folder and click on Add > New Folder and name it SalesOrders. Right-click on the SalesOrders folder and click on Add > Razor Page.
On the popup window select Razor Page and click Add.
On the popup window name it Index.
Using the same method mentioned above, add Edit and Create Razor page which is used to create and update the model.
You need to add the DataTable class. The DataTable class mainly defines some attributes for page object which is from jquery datatable (for detail information, please go to https://datatables.net).
Right-click on the "Appeon.SnapObjectsDemo.Web" project. Click Add > New Folder. Name it Models. Then right-click on the Models folder and click Add > Class.
On the popup window, select Class, name it DataTable.cs, and click Add.
DataTable.cs
using System.Collections;
namespace Appeon.SnapObjectsDemo.Web.Models
{
public class DataTable
{
/// <summary>
/// request flag
/// </summary>
public int draw { get; set; }
/// <summary>
/// The total records
/// </summary>
public int recordsTotal { get; set; }
/// <summary>
/// The records of filter
/// </summary>
public int recordsFiltered { get; set; }
/// <summary>
/// The index of page
/// </summary>
public int pageIndex { get; set; }
/// <summary>
/// The size per page
/// </summary>
public int? pageSize { get; set; }
/// <summary>
/// The data per page
/// </summary>
public IList data { get; set; }
}
}
Perform CRUD Operations Using the Razor Page
LoadByPage() method in the Razor Page
You can find Index.cshtml.cs after expanding Index.cshtml.
In the Index.cshtml.cs, the LoadByPage() method is called by OnGet() method. It returns a response type of 200 with the retrieved current page in JSON format. If an internal server error occurs, it returns a response type 500 with the exception message.
Index.cshtml.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Appeon.SnapObjectsDemo.Service.Models;
using Appeon.SnapObjectsDemo.Service.Services;
using Appeon.SnapObjectsDemo.Web.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
namespace Appeon.SnapObjectsDemo.Web.Pages.SalesOrders
{
public class IndexModel : PageModel
{
private readonly ISalesOrderService _salesOrderService;
public IndexModel(ISalesOrderService salesOrderService)
{
_salesOrderService = salesOrderService;
}
public IList<SalesOrder> SalesOrders { get; set; }
[BindProperty(SupportsGet = true)]
[DataType(DataType.Date)]
public DateTime? StartOrderDate { get; set; } = new DateTime(2000, 1, 1).Date;
[BindProperty(SupportsGet = true)]
[DataType(DataType.Date)]
public DateTime? EndOrderDate { get; set; } = new DateTime(2020, 1, 31).Date;
[BindProperty(SupportsGet = true)]
public int? CustomerID { get; set; }
public IActionResult OnGet()
{
DataTable dt = new DataTable();
try
{
int pageSize = dt.pageSize ?? 10;//default 10 records per page
int pageIndex = 1;//default query first page data.
//query data by page
Page<SalesOrder> page = _salesOrderService
.LoadByPage(pageIndex, pageSize, CustomerID ?? 0, StartOrderDate, EndOrderDate);
this.SalesOrders = page.Items;
dt.recordsTotal = page.TotalItems;
dt.recordsFiltered = page.TotalItems;
dt.data = this.SalesOrders.ToList();
return Page();
}
catch (Exception e)
{
return StatusCode(500, e.Message);
}
}
}
}
Index.cshtml
Add the UI/UX of the Index page.
@page
@model Appeon.SnapObjectsDemo.Web.Pages.SalesOrders.IndexModel
@{
ViewData["Title"] = "Order";
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- Meta, title, CSS, favicons, etc. -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>SnapObjects Asp.NetCore Demo</title>
<style type="text/css" >
table {
border-collapse: collapse;
margin: 0 auto;
text-align: center;
}
table td, table th {
border: 1px solid #cad9ea;
color: #666;
height: 30px;
}
table thead th {
background-color: #CCE8EB;
width: 100px;
}
table tr:nth-child(odd) {
background: #fff;
}
table tr:nth-child(even) {
background: #F5FAFA;
}
</style>
</head>
<body >
<form method="get">
<table class="table" border="0" cellspacing="0" cellpadding="0">
<caption>
<h2>Order List</h2>
</caption>
<thead>
<tr>
<th>
ID
</th>
<th>@Html.DisplayNameFor(model => model.SalesOrders[0].SalesOrderID)</th>
<th>@Html.DisplayNameFor(model => model.SalesOrders[0].OrderDate)</th>
<th>@Html.DisplayNameFor(model => model.SalesOrders[0].CustomerID)</th>
<th> @Html.DisplayNameFor(model => model.SalesOrders[0].Status)</th>
<th> @Html.DisplayNameFor(model => model.SalesOrders[0].SalesOrderNumber)</th>
<th>
@Html.DisplayNameFor(model => model.SalesOrders[0].SubTotal)
</th>
<th>
@Html.DisplayNameFor(model => model.SalesOrders[0].TaxAmt)
</th>
<th>
@Html.DisplayNameFor(model => model.SalesOrders[0].Freight)
</th>
<th>
@Html.DisplayNameFor(model => model.SalesOrders[0].TotalDue)
</th>
<th>
@Html.DisplayNameFor(model => model.SalesOrders[0].ModifiedDate)
</th>
<th>
Operation
</th>
</tr>
</thead>
<tbody>
@for (var i = 0; i < Model.SalesOrders.Count; i++)
{
<tr>
<td>@(i + 1)</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].SalesOrderID)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].OrderDate)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].CustomerID)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].Status)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].SalesOrderNumber)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].SubTotal)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].TaxAmt)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].Freight)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].TotalDue)
</td>
<td>
@Html.DisplayFor(modelItem => Model.SalesOrders[i].ModifiedDate)
</td>
<td class="operate">
<a asp-page="./Edit" asp-route-id="@Model.SalesOrders[i].SalesOrderID">Edit</a>
<a asp-page-handler="DeleteById" asp-route-id="@Model.SalesOrders[i].SalesOrderID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
</form>
</body>
</html>
For more complete code example, see the demo application in the GitHub repository.
Note that if you want to run the project and load the data successfully, you must set the proxy that the Razor page uses.
LoadByKey() method in the Razor Page
In the Edit.cshtml.cs, the LoadByKey() method is called by Get() method. It receives an integer of the SalesOrderId. It returns a SalesOrder model and binds the model to razor page. If an internal server error occurs, it returns a response type 500 with the exception message.
Edit.cshtml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Appeon.SnapObjectsDemo.Service.DataContext;
using Appeon.SnapObjectsDemo.Web.Models;
using Appeon.SnapObjectsDemo.Service.Services.Impl;
using Appeon.SnapObjectsDemo.Service.Services;
using Appeon.SnapObjectsDemo.Service.Models;
namespace Appeon.SnapObjectsDemo.Web.Pages.SalesOrders
{
public class EditModel : PageModel
{
private readonly ISalesOrderService _salesOrderService;
public EditModel(ISalesOrderService salesOrderService)
{
_salesOrderService = salesOrderService;
}
public SalesOrder SalesOrder { get; set; }
/// <summary>
/// Return to the unified json format
/// </summary>
/// <param name="code"></param>
/// <param name="message"></param>
/// <param name="id"></param>
/// <returns></returns>
protected JsonResult GenJsonResult(int code, String message, int? id)
{
var result = new Dictionary<string, object>()
{
{ "code", code},
{ "message",message},
{ "id",id}
};
return new JsonResult(result);
}
public IActionResult OnGet(int id)
{
try
{
SalesOrder = _salesOrderService.LoadByKey(true, id);
return Page();
}
catch (Exception e)
{
return StatusCode(500, e.Message);
}
}
}
}
Edit.cshtml
Add the UI/UX of the Edit page as follows:
@page
@model Appeon.SnapObjectsDemo.Web.Pages.SalesOrders.EditModel
@{
ViewData["Title"] = "Order";
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- Meta, title, CSS, favicons, etc. -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>SnapObjects Asp.NetCore Demo</title>
<style type="text/css">
table {
border-collapse: collapse;
margin: 0 auto;
text-align: center;
}
table td, table th {
border: 1px solid #cad9ea;
color: #666;
height: 30px;
}
table thead th {
background-color: #CCE8EB;
width: 100px;
}
table tr:nth-child(odd) {
background: #fff;
}
table tr:nth-child(even) {
background: #F5FAFA;
}
</style>
</head>
<body>
<form id="demo-form2" method="post" data-parsley-validate class="form-horizontal form-label-left ">
<div class="form-group">
<label class="control-label col-md-3 col-sm-3 col-xs-12" for="first-name">
Sales Order ID <span class="required">*</span>
</label>
<div class="col-md-3 col-sm-6 col-xs-12">
<input type="text" asp-for="SalesOrder.SalesOrderID" readonly="readonly" class="form-control col-md-7 col-xs-12" placeholder="Sales Order ID">
</div>
<label class="control-label col-md-3 col-sm-3 col-xs-12" for="first-name">
Order Date <span class="required">*</span>
</label>
<div class="col-md-3 col-sm-6 col-xs-12">
<input type='text' class="form-control" required="required" readonly="readonly" asp-for="SalesOrder.OrderDate" />
</div>
</div>
</form>
</body>
</html>
For more complete code example, see the demo application in the GitHub repository.
Create() method in the Razor Page
In the Create.cshtml.cs, the Create() method is called by onPost() method. It mainly binds the SalesOrder model to the razor create page. If an internal server error occurred, it produces an error message in JSON format.
Create.cshtml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Appeon.SnapObjectsDemo.Service.DataContext;
using Appeon.SnapObjectsDemo.Web.Models;
using Appeon.SnapObjectsDemo.Service.Services.Impl;
using Appeon.SnapObjectsDemo.Service.Services;
using Appeon.SnapObjectsDemo.Service.Models;
namespace Appeon.SnapObjectsDemo.Web.Pages.SalesOrders
{
public class CreateModel : PageModel
{
private readonly ISalesOrderService _salesOrderService;
public CreateModel(ISalesOrderService salesOrderService)
{
_salesOrderService = salesOrderService;
}
public SalesOrder SalesOrder { get; set; }
/// <summary>
/// Return to the unified json format
/// </summary>
/// <param name="code"></param>
/// <param name="message"></param>
/// <param name="id"></param>
/// <returns></returns>
protected JsonResult GenJsonResult(int code, String message, int? id)
{
var result = new Dictionary<string, object>()
{
{ "code", code},
{ "message",message},
{ "id",id}
};
return new JsonResult(result);
}
public IActionResult OnPost()
{
try
{
var insertedCount = _salesOrderService.Create(SalesOrder);
}
catch (Exception e)
{
return GenJsonResult(-1, e.Message, 0);
}
return GenJsonResult(1, "", SalesOrder.SalesOrderID);
}
}
}
Create.cshtml
@page
@model Appeon.SnapObjectsDemo.Web.Pages.SalesOrders.EditModel
@{
ViewData["Title"] = "Order";
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- Meta, title, CSS, favicons, etc. -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>SnapObjects Asp.NetCore Demo</title>
<style type="text/css">
table {
border-collapse: collapse;
margin: 0 auto;
text-align: center;
}
table td, table th {
border: 1px solid #cad9ea;
color: #666;
height: 30px;
}
table thead th {
background-color: #CCE8EB;
width: 100px;
}
table tr:nth-child(odd) {
background: #fff;
}
table tr:nth-child(even) {
background: #F5FAFA;
}
</style>
</head>
<body>
<form id="demo-form2" method="post" data-parsley-validate class="form-horizontal form-label-left " >
<div class="form-group">
<label class="control-label col-md-3 col-sm-3 col-xs-12" for="first-name">
Sales Order ID <span class="required">*</span>
</label>
<div class="col-md-3 col-sm-6 col-xs-12">
<input type="text" asp-for="SalesOrder.SalesOrderID" readonly="readonly" class="form-control col-md-7 col-xs-12" placeholder="Sales Order ID">
</div>
<label class="control-label col-md-3 col-sm-3 col-xs-12" for="first-name">
Order Date <span class="required">*</span>
</label>
<div class="col-md-3 col-sm-6 col-xs-12">
<div class='input-group date' id='myDatepicker1'>
<input type='text' class="form-control" required="required" asp-for="SalesOrder.OrderDate" />
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
</div>
</div>
</div>
</form>
</body>
</html>
For more complete code example, see the demo application in the GitHub repository.
Update() method in the Razor Page
In the Edit.cshtml.cs, the Update() method is called by onPost() method. It mainly binds the SalesOrder model to the razor edit page. If an internal server error occurred, it produces an error message in JSON format.
Add this code to the Edit.cshtml.cs file.
public IActionResult OnPost()
{
try
{
var modifiedCount = _salesOrderService.Update(SalesOrder);
}
catch (Exception e)
{
return GenJsonResult(-1, e.Message, SalesOrder.SalesOrderID);
}
return GenJsonResult(1, "", SalesOrder.SalesOrderID);
}
For more complete code example, see the demo application in the GitHub repository.
Delete() method in the Razor Page
In the Index.cshtml.cs, the Delete() method is called by the OnGetDeleteById() method. It accepts an integer value that corresponds to the actual salesOrder id to delete, and produces a response type 200 if the delete was successful. If an internal server error occurred, it produces a response type 500.
Add this code to the index.cshtml.cs file.
/// <summary>
/// Return to the unified json format
/// </summary>
/// <param name="code"></param>
/// <param name="message"></param>
/// <param name="id"></param>
/// <returns></returns>
protected JsonResult GenJsonResult(int code, String message, int? id)
{
var result = new Dictionary<string, object>()
{
{ "code", code},
{ "message",message},
{ "id",id}
};
return new JsonResult(result);
}
public IActionResult OnGetDeleteById(String id)
{
try
{
var result = _salesOrderService.DeleteByKey(id);
}
catch (Exception e)
{
return GenJsonResult(-1, e.Message, 0);
}
return GenJsonResult(1, "", null);
}
For more complete code example, see the demo application in the GitHub repository.