Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Getting Started

SqlConnection

SqlConnection represents a session with SQL Server. Opening it late and closing it early is the most important habit in ADO.NET.

Beginner 5 min Lesson 5 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:Connection Strings
0/32 in ADO.NET
On this page · 5 min · Beginner

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Beginner
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

SqlConnection is the phone line itself. Pick up as late as possible, hang up as soon as you’re done — using does the hanging up for you.

SqlConnection
A session with SQL Server.
Open / OpenAsync
Start the session.
using
Guarantees the connection is closed.

Learning objectives#

  • Open and close connections correctly
  • Use using declarations and await using
  • Check connection state and server info
  • Handle connection failures

Explanation#

Creating a SqlConnection is cheap; Open is where the real work happens (or where a pooled connection is reused).

Use using var (or await using var in async code). Dispose closes the connection and returns it to the pool.

Open as late as possible — just before executing — and let it close as soon as the work is done. Do not keep connections in fields or static variables.

If the server cannot be reached, Open throws SqlException after Connect Timeout (default 15 seconds).

Code example: Opening a connection safely#

C#CheckConnection.cs Illustrative
using Microsoft.Data.SqlClient;


const string cs = "Server=localhost;Database=ShopDB;Integrated Security=True;TrustServerCertificate=True;Connect Timeout=5;";


try
{
    await using var conn = new SqlConnection(cs);
    Console.WriteLine(
quot;Before: {conn.State}");
    await conn.OpenAsync();
    Console.WriteLine(
quot;After:  {conn.State}");
    Console.WriteLine(
quot;Server: {conn.DataSource}, version {conn.ServerVersion}, database {conn.Database}");
}
catch (SqlException ex)
{
    Console.WriteLine(
quot;Could not connect (error {ex.Number}): {ex.Message}");
}
Expected output
Before: Closed
After:  Open
Server: localhost, version 16.00.1000, database ShopDB

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#

  1. The connection starts Closed.
  2. OpenAsync opens it (or takes one from the pool) without blocking the thread.
  3. Properties expose the server, version and database.
  4. await using disposes the connection at the end of the try block.
  5. SqlException.Number identifies the problem (e.g. 18456 = login failed).

Real-world use case#

A health check endpoint opens a connection and runs SELECT 1 to confirm the database is reachable before the load balancer sends traffic.

Common mistakes#

Sharing one connection across the app

SqlConnection is not thread-safe. Create one per operation; pooling makes this cheap.

Opening at startup and keeping it open

Idle open connections waste server resources and break when the network blips.

Best practices#

  • One connection per unit of work
  • await using in async code
  • Open late, close early
  • Log SqlException.Number with errors

Remember this#

Create cheap, Open real.

using = always closed, even on errors.

Not thread-safe → never share.

Practice exercise#

Your task: Write a method IsDatabaseUpAsync that returns true if SELECT 1 succeeds.

Show solution
C# Illustrative
static async Task<bool> IsDatabaseUpAsync(string cs)
{
    try
    {
        await using var conn = new SqlConnection(cs);
        await conn.OpenAsync();
        await using var cmd = new SqlCommand("SELECT 1", conn);
        return (int)(await cmd.ExecuteScalarAsync())! == 1;
    }
    catch (SqlException) { return false; }
}

Mini quiz#

SqlConnection quizMultiple choice Question 1 of 1

What does disposing a pooled SqlConnection do?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.