Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Framework for execute-only SP access

Author  Topic  andrewbb Starting Member 1 Post Posted - 2012-04-08 : 20:24:58 This article describes an approach to software development that disallows direct table access. I am not a DBA however have faced some issues DBAs face. Suggestions welcome. doPersistenceExample.zip The zip file contains a VS 2010 solution and a SQL script to create a PersistenceTest database. It is intended to describe some basic concepts. doLogic is the business logical layer. doPersistence is the automated mapping layer. TestSite is the UI. Optionally, one might also add a doBusiness layer. Download doPersistenceExample.zip Introduction This article discusses a structured data access methodology which provides a rich and safe business layer that can be customized to suit most developer's needs. The article suggests a technique using carefully designed naming conventions for stored procedures and a data access layer that translates properties to and from the SP parameters/resultsets. From there a business logic layer can be built that allows lazy-loaded objects, a clean database interface with execute-only permissions, and n-layer concepts for MVC-type of implementations. In the example, all persistable classes derive from abstract classes, however it can be easily modified if POCOs are desired. Its current design is not intended to be an academic discussion of design patterns, it is an evolution of a real-life example that can and has been used in production environments requiring security and performance. It can be implemented on legacy systems due to the stored procedure naming convention (SpPrefix) while presenting a stored procedure interface to the business logical layer that can hide internal data structure flaws without changing the existing data structure. Background Mapping stored procedures directly to objects (entities/items) leverages SQL Server's organization, security, and optimization. Referential integrity is not copied into memory from database structures so the class becomes more than a representation of a table. A list of objects (for lack of a better term) becomes more than a list of rows in a table. Frequently table/object mapping becomes little more than intellisensed database column access. Rather, mapping stored procedures to objects/items/entities allows an abstraction and interface outside of direct table access. Column names can be changed, data types returned differently, joins can be made, summaries for read-only fields, etc. Execute-only permissions for production also increases security at the application level reducing chances of data corruption. Stored procedures This section describes the stored procedures. See "doPersistence maps parameters/resultsets to class properties" below for a description of the doPersistence layer and abstract c# classes. For SQL Server 2005 and higher and assuming a user called 'sp_only' exists: use SomeDatabase create role db_spexecutor grant execute to db_spexecutor exec sp_addrolemember 'db_spexecutor', 'sp_only' With the above code, the 'sp_only' database user is granted execute-only permissions to all stored procedures in the database including new ones added later. The 'sp_only' user cannot access tables directly and the stored procedures are the only interface to the database. FYI, for those who use LINQ or other dynamic SQL creation tool, another user/connection can be deployed on a production server. Types of stored procedures Each object has 4 associated types of stored procedures in a structured naming convention: 1. load (prefix_load_ClassName[Action]) 2. store (prefix_store_ClassName) 3. delete (prefix_delete_ClassName) 4. list (prefix_list_ClassName[Action]) A 5th type might be a "merge" stored procedure for bulk imports. Load, store, and delete act upon a single object/entity/item. Example: ab_list_CustomerInZipCode 'ab_' is the prefix for the application or company. 'list_' is hard-coded which indicates more than one row will be returned. 'ClassName' is the name of the Type of the business logic class 'Action' is an optional string to further define the stored procedure to be called ("InZipCode") The doLogic layer makes a call to the stored procedure via doPersistence like this: CustomerList list = new CustomerList(); list.ZipCode = 33812; list.LoadList("InZipCode"); ZipCode is the parameter to the stored procedure and InZipCode is the optional Action to define which SP to call. Loading a single object create procedure ab_load_Customer @CustomerId int as select * from Customer where CustomerId = @CustomerId The following is a more realistic example with data type conversion, display-only field, and column name changes: create procedure ab_load_Customer @CustomerId int as select CustomerId = ID, CustomerName = Name, Address, City, State, Zip, CustomerSince = convert(varchar(10), CustomerSinceDate, 102), c.CustomerTypeId, CustomerType = cs.Name from Customer c join CustomerTypes cs on cs.CustomerTypeId = c.CustomerTypeId where CustomerId = @CustomerId The c# code that executes this via doPersistence: Customer c = new Customer(); c.CustomerId = 3; c.Load(); Properties are used for both parameter values and column result sets. CustomerId is mapped to @CustomerId, the stored proc is called, then each column returned is mapped to properties of the object. The stored procedure in this sense performs the function of the XML mapping files used in many ORMs. Storing a single object create procedure ab_store_Customer @CustomerId int output, @Name varchar(50), @ZipCode int as if exists(select 1 from Customer where CustomerId = @CustomerId) update Customer set Name = @Name, ZipCode = @ZipCode where CustomerId = @CustomerId else begin insert into Customer ( Name, ZipCode ) values ( @Name, @ZipCode ) set @CustomerId = scope_identity() end return @CustomerId The above is a simple case of the object mapping to a single table, however could be more complex with complex udts or relational information or updating other tables. Example that calls the above SP via doPersistence: Customer c = new Customer(); c.Name = "Abc Corporation"; c.ZipCode = 33812; c.Store(); int newIdentityValue = c.CustomerId; Deleting a single object create procedure ab_delete_Customer @CustomerId int as delete from Customer where CustomerId = @CustomerId --or an alternative delete implementation: create procedure ab_delete_Customer @CustomerId int as update Customer set Active = 0 where CustomerId = @CustomerId --cascading deletes could occur here as well C# code that deletes a single object via doPersistence: Customer c = new Customer(); c.CustomerId = 3; c.Delete(); Note the 2nd example where a deletion does not occur and simply sets a flag. In a real-life example, all Load stored procedures might only return Customers where Active = 1. This simplifies and protects the data and is one less thing for the .NET business layer developer to remember. Listing objects from the database -- shows all Customers create procedure ab_list_Customer as select * from Customer -- Active only create procedure ab_list_CustomerActiveOnly as select * from Customer where Active = 1 --queried Customers create procedure ab_list_CustomerInState @State varchar(50) as select * from Customer where State = @State --show child rollup information create procedure ab_list_CustomerWithSummary as ;with summary as ( select CustomerId, InvoiceCount = count(*), InvoiceTotal = sum(Amount) from Invoice group by CustomerId ) select c.*, s.InvoiceCount, s.InvoiceTotal from Customer c left join summary s on s.CustomerId = c.CustomerId The 4th example above retrieves child summary information from related tables for display purposes. This removes the need for loading child objects from the database and leverages SQL Server's unique capabilities for rapid sums and counts. C# code calling the above SP via doPersistence: CustomerList list = new CustomerList(); list.LoadList("WithSummary"); foreach (Customer c in list) { int numberOfInvoices = c.InvoiceCount; } Properties on the CustomerList object correspond to parameters on the various "list" stored procedures. Each stored procedure can have its own custom parameters that must be set on the class. For example: CustomerList c = new CustomerList(); c.ZipCode = 33812; c.LoadList("InZipCode"); c.Clear(); c.State = 'FL'; c.LoadList("InState"); c.Clear(); c.LastName = 'white'; c.State = 'OR'; c.LoadList("ByLastNameInState"); doPersistence maps parameters/resultsets to class properties doPersistence defines three abstract classes and a SqlPersistence static class that performs the column/property or property/parameter mapping. Currently all classes that are Persistable must derive from the abstract Persistent and PersistentList classes, however, this could easily be modified for POCOs if desired. The SqlPersistence.Load, Store, Delete, and List methods can operate on any class that derives from Persistent or PersistentList (Persistable). SqlPersistence assumes a stored procedure exists with a structured naming convention. For example, the load_Customer stored procedure maps to a Customer object of type Persistent. The Customer class contains a property CustomerId which maps to @CustomerId in the stored proc. All columns returned by the stored proc have a corresponding property on the Customer class with the same name. If the name is different than the table column name it can be changed in load_Customer. This provides the first layer of abstraction from direct table access. SqlPersistence.Load public static void Load(Persistent o, string action, string connString) { string spName = SpPrefix + "load_" + o.GetType().Name + action; SqlCommand cmd = GetCommand(spName, connString); SetParameterValues(cmd, o); using (SqlConnection cn = new SqlConnection(connString)) { cmd.Connection = cn; cn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) MapToInstance(reader, o); else throw new ApplicationException("No single result returned by " + spName); } } The method above works for all objects that derive from Persistent. It takes values in the object's properties and sets them on the stored procedure's parameters. The result set is then mapped back to the object. SqlPersistence.GetCommand builds the SqlCommand object with SqlParameters from the system stored procs (sys_ParametersForSP & sys_StructureForTable). GetCommand also caches that SqlCommand for subsequent calls. Each call to doPersistence Clones a copy of that SqlCommand for thread safety. SqlPersistence.SetParameterValues sets SqlParameter values from properties of the same name with some data type matching. It will throw an error if properties or columns or parameters are missing. In other words, it performs its own unit testing with every run. MapToInstance is similar and places the resultset columns into properties on the object. SqlPersistence.Store public static int Store(Persistent o, string connString) { string spName = SpPrefix + "store_" + o.GetType().Name; SqlCommand cmd = GetCommand(spName, connString); SetParameterValues(cmd, o); int rowsAffected = 0; using (SqlConnection cn = new SqlConnection(connString)) { cmd.Connection = cn; cn.Open(); rowsAffected = cmd.ExecuteNonQuery(); //set output parameter on object (eg. identity key) foreach (SqlParameter p in cmd.Parameters) { if (p.ParameterName.ToLower() == "@" + o.PrimaryKeyName.ToLower() && p.Direction == ParameterDirection.Input) throw new ApplicationException("Primary key must be set as an output parameter in the stored procedure: " + spName); if (p.Direction != ParameterDirection.Input) { string name = p.ParameterName; if (name.StartsWith("@")) name = name.Substring(1); o.SetPropertyValueByName(name, p.Value); } } } return rowsAffected; } The Store method is similar in concept to the Load and Delete methods except must also handle Primary Key retrieval and output parameters. Properties on the class must exist for each SQL parameter. It throws an error if an output parameter does not exist. In a similar way, all database contraint violations are thrown directly to the .NET developer who can handle all constraints during development. SqlPersistence.LoadList public static int LoadList(PersistentList list, string action, string connString) { string spName = SpPrefix + "list_" + list.GetItemType().Name + action; SqlCommand cmd = GetCommand(spName, connString); SetParameterValues(cmd, list); using (SqlConnection cn = new SqlConnection(connString)) { int rowCount = 0; cmd.Connection = cn; cn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Persistent newItem = list.CreateNewItem(); MapToInstance(reader, newItem); list.Add(newItem); rowCount++; } return rowCount; } } The action parameter allows loading a list by various criteria. PersistentList object properties are mapped to SQL parameters to provide querying by date or value or ranges or sorting. The action parameter above refers to the name of the stored proc. For example, the stored procedure list_CustomerInZipCode: A CustomerList class is populated with an action "InZipCode" and the parameter ZipCode is a property on the CustomerList class. The above might be called like this: CustomerList list = new CustomerList(); list.ZipCode = 33812; list.LoadList("ForZipCode"); That calls: exec list_CustomerForZipCode 33812 Persistent & PersistentList abstract classes A business logical layer can be built by implementing Persistent and PersistentList classes. Persistent is the base abstract class for a single item/object/entity that can be saved or loaded from a data store. PersistentList is the abstract class for a collection of items/objects/entities. That list is defined at the logical layer or the presentation layer to load all items or a subset. Persistent.cs public abstract class Persistent : Persistable { public abstract string PrimaryKeyName { get; } public abstract int PrimaryKeyValue { get; set; } public virtual void Store() { SqlPersistence.Store(this, SqlPersistence.ConnectionString); } public virtual void Delete() { SqlPersistence.Delete(this, SqlPersistence.ConnectionString); } public virtual void Load() { this.Load(""); } public virtual void Load(int id) { this.PrimaryKeyValue = id; this.Load(""); } public virtual void Load(string action) { SqlPersistence.Load(this, action, SqlPersistence.ConnectionString); } } Abstract classes wrap SqlPersistence and provide the root for a business logic layer of classes. In the example project this refers to the doLogic project. For example, the logical class Customer derives from Persistent. This brings the base Store, Load, and Delete methods as well as abstract properties to let the Persistence layer know the primary key property for that class. PersistentList.cs public abstract class PersistentList : Persistable { List<Persistent> _list = new List<Persistent>(); public abstract Persistent CreateNewItem(); public abstract Type GetItemType(); //public int PageNumber; //public int PageSize; //public int PageCount; public List<Persistent> GetList() { return _list; } public int LoadList() { return this.LoadList(""); } public int LoadList(string action) { return this.LoadList(action, 0, 0, 0); } public int LoadList(string action, int pageCount, int pageSize, int pageNumber) { return SqlPersistence.LoadList(this, action, SqlPersistence.ConnectionString); } public int Count { get { return _list.Count(); } } public void Add(Persistent o) { _list.Add(o); } public void Remove(Persistent o) { _list.Remove(o); o.Delete(); } public void Store() { foreach (Persistent o in _list) { o.Store(); } } public void StoreBulk() { SqlPersistence.StoreBulk(this.GetItemType().Name, SqlPersistence.ToDataTable(this)); } public DataTable ToDataTable() { DataTable table = this.ToDataTableStructureOnly(); foreach (Persistent o in this.GetList()) { DataRow row = table.NewRow(); foreach (DataColumn col in table.Columns) { object value = o.GetPropertyValueByName(col.ColumnName); if (value == null && col.AllowDBNull) value = DBNull.Value; row[col.ColumnName] = value; } table.Rows.Add(row); } return table; } public DataTable ToDataTableStructureOnly() { DataTable table = new DataTable(); foreach (PropertyInfo pi in this.GetItemType().GetProperties()) { Type t = pi.GetType(); DataColumn col = new DataColumn(); col.ColumnName = pi.Name; col.DataType = pi.PropertyType; table.Columns.Add(col); } return table; } public Persistent ItemById(int id) { foreach (Persistent o in _list) { if (o.PrimaryKeyValue == id) return o; } return null; } } Properties on this class usually map directly to parameters in a queryable List stored procedure. You might place sort orders on this that the stored procedures use in case statements. PersistentList.Store loops through its items storing one item/object at a time which is good for multi-user databases because it allows breathing time between calls. However, for importing large amounts of data a StoreBulk routine with a SQL merge stored procedure is implemented. StoreBulk accepts a DataTable in the structure derived from either the table structure or the class property structure. Rapid bulk add via a temporary table and merged into the production table. SqlPersistence.StoreBulk public static void StoreBulk(string tableName, DataTable table) { using (SqlConnection conn = new SqlConnection(SqlPersistence.ConnectionString)) { conn.Open(); //create temporary table string sql = String.Format("select * into #{0} from {0} where 1=0", tableName); SqlCommand cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); //bulk copy using (SqlBulkCopy bulk = new SqlBulkCopy(conn)) { bulk.DestinationTableName = "#" + tableName; bulk.WriteToServer(table); } //merge temp table into production cmd = new SqlCommand(SpPrefix + "merge_" + tableName, conn); cmd.ExecuteNonQuery(); } } Calling this from the doLogic layer: CustomerList c = new CustomerList(); c.LoadFromTextFile("c:\importfile.txt"); c.BulkStore(); Note the merge stored procedure which leverages SQL Server's ability to update or insert based on a record's existence. The DataTable is filled automatically from the PersistentList class so any class that derives from PersistentList can bulk store its items. The above describes a technique for loading and storing individual objects and collections of such. A logical business layer can be built from here. See the attached sample solution which includes a script to create a database and a basic implementation of a Customer/Invoice relationship. This includes lazy-loading and storing. Building a Logical Business Layer In the sample solution included, Customer and Invoice both derive from Persistent. CustomerList and InvoiceList derive from PersistentList. A clean separation of business and data layers can be created. While this can be used as the Business layer, another layer could be built upon this logical layer so the layers involved are: 1. data tables in SQL Server 2. stored procedures transforming columns and including joins to foreign keys 3. doPersistence handling mapping of parameters and resultsets to object properties 4. doLogic where business rules can be implemented 5. an optional doBusiness layer which can wrap the doLogic classes for a higher level abstraction 6. an optional MVC-type of pattern can be applied to either the doLogic layer or the doBusiness layer. Customer class public class Customer : Persistent { public Invoice AddInvoice(Decimal amount) { if (this.CustomerId < 1) throw new ApplicationException("This customer has not been assigned a primary key. Unable to add invoice."); Invoice i = new Invoice(this); i.Amount = amount; return i; } //override Persistent.Store to save invoices with the customer public override void Store() { base.Store(); if (_InvoiceList != null) _InvoiceList.Store(); } //constructors public Customer() { } public Customer(int id) { PrimaryKeyValue = id; this.Load(); } //implement Persistent abstract method public override string PrimaryKeyName { get { return "CustomerId"; } } public override int PrimaryKeyValue { get { return CustomerId; } set { CustomerId = value; } } //lazy loaded children InvoiceList _InvoiceList = null; public InvoiceList Invoices { get { if (_InvoiceList == null) _InvoiceList = new InvoiceList(this.CustomerId); return _InvoiceList; } } //transient properties public int InvoiceCount { get; set; } public Decimal InvoiceTotal { get; set; } //persistent properties public int CustomerId { get; set; } public string Name { get; set; } } Customer derives from Persistent so contains Load, Store, and Delete methods. It also must implement PrimaryKeyName and PrimaryKeyValue to tell the doPersistence layer how to store and load the object. A lazy-loaded InvoiceList is available. Summary properties are contained for optionally calling list_CustomerWithSummary. InvoiceList class public class InvoiceList : PersistentList { //constructors public InvoiceList() { } public InvoiceList(int customerId) { this.CustomerId = customerId; this.LoadList("ForCustomer"); } //implement Persistent abstract methods public override Persistent CreateNewItem() { return new Invoice(); } public override Type GetItemType() { return typeof(Invoice); } //transient properties (mapped to sp parameters) public int CustomerId { get; set; } } InvoiceList derives from PersistentList so can be bulk stored, stored individual items one at a time, loading of the list by various criteria. For example, loading a list from the stored procedure list_InvoiceForCustomer is called by: InvoiceList invoices = new InvoiceList(myCustomer.CustomerId); One implementation of an InvoiceList constructor might be: public InvoiceList(int customerId) { this.CustomerId = customerId; this.LoadList("ForCustomer"); } "ForCustomer" is the action verb passed to SqlPersistence.LoadList. The property CustomerId is mapped to the @CustomerId parameter in the stored procedure: list_InvoiceForCustomer. Display a list of Customers on an ASP.net page //list all customers with invoice summary CustomerList summary = new CustomerList("WithSummary"); grdSummary.DataSource = summary.ToDataTable(); grdSummary.DataBind(); Note the ToDataTable is a method on the PersistentList abstract class which creates a DataTable from either a SQL Server table structure or the properties on the item class. It fills the data that can be used to bulk store for imports or display in a grid as above. Conclusion The above article describes one technique for data retrieval and storage directly through stored procedures. The stored procedures provide the only interface to the underlying tables while allowing a rich, flexible framework for loading and storing data in a secure environment. For more robust security the Persistence layer might include a logged in user call into every stored procedure for row level security in the database. Much can be added and the doLogic layer could easily be turned into POCOs rather than deriving from Persistent and PersistentList. That is more of a style choice and straightforward to cut and paste a few methods. I will likely update the article in response to any feedback. Possible reorganization might be required to quickly grasp the concepts.