Adventures in Development Babak's experiences in software development

Enterprise Library Data Access Application Block + SQL Optional Parameters

The Enterprise Library Data Access Block provides a number of ways of calling a stored procedure.

Database _db = DatabaseFactory.CreateDatabase(<Connection String Name>);

Let's now assume that we have a stored procedure usp_MyStoredProc that takes a single parameter @param1. If this is a require parameter, both of the following statement blocks will result in the same outcome:

public DataSet CallMyStoredProc( int value ){
  return _db.ExecuteDataSet( "usp_MyStoredProc", value )
}

public DataSet CallMyStoredProc( int value ){
  DbCommand command = _db.GetStoredProcCommand("usp_MyStoredProc" );
  _db.AddInParameter( command, "param1", value );
  return _db.ExecuteDataSet( command );
}

However, now let's assume that @param1 is an optional parameter that we don't want to pass in from our data access method, we are limited in our options.

public DataSet CallMyStoredProc(){
  return _db.ExecuteDataSet( "usp_MyStoredProc")
}

This method will fail with an exception because the parameters provided to the stored procedure (none) don't match the parameter count for the stored procedure (1).

Creating an actual DbCommand and executing it, however, will not cause an exception. In this scenario, the following code block will work without causing an exception:

public DataSet CallMyStoredProc( int value ){
  DbCommand command = _db.GetStoredProcCommand("usp_MyStoredProc" );
  return _db.ExecuteDataSet( command );
}