ADO.NET – Connections And Data Providers


ADO.NET is a framework for accessing, interrogating, manipulating and persisting data in relational databases.

Source Code

All source code can be found on GitHub here.

This is part of my HowTo in .NET seriies. An overview can be seen here,

Data Providers

The framework abstracts consuming code away from the specifics of each vendor’s database implementation allowing code to be written which is virtually agnostic to the data source.

Each ADO.NET capable database provides an ADO.NET data provider which handles the databases specific implementation needs.

Through a series of classes, abstract classes and interfaces, ADO.NET provides functionality which is consistant regardless of the database vendor.

ADO.NET data providers exist for most database vendors. A list can be found here:

Configuring A Database Provider

Depending upon your choice of database and data provider, you might need to configure the provider to be used with .NET.

SQL Server is configured automatically on Windows/.NET. Under Linux/Mono SQLIte is configured automatically yet MySQL is not.

If you are using MySQL I would recommend the Connector/Net ADO.NET data provider. You can download the driver here:

The data provider is written in .NET and is provided as an assembly which needs to be installed into the GAC.

sudo gacutil -i MySql.Data.dll

To check the installation went ok. You can list the contents of the gac with the gacutil command. The command below uses the POSIX command grep to filter the results:

gacutil -l | grep My

Data providers are registered within the machine.config of the .NET version you are running.

For windows the machine.config location will look something like:


For Mono and Debian based Linux the machine.config location will look something like:


The configuration will be specific to the version of your data provider. You should check the installation page of you vender to determine this. The following is for MySQL Connector provider version

<add name=”MySQL Data Provider”
description=”.Net Framework Data Provider for MySQL”
type=”MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
Version=, Culture=neutral,
PublicKeyToken=c5687fc88969c44d” />

This should be copied into the Machine.Config within the <DbProviderFactories> node which sits within the <System.Data> node. You should find other providers already configured.

The data provider can then be referenced as any other assembly within the GAC.

Bases Classes

ADO.NET provides a series of core and abstract classes; they provide common functionlaity that is not specific to any data priovider.

ADO.NET also provides a series of interfaces. ADO.NET providers implement these interfaces.

The data providers are welcome to inherit from the abstract classes, and most do, to reuse the common functionality associated with them.

ADO.NET API consumes the interfaces rather than the abstract classes to allow a data provider with greater control of their required funcitonality.

Below we describe the core abstract classes and their interfaces.

Class / Name Space



Provides a common namespace for most of the abstract classes and interfaces which each data provider inherit from or implement.

It also contains common entities which are not specific to a data provider; datasets, tables, rows, columns, relational constraints etc.



Supports for configuring connections



Support for database transactions



Support for calling SQL statements, stored procedures and parameterized queries.

Provides access to the an instance of the  DataReader class through the ExecuteReader() method



Provides a collection of DbParameters to IDBCommand.




Provides a parameter to sql statements and stored procedures. It is used by IDBCommand.



Provides a read only iterative view of the data returned from a SQL statement.

Provides access to strongly typed data via field names or their ordinal positions.




Provides access to a cached subset of data, monitoring of any changes which can then be persisted back into the database at a later time.

Below we map the abstract classes and interfaces to the SQL Server and MySQL data provider classes. As you can see the naming convention is consistent:

Class / Name Space

SQL Server































Though it is possible to code directly to the MySQL or SQL Server data provider classes, it is not recommended and considered bad practice.

All the examples will be make use of the DbProviderFactories class and the associated DbProviderFactory class to create instance of the required data provider specific classes. The DbProviderFactories uses late binding to to determine which class the data provider has provided as part of their implementation.

Database Connections

Connection Strings

ADO.NET allows a virtually database agnostic approach to database access within .NET.

The connection strings are vendor specific and can turn off and on a number of features each of which might be specific to each vendor.

In short the minimum requirements is the server name, the database name and some form of login criteria. As each vendors connection string is specific to the vendor and data provider. Here are some examples:

Connection String
SQL Server
Data Source=localhost;Integrated Security=SSPI;Initial Catalog=MyDB

SSPI means the logged in user will be used to connect with windows integrated security.

Alternative you could provide the username and password
MySQL data adapter does not appear to support SSI

It is advised to use connection pooling when concurrent database access is required. You should check each vendor for how to set this up. provides an excellent knowledge base for connecting to most databases in most technologies including ADO.NET.


Though connection strings can be defined anywhere, it is good practice to place them within the machine.config. This can allow changing connection criteria including the database server without compiling code; great for having separate databases for development, testing and production.

Connection Strings should be placed between the appSettings and the connectionStrings node.

The connection string can be named with the name field for reference in code later.

The providers assembly is referenced with the providerName field.

SQL Server:

<add name =”MyDatabase”
connectionString = “Data Source=localhost; Integrated
Security=SSPI;Initial Catalog=MyDB”/>


<add name=”MyDatabase”
ID=Me;Password=MyPassword” />


The ConfigurationManager provides access to the defined connections within the Machine.Config (and App.Config) files via the name provided. Above we defined a connection called MyDatabase:

var connectionDetails =

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

Connecting To a Database

To connect to a database an instance of DbConnection should be initialised against the database vendor specific connection string.

The The DbProviderFactories can be used to create the database connection object. It takes the database provider name which we configured as part of the connection information.

DbProviderFactory dbFactory =

using (DbConnection connection = dbFactory.CreateConnection())
connection.ConnectionString = connectionString;

DbConnection implements IDisposable; it should be contained within a using scope.


