C# ADO.NET Overview

ADO.NET (ActiveX Data Objects .NET) is a set of components in the .NET Framework that provides a rich set of functionality to interact with various data sources like databases and XML files. In this tutorial, we'll give you an overview of ADO.NET and its main components, as well as a simple example of how to connect to a database, retrieve data, and perform basic CRUD operations.

ADO.NET Components

The main components of ADO.NET are:

a. Connection: Represents a connection to a data source, such as a database server.

b. Command: Represents a SQL statement or stored procedure to be executed against a data source.

c. DataReader: Provides a forward-only, read-only stream of data from a data source.

d. DataAdapter: A bridge between a DataSet and a data source for retrieving and saving data.

e. DataSet: An in-memory cache of data, which can hold multiple DataTables.

Connecting to a Database

To connect to a database, you'll need a connection string, which specifies details like the server address, database name, and authentication information. For this example, we'll use SQL Server.

First, add the following namespaces to your C# project:

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

Create a connection string and a SqlConnection object:

string connectionString = "Server=localhost;Database=myDatabase;User Id=myUser;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);

Retrieving Data

To retrieve data from a database, you'll need a SqlCommand and a SqlDataReader:

string query = "SELECT * FROM Employees";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

Now, you can loop through the results:

while (reader.Read())
{
    Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
}
reader.Close();
connection.Close();

Performing CRUD Operations

To perform Create, Read, Update, and Delete (CRUD) operations, you can use SqlCommand objects:

Create (INSERT):

string insertQuery = "INSERT INTO Employees (FirstName, LastName) VALUES ('John', 'Doe')";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
connection.Open();
insertCommand.ExecuteNonQuery();
connection.Close();

Update:

string updateQuery = "UPDATE Employees SET FirstName = 'Jane' WHERE LastName = 'Doe'";
SqlCommand updateCommand = new SqlCommand(updateQuery, connection);
connection.Open();
updateCommand.ExecuteNonQuery();
connection.Close();

Delete:

string deleteQuery = "DELETE FROM Employees WHERE LastName = 'Doe'";
SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection);
connection.Open();
deleteCommand.ExecuteNonQuery();
connection.Close();

This tutorial provides a basic introduction to ADO.NET in C#. For more advanced features, you can explore topics such as connection pooling, transactions, and asynchronous programming with ADO.NET.