blog.sparetomato.com

What is wrong with the following statement?

private SQLCommand _cmd = new SQLCommand();

public void ExecuteStoredProcedure(string procedureName SQLParameter[] parms)
{
  _cmd.Connection = _dbConn;
  _cmd.CommandType = CommandType.StoredProcedure;
  _cmd.CommandText = procedureName;
  _cmd.Parameters.AddRange(parms);
  _cmd.ExecuteScalar();
}

Looks OK on the surface - we have our command object, give it a connection to play with, set it as a stored procedure and then supply the stored procedure name.

However, when this code is run, a “Syntax error near [Stored Procedure Name]” is thrown.

You double check your procedure name is correct, that your parameter array is OK, but still you get the error.

Why?

The problem is that I am setting the .CommandText property after setting the .CommandType property. Setting the CommandText property overwrites the value of .CommandType to ‘text’.

This is a demonstration that even the professionals can get it wrong. The inside of the SQLCommand object probably looks something like:

public string CommandText
{
  set
    {
     this.commandText = value;
     this.commandType = text;
    }
   …
}

or something similar - the point is that the one property is resetting another. This is bad practice - the person using your code does not have any indication of what they are doing. At worst, a compilation warning should be shown or a note in the intellisense summary of the property.

In the MSDN reference for the CommandText property, there are no notes to suggest that the CommandType is overwritten. Although in fairness, the examples do show the properties being set in a particular order.

The correct order to set up a Stored Procedure is:

public void ExecuteStoredProcedure(string procedureName SQLParameter[] parms)
{
  _cmd.Connection = _dbConn;
  _cmd.CommandText = procedureName;
  _cmd.CommandType = CommandType.StoredProcedure;
  _cmd.Parameters.AddRange(parms);
  _cmd.ExecuteScalar();
}

It could quite easily be argued that my approach to creating the object was wrong, in that I should be setting the text of my command before setting the type, but my mind does not work in that order - I think “I want to create a Stored Procedure Command and this is the Procedure I want to call”. The fluidity of Object Oriented programming should allow the coder to code in a logical way to them.