SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

EzAdo Part 4

By Alan Hyneman,

Since SQLServerCentral is dedicated to database professionals, many subscribers may find this subject off topic. However, since most of us need to deliver data to client applications, I’d like to share an approach that I initially developed for Oracle, later expanded to SQL Server, and finally arrived at this version, which was built to exploit new features in SQL Server 2016. Though the API doesn’t contain a significant amount of code, it is quite comprehensive in its offering, therefore I have decided to divide this article into 4 parts.

The API as well as the full source code, sample database, test and sample projects with full documentation is available at: https://github.com/ahhsoftware/EzAdoVSSolution

Pert 4 - REST - How to use the API to dynamically create web services

In the database world we have crud operations create, read, update, and delete. In the rest world we have web methods GET, PUT, POST, and DELETE. So how do these two correlate to one other, it’s really quite simple.

GET - Read

POST - Create or Insert

PUT - Update

DELETE - Delete

In the db world a stored procedure can always return a value, some developers choose rows affected, some choose (0 or 1) for true and false, and others simpley don’t bother at all. Part of moving to rest oriented procedures involves utilizing this return value to represent an Http Status code. I’m not going to cover every code here but instead focus on a select few.

200 - OK - means that everything is good - use for updates and deletes

201 - Record Created - the record was created

409 - Conflict - duplicate record or foreign key constraint violation

In addition to returning an HTTP Status code, we can utilize an output parameter to provide additional information. So lets see how we can apply what we learned so far about naming conventions, annotations, mappings and http statuses to build a rest oriented stored procedure that inserts a simple person record.

  CREATE PROCEDURE [open].[POST_PERSON]
(
    /*Returns Json*/
    /*Single Result*/
    @FIRST_NAME nvarchar(32),
    @LAST_NAME nvarchar(32),
    @EMAIL nvarchar(256),
    @MESSAGE_RESULT VARCHAR(256) OUTPUT
)
AS
BEGIN

SET NOCOUNT ON;

    IF @FIRST_NAME IS NULL THROW 50001, '@FIRST_NAME', 1;
    IF @LAST_NAME IS NULL THROW 50001, '@LAST_NAME', 1;
    IF @EMAIL IS NULL THROW 50001, '@EMAIL', 1;

    BEGIN TRY

    IF EXISTS(SELECT 1 FROM dbo.PERSON WHERE EMAIL = @EMAIL)
    BEGIN
        SET @MESSAGE_RESULT = ‘PERSON EXISTS’;
        RETURN 409;
    END;

    INSERT INTO [dbo].[PERSON]
    (
        [FIRST_NAME],
        [LAST_NAME],
        [EMAIL]
    )
    OUTPUT inserted.PERSON_ID INTO @RESULT_TABLE(PERSON_ID)
    VALUES
    (
        @FIRST_NAME,
        @LAST_NAME,
        @EMAIL
    )
    SELECT TOP 1 
        PERSON_ID personId,
        @FIRST_NAME firstName,
        @LAST_NAME lastName,
        @EMAIL email
    FROM @RESULT_TABLE
    FOR Json PATH, WITHOUT_ARRAY_WRAPPER;
    SET @MESSAGE_RESULT = ‘CREATED’
    RETURN (201);

    END TRY
    BEGIN CATCH
        — LOG EXCEPTION
        SET @MESSAGE_RESULT = ‘ERROR_OCCURED’ 
        RETURN (500);
    END CATCH
END;

The significant part here is that we are returning http status codes and those status codes can be returned to the calling application of the service. This also forces a consistent approach to processing results, and that’s a good thing.

So with our procedure in place lets have a look at some web api code, which is part of the .net framework and provides the infrastructure for building REST services. A full discussion of web API is beyond the scope of this article so here is a resource if you’d like to learn more https://www.asp.net/web-api. Here is the code.

 [HttpPost]
