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

EzAdo Part 3

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 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:

dbo

open

inspect

secure

TABLE_A

open.PROC_A

inspect.PROC_A

secure.PROC_A

TABLE_B

open.PROC_B

inspect.PROC_B

secure.PROC_B

TABLE_C

open.PROC_C

inspect.PROC_C

secure.PROC_C

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.
 

This article is part of the series EzAdo:

Total article views: 943 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Connection Strings 101

This article introduces connection strings and suggests using MDAC to easily write efficient connect...

FORUM

Error on Connection String

Error on Connection String

FORUM

Extracting schema from a connection manager

connection manager schema extract

FORUM

Create procedure permission in schema

Create procedure permission in schema

FORUM

Stored procedures and schemas???

Same stored procedure over different schemas

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