Rediger

Connect to a data source with Microsoft.Data.SqlClient

SqlConnection represents one logical connection to SQL Server, Azure SQL Database, Azure SQL Managed Instance, or SQL database in Microsoft Fabric. Opening the object gets a physical connection from the connection pool when one is available. Closing or disposing it returns that physical connection to the pool.

Use short-lived SqlConnection objects for units of work. Don't keep one global connection open for the application.

Prerequisites

  • The .NET 10 SDK or a later supported .NET SDK. The SDK runs on Windows, Linux, and macOS. It also builds the .NET Framework version of each sample, which runs only on Windows and needs .NET Framework 4.8.1.
  • A database that you can connect to in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or SQL database in Microsoft Fabric.

Create the project

Microsoft.Data.SqlClient 7.0 and later versions need the version-matched Microsoft.Data.SqlClient.Extensions.Azure package for driver-provided Microsoft Entra ID authentication modes, such as Active Directory Default. If your app uses only SQL authentication or Windows integrated authentication, you can omit it.

In an empty folder, run these commands:

dotnet new console --framework net10.0 --name SqlClientConnect
cd SqlClientConnect
dotnet add package Microsoft.Data.SqlClient --version 7.1.1
dotnet add package Microsoft.Data.SqlClient.Extensions.Azure --version 7.1.1

The samples in this article read the connection string from the SQL_CONNECTION_STRING environment variable. Set it before you run a sample. For connection string formats, see Connection strings.

Every sample except the transaction sample is a complete program. To run one, replace the contents of Program.cs with it, and then run dotnet run. The transaction sample is a fragment for your own method. The .NET tab uses top-level statements, implicit using directives, and await using. The .NET Framework tab uses C# 7.3, which doesn't have await using, so it uses using statements instead.

Open a connection

Call OpenAsync in asynchronous code or Open in synchronous code. Declare the connection with await using or using so it's disposed when the code leaves the scope, even when an exception is thrown.

With Windows integrated authentication, the connection uses the identity of the process. For a web application or a Windows service, that's the application pool or service account, not the signed-in user. For more information, see Choose an authentication pattern.

This complete program opens a connection, reports each state change, and reads two server properties:

using Microsoft.Data.SqlClient;

string connectionString =
    Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException(
        "Set the SQL_CONNECTION_STRING environment variable.");

await using var connection = new SqlConnection(connectionString);
connection.StateChange += (_, e) =>
    Console.WriteLine($"State: {e.OriginalState} to {e.CurrentState}");

await connection.OpenAsync();

await using var command = new SqlCommand(
    """
    SELECT CAST(SERVERPROPERTY('Edition') AS nvarchar(128)),
           CAST(SERVERPROPERTY('EngineEdition') AS int);
    """,
    connection);

await using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
    await reader.ReadAsync();
    Console.WriteLine($"Edition: {reader.GetString(0)}");
    Console.WriteLine($"EngineEdition: {reader.GetInt32(1)}");
}

Against SQL Server 2025 Enterprise edition, the output is:

State: Closed to Open
Edition: Enterprise Edition: Core-based Licensing (64-bit)
EngineEdition: 3
State: Open to Closed

The last line appears when the connection is disposed at the end of the program.

Close and dispose connections

Always close a connection when you finish with it so the physical connection returns to the pool. The using and await using statements dispose the connection when the code exits the block, including when an unhandled exception is thrown. You can also call Close or Dispose directly. Close and Dispose have the same effect on the physical connection.

Follow these rules:

  • Dispose readers and commands before the connection that owns them.
  • Don't rely on garbage collection to return a connection to the pool. A connection that goes out of scope without being closed isn't returned promptly, and the pool can run out of connections.
  • Don't call Close or Dispose on a connection, a reader, or any other managed object in a class finalizer. A finalizer should release only unmanaged resources that its class owns directly. For more information, see Garbage collection.
  • After Close, you can call Open again on the same object. After Dispose, create a new object.

With pooling enabled, closing a connection returns it to the pool without closing the network connection. The pool resets the session state when the connection is reused. For more information, see SQL Server connection pooling.

Use asynchronous APIs

Use asynchronous calls for network-bound database work in web servers, services, user interfaces, and workers:

  • OpenAsync(cancellationToken)
  • ExecuteNonQueryAsync(cancellationToken)
  • ExecuteReaderAsync(cancellationToken)
  • ExecuteScalarAsync(cancellationToken)
  • ReadAsync(cancellationToken)

Don't start another operation on a connection, command, or reader before the current asynchronous operation finishes.

Apply cancellation and timeouts

Pass the caller's CancellationToken through every asynchronous database call. Cancellation asks the driver to stop the pending work, but completion isn't guaranteed to be immediate. Continue to use bounded connection and command timeouts.

These controls have separate scopes:

Control Scope
Connect Timeout Connection establishment, or the wait for a pooled connection
SqlCommand.CommandTimeout One command execution
CancellationToken Caller-requested cancellation of an asynchronous operation

