ADO.NET Tutorial

Connecting to a Database with ADO.NET

Before you can read or write data, you need to establish a connection to a data source. ADO.NET provides the SqlConnection class (for SQL Server) and other provider‑specific connection classes to manage this.

1. Build a Connection String

A connection string contains the information required to locate and authenticate with the database.

string connString = @"Server=localhost\SQLEXPRESS;
                       Database=AdventureWorks;
                       Integrated Security=True;
                       TrustServerCertificate=False;
                       Encrypt=True;";

2. Open a SqlConnection

Wrap the SqlConnection in a using block to ensure it is closed and disposed automatically.

using System.Data.SqlClient;

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    Console.WriteLine($"Connected to {conn.Database}");
    // Perform database operations here
}

3. Handling Exceptions

Connections can fail for many reasons. Catch SqlException to handle SQL‑related errors.

try
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        // operations
    }
}
catch (SqlException ex)
{
    Console.Error.WriteLine($"SQL Error: {ex.Message}");
}

4. Using Configuration Files

Store connection strings securely in appsettings.json (for .NET Core) or Web.config (for .NET Framework).

{
  "ConnectionStrings": {
    "AdventureWorks": "Server=localhost\\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;Encrypt=True;TrustServerCertificate=False"
  }
}

Retrieve it in code:

using Microsoft.Extensions.Configuration;

var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .Build();

string connString = config.GetConnectionString("AdventureWorks");