Sql connection is closed but still exists after “Using Blockâ€

  • Thread starter Thread starter JeffDotNet
  • Start date Start date
J

JeffDotNet

I was testing a method that updates several stronglytyped tables with a
sqltransaction and noticed that the number of connections never returned to
zero until I exited my application. (It ended with 1 connection)

My code had made use of the "using" statement so that the connection would
be closed and disposed automatically I was done using it.

I created a greatly simplified example to isolate the problem. I noticed
that even if I just open and close a sqlConnection inside a using block the
connection is automatically close but hangs around until I shut my
application (or nunit).

I aslo notice that if I remove the asserts and run this repeatedly from my
sample application the connection count never exceeds 1. As soon as I quit
my application the connection count for this user returns to zero.

I thought that when my method was done with the database that the connection
would disappear from the sysprocesses table in master.

Shouldn't the connection disappear from sysprocesses without having to close
the application?

What am I missing?

Thanks,

Jeff




Please note that I am verifying my connection count from a seperate
sqlaccount.


I'm using:
sql2000
vs2005
Strongly typed dataset

-----# Code #---------

<Test()> Public Sub ConnectionCountTests()
Dim conStr As String = "Data Source=localHost;Initial
Catalog=myDataBase;Persist Security Info=True;User
ID=TheSqlUserName;Password=SomePassword"

Dim sqlCon As New SqlConnection(conStr)
Console.WriteLine("Before entering using Connection block --->
ConnectionCount: {0}",GetConnectionCount())
Console.WriteLine()

Using (sqlCon)
Console.WriteLine(String.Format("connection state: {0}",
sqlCon.State))
Console.WriteLine("inside using Connection block but before open
---> ConnectionCount: {0}", GetConnectionCount())
sqlCon.Open()
Console.WriteLine()
Console.WriteLine(String.Format("connection state: {0}",
sqlCon.State))
Console.WriteLine("after Open connections ---> ConnectionCount:
{0}", GetConnectionCount())
Assert.AreEqual(1, GetConnectionCount(), "1 ShimServer
Connections")
Console.WriteLine()

End Using
Console.WriteLine(String.Format("Outside using block ---->
Connection State: {0}", sqlCon.State))
Console.WriteLine("Outside using block ---> ConnectionCount: {0}",
GetConnectionCount())



'note: I first tried the using statement as below with the same results

'Using sqlCon As New SqlConnection(conStr)
' 'do stuff here
'end using

---# sql code to check connection count #-------

use master select * from sysprocesses(nolock) 'note this is run from a
seperate sql account
where hostname = 'MyHostName'
and loginame = 'TheSqlUserName'





-----# output from test #---------
Using Con startTest Connecection: 0
Before entering using Connection block ---> ConnectionCount: 0

connection state: Closed
inside using Connection block but before open ---> ConnectionCount: 0

connection state: Open
after Open connections ---> ConnectionCount: 1

Outside using block ----> Connection State: Closed
Outside using block ---> ConnectionCount: 1
 
Jeff,

Connection pooling.

Kerry Moorman


JeffDotNet said:
I was testing a method that updates several stronglytyped tables with a
sqltransaction and noticed that the number of connections never returned to
zero until I exited my application. (It ended with 1 connection)

My code had made use of the "using" statement so that the connection would
be closed and disposed automatically I was done using it.

I created a greatly simplified example to isolate the problem. I noticed
that even if I just open and close a sqlConnection inside a using block the
connection is automatically close but hangs around until I shut my
application (or nunit).

I aslo notice that if I remove the asserts and run this repeatedly from my
sample application the connection count never exceeds 1. As soon as I quit
my application the connection count for this user returns to zero.

I thought that when my method was done with the database that the connection
would disappear from the sysprocesses table in master.

Shouldn't the connection disappear from sysprocesses without having to close
the application?

What am I missing?

Thanks,

Jeff




Please note that I am verifying my connection count from a seperate
sqlaccount.


I'm using:
sql2000
vs2005
Strongly typed dataset

-----# Code #---------

<Test()> Public Sub ConnectionCountTests()
Dim conStr As String = "Data Source=localHost;Initial
Catalog=myDataBase;Persist Security Info=True;User
ID=TheSqlUserName;Password=SomePassword"

Dim sqlCon As New SqlConnection(conStr)
Console.WriteLine("Before entering using Connection block --->
ConnectionCount: {0}",GetConnectionCount())
Console.WriteLine()

Using (sqlCon)
Console.WriteLine(String.Format("connection state: {0}",
sqlCon.State))
Console.WriteLine("inside using Connection block but before open
---> ConnectionCount: {0}", GetConnectionCount())
sqlCon.Open()
Console.WriteLine()
Console.WriteLine(String.Format("connection state: {0}",
sqlCon.State))
Console.WriteLine("after Open connections ---> ConnectionCount:
{0}", GetConnectionCount())
Assert.AreEqual(1, GetConnectionCount(), "1 ShimServer
Connections")
Console.WriteLine()

End Using
Console.WriteLine(String.Format("Outside using block ---->
Connection State: {0}", sqlCon.State))
Console.WriteLine("Outside using block ---> ConnectionCount: {0}",
GetConnectionCount())



'note: I first tried the using statement as below with the same results

'Using sqlCon As New SqlConnection(conStr)
' 'do stuff here
'end using

---# sql code to check connection count #-------

use master select * from sysprocesses(nolock) 'note this is run from a
seperate sql account
where hostname = 'MyHostName'
and loginame = 'TheSqlUserName'





-----# output from test #---------
Using Con startTest Connecection: 0
Before entering using Connection block ---> ConnectionCount: 0

connection state: Closed
inside using Connection block but before open ---> ConnectionCount: 0

connection state: Open
after Open connections ---> ConnectionCount: 1

Outside using block ----> Connection State: Closed
Outside using block ---> ConnectionCount: 1
Click to expand...
 
Thanks for Kerry's prompt reply.

Hello Jeff,

Connection pooling is used for reducing the number of times that new
connection must be opened. The pooler maintains the ownership of physical
connection. By default, connection pooling is enabled in ADO.net. Unless
you disable it explicitly in connection string ("Pooling=false"). I believe
this is reason why you noticed that, only when you shutdown application
would connection be released.

For more detailed information about connection pool and connection string,
you may refer to the following articles.

http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx
[Using Connection Pooling with SQL Server]

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
..connectionstring.aspx
[SqlConnection..::.ConnectionString Property]

Hope this helps. Please feel free to let us know if there is anything
unclear. We are glad to assist you.
Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Wen,

Thanks so much for your quick follow up and detail.

I was able to update the connection string and pass all of my assertions.

Jeff
 
You are welcome, Jeff.

Have a great day,
Best regards,
Wen Yuan

Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top