Enterprise lib 5.o connection pooling issue

Topics: Data Access Application Block, Enterprise Library Core
Jan 12, 2011 at 6:21 AM
Edited Jan 12, 2011 at 6:25 AM

I am using microsoft enterprise libarary to connect to sql server 2008

But i am facing a problem of too many connections and connection pool reached maximum limit issue.

As connection is disposed by enterprise library iteself so i dont close the connection anywhere.

So i want to ask is there any issue with my code or is there any issue with  Entelib 5.0 because i was using entelib 4 earlier had no issues .

please suggest me a solution so that i can rectify this issue as it is causing a lot of trouble to me.

 

 

Regards

Sankardeep V

Jan 12, 2011 at 6:47 AM

Do you have the specific lines of codes?  EntLib doesn't actually do the connection pooling, the underlying ADO.NET still does that.  Are you using data readers or transactions?  These are the 2 instances where you might be having trouble.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jan 12, 2011 at 6:54 AM

Following is the one of the area were i had the connection pool issue . Yes we are using data readers in the code .

 

        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim command As DbCommand = db.GetStoredProcCommand(sp)
        db.AddInParameter(command, "@actorID", DbType.Int32, actorID)
        Dim r As IDataReader = Nothing
        Dim ai As actorItems = New actorItems
        Dim reader As RefCountingDataReader = DirectCast(db.ExecuteReader(command), RefCountingDataReader)
        r = DirectCast(reader.InnerReader, IDataReader)
        Do While r.Read
            Dim a As actorItem = New actorItem
            a.itemId = CInt(r(0))
            a.itemName = r(1).ToString
            ai.items.Add(a)
        Loop
        Return ai

 

Regards

Sankardeep V

Jan 12, 2011 at 7:06 AM

You need to manually close your datareader.  And in your sample code, why are you casting it to a RefCountingDataReader when afterwards, you convert it back to an IDataReader?  You should just use it as it is:

Dim reader = db.ExecuteReader(command)

'perform read operation

reader.Close()

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jan 12, 2011 at 7:29 AM
Edited Jan 12, 2011 at 7:34 AM

Hey Sarah,

           I will give the reader.close() in the all areas were its not given .

I have used the type casting  because in some areas i had an error cannot type cast from RefCountingDataReader to sqldatareader while using

the executedatareader  function . I am actually new to entelib 5.0 so i actually  didn't understand the behavior of  executereader() which seems different from entelib 4.0 .l

 

 

thanks,

Sankardeep . V


Jan 12, 2011 at 7:41 AM

In cases where you need to convert to a SqlDataReader, then yes, convert it first to a RefCountingDataReader.  And in those cases, make sure to close the RefCountingDataReader (outer reader) instead of the SqlDataReader (inner reader).  In places where you don't need to convert to a SqlDataReader, then just make use of an IDataReader.  This was a change in EntLib 5 as a fix for a bug in a previous version.

You'll find the same discussion in this thread.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jan 12, 2011 at 8:36 AM

ok thanks.

Regards,

Sankardeep V

Associate Software Engineer

Digital Mesh Tech

Jan 12, 2011 at 9:07 AM

Hi ,

One more help please. How can we close the connection below code ,

 

 Public Function actorLimitsSelect(ByVal actorID As Int32) As SqlDataReader
        'Create a database object
        Dim db As Database = DatabaseFactory.CreateDatabase()
        'Get a GetSqlStringCommandWrapper to specify the query and parameters
        Dim command As DbCommand = db.GetStoredProcCommand("c_actorWillYou_select")
        'add parameters.
        db.AddInParameter(command, "@actorID", DbType.Int32, actorID)
        'Return db.ExecuteReader(command)                                                                   'Enteprise library 4.0 return function     
        Dim reader As RefCountingDataReader = DirectCast(db.ExecuteReader(command), RefCountingDataReader)  'Enteprise library 5.0 Modification 
        Return DirectCast(reader.InnerReader, SqlDataReader)                                                'Enterprise Library 5, Database.ExecuteReader IReader cannot cast to SQLDataReader
    End Function

 

 

 

