Stored Procedure Routing

Stored Procedure Routing

Code generating data-centric models and controllers allows you to quickly and easily expose a lot of basic CRUD functionality, and OData makes it possible to perform advanced querying of those generated endpoints that can satisfy a wide range of client application use-cases, but in most cases code generation of CRUD endpoints, even with the power of OData queries, will not satisfy 100% of the use cases for an API. There is also a requirement for developers to be able to design and implement custom endpoints which when called execute custom business logic in order to implement some piece of functionality. Harmony Core supports several mechanisms by which custom API endpoints can be implemented, one of whihc is called Stored Procedure Routing (sometimes referred to as SPROC Routing).

SPROC routine enables arbitrary code in the form of public methods of an arbitrary class to be exposed as web service operations. To enable this functionality classes containing such methods are decorated with a {Controller} attribute.

If you wish to make use of stored procedure routing witin your API then the first thing you will needt to do is make a change to our Startup class to enable the use of SPROC support. This involves enabling an optional setting in the regen.bat file.

• Edit regen.bat then locate and un-comment the ENABLE_SPROC option.

• Save your changes to regen.bat and then execute the batch file to regenerate the code.

Enabling this option caused one additional line of code to be inserted into the Startup class:

This code extends the Web API routing rules by adding a mechanism to route inbound requests directly to methods in classes thatn are decorated with a {Controller} attribute.

The project template that we used to create the solution that we are curently working in included a sample class containing code that can be exposed via the "SPROC" mechanism, but that source file was set to not be compiled. We need to change that now.

• In Solution Explorer right-click on the Services project and select Unload Project.

• Right-click on the Services project again and select Edit Services.synproj.

• Locate the project file entry for the file CustomFunctionality\OrdersMethods.dbl, which will look like this:

    <None Include="CustomFunctionality\OrdersMethods.dbl" />

• Change the line so that the file is included in the build, like this:

    <Compile Include="CustomFunctionality\OrdersMethods.dbl" />

• In Solution Explorer right-click on the Services project one more time and select Reload Project.

• When prompted to close the open document, click Yes.

• When prompted to save changes, click Yes.

Note: We would generally change the build action of a file via the Properties window, in this case changing the value of the Build Action property from None to Compile. Unfortunately, at the time of writing, there are some issues in this area in Visual Studio and we were finding that changes to the project file via the Properties window were not being reliably saved. This is why we just edited the project file manually!

We need to make one more change in order to enable the "SPROC" routing support:

• Edit StartupCustom.dbl and remove the comment character from this statement:

services.TryAddEnumerable(ServiceDescriptor.Singleton<IActionInvokerProvider, HarmonySprocActionInvokerProvider>())

• Type Ctrl + Shift + B to build the solution then check the Output window and verify that the build was successful.

• Press F5 to start the hosting application.

You should now be able to use Postman to test the new custom operation. You can do this by:

• Importing annother Postman file named Harmony Core Custom Functionality.postman_collection.json which you will find in your main HarmonyCoreWorkshop solution directory.

You will find that a new test collection is created, containing two tests.

The Create New Order (Method) test calls the CreateNewOrder method in the OrdersMethods class via the SPROC routing convention. If you examine the request body of this test you will notice that the JSON data being pased to the method includes both a new order, and two new order items:

The structure of this data coresponds to the parameters defined in the CreateNewOrder method.

• Execute the test. You should see a response like this:

Notice that the HTTP response code is 200 OK which indicates a successful completion of the method, and that the response from the method includes the value 11; which is the order number associated with the newly created order.

• The second test named Read Order and Items is pre-configured to retrieve order 11, so go ahead and execute it. You should see the order successfully retrieved, like this:

• Close the self-host program.