Home » Ado.Net » DbCommand under the microscope

DbCommand under the microscope

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 inherit 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 derive 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.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.