Visual Studio Toolbox
10 ORM and Data Tier Management Tools for Visual Studio
Let these tools generate your data access layer, saving you time and headaches. There's something here for the simplest or most complex projects.
- By Terrence Dorsey
If your software development projects are anything like the ones I encounter every day, you spend a lot of time creating, saving, querying, moving, merging, and munging data.
Last year I wrote "11 Visual Studio Tools to Simplify Data Access," covering some of the more general tools and frameworks for creating and managing data. Back in 2011, my MSDN Magazine column, "Data Integration Tools and Resources," covered data basics, tips, tricks, and other resources to get started using data with SQL, LINQ, Entity Framework, and more.
Both of those articles are still useful. In this article I'll take a look at some newer tools and extensions, specifically those that integrate directly with Visual Studio 2013 and 2015.
Let's start out with the CodeTrigger Code Generator & ORM extension, by Exotechnic Corp., which combines a number of handy code-generation tools into a single toolset.
CodeTrigger (see Figure 1) analyzes the schema of your data source and automatically builds business and data domain models. It also generates data domain classes and business domain classes including typed lists, collections, stored procedures and interface definitions. The generated code includes support for dependency injection using Unity or Ninject, as well as logging and exception handling.
Your source data can include SQL Server, Oracle and MySQL databases, and CodeTrigger can generate C#, Windows Presentation Foundation (WPF), Windows Communication Foundation (WCF), and SQL code for you for Windows Forms, WPF, ASP.NET, WCF services and more. The CodeTrigger extension integrates directly with Visual Studio 2010 through 2013. There's also a standalone version available.
Trial versions are available for both standalone and extension installs. See the CodeTrigger Web site for license information and pricing, IDE integration instructions, downloads, tutorials, and code samples.
Irwsoft Data Framework
Irwsoft Data Framework is a lightweight ORM that integrates directly with Visual Studio to generate table, view, function and procedure classes directly from a database.
You can create objects from SQL Server, MySQL, Oracle, or any OleDB database supported by ADO.NET, and Irwsoft Data Framework works with Visual Studio 2005 through 2015.
There are several versions available. Irwsoft Data Framework Light Edition provides basic functionality at a nice price: free. Irwsoft Data Framework Professional adds additional features, including user-defined functions and data diagrams. A 14-day trial is available. Irwsoft Data Framework Ultimate adds further INSERT features and direct support from Irwsoft. See the Edition Comparison for details.
A sort of uber-ORM you might want to evaluate for more complex development situations is LLBLGen Pro, which provides both data model development and ORM functionality in one package.
As an ORM data modeler and code generator, LLBLGen Pro supports Access, Firebird, IBM DB2, MySQL, Oracle, PostgreSQL, SQL Server, and Sybase data sources and creates objects and classes for C# or Visual Basic, as well as its own mapping framework, Entity Framework, LINQ, and NHibernate.
LLBLGen Pro also provides an integrated visual designer for data-first or model-first mapping and modeling. There's a lot of interesting functionality here.
There are several versions, starting with the free LLBLGen Pro Lite license, which is limited to eight entities per project. LLBLGen Pro Standard offers unlimited entities and support, and the LLBLGen Pro Plus license adds an ORM Profiler license (which we'll get to further down). A 30-day trial license is available.
nHydrate ORM Modeler
Inspired by NHibernate (which I covered in MSDN Magazine back in 2011), nHydrate ORM Modeler (see Figure 2) is an open source ORM solution for mapping relational databases to .NET objects. nHydrate now uses Entity Framework as its internal data access layer and provides a visual modeler of data relationships.
The upside of being open source is that nHydrate is free and seems reasonably well supported by the community. For example, MySQL was recently added as a supported data source.
The downside is that you may have to invest some effort in tracking down information. Although the project now lives on GitHub, you'll find a good overview of nHydrate on the nHydrate CodePlex project page, including links to further sources of "official" information. The CodePlex site also includes a pretty comprehensive set of nHydrate Documentation and tutorials. For a good overview of the differences between nHydrate and its underlying Entity Framework roots, check out "nHydrate vs. Entity Framework" by Christopher R. Davis and Michael S. Knight over on CodeProject.
ORMapster for Visual Studio 2013 is a simple ORM data mapper and code generator extension that does one thing: iI reads your data source and creates a data access layer with LINQ self-tracking entities. That's it. The resulting data entities can then be used in your projects, including WCF, ASP.NET MVC, Web Forms, Windows Forms and so on.
ORMapster integrates directly with Visual Studio, and versions are available for Visual Studio 2010 through 2013. To work with the LINQ entities created by ORMapster, you'll also need the ORMapster Framework NuGet package.
There's a handy Getting Started guide on the ORMapster Web site that walks you through building a data model based on the beloved Northwind sample database. Also, although there's pretty much no documentation explaining it, you might check out the ORMapster Web UI NuGet package, which appears to be a DataSourceControl optimized for data binding with ORMapster-generated entities.
Previously I mentioned that ORM Profiler (see Figure 3) is available as part of the LLBLGen Pro Plus license, but it's also available as a standalone Visual Studio extension.
ORM Profiler provides both code optimization and execution profiling tools. It can highlight data access anti-patterns and programming mistakes and help you track down which parts of your code handle executes particular queries for tuning and troubleshooting. You can also log query execution and data access to determine overall usage and specific task performance.
Documentation, licensing information, and a 15-day trial are available on the ORM Profiler Web site.
Simple Data Access Layer
Roman Tumaykin built a Simple Data Access Layer for Visual Studio 2013 for one of his own projects, and decided to share it with everyone. Simple Data Access Layer is exactly what it claims to be: simple. It doesn't read from or write to your data tables directly, but instead simply reads the stored procedures in your project and generates a data access layer (DAL). Once you've built the DAL, Simple Data Access Layer provides an editor and wizard to edit the model and generate classes to access the data objects.
Extra points to Tumaykin for providing an extremely straightforward overview of what Simple Data Access Layer does (right down to the generated files), how it works, and instructions for use. Many larger, more expensive projects don't make it this easy to get started.
SQLite for Universal App Platform
SQLite is a public domain "self-contained, serverless, zero-configuration, transactional SQL database engine" that you integrate into your projects as a library. If you're building Universal Windows Platform (UWP) apps and want to utilize SQLite as a database, take a look at SQLite for Universal App Platform for Visual Studio 2015, developed by the SQLite Development Team.
Downloads, documentation and support are available from the SQLite Web site. For background information on using SQLite in UWP apps, read Nicolò Carandini's two articles starting with "Universal App with SQLite – Part 1." He focuses on Windows Runtime 8.1, but the general information should get you started with Windows 10, as well. Igor Ralic has a shorter, but more recent, article, "Using SQLite in Windows 10 Universal Apps."
A somewhat different ORM strategy is used by SubSonic, which generates the data access layer modeled on the ActiveRecord functionality in Ruby on Rails. SubSonic also allows you to use a SimpleRepository, a traditional LINQ to SQL repository, or a LINQ to SubSonic template. All are based on the T4 templates built by Visual Studio itself. (See Scott Hanselman's article, "T4 (Text Template Transformation Toolkit) Code Generation - Best Kept Visual Studio Secret," for details.)
SubSonic is a free, open source project and you'll find the code and latest documentation in the SubSonic Project on GitHub. However, you'll find an excellent introduction to SubSonic's ActiveRecord implementation on the original project Web site.
Telerik Data Access
Similar to LLBLGen Pro, Telerik Data Access provides a top-to-bottom approach to enterprise .NET ORM tooling. You can build data models from your database schemas or take a code-first approach, then push your model to the data tier. Telerik Data Access includes a visual designer, as well.
Once you've built the data models, generated data access classes, and integrated them into your application, Telerik Data Access also includes profiling and optimization tools to get the most out of your code.
Telerik Data Access integrates directly with Visual Studio, and supported databases include Advantage, Firebird, Maria DB, MySQL, Oracle, PostgreSQL, SQL Azure, SQL Server, SQLite, Sybase, and VistaDB.
As a bonus, all this functionality is free with extensive documentation and community support. Paid direct support from Telerik is available.
So Many Choices
As you can see, there's a data access solution for just about any project here. Some are lightweight options tailored for common project scenarios. Others provide a wealth of options, supporting just about any combination of data source and code generation requirements. All of them are either free or provide a trial, so install a copy, run the modeler, and see whether the resulting code meets your needs and preferences.
Don't see your favorite ORM tools here? Leave us a note and we'll follow up in a future article.
Terrence Dorsey is a technical writer, editor and content strategist specializing in technology and software development. Over the last 25-plus years he has worked on developer-focused projects at ESPN, The Code Project, and Microsoft. Read his blog at http://terrencedorsey.com or follow @tpdorsey on Twitter.