Back to: ADO.NET Core Tutorial For Beginners and Professionals

ADO.NET Core SqlCommand Class

In this article, I will discuss the ADO.NET Core SqlCommand Class in Depth with Examples. Please read our previous article discussing the ADO.NET Core SqlConnection Class.

What is the ADO.NET Core SqlCommand Class?

The SqlCommand is a class provided by ADO.NET (the data access technology from Microsoft) used to execute Transact-SQL statements and stored procedures against a SQL Server database. In .NET Core applications (using either System.Data.SqlClient or the newer Microsoft.Data.SqlClient package), it provides methods to execute commands that return data in various forms (like result sets, single values, or rows affected). The class is fundamental when building data-driven applications and supports synchronous and asynchronous operations. It provides methods to:

• Executing SQL queries to return data (SELECT)
• Executing SQL commands that modify data (INSERT, UPDATE, DELETE)
• Executing stored procedures

Key Properties and Methods of ADO.NET Core SqlCommand Class

The ADO.NET Core SqlCommand class provides several important properties and methods for database operations:

Key Properties of SqlCommand Class

• CommandText: Stores the SQL query or stored procedure name to be executed.
• Connection: The SqlConnection object that this command will use for executing the query.
• CommandType: Determines whether CommandText is a raw SQL statement (CommandType.Text) or a stored procedure (CommandType.StoredProcedure).
• Parameters: A collection of SqlParameter objects associated with the SqlCommand for parameterized queries.
• Transaction: The SqlTransaction object within which this SqlCommand executes.
• CommandTimeout: Time in seconds before the command times out.

Key Methods of SqlCommand Class

• ExecuteReader(): Executes the command and returns a SqlDataReader for reading returned rows.
• ExecuteScalar(): Executes the command and returns the first column of the first row in the result set. It is useful for retrieving single values (such as counts or max/min values).
• ExecuteNonQuery(): Executes a command that does not return any rows; typically used for UPDATE, INSERT, or DELETE operations. Returns the number of rows affected.
• Dispose(): Releases the resources used by the command.
• Asynchronous Methods: Async versions like ExecuteReaderAsync(), ExecuteScalarAsync(), and ExecuteNonQueryAsync() allow for non-blocking database calls.

Example to understand the ADO.NET Core SqlCommand Object:

We will use the following Employee table to understand the ADO.NET Core SqlCommand object using a .NET Console Application.

Please execute the following SQL script in your SQL Server to create the EmployeeDB database and the Employee table and insert some dummy data into it.

CREATE DATABASE EmployeeDB;
GO

USE EmployeeDB;
GO

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email VARCHAR(50),
    Position NVARCHAR(50),
    Salary DECIMAL(18, 2)
);

INSERT INTO Employee (FirstName, LastName, Email, Position, Salary)
VALUES 
('Anurag', 'Mohanty', 'Anurag@Example.com', 'Software Developer', 60000),
('Pranaya', 'Rout', 'Pranaya@Example.com', 'Project Manager', 65000),
('Hina', 'Sharma', 'Hina@Example.com', 'Database Administrator', 59000);

Different Ways to Create an Instance of SqlCommand Object in ADO.NET Core

The SqlCommand class provides several constructors to initialize command objects, giving developers flexibility in setting up commands. Each constructor is designed to handle specific scenarios, from creating a command without setting any properties upfront to specifying the SQL query and connection at the instantiation time. The following are the available constructors with their syntax and typical use-cases:

Default Constructor:

This constructor initializes a new instance of the SqlCommand class with no properties initialized. This means that before executing the command, you need to assign the CommandText, Connection, and optionally Transaction and other properties. It is ideal to dynamically assign the SQL statement, connection, or other properties at runtime. The syntax to use this Constructor is given below:

SqlCommand Constructor with Command Text Only:

This constructor initializes a new instance of the SqlCommand class with the specified command text. At this point, there is no associated SqlConnection. You must set the Connection property before executing. It is useful when you have the SQL statement ready, but the connection to the database is to be set later. The syntax to use this Constructor is given below:

SqlCommand Constructor with Command Text and Connection:

