Using output parameters with the Enterprise Library

It's fairly common to return a IDataReader from a stored procedure call to iterate through the results set. It gives the underlying data source the opportunity to return results lazily, and can potentially avoid your application having a whole data set in memory at once. This is clearly useful if you're dealing with very large data sets.

Typically, code to get one of these data readers looks something like this:

using (DbCommand cmd = db.GetStoredProcCommand("s_sProcName"))  
{
  db.AddInParameter(cmd, "@p_sFoo", DbType.String, "Bar");
}
return db.ExecuteReader(cmd);

This works as you'd expect - the stored procedure s_sProcName executes, and the method returns an IDataReader from which you can fetch results.

However, adding an out parameter doesn't work as you'd expect:

using (DbCommand cmd = db.GetStoredProcCommand("s_sProcName"))  
{
  db.AddInParameter(cmd, "@p_sFoo", DbType.String, "Bar");
  db.AddOutParameter(cmd, "@po_iOut", DbType.Int32, 4)
}

IDataReader reader = db.ExecuteReader(cmd);
int x = db.GetParameterValue("@po_iOut");
return reader;

If you try this, you'll find that x is zero — and in fact, the call to GetParameterValue returned null.

It seems that all the row data has to already have been returned by the DbCommand object before out parameters from the stored procedure actually become available. To maintain your interface, you therefore have to do something like this to get at your data:

DataTable table = db.ExecuteDataSet(cmd).Tables[0];
IDataReader reader = table.CreateDataReader();
int x = db.GetParameterValue("@po_iOut");
return reader;

ExecuteDataSet() fetches all available data from the DbCommand object. Once this has been done, output parameters become available for use. Of course, this approach negates one of the key advantages to using IDataReader at all - that you don't pull all the data into memory at once.

I'd love to hear from anyone with a better way of doing this. It's occurred to be that multiple result sets might be a way forward, though I don't know if multiple IDataReaders are supported in that scenario.

I guess I'll just have to try it to find out.