CRUD Operations with ModelStore

This tutorial demonstrates the basics of creating, in SnapDevelop, a Web API with ASP.NET Core that will use ModelStore as its data manipulation component.

In this tutorial, you will learn to:

• Create a Web API Project
• Test the API
• Add a Database Context
• Register the Database Context
• Add a ModelStore Model
• Add a Service
• Perform CRUD Operations Using the Web API
• Call the Web API from PowerBuilder

Prerequisites

• SnapDevelop 2019
• Sample database setup
  1. Download the database backup file from https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2012.bak. (This specific version of the DB is required for this tutorial)
  2. Install SQL Server Express or SQL Server if it is not installed.
  3. Restore database using the downloaded database backup file.

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 Project....

In the New Project dialog, select .NET Core, and in the list of project templates, select ASP.NET Core Web API. Name the project "WebAPI2", name the solution "WebAPI MS Tutorial" and click Ok.

Test the Web API

The project template creates a sample Web API. Call the GET HTTP verb from a browser to test the app.

Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load.

If it's the first time to run, you may need to wait several seconds for initiating .NET runtime after the browser launched.

The following JSON is returned:

["value1","value2"]

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 WebAPI2 project and select Add > New Item.... In the item template list select DataContext and Name the class AppeonDataContext.cs and click OK.

In the Database Connection dialog, click New.

In the connection properties dialog, fill in database connection information and click OK.

Then back to the Database Connection dialog, Connection String is generated by the connection properties dialog, click OK.

The Connection String is saved in appsettings.json and the AppeonDataContext class is created.

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 controllers.

Update Startup.cs according to the comments:

// Add the following using statements
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;

and modify 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"]));

Add a ModelStore Model

The ModelStore is a data manipulation component that can be used in place of the .NET DataStore. It constructs data storage structures and defines data operations using Model Attributes. And it can be created and maintained using any C# IDE or even a simple text editor.

Create a ModelStore Model

Right-click on the Web API project. Click Add > New Folder. Name it Models. Then right-click on the new folder and click Add > Class.

On the popup window select Class and name it Department.cs and click Ok.

The Model Attributes specify the database tables the model class will be mapped to, as well as its mapping conditions. The Model Properties are the mappings to each individual column from the table. Add these Model Attributes and properties to your Model.

// Department.cs
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using SnapObjects.Data;

namespace WebAPI2.Models
{
    [SqlParameter("departmentId", typeof(int))]
    [Table("Department", Schema = "HumanResources")]
    [SqlWhere("(DepartmentId = :departmentId and :departmentId > 0)")]
    [SqlOrWhere("(1 = 1 and :departmentId = -1)")]
    public class Department
    {
        [Key]
        [Identity]
        public short DepartmentId { get; set; }

        public string Name { get; set; }

        public string GroupName { get; set; }

        public DateTime ModifiedDate { get; set; }
    }
}

Add a Service

The interface contains only the declaration of the methods, properties and events, but not the implementation.

Right-click on the Web API 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 > Interface.

Name it as IDepartmentService.cs and click Ok.

Add the declaration of the methods we will implement on the service.

// IDepartmentService.cs
using PowerBuilder.Data;
using WebAPI2.Models;

namespace WebAPI2.Services
{
    public interface IDepartmentService
    {
        IModelStore<Department> Retrieve();

        IModelStore<Department> RetrieveOne(int id);

        int Create(Department department);

        int Update(IModelStore<Department> department);

        int Delete(int id);
    }
}

To add the Service right-click on the Service > Impl sub-folder and click on Add > Class. Name it DepartmentService.cs and click Ok. (Notice the difference between the name of the interface "IDepartmentService.cs" and the name of the service "DepartmentService.cs")

Update DepartmentService.cs to implement the IDepartmentService interface, and add a constructor to receive the data context:

using System;
using PowerBuilder.Data;
using WebAPI2.Models;
using SnapObjects.Data;

namespace WebAPI2.Services.Impl
{
    public class DepartmentService : IDepartmentService
    {
        private readonly AppeonDataContext _context;

        public DepartmentService(AppeonDataContext Context)
        {
            _context = Context;
        }

        public int Create(Department department)
        {
            throw new NotImplementedException();
        }

        public int Delete(int id)
        {
            throw new NotImplementedException();
        }

