Modifying Data with ADO.NET

This document explains how to modify data in a data source using ADO.NET. ADO.NET provides a rich set of objects for interacting with data, including commands for inserting, updating, and deleting records.

Core Concepts

Modifying data typically involves constructing SQL statements (like INSERT, UPDATE, DELETE) and executing them against the data source. ADO.NET facilitates this through the Command object and its derived classes (e.g., SqlCommand for SQL Server).

Using the Command Object

The Command object represents a SQL statement or stored procedure to be executed against a data source. You can create a command and associate it with a connection.

using System.Data.SqlClient;

// Assume 'connectionString' is properly defined
string connectionString = "Your_Connection_String_Here";

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

    string insertQuery = "INSERT INTO Customers (CustomerID, CompanyName, ContactName) VALUES (@CustomerID, @CompanyName, @ContactName)";

    using (SqlCommand command = new SqlCommand(insertQuery, connection))
    {
        // Using parameters to prevent SQL injection
        command.Parameters.AddWithValue("@CustomerID", "ALFKI");
        command.Parameters.AddWithValue("@CompanyName", "Alfreds Futterkiste");
        command.Parameters.AddWithValue("@ContactName", "Maria Anders");

        int rowsAffected = command.ExecuteNonQuery(); // Execute the insert command

        Console.WriteLine($"{rowsAffected} row(s) inserted.");
    }
}
            

ExecuteNonQuery() Method

The ExecuteNonQuery() method is used to execute Transact-SQL statements that do not return a result set, such as INSERT, UPDATE, and DELETE statements. It returns the number of rows affected by the command.

Using Parameters

It is crucial to use parameterized queries when constructing commands that include user-supplied input. This helps prevent SQL injection vulnerabilities. The Parameters collection of the Command object is used to add parameters.

Updating Data

To update existing records, you can use the UPDATE SQL statement in conjunction with the Command object and parameterized queries.

using System.Data.SqlClient;

string connectionString = "Your_Connection_String_Here";

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

    string updateQuery = "UPDATE Customers SET ContactName = @NewContactName WHERE CustomerID = @CustomerID";

    using (SqlCommand command = new SqlCommand(updateQuery, connection))
    {
        command.Parameters.AddWithValue("@NewContactName", "Maria Schmidt");
        command.Parameters.AddWithValue("@CustomerID", "ALFKI");

        int rowsAffected = command.ExecuteNonQuery();

        Console.WriteLine($"{rowsAffected} row(s) updated.");
    }
}
            

Deleting Data

Similarly, deleting records is achieved using the DELETE SQL statement.

using System.Data.SqlClient;

string connectionString = "Your_Connection_String_Here";

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

    string deleteQuery = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

    using (SqlCommand command = new SqlCommand(deleteQuery, connection))
    {
        command.Parameters.AddWithValue("@CustomerID", "ALFKI");

        int rowsAffected = command.ExecuteNonQuery();

        Console.WriteLine($"{rowsAffected} row(s) deleted.");
    }
}
            

Best Practices

• Use using statements: Ensure that disposable objects like connections and commands are properly disposed of to release resources.
• Parameterize queries: Always use parameters to prevent SQL injection.
• Handle exceptions: Implement robust error handling to catch potential issues during database operations.
• Minimize round trips: Batch operations where possible to reduce network latency.
• Understand data concurrency: Be aware of how multiple users or processes might affect data modifications and consider appropriate concurrency control mechanisms.