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

Adventures in WCF (Windows Communication Foundation)

My latest project at work has me working with WCF for the first time and it has been an interesting experience. Over the next few weeks, I will be discussing configuration, debugging, and deployment of the WCF service and connecting to it.

Microsoft provides a few tools that can help debug your WCF services.

Error Handling

Typically, I add Global.asax to a web application in ASP.NET and use the Application_Error event to handle any uncaught exceptions.

protected void Application_Error(object sender, EventArgs e)
{
//Insert your application’s exception logging here
}

This doesn’t work (still looking into why) for WCF services, even when hosted on IIS. A good work around is to wrap each service method with a try…catch block and calling the exception logger in the catch block.

public Object MyWcfWrapperMethod( int i )
{
try
{
//Call the method being exposed by WCF
}
catch( Exception e ){
//Log the exception
}
finally
{
//do your clean up here
}
}

Testing

Unit tests are especially helpful (aren’t they always?) in the development of your WCF services as messages from errror and exceptions can (and will) be hidden by subsequent errors. An invalid parameter to a stored procedure call can result in a high level System.ServiceModel.CommunicationObjectFaultedException.