Handling Data from a PowerBuilder Application with SqlModelMapper
Last Updated: December 2023
In this tutorial, we create a Web API that uses SqlModelMapper as its data manipulation component, and uses DWNet.Data.AspNetCore to receive and handle requests with standard-format DataWindow JSON from a PowerBuilder application.
In this tutorial, you learn how to:
- Add a SqlModelMapper Model
- Add a Web API service for processing stateful data objects from PowerBuilder
- Call the Web API from PowerBuilder
Prerequisites
- SnapDevelop 2022 R3
- PowerBuilder 2022 R3
- Sample database setup
Download the database backup file from https://github.com/Appeon/.NET-Project-Example-Database/blob/master/AdventureWorks_for_sqlserver.zip.
Install SQL Server Express or SQL Server if it is not installed.
Restore database using the downloaded database backup file.
- Create an ASP.NET Core 3.1 Web API project named WebApplication1 (using the Basic sample code) in SnapDevelop.
- Install the NuGet package DWNet.Data.AspNetCore 3.1.0 to your Web API project.
Configure the MVC Service
Add the following using statements to Program.cs:
using DWNet.Data.AspNetCore;
In Program.cs, replace the builder.Services.AddControllers(); line with:
builder.Services.AddControllers(m =>
{
m.UsePowerBuilderIntegrated();
});
Create a SqlModelMapper Model
SqlModelMapper can handle 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 project node in the Solution Explorer, and select Add > New Folder to create a folder named Models to store the generated C# models.
Select View > DB Server Explorer > Connect to the database. After the database connection is created, right click on the table Department and select Convert to C# Model.
In the C# Model Generator dialog, select SqlModelMapper for the C# Model Type, and select the folder Models as the target folder. Click Export. Notice that a file named Department.cs is created in the folder Models:
using System;
using System.Linq;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SnapObjects.Data;
namespace WebApplication1.Models
{
[Table("Department", Schema = "HumanResources")]
public class Department
{
[Key]
[Identity]
[SqlColumn("DepartmentID")]
public Int16 DepartmentID { get; set; }
[SqlColumn("Name")]
public String Name { get; set; }
[SqlColumn("GroupName")]
public String GroupName { get; set; }
[SqlColumn("ModifiedDate")]
public DateTime ModifiedDate { get; set; }
}
}
Inject the DataContext
Right-click on the project node in the Solution Explorer, and select Add > New Item.... Select DataContext from the list and click OK. Notice that a file named DefaultDataContext.cs is created in the Solution Explorer.
In Program.cs, right-click the line after builder.Services.AddSwaggerGen();, and select Inject DataContext.
using DWNet.Data.AspNetCore;
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;
using WebAPI2;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers(m =>
{
m.UsePowerBuilderIntegrated();
});
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(builder.Configuration, "localhost,1433.AdventureWorks2012"));
/// ... more code
Add a Service
In this section, we create and implement an interface named IDeparmentService first, and then register the interface with the dependency injection (DI) container.
Add an interface
Right-click on the project node in the Solution Explorer, and select Add > New Folder to create a folder named Services. Right-click on the Services folder, and select Add > Interface to create an interface named IDeparmentService.
In IDeparmentService.cs, remove all of the template code and replace it with the following code:
using SnapObjects.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebApplication1.Models;
namespace WebApplication1.Services
{
public interface IDepartmentService
{
Task<IList<Department>> LoadAsync();
Task<int> UpdateAsync(IList<IModelEntry<Department>> department);
}
}
Note that the UpdateAsync method defines an IList<IModelEntry<Department>> type parameter to pass in a list of IModelEntry<Department> objects which contains data and state, just like rows of data from DataWindow.
Implement the service
Right-click on the Services folder in the Solution Explorer, and select Add > New Folder to create a sub folder named Impl. Right-click on the Impl folder and select Add > Class to create a class named DepartmentService.cs.
In DepartmentService.cs, remove all of the template code and replace it with the following code:
using SnapObjects.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebApplication1.Models;
namespace WebApplication1.Services.Impl
{
public class DepartmentService : IDepartmentService
{
// Please make sure DefaultDataContext has been created.
private readonly DefaultDataContext _context;
private readonly ISqlModelMapper _mapper;
public DepartmentService(DefaultDataContext context)
{
_context = context;
_mapper = _context.SqlModelMapper;
}
public async Task<IList<Department>> LoadAsync()
=> (await _mapper.LoadAsync<Department>()).ToList();
public async Task<int> UpdateAsync(IList<IModelEntry<Department>> department)
{
_mapper.TrackRange(department);
return (await _mapper.SaveChangesAsync()).ModifiedCount;
}
}
}
The class DepartmentService.cs implements the IDepartmentService interface, and adds a constructor to receive the data context. The UpdateAsync method calls an instance of SqlModelMapper first, and then uses the TrackRange method of the SqlModelMapper to track the table update operation(s). The data will be updated into the database table through the cached Department model. Finally, the UpdateAsync method uses the SaveChangesAsync method of the SqlModelMapper to save the changes to the database. The SaveChangesAsync method generates a SQL UPDATE statement using the cached data of the Department object and the mapping information defined in the Department class first, and then executes the SQL UPDATE statement. If no errors occur, SqlModelMapper automatically commits the changes to the database, otherwise, it rollbacks the operation.
Register the service
In ASP.NET Core, services such as the DepartmentService must be registered with the dependency injection (DI) container. The container provides the service to controllers.
In Program.cs, add the following using statements:
using WebApplication1.Services;
using WebApplication1.Services.Impl;
In Startup.cs, add the AddScoped method after the builder.Services.AddDataContext method:
builder.Services.AddScoped<IDepartmentService, DepartmentService>();//Service registered
Add Methods to Sample Controller
In this section, we add the methods LoadAsync() and UpdateAsync() to the sample controller.
Add LoadAsync() Method
The LoadAsync() method is called by the client using the HTTP verb GET. It returns a response type of 200 with the retrieved rows in JSON format. If no rows were found it returns a response type 404. If an internal server error occurs, it returns a response type 500 with the exception message.
In SampleController.cs, remove all of the template code and replace it with the following code:
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using SnapObjects.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebApplication1.Models;
using WebApplication1.Services;
namespace WebApplication1.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class SampleController : ControllerBase
{
private readonly IDepartmentService _DepartmentService;
public SampleController(IDepartmentService DepartmentService)
{
_DepartmentService = DepartmentService;
}
// GET api/Sample/load
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<Task<IList<Department>>> LoadAsync()
{
IList<Department> results;
try
{
results = _DepartmentService.LoadAsync().Result;
}
catch (System.Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
if (results.Count <= 0)
{
return NotFound("No rows retrieved.");
}
else
{
return Ok(results);
}
}
}
}
Add UpdateAsync() Method
The UpdateAsync() method is called by the client using the HTTP verb POST. It accepts a JSON string with the row that corresponds to the actual table to update. It calls the UpdateAsync() method of the service catching any possible errors. It produces a response of type 200 if the update is successful. If an internal server error occurs, it produces a response of type 500.
Note that IList<IModelEntry<Department>> type is required here to receive DataWindow format JSON.
In SampleController.cs, add the following code to the class SampleController:
/***Add this method to your controller***/
// POST api/sample/update
[HttpPost]
[ProducesResponseType(StatusCodes.Status201Created)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<Task<int>> UpdateAsync([FromBody] IList<IModelEntry<Department>> DepartmentList)
{
int result;
try
{
result = _DepartmentService.UpdateAsync(DepartmentList).Result;
}
catch (System.Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
if (result <= 0)
{
return NotFound("No rows updated.");
}
else
{
return Ok(result);
}
}
Call the Web API from PowerBuilder
Start PowerBuilder and create a template application with no database connection. Then add a Grid DataWindow, using the DataWindow Designer, that maps to the table Department. Save the DataObject with the name d_department.
Add a 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.
Add a JSON DataWindow to your window, and add a button for each CRUD operation.
Retrieve Data to JSON DataWindow
The RESTClient object provides the ability to access the RESTful Web APIs. It loads the JSON-formatted string returned from the RESTful Web Service APIs into the DataWindow object. The JSON string returned from the RESTFul Web Service APIs must have no more than 2 levels, and the top-level must be arrays, the second-level must be objects.
To retrieve a JSON DataWindow, you first need to instantiate the RESTClient object:
RESTClient inv_RestClient
In the Open event of your window, create the object variable.
// 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")
Call the Load() Method of the Web API
In the source code window of your Load button, add the RESTClient.SendGetRequest() function to call your Web API and load it directly into your DataWindow.
// Variable declaration
String ls_response
// Reset the DW
dw_department.Reset()
// Retrieve the DW using the RESTClient
inv_RESTClient.Retrieve(dw_department, "http://localhost:5000/api/Sample/Load")
Run your application. Press Ctrl+R. Click the Retrieve button. (Note: Your Web API needs to be running)
Update Data using the Web API
Use the Submit() method of the RESTClient to perform the Update operations from your DataWindow. Add necessary code to your buttons to update rows from your DataWindow and call the corresponding Web API using the HTTP verb POST. Send the data to the Web API, and the server receives the data.
Call the Update() Method of the Web API
// Variable Declaration
String ls_response
// Retrieve the DW from the Web API
inv_RestClient.Retrieve(dw_department, "http://localhost:5000/api/Sample/Load")
// Changes a value from the DW
dw_department.SetItem(1, 2, "Vine Lane")
// Call the Web API to send the changes
inv_RestClient.Submit("http://localhost:5000/api/Sample/Update", ls_response, dw_department)
Run your application. Press Ctrl+R. Click the Update button. (Note: Your Web API needs to be running)