Oliver Ogg wrote I believe this is because I could explicitly declare that the the CommandType was StoredProcedure.  This meant output parameters behaved correctly.     SqlParameter[] searchParams = new SqlParameter[4];     // add some parameters here  List events = DotNetNuke.Common.Utilities.CBO.FillCollection((IDataReader)(     SqlHelper.ExecuteReader("", CommandType.StoredProcedure, "Search_Event_Upcoming", searchParams))     ); eventCount = (int)searchParams[3].Value;

 
This is a bit old thread, but as I fought a lot with the same problem, here are my two cents.

The problem with output parameters is caused by the IDataReader. As you know, it is a one-way reader to data. Stored procedure output parameter values are appended into the end of data stream. This means the values are readable with real values only after IDataReader is closed. CBO.FillCollection() acutally does this, so after filling a collection the output param values do contain appropriate values.

Should you use code like:

// set searchParams parameters here

IDataReader rdr = SqlHelper.ExecuteReader("", CommandType.StoredProcedure, "Search_Event_Upcoming", searchParams);

eventCount = (int)searchParams[3].Value; // this is the output parameter

List events = DotNetNuke.Common.Utilities.CBO.FillCollection<List>(rdr);

... would just give you a null for output parameter value. That is because parameter values are available only after closing the IDataReader - which happens within FillCollection() method.

This behaviour is a bit problematic if you use the DAL model. In order to work with output parameters, you would need to send DB specific method parameter to the class providing the actual implementation of abstract DataProvider. Until I really am missing something, this breaks horribly the whole nice idea of having abstract factory. Consider a control using, say, GridView myGridView:

// Some ascx control file:
protected void Page_Load(){
    // stuff here
    myGridView.DataSource = Controller.SomeMethod(foo, bar);
    myGridView.Bind();
}

// Controller class:
public List<SomeObjectType> SomeMethod(type foo, type bar){
    return CBO.FillCollection<SomeObjectType>(DataProvider.Instance.SomeMethod(foo, bar));
}

 // Abstract dataprovider:
public abstract IDataReader SomeMethod(type foo, type bar);

// Actual implementation:
public override IDataReader SomeMethod(foo, bar){
    return (IDataReader)SqlHelper.ExecuteReader(
    // All the parameters here
    );
}

How would you get access to SqlParameter set as output one? I see no obvious way for such an operation. We could alter the DB interface like this:

 // Abstract dataprovider:
public abstract IDataReader SomeMethod(type foo, type bar, out SqlParameter zof);

// Actual implementation:
public override IDataReader SomeMethod(foo, bar, out SqlParameter zof){
    // Set up zof as SqlParameter that is output value.
    return (IDataReader)SqlHelper.ExecuteReader(
    // All the parameters here
    );
}

Now the controller would get access to output param:

// Controller class:
public List<SomeObjectType> SomeMethod(type foo, type bar){
    SqlParameter zof;
    List<SomeObjectType> myList = CBO.FillCollection<SomeObjectType>(DataProvider.Instance.SomeMethod(foo, bar, out zof));
    // zof.Value can be used here
   
    return myList;
}

... But this approach forces controller class to use DB specific method call as the last parmeter to the DataProvider call is SqlParameter - which is SQL Server specific class, not a generic parameter for any SQL database. Am I missing something here?

-P