---
title: "ADO.NET Basics"  
description: "ADO.NET (ActiveX Data Objects for .NET) is a data access technology in the .NET framework that allows applications to interact with databases."  
author: "ICSM Computer"  
published: 2025-02-12  
updated: 2025-02-12  
canonical: https://www.mindstick.com/articles/338501/ado-dot-net-basics  
category: "ado.net"  
tags: ["c#", "ado.net"]  
reading_time: 3 minutes  

---

# ADO.NET Basics

[**ADO.NET (ActiveX Data Objects for .NET)**](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-overview) is a data access technology in the .NET framework that allows applications to interact with databases. It provides a bridge between the application and the database using **connected** and **disconnected** architectures.

#### 1. Key Components of ADO.NET

ADO.NET consists of several key components:

**1.1** [**Connection**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=net-9.0-pp)

Establishes a connection to the database.

- **SqlConnection** (for SQL Server)
- **OleDbConnection** (for MS Access, Oracle, etc.)
- **OdbcConnection** (for ODBC data sources)

```cs
using System.Data.SqlClient;

string connectionString = "your_connection_string";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    Console.WriteLine("Connection opened successfully!");
}
```

**1.2** [**Command**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=net-9.0-pp)

Executes SQL queries (SELECT, INSERT, UPDATE, DELETE).

- **SqlCommand** (for SQL Server)
- **OleDbCommand** (for other databases)

```cs
string query = "SELECT * FROM Users";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader["Username"]);
    }
}
```

**1.3** [**DataReader (Connected Mode)**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=net-9.0-pp)

Used for reading data row by row in a **forward-only, read-only** manner.

- **SqlDataReader**
- **OleDbDataReader**

```cs
SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["Username"]);
}
reader.Close();
```

**1.4** [**DataAdapter (Disconnected Mode)**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter?view=net-9.0-pp)

Used to fill a **DataSet** or **DataTable** and perform batch updates.

```cs
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Users", conn);
DataTable dt = new DataTable();
adapter.Fill(dt);

foreach (DataRow row in dt.Rows)
{
    Console.WriteLine(row["Username"]);
}
```

**1.5** [**DataSet**](https://learn.microsoft.com/en-us/dotnet/api/system.data.dataset?view=net-9.0) **&** [**DataTable**](https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable?view=net-9.0)

- **DataSet**: Holds multiple DataTables.
- **DataTable**: Represents a single table of in-memory data.

```cs
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Users", conn);
adapter.Fill(ds, "Users");

foreach (DataRow row in ds.Tables["Users"].Rows)
{
    Console.WriteLine(row["Username"]);
}
```

**1.6** [**ExecuteNonQuery (Insert, Update, Delete)**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery?view=net-9.0-pp)

Used for operations that don’t return data (INSERT, UPDATE, DELETE).

```cs
string insertQuery = "INSERT INTO Users (Username, Email) VALUES ('JohnDoe', 'john@example.com')";
SqlCommand cmd = new SqlCommand(insertQuery, conn);
int rowsAffected = cmd.ExecuteNonQuery();
Console.WriteLine($"Rows Inserted: {rowsAffected}");
```

**1.7** [**ExecuteScalar (Returns Single Value)**](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=net-9.0-pp)

Used when a query returns a **single value** (e.g., COUNT, MAX, SUM).

```cs
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn);
int userCount = (int)cmd.ExecuteScalar();
Console.WriteLine($"Total Users: {userCount}");
```

#### 2. [Transactions in ADO.NET](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-ver16)

Used to execute multiple queries as a **single unit of work**.

```cs
SqlTransaction transaction = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = transaction;

try
{
    cmd.CommandText = "INSERT INTO Users (Username) VALUES ('User1')";
    cmd.ExecuteNonQuery();

    cmd.CommandText = "INSERT INTO Users (Username) VALUES ('User2')";
    cmd.ExecuteNonQuery();

    transaction.Commit();
    Console.WriteLine("Transaction committed successfully!");
}
catch
{
    transaction.Rollback();
    Console.WriteLine("Transaction rolled back.");
}
```

#### 3. Connection String Example

A **connection string** contains information needed to connect to a database.

```cs
string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;";
SqlConnection conn = new SqlConnection(connectionString);
```

#### 4. Best Practices

1. Use `using` statements to automatically close connections.
2. Use **parameterized queries** to prevent SQL injection.
3. Use **transactions** for multiple related queries.
4. Use `DataReader` **for performance**, `DataSet/DataTable` for flexibility.

#### Conclusion

ADO.NET is a powerful and flexible data access technology in .NET, allowing applications to interact with databases efficiently using **connected (DataReader)** and **disconnected (DataSet, DataTable)** approaches. By leveraging key components like `SqlConnection`, `SqlCommand`, `SqlDataReader`, `SqlDataAdapter`, and transactions, developers can perform CRUD operations securely and efficiently.

---

Original Source: https://www.mindstick.com/articles/338501/ado-dot-net-basics

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.