ADO.NET — Essentials

Dark theme quick reference for definitions and a practical SQL Server connection example.

What is ADO.NET?

Data access technology for .NET to connect to many databases (SQL Server, Oracle, MySQL, etc.).
Works in any .NET app: Console, Web, Desktop, services, and more.
Uses data providers (libraries) to talk to specific databases.
System.Data is the base namespace that defines common ADO.NET types.

Common Data Providers

System.Data.SqlClient or Microsoft.Data.SqlClient — SQL Server
System.Data.Odbc — ODBC (MySQL, etc. via ODBC)
Note: System.Data.OracleClient is deprecated; prefer Oracle's Oracle.ManagedDataAccess.Client.

Core Classes (SQL Server)

SqlConnection — opens/closes a database connection.
SqlCommand — executes SQL queries and stored procedures.
SqlDataReader — reads result sets forward-only/streaming.

Connection String (example)

Data Source=DESKTOP-4O1D65I\SQLEXPRESS;Initial Catalog=ADMDOTNET;Integrated Security=True;Trust Server Certificate=True

Tip: Use Trusted/Integrated Security for development. For production, store secrets in configuration (User Secrets, environment variables, or a vault) and avoid Trust Server Certificate=True unless you understand the security implications.

SqlCommand execution helpers

int ExecuteNonQuery() — run DML (INSERT/UPDATE/DELETE); returns rows affected.
SqlDataReader ExecuteReader() — run SELECT; stream rows.
object ExecuteScalar() — get a single value from a query.

Quick start: Connect to SQL Server (.NET 6+ Console)

Add the provider package:

dotnet add package Microsoft.Data.SqlClient

Create Program.cs and paste:

// Program.cs
// Demonstrates connecting to SQL Server and running sample commands using ADO.NET
using System;
using Microsoft.Data.SqlClient; // Prefer this package for new apps

class Program
{
    static void Main()
    {
        // Use a safe way to load from configuration in real apps
        var connectionString = "Data Source=DESKTOP-4O1D65I\\SQLEXPRESS;Initial Catalog=ADMDOTNET;Integrated Security=True;Trust Server Certificate=True";

        using var connection = new SqlConnection(connectionString);
        connection.Open();
        Console.WriteLine($"Connected: {connection.State}");

        // 1) ExecuteScalar - get a single value
        using (var scalarCmd = new SqlCommand("SELECT COUNT(*) FROM sys.objects", connection))
        {
            var count = (int)scalarCmd.ExecuteScalar();
            Console.WriteLine($"Objects in DB: {count}");
        }

        // 2) ExecuteNonQuery - DML example (create a demo table if not exists)
        var ddl = @"
IF OBJECT_ID('dbo.DemoPeople', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.DemoPeople(
        Id INT IDENTITY(1,1) PRIMARY KEY,
        FullName NVARCHAR(100) NOT NULL
    );
END";
        using (var ddlCmd = new SqlCommand(ddl, connection))
        {
            ddlCmd.ExecuteNonQuery();
            Console.WriteLine("Ensured table dbo.DemoPeople exists.");
        }

        // Insert a row
        using (var insertCmd = new SqlCommand("INSERT INTO dbo.DemoPeople(FullName) VALUES (@name)", connection))
        {
            insertCmd.Parameters.AddWithValue("@name", "Ada Lovelace");
            int rows = insertCmd.ExecuteNonQuery();
            Console.WriteLine($"Inserted rows: {rows}");
        }

        // 3) ExecuteReader - read rows
        using (var readCmd = new SqlCommand("SELECT TOP (5) Id, FullName FROM dbo.DemoPeople ORDER BY Id DESC", connection))
        using (var reader = readCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                string name = reader.GetString(1);
                Console.WriteLine($"Row => Id={id}, Name={name}");
            }
        }
    }
}

Optional: Minimal project file

Example .csproj targeting modern .NET:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.*" />
  </ItemGroup>
</Project>

Package choice: Microsoft.Data.SqlClient is the modern provider for SQL Server. System.Data.SqlClient remains for legacy frameworks but is not actively developed for new features.

Hands-on CRUD example: Doctors

Below is your sample using Microsoft.Data.SqlClient with a simple CRUD class and a Program entry point.

