---
title: "ADO.NET SqlCommand Class"  
description: "The SqlCommand class in ADO.NET is used to execute SQL queries, stored procedures, and commands against a SQL Server database."  
author: "ICSM Computer"  
published: 2025-02-12  
updated: 2025-02-12  
canonical: https://www.mindstick.com/blog/305244/ado-dot-net-sqlcommand-class  
category: "ado.net"  
tags: ["c#", "ado.net"]  
reading_time: 3 minutes  

---

# ADO.NET SqlCommand Class

The `SqlCommand` class in ADO.NET is used to execute **SQL queries, [stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application), and commands** against a SQL [Server database](https://www.mindstick.com/forum/155643/how-to-create-sql-server-database-in-google-cloud). It works with the `SqlConnection` object to perform **CRUD (Create, Read, Update, Delete)** operations.

## Namespace:

```plaintext
using System.Data.SqlClient;
```

#### 1. Creating a `SqlCommand` Object

To execute a SQL command, you need a **SQL query** and a **[database connection](https://www.mindstick.com/forum/159617/how-to-connect-to-a-database-connection-in-java)**.

## Example: Creating and Executing a Simple Query

```cs
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](https://www.mindstick.com/forum/157166/what-is-the-best-and-fast-way-to-insert-2-million-rows-of-data-into-sql-server)**.

```cs
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](https://www.mindstick.com/forum/34393/selecting-multiple-columns-in-linq) 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.

```cs
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.).

```cs
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**

```cs
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](https://answers.mindstick.com/qa/41523/which-amendment-to-the-u-s-constitution-protects-against-unreasonable-searches-and-seizures) SQL injection
2. Improves performance by allowing SQL Server to cache execution plans

#### 4. Executing Stored Procedures with `SqlCommand`

**Example: Calling a [Stored Procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net)**

```cs
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](https://yourviews.mindstick.com/view/84076/wordpress-proposal-to-improve-security-and-performance-of-plugins)

#### 5. Handling Transactions with `SqlCommand`

Use transactions when executing **multiple dependent queries**.

```cs
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](https://www.mindstick.com/forum/159960/how-can-you-ensure-data-consistency-in-a-microservices-architecture)** 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](https://www.mindstick.com/forum/776/are-pdo-prepared-statements-sufficient-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.

---

Original Source: https://www.mindstick.com/blog/305244/ado-dot-net-sqlcommand-class

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.