"Kerry Moorman" wrote:

> Jeff,
>
> Connection pooling.
>
> Kerry Moorman
>
>
> "JeffDotNet" wrote:
>
> > 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
> >
> >