Introduction

Most real-world applications need to store and manage data. For example, a student management system stores student details, an e-commerce website stores product information, and a banking application manages customer records.

C# applications commonly use relational databases such as Microsoft SQL Server to store this information.

ADO.NET is a data-access technology in .NET that allows applications to communicate with databases. It provides classes for opening database connections, executing SQL commands, retrieving results, and managing data.

In this tutorial, you will learn how to connect a C# application to SQL Server and perform essential database operations step by step.

1. What Is ADO.NET?

ADO.NET is a set of .NET libraries designed for accessing data from databases and other data sources.

It provides database access components such as connections, commands, data readers, and data adapters.

The main ADO.NET components used with SQL Server include:

Component	Purpose
SqlConnection	Establishes a connection to SQL Server.
SqlCommand	Executes SQL statements or stored procedures.
SqlDataReader	Reads query results sequentially.
SqlDataAdapter	Fills a DataSet or DataTable and can support updates through configured commands.
DataSet	Holds an in-memory collection of data tables and relationships.
DataTable	Represents an in-memory table of data.
SqlParameter	Supplies parameter values to SQL commands.

For modern .NET applications, the SQL Server provider is available through the Microsoft.Data.SqlClient package. Older applications may use System.Data.SqlClient.

This tutorial uses Microsoft.Data.SqlClient.

2. Requirements Before Getting Started

Before writing code, make sure you have:

• Visual Studio or another suitable C# development environment.

• A supported .NET SDK.

• Microsoft SQL Server, either locally or on a reachable server.

• SQL Server Management Studio (SSMS) or another SQL client, if you want a graphical interface to manage the database.

• The Microsoft.Data.SqlClient NuGet package.

Install the SQL Server NuGet package

In Visual Studio:

1. Open your C# project.

2. Right-click the project in Solution Explorer.

3. Select Manage NuGet Packages.

4. Search for Microsoft.Data.SqlClient.

5. Install a compatible stable version.

Alternatively, run this command in the project directory:

dotnet add package Microsoft.Data.SqlClient

After installation, your C# code can import the provider:

using Microsoft.Data.SqlClient;

3. Create a SQL Server Database

First, create a database to store student information.

Open SQL Server Management Studio, connect to your SQL Server instance, and execute the following SQL script:

CREATE DATABASE SchoolDB;
GO

USE SchoolDB;
GO

CREATE TABLE Students
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    Course NVARCHAR(100) NOT NULL
);
GO

Understanding the database structure

• SchoolDB is the database name.

• Students is the table name.

• Id uniquely identifies each student.

• IDENTITY(1,1) automatically generates an ID starting at 1 and increasing by 1.

• Name stores the student's name.

• Age stores the student's age.

• Course stores the course name.

• NOT NULL prevents these columns from containing SQL NULL values.

The table is now ready to receive data.

4. Understand the Connection String

A connection string contains the information required to connect to a database.

Example for a local SQL Server instance:

string connectionString =
    "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

Here is what each part means:

Setting	Explanation
Server=localhost	Connects to the local SQL Server instance.
Database=SchoolDB	Selects the SchoolDB database.
Integrated Security=True	Uses the Windows identity of the application process for authentication.
TrustServerCertificate=True	Trusts the server certificate without validating its trust chain. Use cautiously.

Important: Your SQL Server instance name may differ. For example, SQL Server Express commonly uses localhost\SQLEXPRESS, while LocalDB often uses (localdb)\MSSQLLocalDB. Use the connection string appropriate for your installation.

For production environments, configure encryption and certificate validation properly instead of blindly trusting server certificates. Avoid putting database passwords directly into source code.

5. Connect C# to SQL Server

Now create a C# console application and write a simple program that opens a database connection.

using System;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        try
        {
            connection.Open();

            Console.WriteLine("Database connected successfully!");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Database connection failed: {ex.Message}");
        }
    }
}

Possible output

Database connected successfully!

This output appears only when the connection succeeds.

How the code works

1. SqlConnection creates an object that represents the database connection.

2. connection.Open() attempts to establish the connection.

3. The try block contains the operation that may fail.

4. The catch block handles SQL-related exceptions.

5. The using statement disposes of the connection when it leaves scope.

Always ensure your SQL Server service is running and that your connection string matches your environment.

6. Insert Data Using ADO.NET

The next step is to insert a student record into the Students table.

Use SqlCommand to execute an INSERT statement. For values supplied by users, use parameters rather than concatenating strings into SQL.

using System;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        string query =
            "INSERT INTO Students (Name, Age, Course) " +
            "VALUES (@Name, @Age, @Course)";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlCommand command =
            new SqlCommand(query, connection);

        command.Parameters.AddWithValue("@Name", "Rahul");
        command.Parameters.AddWithValue("@Age", 21);
        command.Parameters.AddWithValue("@Course", "C# Programming");

        try
        {
            connection.Open();

            int rowsAffected = command.ExecuteNonQuery();

            Console.WriteLine(
                $"Records inserted: {rowsAffected}");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Insert failed: {ex.Message}");
        }
    }
}

Possible output:

Records inserted: 1

