ADO.NET (ActiveX Data Objects for .NET) 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

Establishes a connection to the database.

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

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

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

• SqlCommand (for SQL Server)
• OleDbCommand (for other databases)

 

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)

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

• SqlDataReader
• OleDbDataReader

 

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)

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

 

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 & DataTable

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

 

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)

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

 

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)

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

 

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

 

 

2. Transactions in ADO.NET

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

 

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.

 

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.