It initializes a new instance of the SqlCommand class with the specified command text and connection. It is ideal for scenarios where the SQL statement and the connection to the database are known upfront. The syntax to use this Constructor is given below:

SqlCommand Constructor with Command Text, Connection, and Transaction:

This constructor initializes a new instance of the SqlCommand class with the specified command text, connection, and transaction. It is ideal for scenarios where the command is part of a transaction, ensuring that the command execution respects transaction boundaries. It is essential for operations that need to be executed within the context of a transaction, offering a way to ensure data integrity and consistency by either committing or rolling back changes as a single unit. The syntax to use this Constructor is given below:

ExecuteReader Method of ADO.NET Core SqlCommand Class:

The ExecuteReader method of the SQLCommand object executes SQL commands that return rows from a data source. This method is essential for operations that require data to be read from a database, such as SELECT queries.

When you call ExecuteReader, it returns an instance of SqlDataReader. This reader provides a way to read a forward-only stream of rows from the SQL Server database. The SqlDataReader requires an active and open connection to retrieve the data. For a better understanding, please modify the Program class as follows. The following example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient; 

namespace EmployeeApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define the connection string to the EmployeeDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // Create a SQL query to fetch all rows from the Employee table.
            string query = "SELECT EmployeeID, FirstName, LastName, Email, Position, Salary FROM Employee";

            // Initialize a SqlConnection object using a 'using' statement to ensure proper disposal.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Create an instance of SqlCommand using the constructor with command text and connection.
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    try
                    {
                        // Open the database connection.
                        connection.Open();

                        // Execute the command, returning a SqlDataReader.
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // Check if the reader has rows.
                            if (reader.HasRows)
                            {
                                // Loop through the rows in the result set.
                                while (reader.Read())
                                {
                                    // Retrieve data by string column name or integer index position.
                                    int employeeId = reader.GetInt32(0);     // EmployeeID
                                    string firstName = reader.GetString(1);  // FirstName
                                    string lastName = reader.GetString(2);   // LastName
                                    string email = reader.GetString(3);      // Email
                                    string position = reader.GetString(4);   // Position
                                    decimal salary = reader.GetDecimal(5);   // Salary

                                    //Using string column name
                                    //int employeeId = reader.GetInt32(reader.GetOrdinal("EmployeeID"));
                                    //string firstName = reader.GetString(reader.GetOrdinal("FirstName"));
                                    //string lastName = reader.GetString(reader.GetOrdinal("LastName"));
                                    //string email = reader.GetString(reader.GetOrdinal("Email"));
                                    //string position = reader.GetString(reader.GetOrdinal("Position"));
                                    //decimal salary = reader.GetDecimal(reader.GetOrdinal("Salary"));

                                    // Accessing the data from Reader using name
                                    // Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");

                                    // Display the data on the console.
                                    Console.WriteLine($"ID: {employeeId}, Name: {firstName} {lastName}, Email: {email}, Position: {position}, Salary: {salary}");
                                }
                            }
                            else
                            {
                                Console.WriteLine("No rows found.");
                            }
                        }
                    }
                    catch (SqlException ex)
                    {
                        // Handle any SQL related errors.
                        Console.WriteLine("SQL Error: " + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        // Handle any other exceptions
                        Console.WriteLine($"Something went wrong: {ex.Message}");
                    }
                    finally
                    {
                        // The connection will automatically be closed when exiting the using block.
                    }
                }
            }
        }
    }
}

Output:

Code Explanation:

• Using Statement: Automatically disposes of the SqlConnection, SqlCommand, and SqlDataReader objects after use.
• connection.Open(): Opens the database connection.
• reader.Read(): Iterates over each row in the result set.
• GetOrdinal & GetXXX: Retrieves column ordinal and then gets the data using the appropriate data type method.

ExecuteScalar Method of ADO.NET Core SqlCommand Class:

The ExecuteScalar method of the SqlCommand object is used to execute SQL commands that return a single value. The result returned by ExecuteScalar is an object, which you can convert to the expected type. This method is useful when you want to retrieve a single piece of information from a database, such as an aggregate value (e.g., COUNT, MAX, MIN, SUM) or a value from a specific column in a specific row. Here is how to use the ExecuteScalar method within a .NET Core application:

