Saturday, July 22, 2017

From a conversation on Facebook...

Due to IT being so new, the entire industry overlooked their own design principles and security measures. In 1999 or so, we knew software was going to be easy to write in the future. In fact, we thought that by 2010, most software would be complete with software developers being very specialized.
Manage
Andrew Brown Unfortunately, hacking culture took root on the internet and 'hacking principles' were taught throughout the corporate world. Many of the design principles in corporate America are based on hacking. The above software design returns the IT industry to safe practices.
Manage
Andrew Brown We, as the IT industry, must return to good programming principles of separating data from application and push SQL back into the database, where it belongs. I'm proposing a standard of Load/Store/Delete. That can be secured and monitored, eliminating nearly all hacks.

Wednesday, July 19, 2017

Proposal for a new Data Access Standard: Load/Store/Delete (sent to NIST yesterday)

Adopting a new standard will force a re-write of all Federal software.  Push SQL back into the database where it belongs.
  • All software is built to textbook standards.
  • Load/Store/Delete follows proper architectural n-tier design
  • Most systems can be re-written side-by-side with no downtime.
  • Supports surveillance and law enforcement while providing accountability.
  • Sufficient savings can follow through on Congress' 'borrow and pay from savings' plan.
  • Allows control into the, otherwise opaque, 'cloud'.
  • Fits into NIST's Cybersecurity Framework
  • Encourages distributed responsibility.
  • Easier to write, faster, with fewer bugs.  

Proposal
All databases must expose an interface:  Load, Store, & Delete.  NIST reviews each vendors' implementation and decides if the interface is sufficient to meet the standard.

With a standard set, all Federal software would have to meet that spec.

Chris Liddell of the Office of American Innovation can assist in defining timelines for re-writing all Federal software.

Since "Load, Store, & Delete" creates enormous efficiency and accelerates the development cycle, this could be finished in months, including a re-write of a all IRS' software.


Proof-of-concept: System.Persistence

Designed for SQL Server/.NET has been in use for over 15 years.  ORB is supported.  ORM is supported.  See attached for how easy software is to write today.  Software is not rocket-science any more and this approach brings software development to the 'power user'.


Someone please forward this to Chris Liddell, as I do not have his contact information.

Please contact me for further information.  This can be done today.

Thanks,
Andrew B. Brown
4706 Elon Crescent
Lakeland, Florida 33810

Github: System.Persistence for .NET

Proof of concept using the Load/Store/Delete interface.

https://github.com/keihatsu1/LoadStoreDeleteDemo

Friday, July 14, 2017

This would have been known and caught had System.Persistence been the standard.

http://www.mintpressnews.com/analysis-dnc-servers-locally-hacked-russian-interference-unlikely/229699/

Thursday, September 24, 2015

Three pipes to the database

Database.Store
Database.Delete
Database.Load

Supports Windows authentication, mixed, role-based, as well as custom security for individual users.

Deep linking/nesting is not supported for security reasons and to create a conceptual separation between database and application.  A 'stored procedure set' vs. table-mapping.

Monday, September 14, 2015

Data access in THREE lines of code

Email me for the link (with source code) andrewbb@gmail.com Persistence.dll should be self-explanatory.

