How to query an SQL 2022/2019 database with the .NET SQL Data Provider in ASP.NET Print

Querying SQL Server with .NET

Recommended: Microsoft.Data.SqlClient (.NET Core / .NET 5+)

For modern .NET applications (.NET Core, .NET 5, 6, 7, 8, 9, 10), use Microsoft.Data.SqlClient:

Step 1: Install the Package

dotnet add package Microsoft.Data.SqlClient

Step 2: Add the Using Statement

using Microsoft.Data.SqlClient;

Step 3: Create Connection String

string connectionString = "Server=localhost;Database=myDatabase;User Id=myUser;Password=myPassword;TrustServerCertificate=True;";

Important: Include TrustServerCertificate=True for .NET 7+ to avoid SSL certificate validation errors on shared hosting.

Step 4: Query the Database

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

    string query = "SELECT Id, Name, Email FROM Users WHERE Active = @Active";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        // Use parameters to prevent SQL injection
        command.Parameters.AddWithValue("@Active", true);

        using (SqlDataReader reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                string email = reader.GetString(2);
                // Process your data
            }
        }
    }
}

Best Practices

• Always use parameterized queries to prevent SQL injection attacks
• Use async methods (OpenAsync, ExecuteReaderAsync) for better performance
• Wrap connections in using statements to ensure proper disposal
• Store connection strings in appsettings.json, not in code

Connection String in appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=myDb;User Id=myUser;Password=myPass;TrustServerCertificate=True;"
  }
}

Using with Dependency Injection

// Program.cs
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddScoped(_ => new SqlConnection(connectionString));

Legacy: System.Data.SqlClient (ASP.NET Framework 4.x)

For ASP.NET Framework 4.x applications, use System.Data.SqlClient:

using System.Data.SqlClient;

string connectionString = "Server=myServer;Database=myDb;User Id=myUser;Password=myPass;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Same query pattern as above
}

Note: System.Data.SqlClient is in maintenance mode. For new projects, always use Microsoft.Data.SqlClient.