CRUD Operations with Generic .NET DataStore

Last Updated: December 2023

Introduction

This tutorial demonstrates the basics of creating, in SnapDevelop, a Web API with ASP.NET Core that uses generic .NET DataStores to perform CRUD operations. For differences between non-generic and generic .NET DataStores, see .NET DataStore API Reference.

If you are new to creating Web APIs with ASP.NET Core, you are strongly recommended to read Create a Web API first to get familiar with the basic concepts of C# programming and RESTful services (also known as Web APIs).

In this tutorial, you will learn to:

Create a Web API Project
Add a Database Context
Add a .NET DataStore Model
Generate Service and Controller (Scaffolding)
Register Service in Program.cs
Call the Web API from PowerBuilder

Prerequisites

PowerBuilder 2022 R3 and SnapDevelop 2022 R3
Sample database setup
1. Install SQL Server Express or SQL Server 2012 or later.
2. Download the database backup file (AdventureWorks_for_sqlserver.zip) from https://github.com/Appeon/.NET-Project-Example-Database.
3. Restore database using the downloaded database backup file.
  
  For how to restore the database, you can refer to the readme file: https://github.com/Appeon/.NET-Project-Example-Database/blob/master/README.md.

Create a Web API Project

Start SnapDevelop and select Create New Project from the Start page. Or, from the File menu, select New and then New Project...

In the New Project dialog box, select ASP.NET Core Web API and click Next.

Name the project "WebApplication1", and then click Next to go to the next screen.

Select Basic for Type and click Create to generate the project. The project is then opened by SnapDevelop so that you can start to develop.

Test the Sample API

The project template creates a sample API. Click Test > Web API Tester in the menu, double-click the Get() method, and then click Send. In the Start Project painter that pops up, select the project and click OK.

The following information is returned:

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 Web API project and select Add > New Item... In the Add New Item dialog box, select DataContext and name the class AppeonDataContext and click Next.

Click New to create a database connection if no database connection exists.

Fill in the information to connect to the database AdventureWorks2012 as shown in the image below and click OK.

Then back to the Add New Item dialog box, a connection string is automatically generated, click Create.

The connection string is saved in appsettings.json and the AppeonDataContext class is created.

Add a .NET DataStore Model

.NET DataStore is the .NET version of the native PowerBuilder DataStore. It provides almost the same set of APIs as the native PowerBuilder DataStore. A native PowerBuilder DataStore is created using PowerBuilder's DataWindow Designer.

Create a DataObject in PowerBuilder

Start PowerBuilder and create a template application. Then add a Grid DataWindow that maps to the table HumanResources.Department in the AdventureWorks2012 database which was restored in Prerequisites. Save the DataWindow object with the name d_department.

Convert DataWindow to C# Model

In SnapDevelop IDE, right-click the solution name, and select Open PB Workspace to open the application that you have just created in PowerBuilder.

After the PB workspace is loaded in the Solution Explorer, expand the workspace and right-click the DataWindow d_department and select Convert DataWindow to C# Model.

Generate C# Model

The DataWindow Converter opens and loads the DataWindow d_department for converting to .NET DataStore.

Model Export

Click New Connection in the Server dialog box to connect to the AdventureWorks2012 database. Click the Parse button to parse the DataWindow and then click Export to generate the C# model (.NET DataStore), or directly click Export to perform the parsing and exporting at one step.

In the Model Export dialog, make sure the WebApplication1 project is selected and click Export.

A Models folder is created and the .NET DataStore model D_Department.cs is generated and added in this folder. The D_Department.cs model maps to the table HumanResources.Department in the AdventureWorks2012 database.

New DS

Generate Service and Controller (Scaffolding)

Right-click the Models folder and select Scaffolding > Scaffold Service and Controller from Model.

In the Specify Scaffolding Options dialog box, select .NET DataStore for Model and select Repository_Pattern_Service_DataStore_for_AnyClient for Template. Make sure AppeonDataContext which we just created is selected for the DataContext drop-down box.

Click Generate. SnapDevelop will generate AppeonDataContextService and Controller correspondingly according to the model selected.

Modify Program.cs

