The SqlCommand class in ADO.NET is used to execute SQL queries, stored procedures, and commands against a SQL Server database. It works with the SqlConnection object to perform CRUD (Create, Read, Update, Delete) operations.

Namespace:

using System.Data.SqlClient;

1. Creating a SqlCommand Object

To execute a SQL command, you need a SQL query and a database connection.

Example: Creating and Executing a Simple Query

string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string query = "SELECT COUNT(*) FROM Users";
    
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        int userCount = (int)cmd.ExecuteScalar();
        Console.WriteLine("Total Users: " + userCount);
    }
}

1. new SqlCommand(query, conn): Creates a command object.
2. ExecuteScalar(): Returns a single value (useful for counts, sums, etc.).

 

2. Different Ways to Execute Commands

The SqlCommand class provides three main execution methods:

2.1. ExecuteReader() (Retrieve Multiple Rows)

Use this method when you need to fetch multiple rows of data.

using (SqlCommand cmd = new SqlCommand("SELECT Id, Username FROM Users", conn))
{
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["Id"]}, Username: {reader["Username"]}");
    }
}

Selecting multiple rows (e.g., SELECT * FROM Users).

 

2.2. ExecuteScalar() (Retrieve a Single Value)

Use this method when you need only one value, such as a count or sum.

using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn))
{
    int count = (int)cmd.ExecuteScalar();
    Console.WriteLine("Total Users: " + count);
}

Aggregates like COUNT(), SUM(), MAX(), MIN().

2.3. ExecuteNonQuery() (Insert, Update, Delete)

Use this method when performing INSERT, UPDATE, DELETE, or DDL statements (CREATE TABLE, etc.).

string insertQuery = "INSERT INTO Users (Username, Email) VALUES ('JohnDoe', 'john@example.com')";

using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
{
    int rowsAffected = cmd.ExecuteNonQuery();
    Console.WriteLine(rowsAffected + " row(s) inserted.");
}

Best for: INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE.

 

3. Using Parameters to Prevent SQL Injection

Always use parameterized queries instead of concatenating strings to avoid SQL injection.

Example: Using Parameters in an INSERT Query

string insertQuery = "INSERT INTO Users (Username, Email) VALUES (@Username, @Email)";

using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
{
    cmd.Parameters.AddWithValue("@Username", "JaneDoe");
    cmd.Parameters.AddWithValue("@Email", "jane@example.com");

    int rowsInserted = cmd.ExecuteNonQuery();
    Console.WriteLine(rowsInserted + " row(s) inserted.");
}

1. Protects against SQL injection
2. Improves performance by allowing SQL Server to cache execution plans

 

4. Executing Stored Procedures with SqlCommand

Example: Calling a Stored Procedure

using (SqlCommand cmd = new SqlCommand("GetUserById", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserId", 1);

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Username: {reader["Username"]}, Email: {reader["Email"]}");
        }
    }
}

1. Supports parameterized stored procedures
2. Improves security and performance

 

5. Handling Transactions with SqlCommand

Use transactions when executing multiple dependent queries.

SqlTransaction transaction = conn.BeginTransaction();
try
{
    using (SqlCommand cmd = new SqlCommand("UPDATE Users SET Email = 'new@example.com' WHERE Id = 1", conn, transaction))
    {
        cmd.ExecuteNonQuery();
    }

    transaction.Commit(); // Commit the changes
    Console.WriteLine("Transaction committed successfully!");
}
catch
{
    transaction.Rollback(); // Rollback if any error occurs
    Console.WriteLine("Transaction rolled back.");
}

Ensures data consistency in case of errors.

 

6. Best Practices for Using SqlCommand

1. Always use using statements to release resources automatically.
2. Use parameterized queries to prevent SQL injection.
3. Use SqlTransaction for batch operations to maintain data integrity.
4. Close SqlDataReader after use to free memory.
5. Avoid hardcoding queries; use stored procedures when possible.

 

The SqlCommand class is essential in ADO.NET for executing SQL queries and stored procedures. By leveraging parameterized queries, transactions, and error handling, you can build efficient and secure database-driven applications.