A timeout or cancellation doesn't prove that the server rolled back an operation. Use a transaction when multiple changes must commit or roll back as one unit, and make retry decisions from the operation's idempotency and transaction outcome.

Track connection state

The State property returns a snapshot from the ConnectionState enumeration.

State Meaning
Closed The logical connection isn't open.
Connecting An open operation is in progress.
Open The logical connection is open.

The StateChange event fires when the state changes. Its StateChangeEventArgs argument has two properties:

  • OriginalState is the state before the change.
  • CurrentState is the state after the change.

The driver reports the Closed to Open and Open to Closed transitions, as the sample in Open a connection shows. Don't depend on observing Executing, Fetching, or Broken as application lifecycle phases.

Don't use State as a health check before every command. The network can fail after any check. Run the operation and handle the resulting exception. Use StateChange for diagnostics, not for coordinating concurrent work.

Receive informational messages

The InfoMessage event delivers messages that the server sends without raising an error. These messages include PRINT output, RAISERROR messages with a severity of 10 or lower, and server warnings. Errors with a severity of 11 or higher raise a SqlException instead. For severity levels, see Database Engine error severities.

The event handler receives a SqlInfoMessageEventArgs object. Its Errors property holds a collection of SqlError objects. Each one includes the message number, text, severity class, procedure, and line number.

using Microsoft.Data.SqlClient;

string connectionString =
    Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException(
        "Set the SQL_CONNECTION_STRING environment variable.");

await using var connection = new SqlConnection(connectionString);
connection.InfoMessage += (_, e) =>
{
    foreach (SqlError error in e.Errors)
    {
        Console.WriteLine(
            $"Message {error.Number}, severity {error.Class}: {error.Message}");
    }
};

await connection.OpenAsync();

await using var command = new SqlCommand(
    """
    PRINT 'Starting the batch.';
    RAISERROR('Processed %d rows.', 10, 1, 42);
    """,
    connection);

await command.ExecuteNonQueryAsync();

The output is:

Message 0, severity 0: Starting the batch.
Message 50000, severity 0: Processed 42 rows.

The server reports a RAISERROR severity of 10 as class 0, so SqlError.Class shows 0 for both messages.

Handle user errors as informational messages

By default, the driver reports an error with a severity of 11 or higher by throwing a SqlException. That exception doesn't mean the server stopped the batch. Many errors, including RAISERROR with a severity of 16, let the server run the remaining statements, so later statements can change data even when your code catches the exception. To receive these errors as messages instead of exceptions, set FireInfoMessageEventOnUserErrors to true. The driver then sends errors with a severity of 16 or lower to the InfoMessage event instead of throwing. This property changes how the driver reports errors. It doesn't change which statements the server runs.

using Microsoft.Data.SqlClient;

string connectionString =
    Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException(
        "Set the SQL_CONNECTION_STRING environment variable.");

await using var connection = new SqlConnection(connectionString)
{
    FireInfoMessageEventOnUserErrors = true,
};
connection.InfoMessage += (_, e) =>
{
    foreach (SqlError error in e.Errors)
    {
        Console.WriteLine(
            $"Message {error.Number}, severity {error.Class}: {error.Message}");
    }
};

await connection.OpenAsync();

await using var command = new SqlCommand(
    """
    RAISERROR('Row 7 failed validation.', 16, 1);
    SELECT 'The batch continued.';
    """,
    connection);

await using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
    do
    {
        while (await reader.ReadAsync())
        {
            Console.WriteLine(reader.GetString(0));
        }
    }
    while (await reader.NextResultAsync());
}

The output is:

Message 50000, severity 16: Row 7 failed validation.
The batch continued.

Errors with a severity of 17 or higher still throw a SqlException, regardless of this setting. When you enable this property, your handler is responsible for detecting failures. Code that expects an exception for every error doesn't see one.

Report the application identity

Starting with Microsoft.Data.SqlClient 7.1.0, a framework, tool, or other middleware can report which application opened a connection. Set RegisteredApplication to a RegisteredApplication value before you open the connection. The driver sends the value to the server once, during login.

Follow these rules:

  • Set the value through the property. No connection string keyword exists for it.
  • Set the value before you open the connection. Setting it while the connection is opening or open throws an InvalidOperationException.
  • Treat the value as telemetry. The client supplies the value and can report any identifier, so the server doesn't authenticate it.
  • Expect pooled connections to report the original identity. The value isn't part of the pool key. A connection served from the pool reports the identity of the logical connection that caused the physical connection to be created.

Caution

Never use RegisteredApplication for authorization or any other security decision. Any client can report any identifier.

Choose an identifier

The default value is Unknown, which is 0. The enumeration has an underlying type of ushort, so identifiers range from 0 through 0xFFFF.

Range Use
0x0001 through 0x7FFF Microsoft-defined applications. Named enumeration members use this range.
0x8000 through 0xBFFF Reserved for small-scale use.
0xC000 through 0xFFFF Public and developer use.

