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], )
    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.