1) Create table

CREATE TABLE dbo.Doctors (
    DoctorId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Specialization NVARCHAR(100) NOT NULL,
    Qualification NVARCHAR(150) NULL,
    LicenseNumber NVARCHAR(50) NULL,
    ExperienceYears INT NOT NULL,
    PhoneNumber NVARCHAR(20) NULL,
    AvailabilityStart TIME NOT NULL,
    AvailabilityEnd TIME NOT NULL
);

2) Program.cs

using Microsoft.Data.SqlClient;
namespace HandsOnAdo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            CrudOperations crud = new CrudOperations();
            // Insert a new doctor
            crud.InsertDoctor(
                "Dr. Satya Kumar", "Neurosurgeon", "MBBS, MCH Neuro", "LIC99887", 20, "9123456701",
                new TimeSpan(10, 0, 0), new TimeSpan(15, 0, 0)
            );
            // Show all doctors
            crud.GetAllDoctors();
            // Update specialization of doctor with ID 10
            crud.UpdateDoctor(10, "Dr. Harish Iyer", "Family Physician");
            // Delete doctor with ID 9
            crud.DeleteDoctor(9);
        }
    }
}

3) CrudOperations.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System;
using Microsoft.Data.SqlClient;
namespace HandsOnAdo
{
    internal class CrudOperations
    {
        private static string connectionString = "Data Source=ELIZA\\SQLEXPRESS; Initial Catalog=HealthcareSystem; Integrated Security=True; TrustServerCertificate=True";
        // INSERT
        public void InsertDoctor(string name, string specialization, string qualification, string license, int experience, string phone, TimeSpan start, TimeSpan end)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string query = @"INSERT INTO Doctors (Name, Specialization, Qualification, LicenseNumber, ExperienceYears, PhoneNumber, AvailabilityStart, AvailabilityEnd)
                                 VALUES (@Name, @Spec, @Qual, @License, @Exp, @Phone, @Start, @End)";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Spec", specialization);
                cmd.Parameters.AddWithValue("@Qual", qualification);
                cmd.Parameters.AddWithValue("@License", license);
                cmd.Parameters.AddWithValue("@Exp", experience);
                cmd.Parameters.AddWithValue("@Phone", phone);
                cmd.Parameters.AddWithValue("@Start", start);
                cmd.Parameters.AddWithValue("@End", end);
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} doctor(s) inserted.");
            }
        }
        // READ
        public void GetAllDoctors()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string query = "SELECT * FROM Doctors";
                SqlCommand cmd = new SqlCommand(query, conn);
                SqlDataReader reader = cmd.ExecuteReader();
                Console.WriteLine("\n--- Doctor List ---");
                while (reader.Read())
                {
                    Console.WriteLine($@"ID: {reader[""DoctorId""]}, Name: {reader[""Name""]}, Specialization: {reader[""Specialization""]}, 
Qualification: {reader[""Qualification""]}, License: {reader[""LicenseNumber""]}, Experience: {reader[""ExperienceYears""]}, 
Phone: {reader[""PhoneNumber""]}, Start: {reader[""AvailabilityStart""]}, End: {reader[""AvailabilityEnd""]}");
                }
                reader.Close();
            }
        }
        // UPDATE
        public void UpdateDoctor(int id, string name, string specialization)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string query = "UPDATE Doctors SET Name = @Name, Specialization = @Spec WHERE DoctorId = @Id";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Spec", specialization);
                cmd.Parameters.AddWithValue("@Id", id);
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} doctor(s) updated.");
            }
        }
        // DELETE
        public void DeleteDoctor(int id)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string query = "DELETE FROM Doctors WHERE DoctorId = @Id";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.AddWithValue("@Id", id);
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} doctor(s) deleted.");
            }
        }
    }
}

Build/run: Ensure the database HealthcareSystem exists on ELIZA\\SQLEXPRESS, install the provider (dotnet add package Microsoft.Data.SqlClient), then build and run your console app.

Troubleshooting tips

If connection fails, verify server name, instance (e.g., \\SQLEXPRESS), and that SQL Server is running.
For remote connections, ensure TCP/IP is enabled and firewall rules allow access.
Use Trusted_Connection=True on Windows for integrated auth, or provide User ID/Password.