        public IModelStore<Department> Retrieve()
        {
            throw new NotImplementedException();
        }

        public IModelStore<Department> RetrieveOne(int id)
        {
            throw new NotImplementedException();
        }

        public int Update(IModelStore<Department> department)
        {
            throw new NotImplementedException();
        }
    }
}

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.

Update Startup.cs according to the comments:

// Add the following Using statement
using WebAPI2.Services;
using WebAPI2.Services.Impl;

// The service needs to be registered in the ConfigureServices method of the Startup class.
services.AddScoped<IDepartmentService, DepartmentService>();

Perform CRUD Operations Using the Web API

Since the ModelStore and PowerBuilder's DataStore are very similar, you perform CRUD operations in a similar way as you do in the Web API.

The Web API requires one more component called Controller. This component was created when we created the Web API project. You can also create a new controller by right-clicking on the Controller folder then Add -> New Item -> Class. A controller determines what response to send back to a user when a user makes an HTTP request.

Retrieve the ModelStore

To retrieve data from the ModelStore we will use the Retrieve() method declared in the Interface.

Retrieve() method in the Service

Create an instance of ModelStore using specified Data Model, then call the ModelStore's Retrieve method to retrieve data.

// Replace your Retrieve method in the DepartmentService.cs class with this code
public IModelStore<Department> Retrieve()
{
    var modelStore = new ModelStore<Department>();

    // Parameter value as -1 to retrieve all data. 
	// Refer to SQL Where condition defined by the SqlWhere and SqlOrWhere 
    // attributes applied in Department class.
    modelStore.Retrieve(_context, -1);

    return modelStore;
}

Retrieve() method in the Controller

The Retrieve() method is called by the client using the HTTP verb GET. It returns the retrieved rows in JSON format.

using System;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using PowerBuilder.Data;
using WebAPI2.Models;
using WebAPI2.Services;
using WebAPI2.Services.Impl;
using SnapObjects.Data;
using System.Linq;

namespace WebAPI2.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class SampleController : ControllerBase
    {
        private readonly IDepartmentService _service;

        public SampleController(IDepartmentService Service)
        {
            _service = Service;
        }

        [HttpGet]
        [ProducesResponseType(StatusCodes.Status200OK)]
        [ProducesResponseType(StatusCodes.Status404NotFound)]
        [ProducesResponseType(StatusCodes.Status500InternalServerError)]
        public ActionResult<IModelStore<Department>> Retrieve()
        {
            var ms_Department = new ModelStore<Department>();

            try
            {
                ms_Department = (ModelStore<Department>)_service.Retrieve();
            }
            catch (Exception ex)
            {
                return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
            }

            if (ms_Department.Count <= 0)
            {
                return NotFound("No rows retrieved.");
            }
            else
            {
                return Ok(ms_Department);
            }
        }

        [HttpGet]
        public ActionResult<IEnumerable<string>> Load()
        {
            return new string[] { "value1", "value2" };
        }
    }
}

The project template creates a Sample Web API and we have added a Retrieve method. Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load.

Change the URL to: http://localhost:5000/api/Sample/Retrieve and Refresh the browser.

The following JSON is returned:

[{"departmentId":1,"name":"Engineering","groupName":"Research and Development","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":2,"name":"Tool Design","groupName":"Research and Development","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":3,"name":"Sales","groupName":"Sales and Marketing","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":4,"name":"Marketing","groupName":"Sales and Marketing","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":5,"name":"Purchasing","groupName":"Inventory Management","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":6,"name":"Research and Development","groupName":"Research and Development","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":7,"name":"Production","groupName":"Manufacturing","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":8,"name":"Production Control","groupName":"Manufacturing","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":9,"name":"Human Resources","groupName":"Executive General and Administration","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":10,"name":"Finance","groupName":"Executive General and Administration","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":11,"name":"Information Services","groupName":"Executive General and Administration","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":12,"name":"Document Control","groupName":"Quality Assurance","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":13,"name":"Quality Assurance","groupName":"Quality Assurance","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":14,"name":"Facilities and Maintenance","groupName":"Executive General and Administration","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":15,"name":"Shipping and Receiving","groupName":"Inventory Management","modifiedDate":"2008-04-30T00:00:00"},{"departmentId":16,"name":"Executive","groupName":"Executive General and Administration","modifiedDate":"2008-04-30T00:00:00"}]

