Data Providers
ADO.NET is a common pattern; each database supplies a provider that implements it. Learn one and the others feel familiar.
On this page · 5 min · Beginner
On this page
- In simple words
- Learning objectives
- Explanation
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Beginner
- Updated
- Oct 29, 2026
In simple words#
Providers are SIM cards for different networks: SqlClient for SQL Server, Npgsql for PostgreSQL. The phone (ADO.NET) works the same way with each.
- Data provider
- ADO.NET implementation for one database.
- DbConnection
- Common base class for all providers.
- Microsoft.Data.SqlClient
- The current SQL Server provider.
Learning objectives#
- Name providers for popular databases
- Understand the Db* base classes
- Write provider-independent code
- Know why System.Data.SqlClient is deprecated
Explanation#
Each provider implements the same abstract classes from System.Data.Common: DbConnection, DbCommand, DbDataReader, DbParameter and DbTransaction.
Popular providers: Microsoft.Data.SqlClient (SQL Server, Azure SQL), Npgsql (PostgreSQL), MySqlConnector or MySql.Data (MySQL/MariaDB), Microsoft.Data.Sqlite (SQLite) and Oracle.ManagedDataAccess.Core (Oracle).
Because classes share base types, code written against DbConnection can work with several databases — although SQL dialects still differ.
System.Data.SqlClient is the original SQL Server provider and is deprecated. Microsoft.Data.SqlClient is its actively maintained replacement with the same API.
Code example: Same code, different databases#
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Microsoft.Data.Sqlite;
static async Task<long> CountAsync(DbConnection conn)
{
await conn.OpenAsync();
await using DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM Products";
return Convert.ToInt64(await cmd.ExecuteScalarAsync());
}
await using var sql = new SqlConnection("Server=localhost;Database=ShopDB;Integrated Security=True;TrustServerCertificate=True;");
await using var lite = new SqliteConnection("Data Source=shop.db");
Console.WriteLine(quot;SQL Server: {await CountAsync(sql)}");
Console.WriteLine(quot;SQLite: {await CountAsync(lite)}");
SQL Server: 3 SQLite: 3
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- CountAsync accepts any DbConnection.
- CreateCommand returns the provider’s own command type behind the DbCommand base class.
- Convert.ToInt64 handles the different integer types providers return for COUNT(*).
Real-world use case#
A product that ships with SQLite for single-user installs and SQL Server for enterprise customers shares most of its data access code.
Common mistakes#
TOP vs LIMIT, identity columns and date functions differ. Provider-independent classes do not translate SQL.
System.Data.SqlClient.SqlConnection and Microsoft.Data.SqlClient.SqlConnection are different types. Pick Microsoft.Data.SqlClient everywhere.
Best practices#
- Use the official or most-maintained provider for each database
- Program against Db* types only when you truly need portability
- Keep SQL in one layer so dialect changes are contained
- Keep provider packages up to date for security fixes
Remember this#
Provider = ADO.NET for one database.
Db* base classes = provider-independent code.
SQL Server → Microsoft.Data.SqlClient.
Practice exercise#
Your task: Which NuGet packages would you install for PostgreSQL and for SQLite?
Show solution
dotnet add package Npgsql
dotnet add package Microsoft.Data.Sqlite
Mini quiz#
Which base class do all ADO.NET connection classes inherit from?
Want more? Practise all ADO.NET MCQs · ADO.NET interview questions
My notes#
Saved in this browser.