The ConnectionStringBuilder class can be used in conjunction with a connection string to help customise a connection string in code. The index method [] can be used to set key value pairs representing connection properties.

It can be initialised with a connection string:

var connectionDetails =
ConfigurationManager.ConnectionStrings ["MyDatabase"];

var providerName = connectionDetails.ProviderName;
var connectionString = connectionDetails.ConnectionString;

var dbFactory = DbProviderFactories.GetFactory (providerName);

var builder = dbFactory.CreateConnectionStringBuilder ();
builder.ConnectionString = connectionString;
builder ["ConnectionTimeout"] = 60;

using (var connection = dbFactory.CreateConnection ()) {
connection.ConnectionString = builder.ConnectionString;
connection.Open ();

Connected vs Disconnected Layers

ADO.NET provides two conceptual layers; connected and disconnected.

The connected layer requires a live connection to the database to remain open during interaction. It is used for short transactions to the database such as calling stored procedures, running schema update or writeable transactions. The layer can be used for reading data though as the connection remains open; it is advised to limit the usage for short use.

The disconnected layer allows populating a DataSet object which allows an offline representation of the data. The connection remains open only for the amount of time it takes to populate the required data into the DataSet. The dataset can then be manipulated independently without the connection being open. The data can even be persisted back to the database at a later date.


Day 8.3 – Agnostic database connection with ADO.NET

The Source Code

This post is part of my Dot Net On Linux posts. All the source code is available on GitHub here


The ADO.NET framework abstracts each database provider such that our code needs to know very little of the actual implemented database. What little we do require can be abstracted into a factory for each provider and then injected into our code via a DI kernel. Only each database provider requires any knowledge of the actual database.

The following constitutes the few items which require an understanding of the actual database implemented.

  • Connection and connection string
    • Initiation of MySQLConnection or SQLiteConnection with the correct connection string
    • Connection string should be placed within machine.config ConnectionStrings.
    • Each connection implements IDbConnection
  • DataAdapter
    • Initiation of MySQLDataAdapter or SQLiteDataAdapter
    • Each adapter implements IDbDataAdapter
  • Command
    • Though not actually required by our factory it feels right to include this here.
    • Initiation of DbCommand with our connection via the CreateCommand upon the connection object
  • Database Schema
    • Even between SQLServer and .NET we don’t have the CTE. SQLite’s 4 data types proved a little troublesome.

Connection and connection string

A good place for storing our ADO.NET DataProvider connection string is in the machine.config, there is even a pre existing section to keep them all together called ‘ConnectionStrings’. We can then reference the hard coded database provider specific connection string via:

Creating an instance of our database provider connection is as simple as:

new MySQLConnection(connectionString);
new SQLiteConnection(connectionString);


Our factory simply requires to return an instantiated version of our providers data adapter

new SqliteDataAdapter();
new MySqlDataAdapter();



An Example Factory

 public class MySQLADOProviderFactory : IADODataProviderFactory
        public IDbConnection GetConnection()
            return new MySqlConnection(GetDBConnectionString());

        public IDbDataAdapter GetDataAdapter()
            return new MySqlDataAdapter();

        public string GetDBConnectionString()
            return System.Configuration.ConfigurationManager.ConnectionStrings ["DotNetOnLinux"].ConnectionString;
public class ProviderFactory : IProviderFactory
        public IADODataProviderFactory GetADODataProviderFactory()
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();

Injecting The Factory

public class ProviderFactory : IProviderFactory
        public IADODataProviderFactory GetADODataProviderFactory()
            return InjectionFactory.Instance.kernel.Get<IADODataProviderFactory>();

Database Schema

The hardest part of this work was getting the model and the two database table schemas to behave nicely with each other.

I had already coded the Id field in MySQL as INT and the associated Id column on SampleModel.cs as int. I had casting issues when mapping SQLite Integer data type as it actually mapped more directly to MySQL BIGINT(20) and c# long types.

The name field had to be changed in MySQL to a VARCHAR(45) to accommodate the size of SQLite TEXT data type.

Dates worked fine though both databases required the format to be ‘yyyy-MM-dd

Developing C# ASP.NET MVC & WCF Web Services in Linux

I am not sure what I am trying to prove in these articles. Personal ‘learning development’ is always high on the agenda being a software developer, however making the programming skills I learn in my spare time applicable to my job as a .NET Developer is sometimes a hard choice being such a Linux enthusiast.

On one side I would like to see if it is possible to write and deploy an ASP.NET MVC website on Linux, while on the other hand I would like to develop the skills which are applicable to my job.

Hopefully once I have tested the water with these skills in Linux, I will be able to think of a worth wile personal development project which can provide me a bases to improve to the skills needed in my job while satisfying my hobby of coding under Linux.

Below is a list of skills, technologies and frameworks which I use daily in my job. I would like to improve these skills in some way; even if it is just to see if I can code these in Linux.

  •     C#
  •     ASP.NET MVC
  •     ASP.NET
  •     ADO.NET
  •     Unit Testing
  •     Mock Testing
  •     Source Control
  •     Database Access
  •     WCF Web Services
  •     SQL
  •     Generics
  •     Linq
  •     Lambda Expressions
  •     Object Relational Mapping (NHibernate/Entity)
  •     JavaScript/JQuery
  •     CSS

The mission plan

  1. Provide a series of How-To articles providing insight into my first exploit of developing .NET technologies under Linux.
  2. Provide the source code on line.
  3. Hopefully to provide help to someone else by writing about my experience.