Since SQLServerCentral is dedicated to database professionals, many subscribers may find this subject a little 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 and later expanded to SQL Server. I 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. They are:

• Part 1 Comparisons (this article) - 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 - 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.

Part 1 - Comparisons

EzAdo is about service layer code, or more specifically, the utilization of a naming convention, an API, and some creativity, to greatly reduce the level of effort required to deliver data to client applications. Plus, with the introduction of FOR JSON PATH in SQL Server 2016, it will also show techniques for exploiting that feature to streamline data from the database to end users with only a few lines of service layer code.

Let me start by saying I prefer all queries against the database to be in the form of stored procedures. This provides a clear boundary between the author of a given query and the person calling the code to execute the procedure. Arguments against that approach have always been that native ADO is tedious to write, as is the mapping from a reader to an object in loop. Let's start by solving those problems first.

EzAdo queries the system tables and uses the results of that query to pre-build objects that represent the ADO commands. These objects are fully aware of all parameters, type mappings, size, precision, scale, direction and so on. EzAdo also knows the type mapping from C# to SQL, and enforces that validation when the value of a parameter is set. It is also aware of the schema and connection string settings that will be used to execute the procedure. We’ll cover that later. For now, just know that you no longer define commands and attach parameters, you simply ask the API for a procedure by name, set the values, and call one of the execution methods.

In contrast native ADO requires defining the command, adding all the parameters, specifying the data type, size and other attributes, as well as the wrapper code for calling it. It is rare that you get all the parameters correct on the first pass, but after a few tries you get it ironed out, but that is a tedious process, especially when commands have a large set of parameters.

These two examples call the same stored procedure - open.GET_ORDER which has an input parameter, output parameter, and returns the data using FOR JSON PATH.

EzAdo approach

    Procedure proc = ProcedureFactory.GetProcedure("open", "GET_ORDER");
    proc.SetValue<int>("orderId", 1);
    var json = proc.ExecuteJson();
    var message = proc.GetValue<string>(“messageResult”);

Native ADO approach

    using (SqlConnection cnn = new SqlConnection(“connection string"))
    {
        var message = null;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = cnn;
            cmd.CommandText = "[open].[GET_ORDER]";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //defining these parameters is alway prone to errors 
            cmd.Parameters.Add("@ORDER_ID", SqlDbType.Int, 8).Value = 1;
            cmd.Parameters.Add("@MESSAGE_RESULT", SqlDbType.VarChar, 256);
            cmd.Parameters[“@MESSAGE_RESULT”].Direction = ParameterDirection.Output;
            cnn.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while(rdr.Read())
                {
                    //larger json results will return as a single column reader
                    bldr.Append(rdr.GetString(0));
                }
                rdr.Close();
                message = cmd.Parameters[“@MESSAGE_RESULT”].value;
            }
            cnn.Close();
        }
    }
    message = bldr.ToString();

A Few Key Points to Remember

With EZADO, there is no more defining command and parameters - commands are pre-built waiting for values to be set. There is also no more managing connections. Each procedure is aware of the connection string. There is none of the wrapping code - the API uses the connection as efficiently as possible.

In Part 2 we will start to explore the Fundamentals and look at annotations, a key element of the API.