For a better understanding, please modify the Program class as follows. The following example demonstrates how to use ExecuteScalar to fetch a single value, in this case, the count of employees in the table. The following example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient; 

namespace EmployeeApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection string to the EmployeeDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // SQL query to count the number of records in the Employee table.
            string query = "SELECT COUNT(*) FROM Employee";

            // Create and open the connection within a using block.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Initialize the SqlCommand using the constructor that takes command text and connection.
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        // Set the SQL query to count the number of employees.
                        command.CommandText = query;

                        // Associate the command with the connection.
                        command.Connection = connection;

                        // Open the database connection.
                        command.Connection.Open();

                        // ExecuteScalar returns the first column of the first row from the result.
                        object result = command.ExecuteScalar();

                        // Convert the result to an integer.
                        int count = Convert.ToInt32(result);

                        // Display the result to the console.
                        Console.WriteLine("Total number of employees: " + count);
                    }
                    catch (SqlException ex)
                    {
                        // Display any SQL related exceptions.
                        Console.WriteLine("SQL Error: " + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        // Handle any other exceptions
                        Console.WriteLine($"Something went wrong: {ex.Message}");
                    }
                }
            }
        }
    }
}

Output: Total number of employees: 3

Code Explanation:

• Default Constructor: Initializes the command without parameters.
• Setting CommandText & Connection: The command text and connection are set as properties of the command.
• ExecuteScalar: This function executes the query to retrieve a single value (count) and then converts it to an integer.

ExecuteNonQuery Method of ADO.NET Core SqlCommand Class:

The ExecuteNonQuery method of the SqlCommand object is used for executing SQL commands that do not return any data, rather it returned the number of rows affected by the command in the database. These include INSERT, UPDATE, DELETE, and DDL (Data Definition Language) commands like CREATE TABLE and ALTER TABLE.

For a better understanding, please modify the Program class as follows. Here, we are inserting a new employee into the Employee database table. The following example shows how to use ExecuteNonQuery to insert a new employee record into the Employee table. We also demonstrate using the constructor with command text, connection, and transaction. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient; 

namespace EmployeeApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Define the connection string to the EmployeeDB database.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            // SQL command to insert a new employee record.
            string insertQuery = @"INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) 
                                   VALUES (@FirstName, @LastName, @Email, @Position, @Salary)";

            // Create a connection object using a using block.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Open the connection.
                connection.Open();

                // Begin a transaction to ensure the insert operation is atomic.
                SqlTransaction transaction = connection.BeginTransaction();

                // Create a SqlCommand instance using the constructor with command text, connection, and transaction.
                using (SqlCommand command = new SqlCommand(insertQuery, connection, transaction))
                {
                    try
                    {
                        // Define parameters and assign values.
                        command.Parameters.AddWithValue("@FirstName", "John");
                        command.Parameters.AddWithValue("@LastName", "Doe");
                        command.Parameters.AddWithValue("@Email", "john.doe@example.com");
                        command.Parameters.AddWithValue("@Position", "Quality Assurance");
                        command.Parameters.AddWithValue("@Salary", 55000);

                        // Execute the command.
                        // ExecuteNonQuery returns the number of affected rows.
                        int rowsAffected = command.ExecuteNonQuery();

                        // Commit the transaction if the insert was successful.
                        transaction.Commit();

                        // Write the number of affected rows to the console.
                        Console.WriteLine($"{rowsAffected} row(s) inserted into the Employee table.");
                    }
                    catch (Exception ex)
                    {
                        // Roll back the transaction if an error occurs.
                        transaction.Rollback();
                        Console.WriteLine("Error: " + ex.Message);
                    }
                }
            }
        }
    }
}

Output: 1 row(s) inserted into the Employee table.

ExecuteReader vs ExecuteScalar vs ExecuteNonQuery

The SqlCommand object in ADO.NET Core provides different methods for executing commands against a database connection.

