Simple Data Access Layer for Visual Studio 2013

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 2013, 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. 

Update:

1.0.1:

• fixes the template location bug - now it is in the "Data" subfolder of "C# Items". 
• added Microsoft.SqlServer.Types nuget package to support scenario where there is no SQL server installation on development or deployment computer.

1.0.2:

• fixed a minor bug in the template file when an unconfigured or new project could have an extra closing curly bracket and no namespace.