Creating a business logical layer

Data persistence with one line of code.

doPersistence

[Persistable("Customers", "CustomerId", true)] //DataSource, PrimaryKeyName, UseDirectSql (if false, SPs are required)
public class Customer { ...

public class CustomerList : PersistentList<Customer>

Highlights

• Persists any object to any database (SqlServer plugin is done)
• If using dynamic Sql, properties/columns must be the same name
• If using stored procedures, column name and data types can be different
• PersistentList<T> provides an interface to result sets with data pagination and bulk copy
• PersistentList<T> also exposes List<T> and ToDataTable() for LINQ expressions
• AllowsDirectSql=false (default) forces all data access through stored procedures

Performance

• Loads 500 objects in 54 milliseconds (12 milliseconds, if paginated)
• ~10,000 objects per second
• ~2,000 objects per second if loaded one row at a time

Customer class

using System;
using System.Collections.Generic;
using System.Text;

using doPersistence;

namespace doLogic
{
    [Persistable(DataSource="Customers", PrimaryKeyName="CustomerId", UseDirectSql=true)]
    public class Customer
    {
	public int CustomerId { get; set; }
	public string Name { get; set; }
	public int CustomerType { get; set; }

	public void Load() { Database.Load(this); }
	public void Store() { Database.Store(this); }
	public void Delete() { Database.Delete(this); }
    }
}

CustomerList class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using doPersistence;

namespace doLogic
{
    public class CustomerList : PersistentList<Customer>
    {
	//constructors
	public CustomerList() { }

	public CustomerList(string action)
	{
		this.Load(action);
	}
    }
}

InvoiceList class

using System;
using System.Collections.Generic;
using System.Text;

using doPersistence;

namespace doLogic
{
    public class InvoiceList : PersistentList<Invoice>
    {
        public InvoiceList() { }

        public InvoiceList(int customerId)
        {
            this.CustomerId = customerId;
            this.Load("ForCustomer");
        }

        //transient properties (mapped to sp parameters)
        public int CustomerId { get; set; }
    }
}

Usage

Customer c = new Customer();
c.CustomerId = 1;
c.Load();

//update CustomerId 2
Customer c2 = new Customer(2);
c.CustomerType = 3;
c.Store();

//create new Customer
Customer newCust = new Customer();
c.Name = "Maylander";
c.Store();

//retrieve new identity value
int newId = c.CustomerId;

//list customers (page 2, 10 per page)
CustomerList list = new CustomerList();
list.PageNumber = 2;
list.PageSize = 10;
list.Load();
grdCustomers.DataSource = list;
grdCustomers.DataBind();

//list all customers with invoice summary
CustomerList customers = new CustomerList("WithSummary");
grdCustomers.DataSource = (from customer in customers
                           orderby customer.Name
                           select customer).Take(10);
grdCustomers.DataBind();

//alternatively bind it to a DataTable:
grdCustomers.DataSource = summary.ToDataTable();
grdCustomers.DataBind();

//uses Sql server's bulk store to a temp table and merges changes into production
list.StoreBulk();

//update all items in list
foreach (Customer item in list)
    item.Field2 = "some value";
list.Store();

doPersistence (code walkthrough and conventions)

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.

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.

The rest of this page describes a product built in VB6 using some of the above design principles

ASP page:

Waitress class:

Waitress List class:

Stored procedures:

Abstract classes (can modify to suit the developer, but works as is):

System DLL object model (ABSystem.dll is ~400k and runs in-process):

Requirements:

Description: