ADO.NET Tutorial - Understanding Data Access in .NET

ADO.NET is a data access technology developed by Microsoft as part of the .NET framework. It allows you to access any data source it can find, providing a comprehensive collection of data components for building high-performance client-server programs and networked contexts via the Internet and intranets.

ADO.NET Architecture

ADO.NET consists of a set of classes that provide data access and manipulation capabilities. The main components of ADO.NET are:

Connection: Represents a connection to a data source.
Command: Represents a SQL statement or stored procedure to execute against a data source.
DataReader: Provides a forward-only, read-only cursor that enables you to retrieve rows from a data source.
DataSet: Represents an in-memory cache of data.
DataTable: Represents a single table within a DataSet.
DataRow: Represents a row in a DataTable.
DataColumn: Represents a column in a DataTable.

Accessing Databases with ADO.NET

To access a database with ADO.NET, you need to follow these steps:

Create a Connection object to connect to the database.
Create a Command object to execute a SQL query or stored procedure.
Execute the Command object and retrieve the results using a DataReader or DataSet.

Here's an example of how to retrieve data from a SQL Server database using ADO.NET:

// Step 1: Create a Connection object
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=MyDatabase;Integrated Security=True"))
{
    // Step 2: Create a Command object
    SqlCommand command = new SqlCommand("SELECT * FROM Customers", connection);

    // Step 3: Open the connection and execute the Command object
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    // Process the results
    while (reader.Read())
    {
        Console.WriteLine("Customer ID: {0}, Customer Name: {1}", reader["CustomerID"], reader["ContactName"]);
    }

    // Close the connection
    connection.Close();
}

In this example, we create a SqlConnection object to connect to a SQL Server database. We then create a SqlCommand object to execute a SQL query that retrieves all rows from the Customers table. Finally, we open the connection, execute the command, and process the results using a SqlDataReader.

SQL Queries and Stored Procedures

ADO.NET supports both SQL queries and stored procedures. To execute a SQL query, you can use the Command object's ExecuteReader method, as shown in the previous example. To execute a stored procedure, you can use the Command object's ExecuteNonQuery method, like this:

// Create a Connection object
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=MyDatabase;Integrated Security=True"))
{
    // Create a Command object
    SqlCommand command = new SqlCommand("usp_InsertCustomer", connection);

    // Set the CommandType property to StoredProcedure
    command.CommandType = CommandType.StoredProcedure;

    // Add parameters
    command.Parameters.AddWithValue("@CustomerName", "New Customer");
    command.Parameters.AddWithValue("@ContactName", "John Doe");

    // Open the connection and execute the Command object
    connection.Open();
    command.ExecuteNonQuery();

    // Close the connection
    connection.Close();
}

In this example, we create a SqlCommand object to execute a stored procedure named usp_InsertCustomer. We set the CommandType property to StoredProcedure and add parameters using the Parameters property.

Coming Soon