Connection Pooling

[ugurceng]

Hi everbody ,

I need more info about connection pooling ,
We are developing a CRM project with ASP.NET and when more users connect to
the MS SQL DB at the same time , which problems would be occured ?
Our connection structure is like below what should you offer ?



' This is connection Class

Imports System.Data.SqlClient

Imports System.Configuration

Class CrmDBConn

Public Shared sqlconnBaglanti As SqlConnection

Public Shared Function connectToDB() As Boolean

Dim sqlstr As String

'sqlstr = ConfigurationSettings.AppSettings("ConnectionString")

sqlstr = " data source=servername;uid=sa;password=12345;initial
catalog=CRM_DB"

sqlconnBaglanti = New SqlConnection(sqlstr)

Try

If sqlconnBaglanti.State <> ConnectionState.Open Then

sqlconnBaglanti.Open()

End If

Return True

Catch ex As Exception

Return False

End Try

End Function

End Class

***************************************************

' This is any class that uses Connection class

Imports DataCRM.CrmDBConn

Imports System.Data.SqlClient

Public Class BankData

Shared Function bankInsertData(ByVal tds As dsBank) As Boolean

Dim strSqlString As String

Dim sqlAdap As New SqlClient.SqlDataAdapter

Dim dset As New DataSet

If connectToDB() = False Then

'Redirect error page

Exit Function

End If

Dim thisRow As dsBanka.ARG_CRM_BANKARow =
tds.ARG_CRM_BANKA.Rows(tds.ARG_CRM_BANKA.Rows.Count - 1)

strSqlString = "INSERT INTO ................"

Dim cmdInsert As SqlCommand = New SqlCommand(strSqlString, sqlconnBaglanti)

cmdInsert.ExecuteNonQuery()

FillCombo.initBankData()

sqlconnBaglanti.Close()

Return (True)

End Function

So when I call the bankInsertData func. I request a connection from
connection pool, As I know a connection pool has 100 connectios, so at the
same time 110 users call this func. what will be? And how can I know that
how many connections are being used at any time? Using a counter ?

One more thing , when we change the connection string a new connection pool
is being created, So if we request more than 100 connection from connection
pool ,the only way is to change the connection string to crate new conn
pool?

Best regards...

UGURCENG

 

[Patrice]

Note that shared variables are shared accross the whole application. For
ASP.NET it means that all users are sharing the same connection making it a
bottleneck.
The usual scheme is rather to create/release them as needed (pooling takes
care from efficient reuse of previous connections).

You can control the size of the pool but if you have the very first thing to
check before doing so is to see if you don't have connections left open.