ADO - SqlCommand Class

The SqlCommand class is part of the ADO.NET library in C# and is used to execute SQL commands against a database. It represents a parameterized SQL statement or stored procedure to be executed on a database.

What is the SqlCommand Class and its need in C#?

The SqlCommand class is used to execute SQL commands against a database in ADO.NET. It provides methods and properties to set the command text, parameters, and execute the command. It is essential when you need to interact with a database by executing SQL queries, updates, inserts, or deletes.

How to create an instance of the SqlCommand class?

To create an instance of the SqlCommand class, you need to provide the command text and a SqlConnection object representing the database connection as parameters to the constructor.

string commandText = "SELECT * FROM TableName";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(commandText, connection);

Understanding the constructors and methods of SqlCommand Class:

The SqlCommand class has several constructors to initialize the SqlCommand object with different parameters. The most commonly used constructor takes the command text and a SqlConnection object. Additional constructors allow you to specify the command text, connection, and transaction.

Some of the important methods of the SqlCommand class include:

• ExecuteReader(): Executes the command and returns a SqlDataReader object for retrieving the query results.
• ExecuteScalar(): Executes the command and returns the first column of the first row as a single value.
• ExecuteNonQuery(): Executes the command and returns the number of rows affected.

ExecuteReader(): Use this method when you want to retrieve a result set from the database. It is typically used with SELECT queries and returns a SqlDataReader object that allows you to read and process the returned data.

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    // Process the data
}
reader.Close();

ExecuteScalar(): Use this method when you expect a single value to be returned, such as aggregate functions like COUNT, MAX, etc. It executes the command and returns the first column of the first row as an object, which you can cast to the appropriate data type.

object result = command.ExecuteScalar();
if (result != null)
{
    // Process the result
}

ExecuteNonQuery(): Use this method when you want to execute SQL statements that do not return any data, such as INSERT, UPDATE, DELETE, or stored procedures. It returns the number of rows affected by the command.

int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"Rows affected: {rowsAffected}");

These methods provide different ways to interact with the database based on the type of operation you need to perform. Choose the appropriate method based on the specific requirements of your application.

Remember to handle any exceptions that may occur when executing the command by using try-catch blocks.

try
{
    // Execute the command
}
catch (Exception ex)
{
    // Handle any errors
    Console.WriteLine("Error: " + ex.Message);
}

By utilizing the SqlCommand class and its methods, you can execute SQL commands and retrieve data from a database, making it an essential component for database interaction in C#.