RetrieveOne() method in the Service

Create an instance of ModelStore using specified Data Model, then call the ModelStore's Retrieve method to retrieve one row.

// Replace your RetrieveOne method in the DepartmentService.cs class with this code
public IModelStore<Department> RetrieveOne(int id)
{
    var ms_Department = new ModelStore<Department>();

    ms_Department.Retrieve(_context, id);

    return ms_Department;
}

RetrieveOne() method in the Controller

The RetrieveOne() method is called by the client using the HTTP verb GET. It returns the retrieved one row in JSON format.

/***Add this method to your controller***/
// GET api/Sample/RetrieveOne/{id}
[HttpGet("{id}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<IModelStore<Department>> RetrieveOne(int id)
{
    var ms_Department = new ModelStore<Department>();

    try
    {
        ms_Department = (ModelStore<Department>)_service.RetrieveOne(id);
    }
    catch (Exception ex)
    {
        return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
    }

    if (ms_Department.Count <= 0)
    {
        return NotFound("No rows retrieved.");
    }
    else
    {
        return Ok(ms_Department);
    }
}

The project template creates a Sample Web API and we have added a RetrieveOne method. Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load. Change the URL to: http://localhost:5000/api/Sample/RetrieveOne/1 and Refresh the browser..

The following JSON is returned:

{"departmentId":1,"name":"Engineering","groupName":"Research and Development1","modifiedDate":"2008-04-30T00:00:00"}

Create/Insert a new row into the ModelStore

To insert data into the ModelStore we will use the Create() method declared in the Interface.

Create() method in the Service

Create an instance of ModelStore using specified Data Model, then call the ModelStore's Add() method passing in the rows in JSON format. Call the SaveChanges() method of the ModelStore and return the count of Inserted Rows.

// Replace your Create method in the DepartmentService.cs class with this code:
public int Create(Department department)
{
    var ms_Department = new ModelStore<Department>(ChangeTrackingStrategy.PropertyState);

    ms_Department.Add(department);

    return ms_Department.SaveChanges(_context).InsertedCount;
}

Create() method in the Controller

The Create() method is called by the client using the HTTP verb POST. It accepts a JSON string that corresponds to the actual department table to create, and produces a response type 200 with the department data. It produces a response type 404 if an invalid id was received. If an internal server error occurred, it produces a response type 500.

/***Add this method to your controller***/
// POST api/Sample/Create
[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<Department> Create([FromBody]IList<Department> department)
{
    int insertedRows;

    try
    {
        insertedRows = _service.Create(department.FirstOrDefault());
    }
    catch (Exception ex)
    {
        return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
    }

    if (insertedRows <= 0)
    {
        return NotFound("No rows inserted.");
    }
    else
    {
        return Ok(department);
    }
}

The project template creates a Sample Web API and we have added a Create method. Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load.

To consume this method of the Web API, call the URL http://localhost:5000/api/Sample/Create with your client application passing in the body of the request the JSON containing the row you want to Create/Insert. (To learn how to call this method from PowerBuilder, refer to the following section: Call the Web API from PowerBuilder)

Update changes into the ModelStore

To insert data into the ModelStore we will use the Update() method declared in the Interface.

Update() method in the Service

Receive, in JSON format, the rows to update. Call the SaveChanges() method of the ModelStore and return the count of affected rows.

// Replace your Update method in the DepartmentService.cs class with this code:
public int Update(IModelStore<Department> department)
{
    return department.SaveChanges(_context).AffectedCount;
}

Update() method in the Controller

The Update() method is called by the client using the HTTP verb POST. It accepts a JSON string with the rows that correspond to the actual department table to update. Calls the Update() method of the service catching any possible errors. Then produces a response type 200 if the update was successful. If an internal server error occurred, it produces a response type 500.

/***Add this method to your controller***/
// POST api/Sample/Update
[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<int> Update([FromBody]IModelStore<Department> department)
{
    int modifiedRows;

    try
    {
        modifiedRows = _service.Update(department);
    }
    catch (Exception ex)
    {
        return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
    }

    if (modifiedRows <= 0)
    {
        return NotFound("No rows modified.");
    }
    else
    {
        return Ok(modifiedRows);
    }
}

The project template creates a Sample Web API and we have added an Update method. Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load.

To consume this method of the Web API, call the URL http://localhost:5000/api/Sample/Update with your client application passing in the JSON containing the rows you want to Update. (To learn how to call this method from PowerBuilder, refer to the following section: Call the Web API from PowerBuilder)

Delete data from the ModelStore

To delete data from the ModelStore we will use the Delete() method declared in the Interface.

Delete() method in the Service

Create an instance of ModelStore by using the Department Model and the data context. Retrieve the ModelStore using the received id and use the RemoveAt() method of the ModelStore to delete the row. Save the changes and return the deleted count.

// Replace your Delete method in the DepartmentService.cs class with this code:
public int Delete(int id)
{
    var ms_Department = new ModelStore<Department>(ChangeTrackingStrategy.PropertyState);

    ms_Department.Retrieve(_context, id);

    ms_Department.RemoveAt(0);

    return ms_Department.SaveChanges(_context).DeletedCount;
}

Delete() method in the Controller

The Delete() method is called by the client using the HTTP verb DELETE. It accepts an integer value that corresponds to the actual department id to delete, and produces a response type 200 if the delete was successful. It returns a response type 404 if the id is invalid. If an internal server error occurred, it produces a response type 500.

/***Add this method to your controller***/
// DELETE api/Sample/Delete/{id}
[HttpDelete("{id}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<int> Delete(int id)
{
    int deletedRows;

    try
    {
        deletedRows = _service.Delete(id);
    }
    catch (Exception ex)
    {
        return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
    }

    if (deletedRows <= 0)
    {
        return NotFound("No rows deleted.");
    }
    else
    {
        return Ok(deletedRows);
    }
}

The project template creates a Sample Web API and we have added a Delete method. Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/Sample/Load.

To consume this method of the Web API, call the URL http://localhost:5000/api/Sample/Delete/ with your client application passing in the department ID you want to delete. (To learn how to call this method from PowerBuilder, refer to the following section: Call the Web API from PowerBuilder)

Call the Web API from PowerBuilder

Start PowerBuilder and create a template application with no database connection. Then add a new Grid DataWindow, using the DataWindow Designer, that maps to the table: HumanResources.Department. Add a retrieval argument named departmentId that will be used in the SQL WHERE clause to filter by DepartmentID. Save the DataObject with the name d_department.

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.

Add the JSON DataWindow to your Window. And add a button for each CRUD operation.

Retrieve the 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. Add an instance variable of the RESTClient object.

RESTClient inv_RestClient

On 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")

On the source code window of your Retrieve button, add the RESTClient.Retrieve() function to call your Web API and load it directly into your DataWindow.

// Retrieve the DW using the RESTClient
inv_RestClient.RetrieveOne(dw_department, "http://localhost:5000/api/Sample/RetrieveOne/" + ll_id)

Run your application. Press Ctrl+R. Click on the Retrieve button. (Note: Your Web API needs to be running)

Insert/Update and Delete data using the Web API

On the source code window of your Insert button, add DataWindow.ExportRowAsJson() function to export the new data row as plain-format JSON string, and add the RESTClient.SendPostRequest() function to call your Web API using the HTTP verb POST to sends the data to the Web API.

On the source code window of your Update button, add the RESTClient.Submit() function to call the Web API using the HTTP verb POST. It automatically exports the DataWindow JSON, as well as all of its Buffers, and sends the all rows to the Web API.

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

Insert

// 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]			= "Research2"
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( )

// 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/Sample/Create", ls_json, ls_response)

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

Alternatively, you can also insert data from the JSON DataWindow with the RESTClient.Submit() function if the Web API can receive an IModelStore<TModel> argument type.

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

Update

// Update data from the JSON DataWindow with the RESTClient
inv_RestClient.Submit("http://localhost:5000/api/Sample/Update", ls_response, dw_department)

Delete

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

// ID of the department to delete
// 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.
li_dep_id = 15

// Delete data from the JSON DataWindow with the RESTClient
li_rtn = inv_RestClient.SendDeleteRequest("http://localhost:5000/api/sample/delete/" + String(li_dep_id), 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 also delete data from the JSON DataWindow with the RESTClient.Submit() function if the Web API can receive an IModelStore<TModel> argument type.

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

Run your application. Press Ctrl+R. Click on the Retrieve button. (Note: Your Web API needs to be running)