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

EzAdo - Part 2

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 four parts.

  • Part 1 -  Comparisons - Compares EzAdo to existing technologies.
  • Part 2  - Fundamentals (this article) - Covers the basics including annotations and conventions.
  • Part 3 - Managing connections and schemas - addresses security and configuration.
  • Part 4 - REST - 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

Annotations

Most computer languages provide a mechanism to annotate units of code.  Some annotations, like this one provide information to the executing code.

[Conditional("DEBUG")]
public void DebugOnlyFunction()
{
    // code that will execute only if the condition DEBUG is set
}

We need to establish the same functionality in SQL server so the executing code (EzAdo) can examine and utilize these instructions.  We can annotate at the procedure level with the following comments:

  1. /*Returns Json*/ - identifies the procedure as one that utilizes FOR JSON PATH
  2. /*Single Result*/ - identifies the procedure as returning a single row
  3. /*Non Query*/ - identifies the procedure as a non query type
  4. /*Always Encrypted*/ - alters the connection with appropriate encryption setting

And at the parameter level by simply following this syntax for parameters:

  IF @ORDER_ID IS NULL THROW

which identifies the parameter in the API as non-nullable.

Conventions

  • Case is important particularly at the DB level.
  • Schema names should be lower case
  • Procedure Names should be UPPER_UNDERSCORE_CASE
  • Parameter Names should be UPPER_UNDERSCORE_CASE

When EzAdo consumes the information from system objects to pre-build the commands, it creates a mapping system to facilitate setting values where the source may be in camelCase, ProperCase, or UNDERSCORE_CASE.

It is important to note is that mappings from objects adhere to UPPER_UNDERSCORE = ProperCase for objects where object.PropertyName = Command PARAMETER_NAME

Mappings from JSON adhere to UPPER_UNDERSCORE = camelCase for JSON where parameter = PARAMETER

So again, let's look at an example of how EzAdo makes the service layer code so much simpler.  Consider the following stored procedure.  Note this is in no way production code.

CREATE PROCEDURE [open].[POST_PERSON]
(
    @FIRST_NAME nvarchar(32),
    @LAST_NAME nvarchar(32),
    @EMAIL nvarchar(256),
)
AS
BEGIN

    INSERT INTO [dbo].[PERSON]
        ( [FIRST_NAME],[LAST_NAME], [EMAIL])
    VALUES
        ( @FIRST_NAME, @LAST_NAME, @EMAIL)
        
    SELECT TOP 1 
        PERSON_ID personId,
        @FIRST_NAME firstName,
        @LAST_NAME lastName,
        @EMAIL email
    FROM
        dbo.PERSON 
    WHERE
        PERSON_ID = SCOPE_IDENTITY()
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

    RETURN 200;

END;

And the cumbersome ADO code to call it.

using (SqlConnection cnn = new SqlConnection(“your connection"))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "open.POST_PERSON";
        cmd.Parameters.Add("@FIRST_NAME", SqlDbType.NVarChar, 32).Value = "firstName";
        cmd.Parameters.Add("@LAST_NAME", SqlDbType.NVarChar, 32).Value = "lastName";
        cmd.Parameters.Add("@EMAIL", SqlDbType.NVarChar, 256).Value = "email@email.com";
        cmd.Connection = cnn;
        cnn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while(rdr.Read())
        {
            bldr.Append(rdr.GetString(0));
        }
        rdr.Close();
        if(cmd.Parameters[0].Value != null)
        {
            procedureResult = (int)cmd.Parameters[0].Value;
        }
        cnn.Close();
    }
}

Again EzAdo makes the code much simpler for the consumer of the procedure - notice the case of the parameter setters, accepting the parameter name in the case the devloper is most comfortable with.

Procedure proc = ProcedureFactory.GetProcedure("open", "POST_PERSON");
proc.SetValue("firstName", "a");//camel Case
proc.SetValue("LastName", "a");//ProperCase
proc.SetValue("EMAIL", "a@a.com");Upper_Underscore
string json = proc.ExecuteJson();
int result = proc.ReturnValue();

The mapping infrastructure also provides the ability to automap from an object:

Person person = new Person() 
{ 
    FirstName = "John",//Maps to @FIRST_NAME
    LastName = "Doe", //Maps to @LAST_NAME
    Email = “JohnDoe@gmail.com” //Maps to @EMAIL
};

Procedure proc = ProcedureFactory.GetProcedure("open", "POST_PERSON");
proc.LoadFromObject(person);
string json = proc.ExecuteJson();
int result = proc.ReturnValue();

To an object:

Person person = Proc.ExecuteJson<Person>()

From JSON:

string Json = "{'firstName': 'Joe', 'lastName': 'Shmo', 'email': 'From@idaho.com'}";
proc.LoadFromJson(Json);

Again, the primary goal here is to free the developer from the tedious and error prone method of setting parameters with native Ado.

 

This article is part of the series EzAdo:

Total article views: 1178 | Views in the last 30 days: 1
 
Related Articles
FORUM

stored procedure parameters question

stored procedure parameters question

FORUM

Need help on HTML formatted email from Stored Procedure

Stored Procedure email like HTML format

FORUM

stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequent execution it give instantaneous results regardless of parameter values

stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequen...

FORUM

Stored procedure- help with date parameter validation

Stored procedure- help with date parameter validation

FORUM

Stored procedures with output parameters

Stored procedures with output parameters

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