Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Populating a business logical layer from Stored Procedures

, 22 Sep 2014 CPOL
A brief look at a technique mapping tables to a basic business layer.
doPersistence-noexe.zip
doPersistence
doPersistence.csproj.user
doPersistence.csproj.vspscc
obj
Debug
Release
Properties
doLogicCode
bin
Debug
doLogicCode.vshost.exe.manifest
doLogicCode.csproj.vspscc
obj
x86
Debug
doLogicCode.frmCodeGenerator.resources
doLogicCode.Properties.Resources.resources
Properties
Settings.settings
doPersistence.zip
Test
doLogic
bin
Debug
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
Release
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Properties
doLogicCode
bin
Debug
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doLogicCode.vshost.exe.manifest
doPersistence.dll
doPersistence.pdb
obj
x86
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.exe
doLogicCode.frmCodeGenerator.resources
doLogicCode.pdb
doLogicCode.Properties.Resources.resources
GenerateResource.read.1.tlog
GenerateResource.write.1.tlog
ResolveAssemblyReference.cache
TempPE
Properties.Resources.Designer.cs.dll
Properties
Settings.settings
doPersistence
bin
Debug
doPersistence.dll
doPersistence.pdb
Release
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Properties
Test.suo
TestSite
Account
App_Data
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
Release
CSAutoParameterize
original
Account
transformed
Account
Database
DesignTimeResolveAssemblyReferencesInput.cache
Package
PackageTmp
Account
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
Scripts
Styles
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
TransformWebConfig
original
transformed
Account
Properties
Scripts
Styles
TestSite.csproj.user
dopersistenceexample.zip
doLogic.dll
doPersistence.dll
doPersistence.dll
doLogic.dll
doPersistence.dll
TestSite.dll
Global.asax
TestSite.csproj.user
dopersistencepocodynamicsqlexample.zip
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
Test.suo
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
TestSite.csproj.user
PersistenceTest20131129-noexe.zip
PersistenceTest_orig
Account
App_Code
App_Data
Bin
Persistence.pdb
Global.asax
Scripts
Styles
Persistence
bin
Debug
doPersistence.pdb
Persistence.pdb
Release
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.pdb
Persistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.pdb
TempPE
Persistence.csproj.user
Properties
PersistenceTest_orig.v11.suo
CodeGenerator
bin
Debug
doLogicCode.pdb
doLogicCode.vshost.exe.manifest
doPersistence.pdb
Persistence.pdb
CodeGenerator.csproj.vspscc
obj
x86
Debug
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.pdb
TempPE
Properties
Settings.settings
PersistenceTest20131129.zip
AjaxControlToolkit.dll
Persistence.dll
Persistence.pdb
Global.asax
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
Persistence.csproj.user
PersistenceTest_orig.v11.suo
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doLogicCode.vshost.exe.manifest
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
CodeGenerator.csproj.vspscc
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.exe
doLogicCode.pdb
Properties.Resources.Designer.cs.dll
Settings.settings
 
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.
 
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.
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.  PThe 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 from the system stored procs and maps parameter values.  GetCommand also caches that SqlCommand for subsequent calls.

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.

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

andrewbb@gmail.com
Architect
United States United States
No Biography provided

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 22 Sep 2014
Article Copyright 2012 by andrewbb@gmail.com
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid