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?