ADO .NET Part 1: Introduction

Hi everyone, and welcome to this mini-series on ADO .NET, a fundamental API that lets us connect to and work with relational databases from our .NET code. It’s been around since the earliest versions of the .NET Framework.

What is ADO .NET?

The meaning of “ADO” is not really important, since it comes from a bygone era when dinosaurs ruled the land. What’s important is that it lets us connect to a database, run queries, update data, execute stored procedures, manage transactions, and all the things you would expect to do when working with a relational database. The part we’ll be covering is called the connected layer.

There is another aspect called the disconnected layer which we won’t be covering here. Using things like DataSets and DataTables, it essentially allows you to work on an in-memory copy of part of your database, and handles connections for you. Since it needs to model database schema constructs, it ends up using a lot more memory than simply retrieving your data using the connected layer.

Why Bother?

Over the course of my career, I occasionally found myself teaching ADO .NET to colleagues who fell into one of the following categories:

  • They just graduated, so they haven’t had the chance to learn ADO .NET yet
  • They’ve been using Entity Framework all along, and never needed ADO .NET
  • They’ve been living under a rock

ADO .NET is worth learning because it is the core database connectivity technology for .NET. Object Relational Mappers (ORMs) like Entity Framework (EF) or NHibernate use ADO .NET underneath. ADO .NET is very easy to use, as we shall see in this mini-series, and gives you all the flexibility and performance you can get. This means you can resort to it even for things that ORMs can’t handle.

As it happens, nowadays, it seems like many people automatically resort to Entity Framework by default, without considering the tradeoffs or alternatives. Entity Framework has quite a learning curve and there is a lot you might be paying in terms of performance if you don’t learn to tame it and work in the way it expects. This might be overkill if I just want to run some queries against a database.

Consider this: with Entity Framework I have to at a minimum translate the query from SQL to LINQ or lambda syntax, and then translate it back to SQL every time I need to troubleshoot or optimise it. I don’t see what we gain by representing SQL in OOP style. If anything, it’s a leaky abstraction (try using string.IsNullOrEmpty() with Entity Framework 6) with a lot of baggage.

There are two main things that Entity Framework can help us with: keeping hardcoded SQL strings out of our logic, and mapping result data to our OOP models. The former can easily be achieved by using a repository pattern (which you should be doing anyway even with EF), and the latter can be facilitated by a micro-ORM called Dapper, as we’ll see in the last article in this mini-series.

So while Entity Framework or other ORMs might have their appeal, and ADO .NET might sound like something ancient and low-level, it’s actually helpful to understand this core technology, add it to your toolkit, and understand how it compares to similar technologies.

Opening a Connection

Enough talk! Let’s open a connection against a database. Assuming we’re working with SQL Server (we’ll see how to work with other databases later in this mini-series), we’ll need the following using statement:

using System.Data.SqlClient;

The above namespace is part of the .NET Framework (so no extra steps necessary there), but if you’re using .NET Core, you’ll need to intall the System.Data.SqlClient package first.

            const string connStr = @"Server=.\SQLEXPRESS; Integrated Security=True";

            using (var conn = new SqlConnection(connStr))

The above code (actually, just the two highlighted lines) is all you need to establish a connection to a local SQL Server Express instance (no specific database) using the default Windows Authentication. If it’s successful, you’ll see “Connected” written to the console window. Otherwise, it will throw an exception. We’re not doing any exception handling for the sake of brevity, but ovbiously, you should have that in real production code. Also, Open() has an asynchronous counterpart (OpenAsync()) which we’ll talk about later in this mini-series.

This trivial example is not only a starting point that we will build on in the next articles. It also stands on its own as a simple application to test basic database connectivity when no other tools are available.

Connection Pooling

In the connected layer of ADO .NET, we obviously need to manage connections, and it might not be immediately evident how this should be done.

The good news is that it’s easier than you might think. ADO .NET internally has a connection pooling mechanism. This means that connections you close aren’t necessarily closed (at least not immediately) and can internally be reused, avoiding certain overheads with opening and closing connections.

This means that you’re encouraged to open and close connections as much as you need, especially because ADO .NET objects such as SqlConnection (and others we’ll see later) are not thread-safe; thus reusing instances can lead to severe problems in environments such as web applications where multiple requests may need to access the database concurrently.

Summary and Next Steps

In this article, we’ve discussed why it is useful to learn ADO .NET if you don’t know it already, and we’ve limited the scope to the connected layer only. This mini-series is meant to teach you the essentials as concisely a possible, and is not intended to be a comprehensive reference.

In the second half of the article, we saw how easy it is to open a connection, and discussed the connection pooling mechanism that ADO .NET automatically applies whenever we create or dispose of a connection object.

This lays the foundation for the next article, in which we’ll learn how to run different types of queries.