Simple Data Access Layer

Description:

This is a simple data access layer generator I built for my own project, but thought it might be useful for others as well. 
This package includes a project item which consists of:

• a main item with .dal extension, which is a configuration file;
• a T4 template with .tt extension which generates an access layer code;
• an editor for the .dal file which allows to use a friendly UI instead of editing xml config directly.

This code generates enums from the database tables and classes to call stored procedures. No direct table access methods are generated. 

It all works the following way. During the configuration, you choose a generated code namespace. All objects will use this namespace as root. Enums will all be in the <root code namespace>.Enums namespace, stored procedure interfaces will be generated in the <root code namespace>.Executables namespace. Then the object name (including schema) will follow. For example if an enum is generated from the table [dbo].[states], and the root code namespace is DataAccessLayer then the enum full name will be DataAccessLayer.Enums.dbo.states. 

Stored procedures interface is generated, with the same naming schema as enums, as a class with 2 static methods - Execute and ExecuteAsync. The retiurn value is that class itself filled with parameter values, recordsets, and return parameter. 

The Data Access Layer supports transactions - there is a class ExecutionScope in the root code namespace which starts the sql transaction when instantiated and rolls back the transaction when disposed (if the transaction has not been explicitly committed). Also the transaction can be explicitly rolled back.

There are several limitations:

1. It only works in Visual Studio 2012 and higher, and requires .NET 4.5 since I use async a lot.
2. It can only generate interfaces to the stored procedures of your choice. I do not generate any code that does direct table reads or writes (coming from the SQL DBA background I have always hated direct code access). If you need that functionality then please use Entity Framework.
3. The code does not check on any reserverd keywords during the code generation. So if after generating the code you see errors, please change the generator parameters or data in the lookup tables. Also you can use aliases to the tables or procedures if their names cause errors.

Usage:

In the project - select "Add new item", choose "Visual C# items->Data->Simple Data Access Layer", choose a name and click OK.

The UI will be displayed (empty at first). Click on "Edit Model" and follow the prompts. Once you click "Finish", save the item (file) and classes will be generated.

I will provide more documentation as time permits. 

New in 1.0.1

Changed the serialization settings from UTF-8 to Unicode (UTF-16)

New in 1.0.2

Fixed a closing curly bracket issue that was causing an error when there is no Enums selected;

Fixed a bug where if a scope object was passed into the execute method, the connection.Open was still called despite of the fact that connection was already opened.

Published source code in on GitHub: https://github.com/rtumaykin/SimpleAccessLayer

New in 1.0.3

Added a retry mechanism. If a retryable SQL error is encoutered (deadlock, failover, etc), then the layer would resubmit the request upt to 10 times with 1 second interval between retries, and if this fails then it will bubble up an error further up to the caller.

New in 1.0.4

Fixed a bug with returned result set being disposed.

Upgrade note:

I have not implemented an automatic upgrade of the solution items yet, so if you upgrade from one version to another, please create just a new file using the new version of the template, and then copy the .tt file text from the new version over to the old .tt file in your existing project. I promise I will implement the automatic upgrade next time!