SqlCommand Class

Represents a Transact-SQL statement or stored procedure to execute against a SQL Server data source.

The SqlCommand class is used to execute commands against a SQL Server database. It supports parameterized queries, which help prevent SQL injection attacks. You can use it to perform operations such as querying data, inserting, updating, or deleting records.

Syntax

public sealed class SqlCommand : DbCommand

Remarks

To execute a T-SQL statement or call a stored procedure, you create an instance of SqlCommand and associate it with a SqlConnection. You can set the CommandText property to the SQL statement or stored procedure name, and the CommandType property to indicate whether CommandText is a text string, a stored procedure, or a table name.

Use the Parameters collection to pass parameters to a parameterized query or a stored procedure. For stored procedures that return values, use an SqlParameter with the Direction property set to ParameterDirection.ReturnValue.

The ExecuteNonQuery method is used for Transact-SQL statements that do not return any rows, such as UPDATE, INSERT, and DELETE statements.

The ExecuteReader method returns a SqlDataReader object that can be used to read the rows returned by a query.

The ExecuteScalar method returns the value of the first column of the first row in the result set returned by the query.

The ExecuteXmlReader method returns an XmlReader instance that can be used to read the result set returned by a SQL query that returns XML data.

Constructors

Name	Description
`SqlCommand()`	Initializes a new instance of the `SqlCommand` class.
`SqlCommand(string? cmdText)`	Initializes a new instance of the `SqlCommand` class with the text of the query.
`SqlCommand(string? cmdText, SqlConnection? connection)`	Initializes a new instance of the `SqlCommand` class with the text of the query and a `SqlConnection`.
`SqlCommand(string? cmdText, SqlConnection? connection, SqlTransaction? transaction)`	Initializes a new instance of the `SqlCommand` class with the text of the query, a `SqlConnection`, and a `SqlTransaction`.

Properties

Name	Description
`CommandText`	Gets or sets the Transact-SQL statement or stored procedure to execute.
`CommandTimeout`	Gets or sets the wait time in seconds before the attempt to execute the command times out.
`CommandType`	Gets or sets a value indicating how the `CommandText` property is to be interpreted.
`Connection`	Gets or sets the `SqlConnection` on which to execute the `SqlCommand`.
`Parameters`	Gets the collection of parameters associated with the `SqlCommand`.
`Transaction`	Gets or sets the `SqlTransaction` within which the `SqlCommand` executes.
`UpdatedRowSource`	Gets or sets how the results are applied to the row source when the statement executes.

Methods

Name	Description
`ExecuteNonQuery()`	Executes the command text against the connection and returns the number of rows affected.
`ExecuteReader()`	Executes the command text against the connection and returns a `SqlDataReader` object.
`ExecuteScalar()`	Executes the command text against the connection and returns the first column of the first row in the result set.
`ExecuteXmlReader()`	Executes the command text against the connection and returns an `XmlReader`.
`Prepare()`	Creates a prepared version of the command object on the data source.

Events

Name	Description
`StatementCompleted`	Occurs when the command is executed.

Examples

Executing a SELECT Query

using System;
using System.Data.SqlClient;

public class Example
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string sqlQuery = "SELECT COUNT(*) FROM Customers;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                try
                {
                    connection.Open();
                    object result = command.ExecuteScalar();
                    if (result != null)
                    {
                        Console.WriteLine($"Number of customers: {result}");
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}

Executing an INSERT Statement with Parameters

using System;
using System.Data.SqlClient;

public class Example
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string sqlInsert = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sqlInsert, connection))
            {
                command.Parameters.AddWithValue("@Name", "New Widget");
                command.Parameters.AddWithValue("@Price", 19.99);

                try
                {
                    connection.Open();
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) inserted.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}