Skip to content
| Marketplace
Sign in
Visual Studio>Tools>Simple Data Access Layer

Simple Data Access Layer

Roman Tumaykin

|
1,947 installs
| (3) | Free
Simple Data Access Layer is a Visual Studio extension package that generates enums from the database tables and classes to call stored procedures.

*** I added a walkthrough for this extension on my blog here: http://roman.tumaykin.com/tools/2016/01/17/Simple-Data-Access-Layer-User-Manual.html

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 cs file with generated 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 2015, 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: fixed table variable code resulting in multiple definitions

 

 

** update #2 (1/17/2016): 

  • Fixed an error when saving an empty (unconfigured) file
  • Async methods are only generated when project's target framework supports it
  • Made the extension fully compatible with previous versions of Visual studio (and very likely - with the future too). However I decided to leave the declared compatibility level the same to avoid confudion
  • Table type definitions are now only generated if they are used in any procedure included in the config (before they were always generated regardless of above condition
  • Replaced icons
  • General bug fixes

** update #3 -- new in version 1.0.1 - Digitally signed VSIX package

** update #4 - FIxed a template trust issue

  • Contact us
  • Jobs
  • Privacy
  • Terms of use
  • Trademarks
© 2023 Microsoft