DAAB Output Parameter Returns Nothing

Topics: Building and extending application blocks, Data Access Application Block, General discussion
Feb 16, 2007 at 4:29 PM
Edited Feb 16, 2007 at 4:30 PM
I'm using SQL Server 2005. I have Simple Stored Procedure that returns Table Records and RowCount as output parameter:

@RecordCount bigint OUTPUT
-- get record count
SELECT @RecordCount = (SELECT COUNT(ProductId) FROM StoreProducts)
-- get table
SELECT * FROM StoreProducts

I'm using DAAB to get data from the procedure, but Output parameter returns nothing.
The only time it returns a result if I don't use second Sql Statement in procedure that actually returns table. Am I doing something wrong? Any ideas? Thanks !

Code :
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim proc As String = "GetListByPage"
Dim cmd As DbCommand = db.GetStoredProcCommand(proc)
db.AddOutParameter(cmd, "@RecordCount", DbType.Int64, 0)

Dim dataReader As IDataReader = db.ExecuteReader(cmd)

Dim o As Object = db.GetParameterValue(cmd, "@RecordCount")

Feb 16, 2007 at 6:37 PM
Edited Feb 16, 2007 at 6:38 PM
It has to do with the fact that output parameters are not available until after the DataReader has been closed in ADO.NET.

Hence if you change your code to something like this it will work:

using (IDataReader dataReader = db.ExecuteReader(cmd))
   while (dataReader.Read())
      // Do Something...
Int64 recordCount = (Int64)db.GetParameterValue(cmd, "@RecordCount");




David Hayden
Microsoft MVP C#