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 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:
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.
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!