ADO.NET: Executing Non-Query Commands (INSERT, UPDATE, DELETE)

This code snippet demonstrates how to execute non-query commands such as INSERT, UPDATE, and DELETE statements using ADO.NET. It illustrates the use of the `ExecuteNonQuery()` method of the `SqlCommand` object to modify data in the database.

Code Snippet

This code demonstrates how to execute INSERT, UPDATE, and DELETE statements using ADO.NET. It creates `SqlCommand` objects for each operation, sets the command text to the corresponding SQL query, adds parameters to the command to prevent SQL injection, and calls the `ExecuteNonQuery()` method to execute the command. The `ExecuteNonQuery()` method returns the number of rows affected by the operation. The code includes `try-catch` blocks for error handling and uses `using` statements to ensure proper resource disposal.

using System; 
using System.Data.SqlClient;

public class DatabaseNonQuery
{
    public static void Main(string[] args)
    {
        // Connection string to the database
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user_id;Password=your_password;";

        // SQL query to insert data
        string insertQuery = "INSERT INTO YourTable (Name, Value) VALUES (@Name, @Value)";

        // SQL query to update data
        string updateQuery = "UPDATE YourTable SET Value = @Value WHERE Id = @Id";

        // SQL query to delete data
        string deleteQuery = "DELETE FROM YourTable WHERE Id = @Id";

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

                // Insert Example
                using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))
                {
                    insertCommand.Parameters.AddWithValue("@Name", "New Record");
                    insertCommand.Parameters.AddWithValue("@Value", 123.45);
                    int rowsAffected = insertCommand.ExecuteNonQuery();
                    Console.WriteLine($"Insert: {rowsAffected} rows affected.");
                }

                // Update Example
                using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                {
                    updateCommand.Parameters.AddWithValue("@Id", 1);  // Replace with an existing ID
                    updateCommand.Parameters.AddWithValue("@Value", 500.00);
                    int rowsAffected = updateCommand.ExecuteNonQuery();
                    Console.WriteLine($"Update: {rowsAffected} rows affected.");
                }

                // Delete Example
                using (SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection))
                {
                    deleteCommand.Parameters.AddWithValue("@Id", 2);  // Replace with an existing ID
                    int rowsAffected = deleteCommand.ExecuteNonQuery();
                    Console.WriteLine($"Delete: {rowsAffected} rows affected.");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"SQL Exception: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Exception: {ex.Message}");
        }
    }
}

Concepts Behind the Snippet

This snippet showcases the `ExecuteNonQuery()` method, which is crucial for executing SQL commands that don't return a result set (i.e., commands that modify data). The usage of parameterized queries is also a key security practice that prevents SQL injection vulnerabilities. Each command is constructed with appropriate parameters to ensure data integrity and security.

Real-Life Use Case

In a content management system (CMS), you might use this pattern to add new articles to the database (INSERT), update the content of existing articles (UPDATE), or remove articles from the database (DELETE). These operations are fundamental to managing dynamic content in web applications and other systems.

Best Practices

• Parameterized Queries: Always use parameterized queries to prevent SQL injection vulnerabilities.
• Error Handling: Implement robust error handling using `try-catch` blocks to handle exceptions and log errors.
• Transaction Management: For operations that involve multiple database modifications, use transactions to ensure data consistency. If one operation fails, all operations should be rolled back.
• Minimal Permissions: Grant the database user only the necessary permissions to perform the required operations. Avoid granting excessive privileges.

Interview Tip

Explain the difference between `ExecuteNonQuery()`, `ExecuteReader()`, and `ExecuteScalar()`. Be able to describe how to use parameterized queries to prevent SQL injection. Also, be prepared to discuss the importance of transaction management for maintaining data consistency.

When to Use ExecuteNonQuery

`ExecuteNonQuery` should be used when you are executing SQL commands that modify the database but do not return rows of data. Common uses include `INSERT`, `UPDATE`, `DELETE`, `CREATE TABLE`, `ALTER TABLE`, and other DDL (Data Definition Language) and DML (Data Manipulation Language) statements.

Alternatives

Alternatives for modifying data:

• Stored Procedures: Encapsulate SQL logic within stored procedures, which can improve performance and security.
• ORM Frameworks: Use an ORM like Entity Framework Core or Dapper for more convenient data access and manipulation.

Pros

• Direct control: You have direct control over the SQL commands being executed.
• Performance: Can be more performant than ORMs for simple data modification operations.

Cons

• Manual mapping: You need to manually construct SQL commands and handle parameters.
• More verbose code: Requires more code compared to using an ORM.