We are big believers in building layered applications where the code to perform a CRUD data operation is separate from the code to access the data – often using stored procedures in our applications and rarely using Entity Framework. We have this philosophy partially for performance, partially for security and partially for scalability in avoiding distributed transactions. This approach often lets us send down batches of records to the data layer to be processed in a single transaction without the overhead of a distributed transaction.
To help reduce, if not remove, the overhead inherent with this approach we have developed a pair of data access layer base classes and SQL Client extensions to streamline the coding process. This library is available as an MIT open source piece of software available on Nuget under SoundPower.DAL or at https://www.nuget.org/packages/SoundPower.DAL/.
SQL Client Extensions
We have created a set of extensions to make it simple to add parameter values to a SQL command along with setting up a variable to catch the return value from the call. We have also created a set of extensions to make it easier to interpret and process information from a Data Reader. This includes a method to create a dictionary mapping field names to result set ordinals. That dictionary can then be used in many other methods to retrieve nullable and non-nullable field values in single line commands that we have included in this extension set. Use of these extensions has made it easy to create highly readable data access code for large domain models inside of minutes.
Base SQL Data Access Layer
Our base data access layer class includes several constructors to provide the ultimate flexibility in specifying the connection to the SQL database. Simple methods for instantiating a SQL command object, ensuring a database connection is open and ensuring it is closed are present. It also includes methods for managing a transaction across multiple data access layer calls – when a distributed transaction is a must. Disposing of the class ensures the connection is closed and any open and uncommitted transaction is rolled back.
Encrypted Data Access Layer
Often we have found it important to encrypt and decrypt system data as it transverses the data access layer. Reasons for this include not wanting to use Transparent Data Encryption techniques and simply wanting to gain more security by keeping the encryption keys separate from the data. When this approach is desired, we have used our encrypted data access layer which inherits from our data access layer object. The difference is that is defining the object the coder must specify the entropy key for encryption. Then during instantiation the encryption key must be provided. This allows for a 2 part process where one piece is in configuration and can be rotated as needed by support. A decrypt and encrypt method is present to then selectively apply encryption on a field by field basis. All encryption is done using Rijndael Managed 256-bit encryption.
