Manipulating Data with ADO.NET

ADO.NET provides a rich set of classes for interacting with data sources, allowing you to perform common data manipulation operations such as inserting, updating, and deleting records. This section delves into the fundamental techniques for modifying data.

Introduction to Data Modification

The core of data manipulation in ADO.NET revolves around executing SQL statements (or stored procedures) that perform data modification language (DML) operations. This is typically achieved using the Command object and its associated methods.

Executing INSERT, UPDATE, and DELETE Statements

The most straightforward way to modify data is by creating a Command object with an appropriate SQL statement and executing it against the data source. The ExecuteNonQuery() method is used for commands that do not return a result set, such as INSERT, UPDATE, and DELETE.

Example: Inserting a New Record

The following C# code snippet demonstrates how to insert a new customer record into a database:


using System;
using System.Data;
using System.Data.SqlClient; // Or your specific provider

public class DataManipulator
{
    private string connectionString = "YourConnectionStringHere";

    public void InsertCustomer(string name, string email)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Email", email);

            try
            {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error inserting customer: {ex.Message}");
            }
        }
    }
}

Using Parameters for Safety

It is crucial to use parameterized queries to prevent SQL injection vulnerabilities. The Command.Parameters collection allows you to add parameters to your SQL statements, ensuring that data is treated as values and not as executable code.

Example: Updating an Existing Record

This example shows how to update a customer's email address based on their ID:


public void UpdateCustomerEmail(int customerId, string newEmail)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "UPDATE Customers SET Email = @Email WHERE CustomerID = @CustomerID";
        SqlCommand command = new SqlCommand(query, connection);

        command.Parameters.AddWithValue("@Email", newEmail);
        command.Parameters.AddWithValue("@CustomerID", customerId);

        try
        {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"{rowsAffected} row(s) updated.");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error updating customer: {ex.Message}");
        }
    }
}

Deleting Records

Deleting records follows a similar pattern, using ExecuteNonQuery() with a DELETE statement. Always be cautious when deleting data and ensure you have appropriate safeguards or backup procedures in place.

Example: Deleting a Customer


public void DeleteCustomer(int customerId)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
        SqlCommand command = new SqlCommand(query, connection);

        command.Parameters.AddWithValue("@CustomerID", customerId);

        try
        {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"{rowsAffected} row(s) deleted.");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error deleting customer: {ex.Message}");
        }
    }
}

Modifying Data via DataAdapters and Datasets

While direct execution of commands is common, ADO.NET also supports updating data through DataAdapter and DataSet objects. This approach is particularly useful when working with disconnected data scenarios or when applying changes made to a dataset back to the original data source.

The DataAdapter has InsertCommand, UpdateCommand, and DeleteCommand properties that can be configured to manage these operations when the Update() method of the DataAdapter is called.

Best Practices