Visual Studio Toolbox

11 Visual Studio Tools to Simplify Data Access

If wrangling queries is getting you down, these 11 data access extensions for Visual Studio can help.

• By Terrence Dorsey
• 12/04/2014

When you're developing a data-centric application, you have a lot of choices just at the database level: SQL Server, Azure Table Storage, DocumentDB, MongoDB, … the list goes on . Having tools in your coding toolbox that simplify working with those databases lets you focus on the application rather than managing the service.

Which database is best for your application is a bigger question than I'm going to tackle here. Back in 2011, I wrote a column for MSDN Magazine on data integration tools and resources; there, you'll find more background, tips and tricks resources focused on general data programming, SQL, LINQ, Entity Framework and a few popular object-relational mapping tools. Check it out if you're looking for a primer. Most of the information is still relevant for getting started.

In this column, I'll take a look specifically at some tools and extensions that will help you wrangle with database access and data programming within Visual Studio.

SQLite and SQL Compact
If you're working with SQL Server Compact Edition or SQLite as local data stores, whether for Windows or Windows Phone apps, there are a number of handy Visual Studio extensions that will help.

The SQL Server Compact/SQLite Toolbox extension shown in Figure 1 provides a huge palette of features that makes light work of moving data between data sources, managing data sources, querying, scripting, and generating code against those data sources, all right within the Visual Studio IDE.

The toolbox extension supports Visual Studio versions 2010 through 2014. It works directly with SQL Server Compact, SQLite, SQL Server (including LocalDB and Express), and even CSV files. In addition, you can generate Windows Phone LINQ to SQL DataContext classes, desktop LINQ to SQL classes, Entity Framework Entity Data Models, and SQLite based sqlite-net code.

There are even standalone versions for Visual Studio Express and non-Visual Studio users, a command-line version of the SQL Server Compact/SQLite Toolbox tools, and even a scripting API. Check out the extension site and the accompanying CodePlex project page for details.

A similar tool worth taking a look at, particularly if you don't need all of the features offered by SQL Server Compact Toolbox, is the SQL Compact Code Generator extension, which provides a graphical interface for navigating your data sources and automatically creating entity classes and data access code, xml-doc code comments for entities and data access methods, entity unit tests and data access unit tests (MSTest, NUnit, and xUnit), as well as Windows Phone LINQ to SQL DataContext classes.

SQL Compact Code Generator supports Visual Studio 2008 and 2010, and the entire project is available on CodePlex under an MIT open source license.

Need a little primer on using SQLite in your apps? Go back to the February 2013 issue of Visual Studio Magazine for Eric Vogel's article "Build a Windows Store App Using SQLite." Microsoft's Visual Studio Gallery has the runtime downloads for SQLite for WinRT , SQLite for WinRT 8.1 and SQLite for Windows Phone 8.1. You can find documentation and additional downloads at the SQLite website.

LINQ
Some folks will tell you LINQ is SQL for the twenty-first century. Or something like that. If you're on the bandwagon, LINQPad is a great tool that helps you experiment, prototype queries, and interactively query LINQ-compatible data sources (see Figure 2). In addition to LINQ to SQL, Entity Framework, OData, and WCF Data Services, LINQPad also supports queries for QL Azure, SQL Table Storage, Oracle, SQLite, and MySQL.

I linked up some useful LINQ resources back in my 2011 article, and those are still a great way to get up to speed with how LINQ and LINQPad help integrate data with your .NET codebase. So don't forget to check that out.

LINQ Insight Express is a Visual Studio extension that gives you design-time LINQ query execution, preview of SQL queries and execution for a LINQ query, and the ability to profile queries from Entity Framework, NHibernate, LinqConnect, and LINQ to SQL.

LINQ Insight Express is a free, lightweight version of Devart Software's paid product, and it includes a 30-day trial of it's big brother's features, but you can continue to use the Express features after the trial.

Entity Framework
If you're working with Entity Framework as the object-relational mapping (ORM) framework for your data tier, the Entity Framework Power Tools Beta 4 extension -- built by the Entity Framework Team at Microsoft -- is a great tool to start with. You can simply right-click on a C# project and generate the classes, derived DbContext and Code First mapping for an existing database or add reverse-engineered T4 templates to your project.

Entity Framework Power Tools enables you to select a DbContext class and view the Code First model, the EDMX XML of the underlying model, or the DDL SQL for the EDM Model. You can also select a DbContent class or EDMX file and directly generate pre-compiled views.

The latest version of the extension is a general-purpose build compatible with Visual Studio 2010, 2012, and 2013, but the Entity Framework team also created version-specific POCO and DbContext generators for Entity Framework 4, 5 and 6, all of which are available in Visual Studio Gallery.

You might also check out Devart Software's free Entity Developer Express extension, which provides similar modeling and code generation capabilities for Entity Framework, as well as NHibernate and LINQ to SQL. It provides a subset of the tools available from Devart's Entity Developer for Entity Framework extension -- trial available on Visual Studio Gallery.

For multi-tier data architectures, take a look at Christof Senn's N-Tier Entity Framework extension shown in Figure 3, which "extends Entity Framework to provide support for n-tier solutions. The framework aims to provide the richness of EF in a distributed architecture while keeping its simplicity." Versions of the extension are available for Visual Studio 2013, Visual Studio 2012, and Visual Studio 2010.

I'm particularly impressed by the extensive documentation provided with the N-Tier Entity Framework extension that explains not just how to integrate it into your project, but also how to use it in architecting n-tier solutions.

## And More...
Here's an interesting option: MongoDB is a popular document database that I've found useful for quick data tasks that don't necessarily require the full grunt of a traditional table-oriented database. The MongoDb Manager extension helps you managed connections, add or drop collections, query documents and even provides Aggregate and MapReduce functionality, all within Visual Studio. MongoDb Manager is still in beta as I write this, but looks well worth giving a try. Open Data Protocol Visualizer from Microsoft's Data Modeling Group creates visualizations of types, properties, and associations within WCF Data Service EDMs. The visualizer is a bit old at this point, supporting only Visual Studio 2010, but incredibly handy if it fits within your project needs.

Last, but not least, Database .NET lets you "create, edit and browse any database" from within Visual Studio. It supports over 20 different database systems and data sources, from Access to Oracle to Sybase to Visual FoxPro.

You'll also find object navigators, table designers, data visualizers and more. As if that's not enough, Database .NET supports Visual Studio 2013, 2012, 2010, 2008, and 14.

These tools ought to make pretty much any data programming task a breeze. You'll spend more time building your application and less time wrangling queries, which has to be a good thing.