SQL Server pooling Issue

Question

Hi Friends

I have written a web application in which I have not allowed connection pooling for this application. I have written sample code as shown below which gets record from my table 20 times and fill in Data set I have close connection at every time.

But if I look in SQL Server Activity monitor it shows me one connection open in sleeping mode.

1. anyone tell me why this happens?
2. does this sleeping connection increase if users increase?
3. If SQL Server pools my connection then why its pooling if I have not allowed pooling for this application? How can I avoid this?

Code to fetch data

Try
  Dim i As Integer
  For i = 0 To 20
    Dim _db As New commonlib.Common.DBManager(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString.ToString())
    GridView1.DataSource = _db.ExecuteDataSet(CommandType.Text, "SELECT * FROM BT_AppSetting")

    GridView1.DataBind()
  Next
Catch ex As Exception
  Response.Write(ex.Message.ToString())
  ex = Nothing
End Try

DBManager constructor

'CONSTRUCTOR WHICH ACCEPTS THE CONNECTION STRING AS ARGUMENT
Public Sub New(ByVal psConnectionString As String)

   'SET NOT ERROR
   _bIsError = False
   _sErrorMessage = Nothing

   _cn = New SqlConnection
   _sConnectionString = psConnectionString
   _cn.ConnectionString = _sConnectionString

   Try
       _cn.Open()
   Catch ex As Exception
       _bIsError = True
       _sErrorMessage = ex.ToString
       ex = Nothing
   End Try
End Sub

ExecuteDataSet Function body

Public Function ExecuteDataSet(ByVal CmdType As CommandType, ByVal CmdText As String, ByVal ParamArray Params As SqlParameter()) As DataSet

Try
  Dim cmd As New SqlCommand
  Dim da As New SqlDataAdapter(cmd)
  Dim ds As New DataSet

  PrepareCommand(cmd, CmdType, CmdText, Params)

  da.Fill(ds)

  cmd.Parameters.Clear()

  If _cn.State = ConnectionState.Open Then
     _cn.Close()
  End If

  Return ds
Catch ex As Exception
   _sErrorMessage = ex.ToString
   _bIsError = True
   ex = Nothing
   Return Nothing
End Try

Please help me.... Waiting for kind reply

Answer 1

1)I THINK sql server does not close the connection right away. That why you see it.

2) Since you are closing the connection you should see only one. Unless your users are running the code at the same time. e.g if it was in a web page and there are 2 users, you will/shoudl see 2 connections.

Also if dont close your connections (just to try) your number of connection will (should :) ) go up.

It is your .net application that pools the connection and not sql server.