Connection

[Joe via DotNetMonster.com]

Hi,

I'm getting a connection timeout. I have a class function that uses a
dataset. I'm calling this function on all the pages. Do you see a problem
with the code. Since it's a dataset, I don't see how I can close any
connections when the function is called:


Imports System

Public Class dataClass


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

Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(strConnection)

Dim queryString As String = "SELECT [tblPage].*, [tblLesson].
[LessonNumber], [tblCourse].[CourseNumber] 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 System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

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

dbConnection.Open
Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function
End Class

Thanks

 

[Kevin Spencer]

You mean since it's a DataReader? No, you can't close the Connection until
you are through with the DataReader. So, what you need to do is to close it
as soon as you are through with the DataReader. One way to do this is to
create a static method that opens a Connection, passing a Connection
variable by ref to it. Then you create a CloseConnection method that also
takes a Connection and variable by ref, and closes it. That way, your data
client classes can open and close their own Connections when necessary.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

Hi,

I'm getting a connection timeout. I have a class function that uses a
dataset. I'm calling this function on all the pages. Do you see a problem
with the code. Since it's a dataset, I don't see how I can close any
connections when the function is called:


Imports System

Public Class dataClass


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

Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(strConnection)

Dim queryString As String = "SELECT [tblPage].*, [tblLesson].
[LessonNumber], [tblCourse].[CourseNumber] 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 System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

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

dbConnection.Open
Dim dataReader As System.Data.IDataReader =
dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function
End Class

Thanks

 

[Joe via DotNetMonster.com]

I'm sorry, I posted the wrong code. I changed it to a dataset since I was
accessing the resultset multiple times. I'm getting a connection leak and
I'm not if there's anything I need to do with the dataset.

This is the dataset code:

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 DataSet

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)

Dim dataAdapter As New SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As DataSet = New DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

End Class
End Namespace

Thanks

 

[Marina]

Connection leaks usually result in an error saying the connection pool is
out of available connections.

If your program is having trouble opening the connection to the database
server, perhaps there is an issue with your network.

 

[Raterus]

I don't see anything wrong with that code that would cause a leaked connection, are you sure it's not some other code causing it?

 

[Kevin Spencer]

A DataAdapter automatically closes its Connection. Perhaps your network is
just overloaded with traffic to and from the database.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Matt Berther]

Hello Kevin,

Another, potentially easier, way to accomplish this is to pass CommandBehaviour.CloseConnection
into the ExecuteReader method. This will close the connection as soon as
you call the datareader's Close method.

[C#]
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
// do whatever you need
} // the connection is closed here

 

[Joe via DotNetMonster.com]

Hi,

I had thought my datareader code might have a problem and it's good to know
it looks okay. There is one other area on the page where I am using a user
control that is used for navigation that has 3 functions in it. So now I'm
thinking that this might be a reason for the connection leak. This is 2 of
the functions. Each function returns a different variable. I'm trying now
to use a dbconnection.dispose() at the end of each functions to see if that
helps:

Function GetLessonID(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].[LessonID]
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_courseNumber As IDataParameter = New
SqlParameter
dbParam_courseNumber.ParameterName = "@CourseNumber"
dbParam_courseNumber.Value = courseNumber
dbParam_courseNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_courseNumber)
Dim dbParam_lessonNumber As IDataParameter = New
SqlParameter
dbParam_lessonNumber.ParameterName = "@LessonNumber"
dbParam_lessonNumber.Value = lessonNumber
dbParam_lessonNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_lessonNumber)
Dim dbParam_pageNumber As IDataParameter = New SqlParameter
dbParam_pageNumber.ParameterName = "@PageNumber"
dbParam_pageNumber.Value = pageNumber
dbParam_pageNumber.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_pageNumber)

dbConnection.Open

Dim dataReader As IDataReader = dbCommand.ExecuteReader
(System.Data.CommandBehavior.CloseConnection)

If (dataReader.Read = True) Then
LessonID=dataReader("LessonID")
End If

Return dataReader
End Function


Function GetMaxPage(ByVal lessonID As Integer) As IDataReader

Dim strConnection As String
strConnection = ConfigurationSettings.AppSettings
("ConnectionString")

Dim dbConnection As New SqlConnection(strConnection)


Dim queryString As String = "SELECT TOP 1 MAX([tblPage].
[PageNumber]) FROM [tblPage] WHERE ([tblPage].[LessonID] = @Lesso"& _
"nID)"
Dim dbCommand As New SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_lessonID As IDataParameter = New SqlParameter
dbParam_lessonID.ParameterName = "@LessonID"
dbParam_lessonID.Value = lessonID
dbParam_lessonID.DbType = DbType.Int32
dbCommand.Parameters.Add(dbParam_lessonID)

dbConnection.Open
Dim dataReader As IDataReader = dbCommand.ExecuteReader
(System.Data.CommandBehavior.CloseConnection)

If (dataReader.Read = True) Then
MaxNumber=dataReader(0)
End If
Return dataReader
End Function



Thanks

 

[Kevin Spencer]

Good point, Matt!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

Hello Kevin,

Another, potentially easier, way to accomplish this is to pass
CommandBehaviour.CloseConnection into the ExecuteReader method. This will
close the connection as soon as you call the datareader's Close method.

[C#]
using (SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
// do whatever you need
} // the connection is closed here

--
Matt Berther
http://www.mattberther.com

You mean since it's a DataReader? No, you can't close the Connection
until you are through with the DataReader. So, what you need to do is
to close it as soon as you are through with the DataReader. One way to
do this is to create a static method that opens a Connection, passing
a Connection variable by ref to it. Then you create a CloseConnection
method that also takes a Connection and variable by ref, and closes
it. That way, your data client classes can open and close their own
Connections when necessary.

Kevin Spencer
Microsoft MVP
.Net Developer
What You Seek Is What You Get.