Any database. Any language. (Currently written for C#/SQL Server)

Wednesday, June 24, 2015

Imagine you are James Bond's wife.

And James Bond goes missing and you want to review his assignment records and last known whereabouts.  But you need to verify the computer data can be trusted.  As you are probably not a database or computer expert, you'd have to be able to review quickly, with or without assistance.

That is why code needs to be clear, concise, and human-readable.  Secondly, you need to know who the database expert is before the mission.

Saturday, June 20, 2015

What does System.Persistence do?

System.Persistence is an "engine" that sits between the database and the software, translating all data back and forth between.  Since all data goes through that "engine", it allows full security and logging.

The code is readable by non-technical people (with some training) so non-technical people can be certain that the system is indeed secure.

Aside from its security, the system works with any application and makes software easier to write and easier to maintain.  Existing systems can be re-written incrementally using the best-practices in System.Persistence with no downtime.

System.Persistence forces best-practices in n-tier software design, and makes it easy to follow with code-generation that is fully customizable.


Note:  the current version is designed for SQL Server and .NET, but it will also work with any database in any language.

Tuesday, June 16, 2015

Three points on security

1. All data-access goes through stored procedures.
2. Log every database "hit" to the individual user, if desired.
3. A single DBA manages security for the database and is publicly accountable.

Wednesday, January 28, 2015

Basic performance numbers

Single loads:  4 ms per object (30 properties/columns/fields).
Lists:  200 ms per 1000 objects.

Monday, September 22, 2014

Free download

1. Email andrewbb@gmail.com for the download link.
2. Create a PersistenceTest database (in SSMS or manually in PersistenceTest.sql)
3. Run PersistenceTest.sql to create the stored procedures and tables
4. Open PersistenceTest_orig.sln in Visual Studio

F5 should work at that point.  The business logic classes are in App_Code.

Note:  the example is a page from an actual application with a very complex database.  It shows the flexibility of the Persistence layer in handling non-normalized data.

Persistence is free for individual development.  For licensing on commercial projects, please contact me.  I am also available for consultation.

Wednesday, December 4, 2013

Persistence.dll - entry points


This is a ready-to-ship piece of code that makes software development available to the average "power user".  It solves the data access layer and is appropriate for over 95% of all business applications in production and development today.  It works for any data-access requirement and works with any database.  The SQL Server plug-in is written.

From the developer’s perspective, he/she writes:
Database.Store(this);
Database.Load(this);
Database.Delete(this);

Requirements by the application developer:
1.       Add a PersistableAttribute to the class.
2.       Create 3 stored procedures (load, store, delete) using a code-generator or hand-code.
3.       Add a connection string to the config file.

Unlike other solutions, referential integrity is not replicated at the business/logical layer.  Referential integrity is checked at the database.

This solution has been in use for over 10 years in high-volume environments with complex data structures of hundreds of tables (GUIDs, identity keys, compound identity keys, and no identity keys).  All are supported.  It is faster and simpler and supports all design patterns.

The solution is ready for inclusion in .NET as System.Persistence namespace.  It consists of a couple hundred lines of code and replaces the Dataset and automates the DataReader into simple loops.

For more information contact me:

Andrew Bransford Brown
512/947-8282

Friday, November 29, 2013

Download the Persistence demo

DESCRIPTION
Persistence is an object-to-stored procedure mapping system that replaces all data access methodologies and disobviates the need for an ORM.  No configuration.  Persistence is faster and simpler and has been proven to work for any imaginable business application regardless of complexity.
Side-by-side development.  Total rewrite is possible with no downtime.  Totally secure too.

Persistence was originally written in Java about 13 years ago.  It was ported to J++, then VB6, and now C#.  It is still in production today. 13 years without any bugs or limitations.  It simplifies application development profoundly while supporting any data structure and client design pattern imaginable.  It can and should be the System.Persistence namespace in the .NET framework.

TRY THE DEMO
1. Add Persistence.dll to your project (email me for a copy)
2. Create a PersistenceTest database (in SSMS or manually in PersistenceTest.sql)
3. Run PersistenceTest.sql

LICENSING
Persistence is free for individual development.  For licensing on commercial projects, please contact me.  I am also available for consultation.

Andrew Bransford Brown
+1 512 947 8282
andrewbb@gmail.com

Copyright 2002 Andrew Bransford Brown

Persistence vs. ORMs

Persistence maps stored procedures to objects and back.  No configuration.  Field name mapping is done inside the stored proc.  While Table-->Object mapping is available, it is Stored Procedure-->Object mapping so is more flexible.

No configuration.
Faster.
Most systems become Lists and Items.
More secure.

LINQ can be used on the Objects in memory rather than querying the database.

Referential integrity is enforced by the database, so no referential integrity information is stored in the application/business logic layer.  This allows the developer to focus on the business problem rather than the tables, rows, and columns format required by the database and reflected into most ORM systems.

The other advantage is side-by-side development.  Persistence works with ANY database.  Generate the basic stored procedures and classes and any application can be rewritten one screen or page at a time without disturbing the existing application.

Thursday, November 21, 2013

Data access via Stored Procedures


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.

http://www.codeproject.com/Articles/362034/Populating-a-business-logical-layer-from-Stored-Pr

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 thePersistable attribute can be saved to a database without any knowledge of the data layer.

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 orPersistentList (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 propertyCustomerId 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 andPersistentList 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.