Install NuGet packages DWNet.Data.AspNetCore 4.0.1 and Microsoft.AspNetCore.Mvc.NewtonsoftJson 6.0.14 to your project, and add the following using statements to Program.cs:

using WebApplication1.Services;
using WebApplication1.Services.Impl;
using DWNet.Data.AspNetCore;
using SnapObjects.Data.AspNetCore;

In ASP.NET Core, services and Datacontext must be registered with the dependency injection (DI) container. The container provides the service to the controllers.

In Program.cs, right click below the builder.Services.AddSwaggerGen(); line and select Inject DataContext.

![Inject_DataContext.png](C:/Users/appeon/Desktop/NET DataStore Startup.cs fixes/.NET DataStore Startup.cs fixes/CRUD Operations with Generic .NET DataStore/01_modify_startup/images/Inject_DataContext.png)

The following line is added automatically to the document:

builder.Services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(builder.Configuration, "AdventureWorks2012"));

Below this newly inserted line, right-click and select Inject Services. In the Inject Service(s) dialog box, make sure WebApplication1 and D_DepartmentService are selected and click OK.

![Inject_Service.png](C:/Users/appeon/Desktop/NET DataStore Startup.cs fixes/.NET DataStore Startup.cs fixes/CRUD Operations with Generic .NET DataStore/01_modify_startup/images/Inject_Service.png)

The following line is added automatically to the ConfigureServices() method:

builder.Services.AddScoped<ID_DepartmentService, D_DepartmentService>();

Replace the line builder.Services.AddControllers(); with:

builder.Services.AddControllers(m => 
{
    m.UsePowerBuilderIntegrated();
});
builder.Services.AddControllers().AddNewtonsoftJson();

Note: The System.Text.Json library is a built-in part of the shared framework for .NET Core 3.0 or higher versions. The library parses and writes DateTime and DateTimeOffset values according to the ISO 8601:-2019 extended profile. The JsonSerializer, Utf8JsonReader, Utf8JsonWriter, and JsonElement types parse and write DateTime and DateTimeOffset text representations according to the extended profile of the ISO 8601-1:2019 format; for example, 2019-07-26T16:59:57-05:00. For more information, refer to https://docs.microsoft.com/dotnet/standard/datetime/system-text-json-support. However, PowerBuilder uses a different DateTime format (yyyy-mm-dd hh:mm:ss:ffffff, for example, 2019-07-26 16:04:57.242000) from the ISO 8601-1:2019 format. This might cause errors when you send JSON data containing PowerBuilder DateTime values to a .NET Core 3.0 or higher Web API. Therefore, the code builder.services.AddControllers().AddNewtonsoftJson() is added above to switch ASP.NET Core 3.0 back to use JSON.NET. If your Web API project is created with .NET Core 2.1, you do NOT need to install the NuGet package Microsoft.AspNetCore.Mvc.NewtonsoftJson or add the code to handle the PowerBuilder DateTime values.

Add app.UseDataWindow(); before the line app.MapControllers();

// ...
// more code

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.UseDataWindow();

app.MapControllers();

app.Run();

Call the Web API from PowerBuilder

Add the JSON DataWindow

A JSON DataWindow is a traditional DataWindow that has a Web API as its data source. You use the same functionalities on the client-side, but the data manipulation is done on the Web API server-side.

In the Create a DataObject in PowerBuilder section, we have created a template PowerBuilder application and a d_department DataObject. Now add a DataWindow control to the sheet window, set its Name to dw_department and DataObject to d_department, and then add four buttons: Retrieve, Insert, Update, Delete on the window.

Retrieve data for the JSON DataWindow

The PowerBuilder RESTClient object provides the ability to access the RESTful Web APIs. It can load the JSON-formatted string returned from the RESTful Web Service APIs into the DataWindow object.

To retrieve data for the JSON DataWindow, you first need to instantiate the RESTClient object.

Add an instance variable of the RESTClient object in the window.

RESTClient inv_RestClient

On the Open event of the window, create the object variable and set the request headers.

// Create the RESTClient object variable
inv_RestClient	= CREATE RESTClient

