Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core SqlCommand Class in Depth with Examples
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. As part of this article, we will discuss the following pointers in detail.
- What is ADO.NET Core SqlCommand Class?
- Key Properties and Methods of ADO.NET Core SqlCommand Class
- ExecuteReader Method of ADO.NET Core SqlCommand Object
- ExecuteScalar Method of ADO.NET Core SqlCommand Object
- ExecuteNonQuery Method of ADO.NET Core SqlCommand Object
- ExecuteReader vs. ExecuteScalar vs. ExecuteNonQuery
- Different Ways to Create an Instance of SqlCommand Object in ADO.NET Core
- Asynchronous Database Operations using ADO.NET Core SqlCommand.
What is ADO.NET Core SqlCommand Class?
The SqlCommand Object in ADO.NET Core prepares and executes SQL statements against a SQL Server database. It performs database operations such as inserting, deleting, updating, and retrieving data. The class is found in the Microsoft.Data.SqlClient namespace. It also allows the execution of SQL Server Stored Procedures. It can execute SQL queries directly against a database to return data, modify data, or manage database structures.
Properties and Methods of ADO.NET Core SqlCommand Class
The ADO.NET Core SqlCommand class provides several important properties and methods for database operations. They are as follows:
Properties of SqlCommand Class
- CommandText: The CommandText property holds the SQL query or stored procedure to be executed in the SQL Server database.
- CommandType: The CommandType property specifies how the CommandText property is interpreted. It can be either Text (for SQL queries) or StoredProcedure (for stored procedures).
- Connection: The Connection property specifies the SqlConnection object used to execute the command.
- Parameters: The Parameters property is a collection of SqlParameter objects. It allows us to pass parameters to SQL queries and stored procedures to prevent SQL injection attacks and improve performance. We can also pass both input and output parameters.
- Transaction: The Transaction property specifies the SqlTransaction within which the SqlCommand executes.
- CommandTimeout: The CommandTimeout property specifies the wait time before terminating the attempt to execute a command and generating an error.
Methods of SqlCommand Class
It provides efficient ways to execute commands using methods like ExecuteReader for retrieving multiple rows, ExecuteScalar for a single value, and ExecuteNonQuery for executing statements that do not return rows.
- ExecuteReader(): The ExecuteReader() method executes commands that return rows (such as SELECT) and returns a SqlDataReader that can be used to read the rows.
- ExecuteNonQuery(): The ExecuteNonQuery() method executes a command that does not return any values, such as an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected in the database.
- ExecuteScalar(): The ExecuteScalar() method executes the query and returns the first column of the first row in the result set. That means it returns a single scalar value.
- ExecuteXmlReader(): The ExecuteXmlReader() method Executes commands that retrieve XML data from an SQL Server database and returns an XmlReader that can be used to read the XML.
- Prepare(): The Prepare() method creates a prepared (or compiled) version of the command on the database server. This is useful for queries that are executed multiple times with different parameters.
- Cancel(): The Cancel() method attempts to cancel the execution of a SqlCommand.
Example to Understand the ADO.NET Core SqlCommand Object:
The following are the steps of how to use the SqlCommand object in the .NET Core Application:
- Creating a Connection: First, establish a connection to the database using the SqlConnection class.
- Creating a Command: Create an instance of the SqlCommand class and associate it with the connection.
- Executing the Command: Use methods such as ExecuteNonQuery, ExecuteScalar, or ExecuteReader to execute the command.
- Handling the Results: Process the results returned by the command if applicable.
- Closing the Connection: Ensure the connection is properly closed after executing the command.
Let’s understand the SqlCommand object using a Console Application. We will use the following Employee table to understand the ADO.NET Core SqlCommand object.
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);
ExecuteReader Method of ADO.NET Core SqlCommand Object
The ExecuteReader method of the SqlCommand object in ADO.NET Core 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 we 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. For a better understanding, please have a look at the following example, which shows how to use the ExecuteReader method. Here, it reads data from the Employee table by executing the command SELECT * FROM Employee.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Query to Read All Employees Using ExecuteReader
string readQuery = "SELECT * FROM Employee";
//Create an Instance of SqlConnection
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// ExecuteReader Example
Console.WriteLine("ExecuteReader Example");
using (SqlCommand command = new SqlCommand(readQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
}
}
} //Command Object will be disposed automatically
} //Connection Object will be disposed automatically
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Key Points
- Connection Management: Ensure the database connection is open (connection.Open()) before executing the command and close it when you’re done to free up resources.
- Using Statement: It’s a good practice to use using statements for managing the lifecycle of SqlConnection, SqlCommand, and SqlDataReader objects. This ensures that resources are properly disposed of.
- Reading Data: The SqlDataReader provides multiple methods to read data from the database, such as Read() for moving to the next row and properties like HasRows to check if rows are available.
- Accessing Column Data: Access column data in each row using the SqlDataReader indexer or Get* methods (e.g., GetString, GetInt32). Columns can be accessed by name or index.
ExecuteScalar Method of ADO.NET Core SqlCommand Object
The ExecuteScalar method of the SqlCommand object in ADO.NET Core is used to execute SQL commands that return a single value. This method is useful when you need 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. The result returned by ExecuteScalar is an object, so we need to convert the result to the expected type.
For a better understanding, please modify the Program class as follows. Here, we are fetching the number of employees from the Employee database table. Here, it is executing the command SELECT COUNT(*) FROM Employee.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Query to Get count of Employees using ExecuteScalar
string countQuery = "SELECT COUNT(*) FROM Employee";
//Create an Instance of SqlConnection
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
Console.WriteLine("ExecuteScalar Example");
// Create a SqlCommand object.
using (SqlCommand command = new SqlCommand(countQuery, connection))
{
// Execute the command and convert the result to the expected type.
int count = (int)command.ExecuteScalar();
// Output the result.
Console.WriteLine($"Total Employees: {count}");
} //Command Object will be disposed automatically
} //Connection Object will be disposed automatically
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
ExecuteNonQuery Method of ADO.NET Core SqlCommand Object
The ExecuteNonQuery method of the SqlCommand object in ADO.NET Core is used to execute SQL commands that do not return any data. These include operations such as INSERT, UPDATE, DELETE, and DDL (Data Definition Language) commands like CREATE TABLE and ALTER TABLE. This method is used when you want to change the database but you don’t need to retrieve any information from the database. The ExecuteNonQuery method returns an int indicating the number of rows affected by the command. This can be used to verify that the operation was successful.
For a better understanding, please modify the Program class as follows. Here, we are inserting a new employee into the Employee database table. It inserts the new employee record by executing the command INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) VALUES (@FirstName, @LastName, @Email, @Position, @Salary).
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Query to Insert a New Employee using ExecuteNonQuery
string sqlQuery = @"INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) VALUES (@FirstName, @LastName, @Email, @Position, @Salary)";
//Create an Instance of SqlConnection
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// ExecuteNonQuery Example
Console.WriteLine("ExecuteNonQuery Example");
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
// Assuming these variables are set from your application's input
string firstName = "Priyanka";
string lastName = "Dewangan";
string email = "Priyanka@Example.com";
string position = "Software Developer";
decimal salary = 75000m;
command.Parameters.AddWithValue("@FirstName", firstName);
command.Parameters.AddWithValue("@LastName", lastName);
command.Parameters.AddWithValue("@Email", email);
command.Parameters.AddWithValue("@Position", position);
command.Parameters.AddWithValue("@Salary", salary);
int result = command.ExecuteNonQuery();
if (result > 0)
{
Console.WriteLine("Employee Added Successfully.");
}
else
{
Console.WriteLine("No Employee Was Added.");
}
} //Command Object will be disposed automatically
} //Connection Object will be disposed automatically
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
ExecuteReader vs ExecuteScalar vs ExecuteNonQuery of ADO.NET Core SqlCommand Object
The SqlCommand object in ADO.NET Core provides different methods for executing commands against a database connection. The following are the differences between ExecuteReader, ExecuteScalar, and ExecuteNonQuery methods:
ExecuteReader
- Usage: The ExecuteReader method is used when the command is expected to return a result set, such as a SELECT statement. This method is suitable for queries that return multiple rows and columns of data.
- Return Type: It returns an instance of SqlDataReader. This reader object provides a forward-only, read-only cursor through the result set returned by the query.
- Example Use Case: Retrieving a list of products from a database where the result includes multiple rows of product data.
ExecuteScalar
- Usage: The ExecuteScalar method is used for commands that return a single value, such as the result of an aggregation function (COUNT, MAX, MIN, etc.) or any query that returns exactly one value (even if it’s from one row and one column).
- Return Type: It returns an object that holds the value of the first column of the first row in the result set. Any other returned columns or rows are ignored.
- Example Use Case: Counting the number of rows in a table or retrieving the maximum value from a table’s column.
ExecuteNonQuery
- Usage: The ExecuteNonQuery method executes commands that do not return any data but affect the state of the database. This includes data definition language (DDL) statements like CREATE, ALTER, and DROP and data manipulation language (DML) statements like INSERT, UPDATE, and DELETE.
- Return Type: It returns an integer representing the number of rows the command affects. For DDL statements that do not affect rows directly, the return value is -1.
- Example Use Case: Inserting a new record into a table, updating records, or deleting records.
Different Ways to Create an Instance of SqlCommand object in ADO.NET Core:
Many overloaded Constructors versions are available in the SqlCommand Class in ADO.NET Core. That means we can create an instance of SqlCommand Class in ADO.NET Core in many ways. They are as follows:
Default Constructor: SqlCommand()
This constructor initializes a new instance of the SqlCommand class with no properties initialized. This means that before executing the command, we need to assign the CommandText, Connection, and optionally Transaction and other properties. It is used when we need to dynamically assign the SQL statement, connection, or other properties at runtime. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "SELECT COUNT(*) FROM Employee";
command.Connection = new SqlConnection(connectionString);
command.Connection.Open();
int count = (int)command.ExecuteScalar();
Console.WriteLine($"Total Employees: {count}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Constructor with Command Text: SqlCommand(string cmdText)
This constructor initializes a new instance of the SqlCommand class with the command text initialized to the provided SQL query or stored procedure name. It is useful when we have the SQL statement ready, but the connection to the database is to be set later. This allows for setting up commands to be executed against a database once a connection is established. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
string sqlQuery = "SELECT COUNT(*) FROM Employee";
using (SqlCommand command = new SqlCommand(sqlQuery))
{
command.Connection = new SqlConnection(connectionString);
command.Connection.Open();
int count = (int)command.ExecuteScalar();
Console.WriteLine($"Total Employees: {count}");
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Constructor with Command Text and Connection: SqlCommand(string cmdText, Microsoft.Data.SqlClient.SqlConnection connection)
It initializes a new instance of the SqlCommand class with the command text and a database connection. It is ideal for scenarios where the SQL statement and the connection to the database are known upfront. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
string sqlQuery = "SELECT COUNT(*) FROM Employee";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
int count = (int)command.ExecuteScalar();
Console.WriteLine($"Total Employees: {count}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
SqlCommand(string cmdText, Microsoft.Data.SqlClient.SqlConnection connection, Microsoft.Data.SqlClient.SqlTransaction transaction)
This constructor initializes a new instance of the SqlCommand class with the command text, connection, and transaction. It is used when the command is part of a transaction. It is used 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. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
try
{
// Step 1: Start a SQL transaction.
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
// Step 2: Create and Execute a SqlCommand
string cmdText = @"INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) VALUES (@FirstName, @LastName, @Email, @Position, @Salary)";
using (SqlCommand command = new SqlCommand(cmdText, connection, transaction))
{
// Add parameters to prevent SQL injection
command.Parameters.AddWithValue("@FirstName", "Rakesh");
command.Parameters.AddWithValue("@LastName", "Sharma");
command.Parameters.AddWithValue("@Email", "Rakesh@Example.com");
command.Parameters.AddWithValue("@Position", "DBA");
command.Parameters.AddWithValue("@Salary", 10000);
// Execute the command
int result = command.ExecuteNonQuery();
Console.WriteLine("Rows affected: " + result);
// Step 3: Commit the Transaction
transaction.Commit();
}
}
catch (Exception ex)
{
Console.WriteLine("An exception occurred. Transaction rolled back.");
Console.WriteLine(ex.Message);
// Rollback the transaction in case of an error
transaction.Rollback();
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
}
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 asynchronous methods provided by the SqlCommand class:
- ExecuteReaderAsync(): Executes commands that return rows asynchronously.
- ExecuteNonQueryAsync(): Executes commands such as SQL INSERT, DELETE, UPDATE, and SET statements asynchronously.
- ExecuteScalarAsync(): Executes commands that return a single value asynchronously.
Please look at the following example for a better understanding. The following example code demonstrates how to use SqlCommand asynchronous methods (ExecuteReaderAsync, ExecuteNonQueryAsync, and ExecuteScalarAsync) to execute a query against the SQL Server database.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static async Task Main(string[] args)
{
try
{
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Query to Read All Employees Using ExecuteReader
string readQuery = "SELECT * FROM Employee";
// Query to Insert a New Employee using ExecuteNonQuery
string insertQuery = "INSERT INTO Employee (FirstName, LastName, Email, Position, Salary) VALUES ('Ramesh', 'Sahoo', 'Ramesh@Example.com', 'HR Manager', 70000)";
// Query to Get count of Employees using ExecuteScalar
string countQuery = "SELECT COUNT(*) FROM Employee";
//Create an Instance of SqlConnection
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
// ExecuteReaderAsync
Console.WriteLine("ExecuteReaderAsync Example");
using (SqlCommand command = new SqlCommand(readQuery, connection))
{
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Email: {reader["Email"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
}
}
}
// ExecuteNonQueryAsync
Console.WriteLine("\nExecuteNonQueryAsync Example");
using (SqlCommand command = new SqlCommand(insertQuery, connection))
{
int result = await command.ExecuteNonQueryAsync();
Console.WriteLine($"{result} row(s) Inserted");
}
// ExecuteScalarAsync
Console.WriteLine("\nExecuteScalarAsync Example");
using (SqlCommand command = new SqlCommand(countQuery, connection))
{
int count = (int) await command.ExecuteScalarAsync();
Console.WriteLine($"Total Employees: {count}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
When you run the above code, you will get the following output:
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 would like your feedback. Please post feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
Nice information.