DbCommand under the microscope

Posted on: 2011-09-27

DbCommand uses the DbConnection to be able to send SQL query or call a stored procedure. It can also execute DDL (Data Definition Language) query to create table or to modify table structure.

Here is an example of update a row with the SqlCommand that inherits from DbCommand.

ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["ApplicationServices"]; 
using (var connection = new SqlConnection(connectionStringSettings.ConnectionString)) { 
  connection.Open(); 
  using (var command = new SqlCommand()) { 
    command.Connection = connection; 
    command.CommandType = System.Data.CommandType.Text; 
    command.CommandText = "UPDATE Region SET RegionDescription = 'Estern Yes!' WHERE RegionID=1"; 
    command.ExecuteNonQuery(); 
    connection.Close(); 
  } 
} 

So, from this example you must have guess that SqlCommand that inherit from DbCommand derives also from IDisposable. This is why the using statement is still use in this example. The command got the connection that was opened previously and than the CommandType is set to Text to execute directly SQL query.

From there the ExecuteNonQuery method is executed. This method is perfect to call stored procedure or Sql statement that does not return data. Also, this command can return the number of row affected.

 int amountOfRowAffected = command.ExecuteNonQuery(); 

To execute stored procedure, the command type need to be changed and the command text must have the stored procedure name. To make a test lets create to the Northwind database the stored procedure.

ALTER PROCEDURE [dbo].[UpdateRegion] ( @id INT ,@txt VARCHAR(50) ) AS BEGIN 
  UPDATE region SET RegionDescription = @txt WHERE RegionID = @id 
END 

To execute lets use this code:

ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["ApplicationServices"]; 
using (var connection = new SqlConnection(connectionStringSettings.ConnectionString)) { 
  connection.Open(); 
  using (var command = new SqlCommand()) { 
    command.Connection = connection; 
    command.CommandType = System.Data.CommandType.StoredProcedure; 
    command.CommandText = "UpdateRegion"; 
    command.Parameters.Add(new SqlParameter("id","1")); 
    command.Parameters.Add(new SqlParameter("txt", "Est")); 
    command.ExecuteNonQuery(); 
    connection.Close(); 
  } 
} 

Not only the CommandType and CommandText have changed, but a new addition appear with parameter. Since the stored procedure take the id of the region and the text to display as new description, the command must be aware of what to pass to the stored procedure.

The creation of the parameter can also be create with the use of the command. That's it, you can create parameter with the CreateParameter method of the DbCommand class.

ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["ApplicationServices"]; 
using (var connection = new SqlConnection(connectionStringSettings.ConnectionString)) { 
  connection.Open(); 
  using (var command = new SqlCommand()) { 
    command.Connection = connection; 
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.CommandText = "UpdateRegion"; 
    SqlParameter param1 = command.CreateParameter();
    param1.ParameterName = "id"; 
    param1.Value = "1"; 
    command.Parameters.Add(param1); 
    command.Parameters.Add(new SqlParameter("txt", "Estern")); 
    command.ExecuteNonQuery(); 
    connection.Close(); 
  } 
} 

The DbParameter, in the few example above was of type SqlParameter. The class SqlParameter derives from DbParameter and contains additional specification for Sql Server. For example, the Db Type are more specific to Sql Server. Also, you can find multiple additional methods to specify the direction of the parameter, the type, the direction, etc.

 SqlParameter param1 = command.CreateParameter(); 
 param1.ParameterName = "id"; 
 param1.Value = "1"; 
 param1.Direction = System.Data.ParameterDirection.Input; 
 param1.SqlDbType = System.Data.SqlDbType.Int;
 param1.IsNullable = false; 

To conclude, the DbCommand is the class to use when you need to execute a query that does not return any data.