Cast error on SQLDataReader

Question

My site is using enterprise library v 5.0. Mainly the DAAB. Some functions such as executescalar, executedataset are working as expected. The problems appear when I start to use Readers

I have this function in my includes class:

Public Function AssignedDepartmentDetail(ByVal Did As Integer) As SqlDataReader
    Dim reader As SqlDataReader
    Dim Command As SqlCommand = db.GetSqlStringCommand("select seomthing from somewhere where something = @did")
    db.AddInParameter(Command, "@did", Data.DbType.Int32, Did)
    reader = db.ExecuteReader(Command)
    reader.Read()
    Return reader
End Function

This is called from my aspx.vb like so:

reader = includes.AssignedDepartmentDetail(Did)
If reader.HasRows Then
    TheModule = reader("templatefilename")
    PageID = reader("id")
Else
    TheModule = "#"
End If

This gives the following error on db.ExecuteReader line:

Unable to cast object of type 'Microsoft.Practices.EnterpriseLibrary.Data.RefCountingDataReader' to type 'System.Data.SqlClient.SqlDataReader'.

Can anyone shed any light on how I go about getting this working. Will I always run into problems when dealing with readers via entlib?

Answer 1

ExecuteReader in Enterprise Library wraps IDataReader into RefCountingDataReader that as SqlDataReader implements IDataReader interface.

RefCountingDataReader has InnerReader property that you can cast to SqlDataReader. The sample below is in C# but you can easily convert it to VB.NET.

SqlDataReader reader;
reader = ((RefCountingDataReader)db.ExecuteReader(command)).InnerReader as SqlDataReader;
if (reader != null)
    reader.Read();
return reader;

Hope it helps

Answer 2

I would be careful with this implementation. There is a thread on the Enterprise Library Codeplex site that explains the backgound for this: http://entlib.codeplex.com/Thread/View.aspx?ThreadId=212973

Chris Tavares explains that it's not good to just return the .InnerReader, because then the connection tracking by Enterprise Library is thrown off (his response from May 20, 5:39PM): "That approach will completely screw up your connection management. The whole reason for the wrapper is so that we could execute extra code to clean stuff up at dispose time. Grabbing the inner reader and throwing out the outer will leak connections! "

So yes, this is a bit of a pain to deal with, we're in the same situation.

Regards, Mike

Answer 3

I agree with Mike. This sucks. I am having leaking connections because all my DA methods require a SqlDataReader. Now I have to return the inner RefCountingDataReader and can never close the outer reader. The old Enterprise Library was working fine with returning a SqlDataReader.

Answer 4

I think I have a working solution.

enter code here

    ' Create the Database object, using the default database service. The
    ' default database service is determined through configuration.
    Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = EnterpriseLibraryContainer.Current.GetInstance(Of Microsoft.Practices.EnterpriseLibrary.Data.Database)(DatabaseName)

    Dim dbCommand As DbCommand
    dbCommand = db.GetStoredProcCommand(StoredProcedureName)

    'create a new database connection based on the enterprise library database connection
    Dim dbConnection As System.Data.Common.DbConnection
    dbConnection = db.CreateConnection
    dbConnection.Open()

    'set the dbCommand equal to the open dbConnection
    dbCommand.Connection = dbConnection

    'return a ADO sqlDatareader but still managed by the EnterpriseLibrary
    Return dbCommand.ExecuteReader(CommandBehavior.CloseConnection)