// Set the Request Headers to tell the Web API you will send JSON data
inv_RestClient.SetRequestHeader ("Content-Type", "application/json;charset=UTF-8")

// Set the Request Headers to accept GZIP compression
inv_RestClient.SetRequestHeader("Accept-Encoding", "gzip")

On the Clicked event of the Retrieve button, add the RESTClient.Retrieve() function to call the Web API and load the data directly into the DataWindow.

// Retrieve the DW using the RESTClient
inv_RestClient.RetrieveOne(dw_department, "http://localhost:5000/api/D_Department/RetrieveOne/3")

Insert, Update, and Delete data using the Web API

On the Clicked event of the Insert button, add the DataWindow.ExportJson() function to export the new data as plain-format JSON string, and add the RESTClient.SendPostRequest() function to call the Web API using the HTTP POST method to send the string to the Web API.

// Variable Declaration
Long	ll_row
Long	ll_rtn
String	ls_json
String	ls_response

// Insert a new row into the DW
ll_row	= dw_department.InsertRow(0)

// Add data to the new row
dw_department.object.departmentid[ll_row]	= 0
dw_department.object.name[ll_row]			= "Research"
dw_department.object.groupname[ll_row]		= "Research and Development"
dw_department.object.modifieddate[ll_row]	= Today()

// Accept the text of the DW
dw_department.AcceptText()

// Export the DW JSON
// The exported JSON string will be a plain-format string.
ls_json	= dw_department.ExportJson(TRUE, FALSE)

// Call the Create method of the Web API by passing the JSON and catching the response
ll_rtn	= inv_RestClient.SendPostRequest("http://localhost:5000/api/D_Department/Create", ls_json, ls_response)

// Add your business logic to validate if the Creation of the department was successful
// ...

Alternatively, you can directly pass the JSON DataWindow using the RESTClient.Submit() function if the Web API can receive an IDataStore type argument.

inv_RestClient.Submit("http://localhost:5000/api/D_Department/Update", ls_response, dw_department)

On the Clicked event of the Update button, add the RESTClient.Submit() function to call the Web API using the HTTP POST method. The RESTClient.Submit() function automatically exports the DataWindow JSON, as well as all of its buffers, and sends all rows to the Web API.

// Variable Declaration
Long	ll_rtn
String	ls_response
String	ls_json

// Retrieve the DW from the Web API
inv_RestClient.Retrieve(dw_department, "http://localhost:5000/api/D_Department/Retrieve")

// Changes the data values
dw_department.SetItem(1, "name", "Training")

// Call the Web API to submit the changes
ll_rtn = inv_RestClient.Submit("http://localhost:5000/api/D_Department/Update", ls_response, dw_department)

// Add your business logic to validate if the Creation of the department was successful
// ...

On the Clicked event of the Delete button, add the RESTClient.SendDeleteRequest() function to call the Web API using the HTTP DELETE method. You can also send your JSON data in the body of the request or you can send individual arguments on the URL.

// Variable Declaration
Int	li_rtn
Int li_dep_id
String	ls_response
String	ls_json

// Note: for the Delete method to actually delete a row, delete a newly inserted row 
// since the existing rows have dependent data on other tables.
dw_department.DeleteRow(dw_department.GetRow())

// Accept the text of the DW
dw_department.AcceptText()

// Export the DW JSON
// The exported JSON string will be a DW JSON-format string.
ls_json	= dw_department.ExportJson(TRUE, TRUE)

// Delete data from the JSON DataWindow with the RESTClient
li_rtn = inv_RestClient.SendDeleteRequest("http://localhost:5000/api/D_Department/Delete", ls_json, ls_response)

// Check if the delete was successful by looking into the Return code of the RESTClient
IF li_rtn <> 1 THEN
	  // Rest of your code....
End If

Alternatively, you can delete data from the JSON DataWindow with the RESTClient.Submit() function if Web API can receive an IDataStore type argument.

inv_RestClient.Submit("http://localhost:5000/api/D_Department/Update", ls_response, dw_department)

Now you can run the PowerBuilder application, and click on each button to test each operation. (Note: Your Web API needs to be running)