ExecuteReader:

• Used when the command returns multiple rows and columns.
• Returns a SqlDataReader object to iterate through the result set.
• Ideal for SELECT queries with multiple results.

ExecuteScalar:

• Used when the command returns a single value from the first column of the first row in the result set.
• Returns an object that needs to be cast or converted to the appropriate type.
• Perfect for queries such as COUNT(*), SUM(), or retrieving a single aggregate value.

ExecuteNonQuery:

• Used for executing commands that do not return any rows (e.g., INSERT, UPDATE, DELETE).
• Returns the number of rows affected. For DDL statements that do not affect rows directly, the return value is -1.
• Often used when you need to modify data rather than retrieve it.

Asynchronous Database Operations using ADO.NET Core SqlCommand

ADO.NET Core provides asynchronous versions of many methods used for database operations, including those for SqlCommand. These asynchronous methods typically have the same name as their synchronous counterparts but with an “Async” suffix. These methods involve working with tasks and the async/await pattern in C#. The following are the key asynchronous methods provided by the SqlCommand class:

• ExecuteReaderAsync(): Executes commands that return rows.
• ExecuteNonQueryAsync(): Executes commands such as SQL INSERT, DELETE, UPDATE, and SET statements.
• ExecuteScalarAsync(): Executes commands that return a single value.

Asynchronous Database Operations using ADO.NET Core SqlCommand

Asynchronous operations in .NET allow the calling thread to continue execution without being blocked while the database operation is in progress. This is especially useful in high-scale or UI applications where you don’t want to block the main thread.

ADO.NET Core supports async database operations through ExecuteReaderAsync, ExecuteScalarAsync, and ExecuteNonQueryAsync. These methods allow non-blocking operations, improving scalability and responsiveness in modern applications.

The following example demonstrates how to use SqlCommand asynchronous methods (ExecuteReaderAsync, ExecuteNonQueryAsync, and ExecuteScalarAsync) to execute a query against a SQL database. The example code is self-explained, so please read the comment lines for a better understanding.

using Microsoft.Data.SqlClient;

namespace EmployeeApp
{
    class Program
    {
        // The Main method is declared async to await asynchronous operations.
        static async Task Main(string[] args)
        {
            // Define the connection string.
            string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";

            try
            {
                // Example asynchronous usage with ExecuteReaderAsync.
                await FetchEmployeesAsync(connectionString);

                // Example asynchronous usage with ExecuteScalarAsync.
                await GetEmployeeCountAsync(connectionString);

                // Example asynchronous usage with ExecuteNonQueryAsync.
                await InsertEmployeeAsync(connectionString);
            }
            catch (Exception ex)
            {
                // Catch any exceptions that bubble up from the asynchronous methods.
                Console.WriteLine($"General Error in Main: {ex.Message}");
            }

            Console.WriteLine("Asynchronous operations completed.");
            Console.ReadKey();
        }

        // Asynchronously fetches employee data using ExecuteReaderAsync.
        static async Task FetchEmployeesAsync(string connectionString)
        {
            string query = "SELECT EmployeeID, FirstName, LastName, Email, Position, Salary FROM Employee";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        await connection.OpenAsync();  // Open the connection asynchronously
                        using (SqlDataReader reader = await command.ExecuteReaderAsync())
                        {
                            Console.WriteLine("Employee List:");
                            while (await reader.ReadAsync())
                            {
                                // Read each field asynchronously.
                                int employeeId = reader.GetInt32(0);
                                string firstName = reader.GetString(1);
                                string lastName = reader.GetString(2);
                                string email = reader.GetString(3);
                                string position = reader.GetString(4);
                                decimal salary = reader.GetDecimal(5);

                                // Output the employee details.
                                Console.WriteLine($"ID: {employeeId}, Name: {firstName} {lastName}, Email: {email}, Position: {position}, Salary: {salary}");
                            }
                        }
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                // Handle SQL-specific exceptions.
                Console.WriteLine($"SQL Error in FetchEmployeesAsync: {sqlEx.Message}");
            }
            catch (Exception ex)
            {
                // Handle any other unexpected exceptions.
                Console.WriteLine($"Unexpected error in FetchEmployeesAsync: {ex.Message}");
            }
        }

