SqlConnection & Connection Pooling

Level: Beginner to Intermediate

ℹ️ Where This Fits

SqlConnection is the first step in any ADO.NET operation. Understanding connection pooling, connection strings, and proper resource cleanup prevents connection leaks and performance issues in production.

ℹ️ What You'll Learn

• SqlConnection purpose: Establish and maintain database connection to SQL Server
• Connection string format: Server=host;Database=name;User Id=user;Password=pwd;
• Connection string storage: appsettings.json, environment variables (never hardcode!)
• Integrated security: Server=localhost;Database=SmsDb;Integrated Security=true; (Windows auth, no password)
• SQL authentication: Server=localhost;Database=SmsDb;User Id=sa;Password=YourPassword; (username/password)
• Using statement: using (var conn = new SqlConnection(connStr)) { conn.Open(); ... } auto-closes on dispose
• Connection pooling: Reuse open connections instead of creating new ones (connection pool managed by framework)
• Connection pooling benefit: First request creates connection, second reuses it (huge performance gain)
• Pool size configuration: Min Pool Size=5, Max Pool Size=100 in connection string
• Connection lifetime: Max Pool Size reached, oldest connections expire after timeout (default 15 min)
• State checking: conn.State == ConnectionState.Open to verify before using
• Connection events: StateChange event fires when connection opens/closes
• Connection timeout: Connection Timeout=30; waits 30 seconds for connection (default 15)
• Async operations: Use async methods (.OpenAsync, .CloseAsync) to not block threads
• School Management connections: Multiple threads accessing StudentRepository need pooled connections
• Common mistakes: Forgetting to close connections (pool exhaustion), hardcoding connection strings, long-lived connections
• Best practice: Always use using statement, keep connections open only as long as needed, read from config

Connection String Anatomy

string connString = "Server=localhost;" +
    "Database=SmsDb;" +
    "User Id=sa;" +
    "Password=YourPassword;" +
    "Connection Timeout=30;" +
    "Min Pool Size=5;" +
    "Max Pool Size=100;";

Part	Purpose
Server	Host/IP of SQL Server
Database	Database name
User Id	Login username
Password	Login password
Connection Timeout	Wait time for connection (seconds)
Min Pool Size	Minimum pooled connections
Max Pool Size	Maximum pooled connections

appsettings.json Storage

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=SmsDb;User Id=sa;Password=YourPassword;Min Pool Size=5;Max Pool Size=100;"
  }
}

Inject into controller:

public class StudentRepository
{
    private readonly string _connString;

    public StudentRepository(IConfiguration config)
    {
        _connString = config.GetConnectionString("DefaultConnection");
    }

    public async Task<List<Student>> GetStudentsAsync()
    {
        using (var conn = new SqlConnection(_connString))
        {
            await conn.OpenAsync();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT Id, Name, ClassName FROM Students";
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    var students = new List<Student>();
                    while (await reader.ReadAsync())
                    {
                        students.Add(new Student
                        {
                            Id = (int)reader["Id"],
                            Name = (string)reader["Name"],
                            ClassName = (string)reader["ClassName"]
                        });
                    }
                    return students;
                }
            }
        }
    }
}

Connection Pooling In Action

Request 1: Create new connection (slow)

Open connection → add to pool → execute query → return to pool (stays open)

Request 2: Reuse pooled connection (fast)

Pull from pool → execute query → return to pool (connection already warm)

Request 100: Same pooled connection (fast)

Connection reused 100 times without overhead

Integrated Security (Windows Auth)

No password needed if using Windows credentials:

string connString = "Server=localhost;Database=SmsDb;Integrated Security=true;";

Common Mistakes

Mistake	Fix
Hardcoding connection string	Store in appsettings.json, inject via config
Not using using statement	Use using for auto-cleanup, prevents pool exhaustion
Creating new connection per query	Reuse connections through pooling
Long-lived connections	Close/dispose connections immediately after use
Ignoring connection timeout	Set appropriate timeout for your environment

Best Practices

✓ Store connection strings in configuration ✓ Use async methods (.OpenAsync) ✓ Always use using statement ✓ Configure pool size for expected load ✓ Monitor connection pool exhaustion ✓ Close connections promptly

💡 Pool Exhaustion Warning

If all 100 pooled connections are in use and request #101 arrives, it waits (connection timeout) then fails. Monitor and tune Max Pool Size based on your app's thread count.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on SqlConnection & Connection Pooling. Try these prompts:

• "Explain connection pooling and why it matters for performance."
• "How do you configure Min/Max pool size in the connection string?"
• "Why should you always use the 'using' statement with SqlConnection?"
• "What happens when the connection pool is exhausted?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 03. SqlCommand & Query Execution