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 );
}

4 thoughts on “Enterprise Library Data Access Application Block + SQL Optional Parameters”

  1. Hi!

    Sure it not causes problems, but if you add a second opcional parameter and just provide the firs one….. the same error occurs.

    Do you know how to avoid this error?

  2. Hi again 🙂

    I found how to do:

    You must obtain the DBCommand first without parameters, then add the parameters manually, and finally execute:

    DBCommand command = _db.GetStoredProcCommand(“usp_MyStoredProc”);
    _db.AddInParameter(command, “param1”, value);
    return _db.ExecuteDataSet(command);

    So, if you would have a second optional parameter, no error occurs.

    🙂

    Have a good day!

    1. Thank you Dan for the extra notes. I’ve actually stopped using the Data Access Blocks all together lately but it’s a good point to know. Sorry I didn’t respond earlier; I haven’t had a chance to review my blog lately.

Leave a Reply

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