The named members are EntityFrameworkCore, SemanticKernel, ManagementStudio, SqlManagementObjects, DataTierApplicationFramework, SqlToolsService, AspNetCoreDistributedSqlServerCache, EntityFramework, AzureFunctionsSqlExtension, OrleansAdoNet, DurableTaskSqlServer, SqlPackage, and DataApiBuilder. To report an identifier that has no named member, cast the number to RegisteredApplication.

The RegisteredApplication type and property aren't CLS-compliant because the enumeration uses ushort. C# code can use them directly.

Set the identifier

This program reports an identifier from the developer range and then shows what happens when code changes the value after the connection opens:

using Microsoft.Data.SqlClient;

string connectionString =
    Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException(
        "Set the SQL_CONNECTION_STRING environment variable.");

await using var connection = new SqlConnection(connectionString)
{
    RegisteredApplication = (RegisteredApplication)0xC001,
};

await connection.OpenAsync();
Console.WriteLine(
    $"Reported identifier: 0x{(ushort)connection.RegisteredApplication:X4}");

try
{
    connection.RegisteredApplication = RegisteredApplication.Unknown;
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

The output is:

Reported identifier: 0xC001
InvalidOperationException: Not allowed to change the 'RegisteredApplication' property. The connection's current state is open.

Understand pooling effects

The driver reports the identifier only when it creates a new physical connection. With pooling enabled:

  • A connection that reuses a pooled physical connection reports the identity of the connection that created the physical connection, not its own.
  • Physical connections that the pool opens in the background to satisfy Min Pool Size report Unknown.

If one connection string serves several identities, treat the reported value as approximate. Disable pooling for that connection string when you need exact attribution, and measure the extra connection cost first.

SqlConnection.Clone copies the RegisteredApplication value to the new connection.

Don't share a connection concurrently

SqlConnection, SqlCommand, SqlDataReader, and SqlTransaction don't support concurrent use by multiple threads. Give each concurrent operation its own connection and let connection pooling reuse the physical connections.

Multiple Active Result Sets (MARS) permits multiple active batches on one connection in supported scenarios. It doesn't make SqlClient objects thread-safe, and it adds session and transaction rules. Leave it disabled unless one operation specifically needs it.

Don't register an open SqlConnection as a singleton in dependency injection. Register the connection string, an immutable options object, or a factory that creates a new connection.

Use transactions deliberately

A local transaction belongs to its connection. Every command in the transaction must use that connection and set its Transaction property.

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);

await using SqlTransaction transaction =
    (SqlTransaction)await connection.BeginTransactionAsync(cancellationToken);

using var command = new SqlCommand(sql, connection, transaction);
command.Parameters.Add(
    new SqlParameter("@value", SqlDbType.Int) { Value = value });
await command.ExecuteNonQueryAsync(cancellationToken);

await transaction.CommitAsync(cancellationToken);

If the operation fails before the commit, disposing the transaction rolls it back. Keep transactions short. Don't do network calls, user interaction, or unrelated computation while a database transaction holds locks.

When System.Transactions.Transaction.Current is active, Open and OpenAsync automatically enlist by default. Set Enlist=false only when the operation must stay outside the ambient transaction.

Measure one logical connection

Set StatisticsEnabled to true to collect driver statistics for one SqlConnection object:

using System.Collections;
using Microsoft.Data.SqlClient;

string connectionString =
    Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
    ?? throw new InvalidOperationException(
        "Set the SQL_CONNECTION_STRING environment variable.");

await using var connection = new SqlConnection(connectionString)
{
    StatisticsEnabled = true,
};

await connection.OpenAsync();
connection.ResetStatistics();

await using var command = new SqlCommand("SELECT name FROM sys.databases;", connection);
await using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
    {
    }
}

IDictionary statistics = connection.RetrieveStatistics();
Console.WriteLine($"ServerRoundtrips: {statistics["ServerRoundtrips"]}");
Console.WriteLine($"SelectRows: {statistics["SelectRows"]}");
Console.WriteLine($"BytesReceived > 0: {(long)statistics["BytesReceived"]! > 0}");

The output shows one round trip. The row count depends on the number of databases on the server:

ServerRoundtrips: 1
SelectRows: 14
BytesReceived > 0: True

RetrieveStatistics returns a snapshot. ResetStatistics starts a new measurement boundary. Set StatisticsEnabled to false to stop collecting. Values collected so far remain available. Statistics are per connection object and add overhead, so enable them for targeted diagnosis instead of every production request.

Handle connection failures

Catch SqlException at a boundary that can log, translate, or retry the failure. Record:

  • Number
  • State
  • Class
  • ClientConnectionId
  • The operation name and configured server and database identifiers

Don't log the connection string, password, client secret, or access token.

Dispose a broken connection. The pool removes invalid physical connections when it detects them. If a credential, token, certificate, DNS target, or server changed, correct the configuration before retrying.

Use bounded retry logic only for transient failures. Initial-open retry, idle connection recovery, and command retry are different mechanisms. For more information, see Configurable retry logic and the connection recovery options in Connection options.

Related content

  • Sidst opdateret den