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 (this article) - 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

Managing Connections and Security

Every application has some type of configuration mechanism.  In the MS world this is typically a web.config or app.config file.  When an application boots, it reads settings like connection strings and makes them available through objects in the application.

EzAdo is a consumer of connection string settings very much like any other application, but it takes up a level.  By leveraging schemas to encapsulate stored procedures on the DB side, and following a simple naming convention in your application configuration files, all the pieces are there to conveniently manage the surface area of client applications by simply following convention.

Consider the following schema structure:

The schemas open, inspect, and secure are mapped to SQL logins open, inspect and secure.  The grants on the logins are limited to the specific schema by the same name, whereby the open login has access to the open schema and nothing else and so on.

To escalate procedures into the app or service layer, all we need to do is follow a simple convention where the connection string name is the schema name and the actual connection string has permissions to the schema.  Here is a sample .config file.

<connectionStrings>
    <add name="open" connectionString="Data Source=?;User=open;Password=?" />
        <add name="login" connectionString="Data Source=?;User=trusted;Password=?" />
        <add name="secure" connectionString="Data Source=?;User=secure;Password=?" />
        <add name="ezado" connectionString="Data Source=?;User=ezado;Password=?" />
      </connectionStrings>
  

How it works

When the ProcedureFactory loads, it reads the connections strings.  The connection string names are then passed to the procedures that retrieve the system objects.

CREATE PROCEDURE [ezado].[PROCEDURES]
(
@SCHEMAS ezado.Schema_Name_Table READONLY
)
-- Code to retrieve the procedures
CREATE PROCEDURE [ezado].[USER_DEFEFINED_TABLES]
(
@SCHEMAS ezado.Schema_Name_Table READONLY
)
-- code to return any types that are required by the procedures

The results of these procedure calls are then utilized to pre-build EzAdo procedures, map the appropriate connection string to the procedure, and provides the mechanism to allow procedures to be retrieved from the factory by schema, procedure name as follows:

 public static Procedure GetProcedure(string specificSchema, string specificName)
 

This returns an EzAdo procedure.

Also, since that procedure is fully aware of its parameter collection, type and size validation is now trusted to the service layer, with the added bonus of managing string case formatting.

Best way to illustrate this is by example.

CREATE PROCEDURE [open].[GET_PERSON]
(
/*Returns Json*//*Single Result*/@PERSON_ID INT
)
IF @PERSON_ID IS NULL THROW 50001, ‘PERSON_ID’, 1;
 SELECT
  COL as ‘name’,
  COL as ‘name…
 FROM dbo.PERSON
 WHERE dob.PERSON.PERSON_ID = @PERSON_ID
 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Since the procedure resides in the open schema, we need this connection string:

<add name="open" connectionString="Data Source=?;User=open;Password=?" />
    

This procedure is now available in the procedure factory via the following call:

var proc = ProcedureFactory.GetProcedure(“open”,”GET_PERSON”);
    

and this object is fully aware or the parameters and return types, with an added convenience of name mapping so the following is all supported:

proc["personId"] = 1;
proc["PersonId"] = 1;
proc["PERSON_ID"] = 1;
proc["@PERSON_ID"] = 1;
proc.SetValue<int>("personId", 1);
proc.SetValue<int>("PersonId", 1);
proc.SetValue<int>("PERSON_ID", 1);
proc.SetValue<int>("@PERSON_ID", 1);

Also, since the parameters are aware of the types and null values cause they are driven by the DB system objects directly, these assignments will all throw an exception in the service layer, and the invalid values will never make it to the DB.

proc["personId"] = null; //parameter cannot be null
proc["personIid"] = 399220929838;//parameter expects and int
proc.SetValue<string>("personId", "1"); //parameter expects generic type <int>

A couple important things to note

• Type and null checks are validated in the service layer.
• At no time are the connections strings exposed to developers.
• A lot less code needs to be written
• The system provides immediate feedback when assignments are invalid.