will reader.close() will close the the RefCountingDataReader ?

 

 

Jan 12, 2011 at 10:00 AM

Hi,

From my understanding on your code, if you close the RefCountingDataReader (reader.close()) before Return DirectCast(reader.InnerReader, SqlDataReader), you won't be able to use the returned SqlDataReader because it is already closed. What I suggest is to revise your function by reading the values from SqlDataReader, put it in a collection, close the SqlDataReader then return the collection. Hope it helps.

Jan 12, 2011 at 10:03 AM

So i will change the return type of function to IdataReader and will return db.ExecuteReader(command) , so that these issues wont occur !!!!!

Regards

Sankar

Jan 12, 2011 at 10:27 AM

Your function may look like this:


Public Function getActors(ByVal actorID As Int32) As actorItems
        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim command As DbCommand = db.GetStoredProcCommand("c_actorWillYou_select")
        db.AddInParameter(command, "@actorID", DbType.Int32, actorID)
        Dim reader As RefCountingDataReader = DirectCast(db.ExecuteReader(command), RefCountingDataReader) 
        Dim ai As actorItems = New actorItems
        while (reader.InnerReader.Read())
            Dim a As actorItem = New actorItem
            a.itemId = CInt(r(0))
            a.itemName = r(1).ToString
            ai.items.Add(a)
        end while
        reader.Close()
        return ai
End Function

 I followed some of your code from your previous posts. I haven't tested this for compilation errors but I hope you do get my idea. Of course you can still improve this code. :)

Jan 12, 2011 at 10:32 AM

Hi  daaberkads,

Actually the above case is ok because there  the return type is an entity class(actorItems) .

The issue occurs when some times i have returned sqldatareader type like the following 

Public Function actorLimitsSelect(ByVal actorID As Int32) As SqlDataReader
        'Create a database object
        Dim db As Database = DatabaseFactory.CreateDatabase()
        'Get a GetSqlStringCommandWrapper to specify the query and parameters
        Dim command As DbCommand = db.GetStoredProcCommand("c_actorWillYou_select")
        'add parameters.
        db.AddInParameter(command, "@actorID", DbType.Int32, actorID)
        'Return db.ExecuteReader(command)                                                                   'Enteprise library 4.0 return function     
        Dim reader As RefCountingDataReader = DirectCast(db.ExecuteReader(command), RefCountingDataReader)  'Enteprise library 5.0 Modification 
        Return DirectCast(reader.InnerReader, SqlDataReader)                                                'Enterprise Library 5, Database.ExecuteReader IReader cannot cast to SQLDataReader
    End Function

In this case i am planning to change the return type to IDataReader so that there is no need of type casting . 

Your thoughts ?

 

Regards

Sankardeep V

 

Jan 13, 2011 at 12:39 AM
Edited Jan 13, 2011 at 12:41 AM

The important thing is, do not return the inner reader because if you do that, you'll lose the reference to the outer reader which is what you should close to avoid leaking connections.  So in this case, yes, return an IDataReader obtained from the call to db.ExecuteReader(command).

If you need to convert to a SqlDataReader, a cleaner implementation would be to create an extension method that does that:

<System.Runtime.CompilerServices.Extension()> _
 Public Function AsSqlReader(ByVal reader As IDataReader) As SqlDataReader
        Return DirectCast(DirectCast(reader, RefCountingDataReader).InnerReader, SqlDataReader)
End Function

so after calling your actorLimitsSelect method. you can call AsSqlReader to the instance of IDataReader returned by it.

Dim dataReader = actorLimitsSelect(actorId)
Using (dataReader)
    Dim sqlDataReader = dataReader.AsSqlReader()
    'Perform processing here
End Using

Hope this helps.

 

Sarah Urmeneta
Global Technologies & Solutions
Avanade, Inc.
entlib.support@Avanade.com

Jan 13, 2011 at 4:43 AM

Hey,

Thanks.