        // Asynchronously fetches the employee count using ExecuteScalarAsync.
        static async Task GetEmployeeCountAsync(string connectionString)
        {
            string query = "SELECT COUNT(*) FROM Employee";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        await connection.OpenAsync();  // Open the connection asynchronously
                        // Execute the query and retrieve the aggregated value.
                        object? result = await command.ExecuteScalarAsync();
                        int count = Convert.ToInt32(result);
                        Console.WriteLine("Total employee count: " + count);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                // Handle SQL-specific exceptions.
                Console.WriteLine($"SQL Error in GetEmployeeCountAsync: {sqlEx.Message}");
            }
            catch (Exception ex)
            {
                // Handle any other unexpected exceptions.
                Console.WriteLine($"Unexpected error in GetEmployeeCountAsync: {ex.Message}");
            }
        }

        // Asynchronously inserts a new employee using ExecuteNonQueryAsync with transaction support.
        static async Task InsertEmployeeAsync(string connectionString)
        {
            string insertQuery = @"INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) 
                                   VALUES (@FirstName, @LastName, @Email, @Position, @Salary)";

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    await connection.OpenAsync();  // Open the connection asynchronously

                    // Begin a transaction to ensure the operation is atomic.
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        using (SqlCommand command = new SqlCommand(insertQuery, connection, transaction))
                        {
                            // Define parameters with sample data.
                            command.Parameters.AddWithValue("@FirstName", "Alice");
                            command.Parameters.AddWithValue("@LastName", "Brown");
                            command.Parameters.AddWithValue("@Email", "alice.brown@example.com");
                            command.Parameters.AddWithValue("@Position", "Business Analyst");
                            command.Parameters.AddWithValue("@Salary", 62000);

                            try
                            {
                                // Execute the command asynchronously.
                                int rowsAffected = await command.ExecuteNonQueryAsync();
                                // Commit the transaction on success.
                                transaction.Commit();
                                Console.WriteLine($"{rowsAffected} row(s) inserted asynchronously.");
                            }
                            catch (SqlException sqlEx)
                            {
                                // Roll back the transaction in case of a SQL exception.
                                try
                                {
                                    transaction.Rollback();
                                }
                                catch (Exception rollBackEx)
                                {
                                    // Log any errors during rollback.
                                    Console.WriteLine($"Rollback Error: {rollBackEx.Message}");
                                }
                                Console.WriteLine($"SQL Error in InsertEmployeeAsync: {sqlEx.Message}");
                            }
                            catch (Exception ex)
                            {
                                // Roll back the transaction for any other exception.
                                try
                                {
                                    transaction.Rollback();
                                }
                                catch (Exception rollBackEx)
                                {
                                    Console.WriteLine($"Rollback Error: {rollBackEx.Message}");
                                }
                                Console.WriteLine($"Unexpected error in InsertEmployeeAsync: {ex.Message}");
                            }
                        }
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                // Handle any connection-specific SQL exceptions.
                Console.WriteLine($"SQL connection error in InsertEmployeeAsync: {sqlEx.Message}");
            }
            catch (Exception ex)
            {
                // Handle any other errors related to connection opening.
                Console.WriteLine($"Unexpected connection error in InsertEmployeeAsync: {ex.Message}");
            }
        }
    }
}

Code Explanation:

• FetchEmployeesAsync: Uses ExecuteReaderAsync() to retrieve multiple rows from the Employee table asynchronously. Each call to reader.ReadAsync() is awaited to ensure rows are read in a non-blocking fashion.
• GetEmployeeCountAsync: Uses ExecuteScalarAsync() to fetch a single value (the count) from the database.
• InsertEmployeeAsync: This demonstrates how to perform an insert operation asynchronously using ExecuteNonQueryAsync() while also managing a transaction to ensure the operation is atomic.

FAQs

What is the purpose of the SqlCommand class in ADO.NET Core?

