ADO.NET CRUD Sample

MSDN / Programming / Data / Samples / ADO.NET CRUD

Overview

This tutorial demonstrates how to perform basic Create, Read, Update, and Delete (CRUD) operations using ADO.NET with a SQL Server database. The sample includes a simple Customers table and a C# console application that showcases each operation.

Prerequisites

Database Setup

Run the following script to create the Customers table and seed it with sample data.

CREATE DATABASE SampleDb;
GO
USE SampleDb;
GO
CREATE TABLE Customers (
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);
GO
INSERT INTO Customers (FirstName, LastName, Email) VALUES
('Alice', 'Smith', 'alice.smith@example.com'),
('Bob', 'Johnson', 'bob.johnson@example.com'),
('Carol', 'Williams', 'carol.williams@example.com');
GO

Sample Code

Copy the code below into a new Console App project (dotnet new console -n AdoCrudDemo).

using System;
using System.Data;
using System.Data.SqlClient;

namespace AdoCrudDemo
{
    class Program
    {
        private const string ConnectionString = @"Server=YOUR_SERVER_NAME;Database=SampleDb;Trusted_Connection=True;";

        static void Main()
        {
            InsertCustomer("John", "Doe", "john.doe@example.com");
            var customers = GetAllCustomers();
            foreach (var c in customers)
                Console.WriteLine($"{c.CustomerId}: {c.FirstName} {c.LastName} ({c.Email})");

            UpdateCustomerEmail(1, "alice.new@example.com");
            DeleteCustomer(3);
        }

        static void InsertCustomer(string firstName, string lastName, string email)
        {
            using var conn = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand(
                "INSERT INTO Customers (FirstName, LastName, Email) VALUES (@fn, @ln, @em)", conn);
            cmd.Parameters.Add("@fn", SqlDbType.NVarChar, 50).Value = firstName;
            cmd.Parameters.Add("@ln", SqlDbType.NVarChar, 50).Value = lastName;
            cmd.Parameters.Add("@em", SqlDbType.NVarChar, 100).Value = email;
            conn.Open();
            cmd.ExecuteNonQuery();
        }

        static List GetAllCustomers()
        {
            var list = new List();
            using var conn = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("SELECT * FROM Customers", conn);
            conn.Open();
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new Customer
                {
                    CustomerId = (int)reader["CustomerId"],
                    FirstName = (string)reader["FirstName"],
                    LastName = (string)reader["LastName"],
                    Email = (string)reader["Email"]
                });
            }
            return list;
        }

        static void UpdateCustomerEmail(int id, string newEmail)
        {
            using var conn = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand(
                "UPDATE Customers SET Email = @em WHERE CustomerId = @id", conn);
            cmd.Parameters.Add("@em", SqlDbType.NVarChar, 100).Value = newEmail;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            conn.Open();
            cmd.ExecuteNonQuery();
        }

        static void DeleteCustomer(int id)
        {
            using var conn = new SqlConnection(ConnectionString);
            var cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId = @id", conn);
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    class Customer
    {
        public int CustomerId { get; set; }
        public string FirstName { get; set; } = string.Empty;
        public string LastName { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
    }
}

Running the Sample

  1. Replace YOUR_SERVER_NAME in the connection string with your SQL Server instance name.
  2. Build and run the project (dotnet run).
  3. Observe console output showing inserted, retrieved, updated, and deleted records.

What’s Next?