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.
- Part 1 - Comparisons - Compares EzAdo to existing technologies.
- Part 2 - Fundamentals - Covers the basics including annotations and conventions.
- Part 3 - Managing connections and schemas - addresses security and configuration.
- Part 4 - REST (this article) - how to use the API to dynamically create web services.
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],
)
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
