Using ADO.NET for Data Access

Introduction

ADO.NET is a data access technology from the .NET Framework that provides communication between relational and non-relational systems through a common set of components. In this tutorial, we will cover how to use ADO.NET for data access, including setting up a connection, executing commands, and performing CRUD operations.

Setting Up ADO.NET

To start using ADO.NET, you need to have the necessary namespaces and libraries available in your project.

Namespaces

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

Establishing a Connection

Establish a connection to your database using the SqlConnection class.

Example Connection String

string connectionString = "Data Source=your_server;Initial Catalog=your_database;Integrated Security=True;";
// OR
string connectionString = "Data Source=your_server;Initial Catalog=your_database;User ID=your_username;Password=your_password;";

Opening a Connection

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Use the connection
    connection.Close();
}

Executing Commands

Use the SqlCommand class to execute commands against the database.

Executing a Query

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "SELECT * FROM Products";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["ProductName"]}, {reader["Price"]}");
            }
        }
    }

    connection.Close();
}

Executing a Non-Query

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "INSERT INTO Products (ProductName, Price) VALUES ('New Product', 9.99)";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }

    connection.Close();
}

CRUD Operations

Performing Create, Read, Update, and Delete operations using ADO.NET.

Create

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "INSERT INTO Products (ProductName, Price) VALUES (@name, @price)";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@name", "New Product");
        command.Parameters.AddWithValue("@price", 9.99);

        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }

    connection.Close();
}

Read

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "SELECT * FROM Products";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["ProductName"]}, {reader["Price"]}");
            }
        }
    }

    connection.Close();
}

Update

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "UPDATE Products SET Price = @price WHERE ProductName = @name";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@name", "New Product");
        command.Parameters.AddWithValue("@price", 19.99);

        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }

    connection.Close();
}

Delete

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string query = "DELETE FROM Products WHERE ProductName = @name";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@name", "New Product");

        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }

    connection.Close();
}

Conclusion

In this tutorial, you learned how to use ADO.NET for data access in a .NET application. We covered setting up the connection, executing commands, and performing CRUD operations.