What does ExecuteNonQuery() do?

ExecuteNonQuery() is used for commands that do not return a result set, such as INSERT, UPDATE, and DELETE. It returns the number of affected rows when the provider can determine it.

Why use SQL parameters?

Parameters such as @Name, @Age, and @Course keep user-supplied values separate from the SQL command text. This helps prevent SQL injection and handles values more reliably.

For more precise type and length control, use explicitly typed parameters such as SqlParameter instead of relying on inferred types from AddWithValue().

7. Retrieve Data Using SqlDataReader

To retrieve rows from SQL Server, use SELECT with ExecuteReader().

SqlDataReader provides an efficient, forward-only way to read results.

using System;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        string query =
            "SELECT Id, Name, Age, Course FROM Students";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlCommand command =
            new SqlCommand(query, connection);

        try
        {
            connection.Open();

            using SqlDataReader reader =
                command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(
                    $"ID: {reader["Id"]}, " +
                    $"Name: {reader["Name"]}, " +
                    $"Age: {reader["Age"]}, " +
                    $"Course: {reader["Course"]}");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Read failed: {ex.Message}");
        }
    }
}

Possible output:

ID: 1, Name: Rahul, Age: 21, Course: C# Programming

The output depends on the records in your database. Running the insert example multiple times can create additional rows.

Understanding the code

• ExecuteReader() executes the query and returns a data reader.

• reader.Read() advances to the next row and returns true when a row is available.

• reader["Name"] accesses the value in the Name column.

• The using statements dispose of the reader, command, and connection.

For strongly typed access, you can use methods such as GetInt32() and GetString() when you know the column types and have handled any possible SQL NULL values.

8. Update Records in SQL Server

To modify an existing student record, execute an UPDATE statement.

using System;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        string query =
            "UPDATE Students SET Age = @Age WHERE Id = @Id";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlCommand command =
            new SqlCommand(query, connection);

        command.Parameters.AddWithValue("@Age", 22);
        command.Parameters.AddWithValue("@Id", 1);

        try
        {
            connection.Open();

            int rowsAffected = command.ExecuteNonQuery();

            Console.WriteLine(
                $"Records updated: {rowsAffected}");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Update failed: {ex.Message}");
        }
    }
}

Possible output:

Records updated: 1

This example updates the age of the student whose ID is 1.

Important: Always consider the WHERE clause when updating or deleting records. Without a suitable condition, the operation may affect every row in the table.

9. Delete Records from SQL Server

To delete a student record, use the DELETE statement with a parameterized condition.

using System;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        string query =
            "DELETE FROM Students WHERE Id = @Id";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlCommand command =
            new SqlCommand(query, connection);

        command.Parameters.AddWithValue("@Id", 1);

        try
        {
            connection.Open();

            int rowsAffected = command.ExecuteNonQuery();

            Console.WriteLine(
                $"Records deleted: {rowsAffected}");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Delete failed: {ex.Message}");
        }
    }
}

Possible output:

Records deleted: 1

This removes the student with ID 1 if that record exists.

For applications that allow users to delete records, consider confirmation prompts, authorization checks, audit logs, and transaction requirements.

10. Use SqlDataAdapter and DataTable

SqlDataReader is useful for reading rows sequentially. Sometimes, however, you need an in-memory table that you can bind to a UI or inspect independently of the reader.

SqlDataAdapter can fill a DataTable with query results.

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

class Program
{
    static void Main()
    {
        string connectionString =
            "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        string query =
            "SELECT Id, Name, Age, Course FROM Students";

        using SqlConnection connection =
            new SqlConnection(connectionString);

        using SqlDataAdapter adapter =
            new SqlDataAdapter(query, connection);

        DataTable table = new DataTable();

        try
        {
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(
                    $"{row["Id"]} - {row["Name"]} - {row["Course"]}");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Data retrieval failed: {ex.Message}");
        }
    }
}

Why use DataTable?

A DataTable stores rows and columns in memory. It can be useful for desktop applications, reporting, data binding, and operations that require working with a local snapshot of query results.

For large result sets, consider streaming records with a reader instead of loading all rows into memory.

11. Use Parameters to Prevent SQL Injection

SQL injection occurs when untrusted input changes the structure of a SQL statement.

Avoid building SQL statements by joining user input directly into query strings.

Unsafe example:

string query =
    "SELECT * FROM Students WHERE Name = '" +
    userInput + "'";

If the input contains specially crafted SQL text, it may alter the query.

Safer example:

string query =
    "SELECT Id, Name, Age, Course " +
    "FROM Students WHERE Name = @Name";

using SqlCommand command =
    new SqlCommand(query, connection);

command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 100)
    .Value = userInput;

The parameter value is treated as data rather than executable SQL syntax.

For identifiers such as table names or column names, SQL parameters cannot substitute for the identifier itself. If identifiers must vary, validate them against a strict allowlist.

12. Handle Database Errors Properly

Database operations can fail for several reasons:

• SQL Server is unavailable.

• The connection string is incorrect.

• Authentication fails.

• The database or table does not exist.

• The application lacks permissions.

