Populating a business logical layer from Stored Procedures

• Download source code - 730.9 KB

1. Run the SQL script to create the database.
2. Change the startup project to TestSite.
3. Enjoy!

Introduction 

This is an update to the original article below. The core technique and code are the same, however the interface was simplified to a single attribute and a generic list class.

Customer class

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

using doPersistence;

namespace doLogic
{
    [Persistable(DataSource="Customer", 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); }
    }
}

Persists lists of objects by inheriting from PersistentList<T>:

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);
	}
    }
}

Add properties to the CustomerList that correspond to parameters in Stored Procedures. Loads 500 objects in 54 milliseconds (9 objects/ms). The Customer class used in testing has 30 fields comprising most datatypes.

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; }
    }
}

The constructor calls the Stored Proc: list_InvoiceForCustomer @CustomerId.

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 summary = new CustomerList("WithSummary");
grdSummary.DataSource = summary.ToDataTable();
grdSummary.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();

Highlights

• Persists any object that has an integer ID
• Choice of direct SQL statements or Stored Procedures (load, store, delete)
• Data pagination
• Extremely fast bulk copy
• All datatypes supported including binary

Maps properties to/from either a Stored Procedure or database table. Any class with the Persistable attribute can be saved to a database without any knowledge of the data layer.

• Download the latest doPersistence.zip

doPersistenceExample (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 developers' 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 four 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 fifth 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 Stored Procedure 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 Procedure 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 second 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 fourth 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 Procedure. All columns returned by the Stored Procedure 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 Procedures (sys_ParametersForSP and 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 we 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 constraint 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 and PersistentList abstract classes

A business logical layer can be built by implementing the 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 a 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.