C# Executing SQL Command or Stored Procedure

The IDbCommand interface represents a database command, and each data provider includes a unique implementation. Here is the list of IDbCommand implementations for the five standard data providers.

• System.Data.Odbc.OdbcCommand

• System.Data.OleDb.OleDbCommand

• System.Data.OracleClient.OracleCommand

• System.Data.SqlServerCe.SqlCeCommand

• System.Data.SqlClient.SqlCommand

To execute a command against a database you must have an open connection and a properly configured command object appropriate to the type of database you are accessing. You can create command objects directly using a constructor, but a simpler approach is to use the CreateCommand factory method of a connection object. The CreateCommand method returns a command object of the correct type for the data provider and configures it with basic information obtained from the connection you used to create the command. Before executing the command, you must configure the properties described in the below table which are common to all command implementations.

Table 10.3: Common Command Object Properties

Property	Description
CommandText	A string containing the text of the SQL command to execute or the name of a stored procedure. The content of the CommandText property must be compatible with the value you specify in the CommandType property.
CommandTimeout	An int that specifies the number of seconds to wait for the command to return before timing out and raising an exception. Defaults to 30 seconds.
CommandType	A value of the System.Data.CommandType enumeration that specifies the type of command represented by the command object. For most data providers, valid values are StoredProcedure, when you want to execute a stored procedure, and Text, when you want to execute a SQL text command. If you are using the OLE DB Data Provider, you can specify TableDirect when you want to return the entire contents of one or more tables; refer to the .NET Framework SDK documentation for more details. Defaults to Text.
Connection	An IDbConnection instance that provides the connection to the database on which you will execute the command. If you create the command using the IDbConnection.CreateCommand method, this property will be automatically set to the IDbConnection instance from which you created the command.
Parameters	A System.Data.IDataParameterCollection instance containing the set of parameters to substitute into the command.
Transaction	A System.Data.IDbTransaction instance representing the transaction into which to enlist the command. (See the .NET Framework SDK documentation for details about transactions.)

Once you have configured your command object, there are a number of ways to execute it depending on the nature of the command, the type of data returned by the command, and the format in which you want to process the data.

To execute a command, such as INSERT, DELETE, or CREATE TABLE, that doesn't return database data, call ExecuteNonQuery. For the UPDATE, INSERT, and DELETE commands, ExecuteNonQuery method returns an int that specifies the number of rows affected by the command. For other commands, such as CREATE TABLE, ExecuteNonQuery returns the value -1. Here is an example that uses UPDATE to modify a record.

public static void ExecuteNonQueryExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.Text;
    com.CommandText = "UPDATE Employees SET Title = 'Sales Director'" +
        " WHERE EmployeeId = '5'";

    // Execute the command and process the result.
    int result = com.ExecuteNonQuery();

    if (result == 1) {
        Console.WriteLine("Employee title updated.");
    } else {
        Console.WriteLine("Employee title not updated.");
    }
}

To execute a command that returns a result set, such as a SELECT statement or stored procedure, use the ExecuteReader method. ExecuteReader returns an IDataReader instance through which you have access to the result data. Most data providers also allow you to execute multiple SQL commands in a single call to the ExecuteReader method. This code excerpt uses the ExecuteReader method to execute the Ten Most Expensive Products stored procedure from the Northwind database and display the results to the console.

public static void ExecuteReaderExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.StoredProcedure;
    com.CommandText = "Ten Most Expensive Products";

    // Execute the command and process the results
    using (IDataReader reader = com.ExecuteReader()) {

        Console.WriteLine("Price of the Ten Most Expensive Products.");

        while (reader.Read()) {

            // Display the product details.
            Console.WriteLine("  {0} = {1}", 
                reader["TenMostExpensiveProducts"],
                reader["UnitPrice"]);
        }
    }
}

If you want to execute a query but only need the value from the first column of the first row of result data, use the ExecuteScalar method. The value is returned as an object reference that you must cast to the correct type. Here is an example.

public static void ExecuteScalarExample(IDbConnection con) {

    // Create and configure a new command.
    IDbCommand com = con.CreateCommand();
    com.CommandType = CommandType.Text;
    com.CommandText = "SELECT COUNT(*) FROM Employees";

    // Execute the command and cast the result.
    int result = (int)com.ExecuteScalar();

    Console.WriteLine("Employee count = " + result);
}

Technorati : SQL in C#