The SqlCommand class executes SQL queries and stored procedures against SQL Server databases. It can return data as a result set via ExecuteReader(), a single value via ExecuteScalar(), or affect rows in the database via ExecuteNonQuery(). It supports parameterization to protect against SQL injection, transactions for data consistency, and asynchronous methods for better performance in high-load or UI applications.

What are the differences between the ExecuteReader, ExecuteScalar, and ExecuteNonQuery methods?

• ExecuteReader: Used for executing commands that return multiple rows and columns. It returns a SqlDataReader to iterate over the result set.
• ExecuteScalar: Returns the first column of the first row in a result set. It is ideal for aggregate functions or when only a single value is needed.
• ExecuteNonQuery: Executes commands like INSERT, UPDATE, DELETE, or DDL statements. It returns the number of rows affected or, for DDL operations, a value of -1.

How do you protect your SQL queries against SQL Injection when using SqlCommand?

The best practice is to use parameterized queries. Instead of concatenating user inputs into a SQL command string, parameters (e.g., @FirstName, @Email) are used along with SqlParameter objects. This ensures that the inputs are safely escaped and reduces the risk of SQL injection attacks.

How does the asynchronous programming model in ADO.NET Core work with the SqlCommand class?

ADO.NET provides asynchronous methods such as ExecuteReaderAsync(), ExecuteScalarAsync(), and ExecuteNonQueryAsync(). These methods use the async/await pattern in .NET, allowing database operations to run on background threads, thus keeping the calling thread responsive. This is especially useful in UI applications or scenarios with high scalability requirements.

What is the role of the SqlDataReader returned by ExecuteReader()?

SqlDataReader provides a way to read data from the database in a forward-only, read-only manner. You loop through the records using Read() or ReadAsync(). Each call moves to the next row in the result set.

Explain the use of transactions with SqlCommand.

Transactions ensure that a set of operations (multiple SQL commands) either all succeed or all fail. In ADO.NET Core, you start a transaction with SqlTransaction transaction = connection.BeginTransaction(); and pass it to the SqlCommand.Transaction property (or via the constructor). If an error occurs, you can call transaction.Rollback(); to revert changes. If everything succeeds, you call transaction.Commit();.

How do you specify a CommandTimeout, and what does it represent?

We need to set the CommandTimeout property on the SqlCommand (e.g., command.CommandTimeout = 60;). It represents the maximum number of seconds the command will wait before timing out and throwing an exception.

The default command timeout for a SqlCommand object in ADO.NET is 30 seconds. If the command does not complete within 30 seconds, it will throw a SqlException indicating a timeout error. This default value can be changed by setting the CommandTimeout property of the SqlCommand object.

What happens if you call ExecuteReader() but never close the SqlDataReader?

The underlying connection remains busy and cannot be used for other operations until the reader is closed (or the connection is closed/disposed). Always ensure you close or dispose of the SqlDataReader (often via a using block).

What is the difference between synchronous and asynchronous methods in SqlCommand?

Synchronous methods block the calling thread until the operation completes, while asynchronous methods (like ExecuteReaderAsync) allow the thread to continue execution and await the result. This improves application responsiveness and scalability, especially for I/O-bound operations.

How do you handle exceptions when using SqlCommand?

Always use try-catch blocks to handle SqlException and general exceptions. Additionally, ensure proper resource cleanup by using using statements or calling Dispose on disposable objects such as SqlConnection and SqlDataReader.

What are SqlCommand parameters, and why are they important?

SqlCommand parameters pass values to SQL queries or stored procedures. They help prevent SQL injection, provide type safety, and make queries more readable. Using parameters also allows for parameterized queries, which can improve performance by enabling query plan reuse.

ADO.NET is a core data access framework within the .NET ecosystem that allows applications to communicate with relational databases such as SQL Server. The SqlCommand class is one of the central classes used to execute SQL queries and stored procedures against a SQL Server database.

In the next article, I will discuss the ADO.NET Core SqlDataReader Class in detail. In this article, I explain the ADO.NET Core SqlCommand Class with Examples. I want your feedback. Please post feedback, questions, or comments about this article.