Difference Between SqlConnection and IDbConnection

Difference Between SqlConnection and IDbConnection

Both SqlConnection and IDbConnection are used for database connections in .NET, but they serve different purposes.

1?? IDbConnection (Interface)

• IDbConnection is an interface defined in the System.Data namespace.
• It provides a database-agnostic way to work with relational databases.
• It does not implement any actual database connection logic; it's just a contract that defines common methods like Open(), Close(), BeginTransaction(), etc.
• Used when you want flexibility to support different database providers (SQL Server, MySQL, PostgreSQL, etc.).

Example: Using IDbConnection for Database Agnostic Code

csharp        

CopyEdit

public class GenericRepository { private readonly IDbConnection _connection; public GenericRepository(IDbConnection connection) { _connection = connection; } public async Task<IEnumerable<T>> GetAllAsync<T>(string query) { return await _connection.QueryAsync<T>(query); } }

?? Here, _connection can be any database provider, making the code flexible.

2?? SqlConnection (Concrete Class)

• SqlConnection is a concrete implementation of IDbConnection, specifically for SQL Server.
• It lives in System.Data.SqlClient (or Microsoft.Data.SqlClient in newer versions).
• Implements the actual logic to establish a SQL Server database connection.
• Provides additional SQL Server-specific features, like SqlBulkCopy for fast inserts.

Example: Using SqlConnection for SQL Server

csharp        

CopyEdit

public class SqlServerRepository { private readonly string _connectionString; public SqlServerRepository(string connectionString) { _connectionString = connectionString; } public async Task<IEnumerable<T>> GetAllAsync<T>(string query) { using (var connection = new SqlConnection(_connectionString)) { return await connection.QueryAsync<T>(query); } } }

?? Here, the repository only works with SQL Server.

?? Key Differences

FeatureIDbConnection (Interface)SqlConnection (Concrete Class)TypeInterfaceClassDatabase-Agnostic? Yes (Can work with any DB)? No (Only for SQL Server)NamespaceSystem.DataSystem.Data.SqlClient (or Microsoft.Data.SqlClient)Instantiation? Cannot be instantiated? Can be instantiated (new SqlConnection(...))Supports Other Databases? Yes (MySQL, PostgreSQL, Oracle, etc.)? No (SQL Server Only)

When to Use What?

ScenarioUse IDbConnectionUse SqlConnectionWant to support multiple databases? Yes? NoOnly using SQL Server? No? YesWriting generic repository? Yes? NoNeed SQL Server specific features? No? Yes

?? Best Practice: Use IDbConnection with Dependency Injection

To make your code database-agnostic, inject IDbConnection instead of SqlConnection:

csharp        

CopyEdit

public class GenericRepository { private readonly IDbConnection _connection; public GenericRepository(IDbConnection connection) { _connection = connection; } public async Task<IEnumerable<T>> GetAllAsync<T>(string query) { return await _connection.QueryAsync<T>(query); } }

Then register it in Dependency Injection (DI):

csharp        

CopyEdit

builder.Services.AddScoped<IDbConnection>(sp => new SqlConnection("your_connection_string"));

This makes it easier to swap databases (e.g., replace SqlConnection with MySqlConnection).

Summary

• IDbConnection → Database-agnostic, interface for flexibility.
• SqlConnection → SQL Server-specific, concrete class for direct use.
• Use IDbConnection in repositories for better maintainability and flexibility.

Would you like a generic DB connection factory to switch between SQL Server, MySQL, and PostgreSQL dynamically?