Timing out

[Jim via DotNetMonster.com]

Hi,

I've been getting the message:

Timeout period elapsed prior to obtaining a connection from the pool.

I'm using this function on all my pages. Can you see anything incorrect
with the code?

Thanks


Imports System
Imports Microsoft.VisualBasic
Imports System.Web
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data

Namespace Data

Public Class dataClass

Public Shared Function GetPage(ByVal courseNumber As Integer,
ByVal lessonNumber As Integer, ByVal pageNumber As Integer ) As IDataReader
Dim strConnection As String
strConnection = ConfigurationSettings.AppSettings
("ConnectionString")

Dim dbConnection As New SqlConnection(strConnection)

Dim queryString As String = "SELECT [tblPage].*, [tblLesson].
[LessonNumber],[tblLesson].[LessonTitle], [tblCourse].[CourseNumber],
[tblCourse].[CourseTitle] FROM [tblPage], [tblLesson], [tblCourse] WHERE ((
[tblPage].[Pa"& _
"geNumber] = @PageNumber) AND ([tblLesson].[LessonNumber] =
@LessonNumber) AND (["& _
"tblCourse].[CourseNumber] = @CourseNumber)) AND tblPage.lessonID
= tblLesson.lessonID AND tblLesson.CourseID = tblCourse.CourseID"

Dim dbCommand As New SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_pageNumber As New SqlParameter
dbParam_pageNumber.ParameterName = "@PageNumber"
dbParam_pageNumber.Value = pageNumber
dbParam_pageNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_pageNumber)
Dim dbParam_lessonNumber As New SqlParameter
dbParam_lessonNumber.ParameterName = "@LessonNumber"
dbParam_lessonNumber.Value = lessonNumber
dbParam_lessonNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_lessonNumber)
Dim dbParam_courseNumber As New SqlParameter
dbParam_courseNumber.ParameterName = "@CourseNumber"
dbParam_courseNumber.Value = courseNumber
dbParam_courseNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_courseNumber)

dbConnection.Open
Dim dataReader As SQLDataReader = dbCommand.ExecuteReader
(CommandBehavior.CloseConnection)
Return dataReader

End Function

End Class
End Namespace

 

[Brock Allen]

This sounds like the classic problem of leaking connections to the database.
For this one snippet make sure that whoever's calling GetPage calls Dispose()
or Close() on the returned DataReader.

-Brock
DevelopMentor
http://staff.develop.com/ballen

 

[Jim via DotNetMonster.com]

Thanks. When I'm calling GetPage, I'm binding it to a dataset. So I have:

<ASP:Repeater id="RepeaterPageText" runat="server" DataSource="<%#
Data.dataClass.GetPage(IntCourseNumber,IntLessonNumber,IntPageNumber) %>">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "pageText") %>
</ItemTemplate>
</ASP:Repeater>

Where would I close the DataReader? Would it be in the Page Load:
Sub Page_Load(sender As Object, e As EventArgs)
Page.DataBind()
End Sub

 

[Brock Allen]

Call Dispose() after you call DataBind()

-Brock
DevelopMentor
http://staff.develop.com/ballen

 

[Jim via DotNetMonster.com]

Thanks so much. I'll try that.

 

[Jim via DotNetMonster.com]

Sorry to hassle you again but just 1 more question.

Would this be the correct way of doing it:

Sub Page_Load(sender As Object, e As EventArgs)
Page.DataBind()
Dispose()
End Sub

I tried to use dbConnection.Close but it doesn't recognize the connection.
Also, is it fine to close the connection in the class as well?

Thanks so much

 

[Brock Allen]

I'd change it to:

Sub Page_Load()
Dim rdr as IDataReader = Nothing
Try
rdr = GetPage(IntCourseNumber,IntLessonNumber,IntPageNumber)
RepeaterPageText.DataSource = rdr
RepeaterPageText.DataBind()
Finally
If Not rdr is Nothing then rdr.Close()
End Try
End Sub


-Brock
DevelopMentor
http://staff.develop.com/ballen

 

[Jim via DotNetMonster.com]

I have several repeaters on the page that I'm binding the data to.

Can I just bind to the page then close the reader? Sorry, I am new at this.

So something like:

Sub Page_Load()
Dim rdr as IDataReader = Nothing
Try
rdr = GetPage(IntCourseNumber,IntLessonNumber,IntPageNumber)
Page.DataBind()
Finally
If Not rdr is Nothing then rdr.Close()
End Try
End Sub

 

[Brock Allen]

The DataReader is a forward-only read-only data access mechanism. So if you
have more than one Repeater than needs to bind to the same result set and
you're using the DataReader, then you're going back to the database for each
one -- this is not good for performance. I'd suggest looking into the DataSet
as an alternate mechanism.

Now, if each of your Repeaters binds to a different set of data from the
database (so different SQL statements), then it's ok to use the DataReader
(you're still going to the database for each one), but you also still need
to call Close() on each one.

Programming's hard. What can I say

-Brock
DevelopMentor
http://staff.develop.com/ballen

 

[Jim via DotNetMonster.com]

Thanks. That makes sense I am using the same resultset and I'll look into
the dataset. If I use a dataset then I won't need to close any connections
from the page calling the function so that should resolve the connection
leak I'm having right?

Thanks again for all your help.