Visual Studio Toolbox

Tools for Data Access in Visual Studio

Whether you're building an application database or generating data access code, we've got a round-up of tools to make the job easier.

• By Terrence Dorsey
• 01/04/2017

Today you have so many choices when you're developing a data-centric applications, from lightweight internal application storage solutions like SQLite to huge, scalable back-end data solutions like Hadoop -- and dozens of options for in-between scenarios. Luckily, you don't have to roll these solutions on your own. There are hundreds of tools, templates, and libraries that simplify working with those databases in Visual Studio and let you focus on the application rather than wrangling the interfaces to your data sources.

Local Storage Options
Let's start small. If you're building a standalone desktop or mobile application that needs internal database functionality, there are quite a few options that you can bake right into the project without making it unnecessarily large or unwieldy.

Microsoft SQL Server 2016 Express and SQL Server Compact 4.0 are still options that leverage all of your existing SQL Server and .NET Framework knowledge. They're solid solutions that have been around for ages and, at least in the case of SQL Server Express, seem to be getting ongoing development support.

My only concern with these tools is documentation support. I encountered a lot of dead links or redirects to unhelpful pages when researching this column, and the documentation that does exist is getting a bit old. That said, if it worked in 2011 it probably works today, so some of these older articles are probably fine. Just be forewarned.

With that said, LocalDB, an improved SQL Express is a feature that lets you get even smaller when your application database needs are simple. There is recently updated documentation for SQL Server 2016 Express LocalDB, and Jerry Nixon has a straightforward article comparing SQL Express versus LocalDB versus SQL Compact Edition that will be a good source for development planning.

SQLite is a self-contained, embedded, public domain SQL database engine that is very popular for applications on many platforms. You'll find support for SQLite integration in tools from a broad range of vendors, and plenty of documentation and guidance all around the Internet.

Erik Ejlskov Jensen's SQLite/SQL Server Compact Toolbox is one of those must-have tools for working with SQLite and SQL Server Compact. It operates both as a standalone application and a Visual Studio extension, enabling you to connect to your application databases, manage databases, script queries, migrate data, generate LINQ classes, and much more. Code, issues, and more information are available in the SQLite/SQL Server Compact Toolbox GitHub repository.

Another option is Sterling, a lightweight, open source, NoSQL object-oriented database for the .NET Framework 4.0, Silverlight 4 and 5, and Windows Phone 7. When they say lightweight, they're not kidding: the DLL is only 85KB. Sterling provides simple mapping between class models and database models, and supports LINQ to Object for fast queries on your data. The project is supported by a small team, and it looks like releases have been quiet for a while, but I recommend checking it out if Sterling's features meet your project's requirements. For more information see Kavitha K. Gowd's Getting Started with Sterling Database in Silverlight article on the C# Corner site.

For projects of all sizes that need document database, RavenDB is an open source NoSQL database that provides a REST API along with .NET Framework and Java client API libraries. You can use LINQ to RavenDB for queries, and scale your data store from in-memory instances for testing to large multi-tenant databases that support several replication schemes and sharding. Licensing programs start at just $7 per month. If you want to get a taste of RavenDB through demos and a live sandbox environment, check out the RavenDB Demo page.

Query & Management Tools
Wherever you decide to store your application data, you'll want some tools at hand that simplify data import, query modeling, testing, export, and general data management tasks.

Database .NET integrates with Visual Studio and lets you import, browse, query, edit, analyze and more through a single, consistent interface. Database .NET supports over 20 data sources from Excel to Visual FoxPro to SQL Server, PostgreSQL, Sybase, Oracle...pretty much any significant DBMS you're likely to come across.

LINQPad is an amazingly useful tool for data-focused development, giving you a scratchpad for LINQ or SQL database queries as well as an interactive environment for experimenting with C#, F# and Visual Basic code snippets. It's very similar to the JSFiddle online Web development sandbox, but LINQPad provides a standalone environment for code experimentation that also allows you to reference your own local assemblies and NuGet packages, export code to local scripts, debug interactively with built-in tools, and more. LINQPad provides drivers and formatters for SQL Server, SQL Azure, Oracle, SQLite, and MySQL, plus many more data sources and ORMs, and all of the .NET Framework LINQ APIs.

This is a great tool that has seen years of constant development and improvement and should definitely be part of your data toolbox. There's a free version that provides basic functionality. Paid licenses provide additional features at several affordable pricing tiers.

Devart offers two useful data modeling tools: Entity Developer is an ORM designer that supports model design and code generation for Entity Framework, NHibernate, LinqConnect, Telerik Data Access, and LINQ to SQL. You can do model-first or data-first design approaches, and it performs model validation as you're developing your model code. LINQ Insight provides design time LINQ query and SQL code testing and profiling, right in Visual Studio. LINQ Insight supports Entity Framework, NHibernate, LINQ to SQL, RavenDB, and more. Entity Developer provides a free, limited version and various professional version license pricing tiers. LINQ Insight offers some free features and a 30-day trial of LINQ Insight Standard edition's full feature set.

AdaptiveLINQ is an interesting solution that lets you use Entity Framework to easily build a dynamic "group by" analysis query engine and expose your analysis query as an OData feed by combining AdaptiveLINQ with WCF Data Services or ASP.Net WebAPI. Sounds like a very easy way to build a dynamic API service. There's somewhat limited documentation, but it gets straight to the point with good code examples for both basic functionality and OData integration. The free edition of AdaptiveLINQ limits the number of cubes and dimensions and does not provide dynamic properties. Unlimited developer licenses are available, with discounts starting at two or more licenses.

Redgate's ReadyRoll database migration scripts for Visual Studio provides features that support both data-access development and database devops. ReadyRoll allows you to work on the database at the same time as you're working on the app, creating SQL migration scripts that enable easy deployment of database changes along with application code changes. Redgate offers a 28-day free trial along with yearly license terms.

More Data Tools and Extensions
Here are a few more tools that simplify a variety of tasks related to templating data model classes and generating data access layers in your projects. So much easier when you can automate the task...

Daniel Lavrushin's LinqToSitecore Templates Code Generator and LinqToSitecore library simplify the tasks of building Sitecore CMS based applications. (If you're not familiar with Sitecore, see What is Sitecore CMS? A Developer's View over on the Box UK blog.) The code generator enables you to browse Sitecore item templates directly from your project in Visual Studio, select select the relevant Sitecore data items, and automatically generate the necessary C# code for those items. The LinqToSitecore library provides LINQ extensions for mapping Sitecore items to your C# data model. Details about both projects, along with documentation and code examples, can be found on the DanielLavrushin/Linq-to-Sitecore GitHub project page.

Domain Template, by Matthew Rowland, is a simple Data Access Layer project template for Visual Studio that provides a basic Entity Framework repository using the Unit of Work in Repository Pattern. Domain Template does not focus on any specific data access technology, so you're able to leverage it for almost any project.

The Griffin DAL Generator is another data access layer code generator that will take the pain out of creating your data repository code. This one supports only SQL Server 2012 and later as the data source, but creates ADO.NET helper code and integration tests along with the repository code.

Finally, I can't finish without offering to you QueryFirst, the world's first implementation of the "Dominic Strauss Kahn data access method". I'm not sure what that means, but if I'm reading this correctly it's a Visual Studio extension that enables you to keep your SQL code in separate, files, edit, validate, and test run queries with design-time mock inputs and IntelliSense, run the generated query code without runtime DLLs or reflection, and run your queries from a single line of code, creating POCOs containing the results. Sounds interesting. To learn more see the article Your SQL Wrapped in Generated Code. Interesting? Original? over on the CodeProject site.