[Route(“api/open/{specificName}")] // This defines the url i.e. /api/open/person
public async Task<HttpResponseMessage> Post(string specificName)
{
    string requestJson = await Request.Content.ReadAsStringAsync();
    Procedure proc = ProcedureFactory.GetRestProcedure("POST", _specificSchema, specificName);

    proc.LoadFromJson(requestJson);

    string Json = proc.ExecuteJson();

    return ProcessProcedureResult(Json, proc);

    int returnValue = proc.ReturnValue<int>();

    HttpResponseMessage result = new HttpResponseMessage((HttpStatusCode)returnValue);

    result.Content = new StringContent(Json, Encoding.UTF8, "application/Json");

    return result;
}
[HttpPost] 

This is an annotation required web api - it says it accepts POST

  [Route(“api/open/{specificName}”)]

This defines the url of the web request. Values inside the curly braces are parameters so the route specified above equates to http”//www.yoursite/api/open/person as an http post to call our stored procedure.

  string requestJson = await Request.Content.ReadAsStringAsync();

This is capturing the post JSON and holding it in the variable requestJson - in this case the JSON would be something like {‘firstName’: ‘Joe’, ‘lastName’: ‘Smith’, ‘email’: ‘JoeSmith@gmail.com’}

  Procedure proc = ProcedureFactory.GetRestProcedure("POST", _specificSchema, specificName);

This is using EzAdo’s get rest procedure method to get the pre-built procedure out of the API - note that the method maps to the post, open is the schema, and the name is then equated to open.POST_PERSON - the procedure that was illustrated above.

  proc.LoadFromJson(requestJson)

Takes a JSON string and maps the values to the actual parameters. Again this process makes use of the mapping strategy where procedure parameter @FIRST_NAME will consume a post parameter ‘firstName’ and so on.

  string Json = proc.ExecuteJson();

Executes the Json and returns the result as a Json string.

  int returnValue = proc.ReturnValue<int>();

This is retrieving the output parameter from the procedure call.

HttpResponseMessage result = new HttpResponseMessage((HttpStatusCode)returnValue);

Web API results are returned as HttpResponseMessages. In this case we are assigning the return value as the status code.

  result.Content = new StringContent(Json, Encoding.UTF8, "application/Json");

This is assigning the JSON result as the content of the response body.

  return result;

returns the HttpResponseMessage with the assigned status and content back to the requesting application.

And that’s that. Notice the mapping and validation of parmaeters just works.  Again, that is a very good thing.

Parting Comments

So now what have we really done, and how does this help?

Well, from this point forward, the only thing you have to write to create another web service is the stored procedure, so if you wanted to create a service to insert person addresses you would just create the procedure as follows:

  CREATE PROCEDURE open.POST_PERSON_ADDRESS

which would then automatically be exposed as http”//www.yoursite/api/open/personAddress.

There are many more examples in the sample project, and many more places this could be implemented. You could for instance, derive from the procedure class and write code that did additional processing based on headers or tokens. You could create helper functions that do explicit processing for authentication and use the surface area of the schema to limit access as well. But the one thing you will not have to do, is all that silly code that creates the procedure, creates the parameters, assign the meta data for the parameter, wire up the connections, open the connection, execute, and blah blah blah.

In fact as I was preparing this article and the samples, it was actually quite painful to revert to those old ways for the sake of presentation. I’ve been using this approach for a few years now, and it is way more productive, less prone to errors, and keeps the service layer very thin so changes can quickly be applied at the procedure level, and those changes are propagated all the way back to the UI.

Thanks for reading, and happy coding.

Alan H. Hyneman

Data Architect - Bank Mobile Labs

Alan.H.Hyneman@gmail.com

 

This article is part of the series EzAdo:

Total article views: 730 | Views in the last 30 days: 4
 
Related Articles
FORUM

Creating view from stored procedure results

Creating view from stored procedure results

FORUM

Create procedure and filter the result

Create procedure and filter the result

ARTICLE

How to save results of a procedure with more than one result set

Sometimes a procedure returns more than 1 result set. The article describes how to save all result s...

BLOG

What is an Easy Way to Return Results from a CLR Stored Procedure?

Introduction What is an Easy Way to Return Results from a CLR Stored Procedure? The question soun...

FORUM

Procedure return value problem

Procedure return value problem

Tags
ado.net    
ezado    
json    
rest services    
sql server 2016    
 
Contribute