• The SQL command contains invalid syntax.

• A network issue interrupts communication.

Use exception handling to manage expected failures.

try
{
    connection.Open();

    // Execute database operations here.
}
catch (SqlException ex)
{
    Console.WriteLine(
        $"A SQL Server error occurred: {ex.Message}");
}
catch (InvalidOperationException ex)
{
    Console.WriteLine(
        $"The connection could not be used: {ex.Message}");
}
finally
{
    // Perform additional cleanup only when needed.
}

In production systems, prefer structured logging and appropriate error responses over exposing detailed database error messages to end users. The using statement already handles disposal, so a finally block is not required solely to close a connection declared inside a using statement.

13. ADO.NET Best Practices

Follow these practices when developing applications with SQL Server:

1. Use parameterized queries. Avoid concatenating user input into SQL commands.

2. Dispose database objects. Use using statements for connections, commands, readers, and adapters when appropriate.

3. Keep credentials secure. Store secrets in configuration or a managed secret store, not in source control.

4. Use least-privilege permissions. Give the application only the database permissions it requires.

5. Handle errors carefully. Log diagnostic details securely and show useful messages to users.

6. Choose the right data-access method. Use SqlDataReader for sequential reading and DataTable when in-memory tabular data is useful.

7. Consider asynchronous APIs. Methods such as OpenAsync(), ExecuteReaderAsync(), and ExecuteNonQueryAsync() can improve responsiveness for I/O-bound applications.

8. Use transactions when required. Transactions help ensure that related database changes succeed or fail together.

9. Avoid unnecessary database calls. Retrieve only the data your application needs.

10. Configure encryption correctly. Use secure connections and validate certificates appropriately.

14. Practical Project: Student Management System

Build a basic student management console application using C#, ADO.NET, and SQL Server.

Project features

• Add a student.

• Display all students.

• Search for a student by ID.

• Update a student's details.

• Delete a student.

• Handle database errors.

• Use parameterized SQL commands.

Suggested development steps

1. Create the SchoolDB database and Students table.

2. Create a C# console project.

3. Install Microsoft.Data.SqlClient.

4. Configure the database connection string.

5. Create separate methods such as AddStudent(), GetStudents(), GetStudentById(), UpdateStudent(), and DeleteStudent().

6. Use parameters for all values supplied by the user.

7. Test each operation against SQL Server.

8. Add input validation and appropriate error handling.

Challenge: Extend the project with course registration, student search by name, and a menu that allows users to select database operations.

15. Quick Quiz

Q1. What is the primary purpose of ADO.NET?

A. Designing web page layouts
B. Accessing and managing data from databases
C. Creating animations
D. Compiling CSS

Q2. Which ADO.NET class represents a connection to SQL Server when using Microsoft.Data.SqlClient?

A. SqlReader
B. SqlConnection
C. DataRow
D. SqlTable

Q3. Which method is commonly used to execute an INSERT statement?

A. ExecuteNonQuery()
B. Read()
C. OpenRead()
D. WriteLine()

Q4. Which component reads query results sequentially?

A. SqlDataReader
B. SqlConnectionString
C. SqlParameterCollection
D. DataColumn

Q5. Why should parameterized queries be used?

A. To make every query execute instantly
B. To help prevent SQL injection and handle values safely
C. To eliminate the need for a database
D. To automatically create tables

Quiz answers

1. B — Accessing and managing data from databases.

2. B — SqlConnection.

3. A — ExecuteNonQuery().

4. A — SqlDataReader.

5. B — To help prevent SQL injection and handle values safely.

16. Frequently Asked Questions

What is ADO.NET in C#?

ADO.NET is a set of .NET data-access libraries used to connect to databases, execute commands, and retrieve or modify data.

How do I connect C# to SQL Server?

Install a SQL Server provider such as Microsoft.Data.SqlClient, configure the connection string, create a SqlConnection, and call Open() or OpenAsync().

What is the difference between SqlDataReader and DataTable?

SqlDataReader reads records sequentially from an active query result. DataTable stores tabular data in memory and can be used after the database operation has finished.

What does ExecuteNonQuery() do?

It executes a command that generally does not return a result set, such as INSERT, UPDATE, or DELETE, and returns an affected-row count when available.

Is ADO.NET still used in modern .NET applications?

Yes. ADO.NET remains useful when developers need direct SQL control and explicit management of database commands and results. Other approaches, such as Entity Framework Core, can be useful when an object-relational mapper better suits the application.

What is SQL injection?

SQL injection is a security vulnerability in which untrusted input changes the structure of a SQL query. Parameterized commands are a key defense for handling user-supplied values safely.

Can ADO.NET execute asynchronous database operations?

Yes. Microsoft.Data.SqlClient provides asynchronous APIs such as OpenAsync(), ExecuteReaderAsync(), and ExecuteNonQueryAsync().

Conclusion

ADO.NET provides the fundamental tools required to connect C# applications to SQL Server. By understanding SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter, and SQL parameters, you can perform essential database operations safely and efficiently.

Practise the CRUD operations in this tutorial and build the Student Management System project to strengthen your understanding of database programming in C#.