Connection Pooling

Discussion in 'ASP .Net' started by ugurceng, Sep 27, 2005.

  1. ugurceng

    ugurceng Guest

    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
     
    ugurceng, Sep 27, 2005
    #1
    1. Advertising

  2. ugurceng

    Patrice Guest

    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.

    --
    Patrice

    "ugurceng" <> a écrit dans le message de
    news:%...
    > 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, Sep 27, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Trevor Hartman

    connection pooling

    Trevor Hartman, Jul 28, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    563
    Mark Heimonen
    Jul 28, 2003
  2. Chris Szabo

    connection pooling error

    Chris Szabo, Aug 19, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    1,884
    Chris Szabo
    Aug 19, 2003
  3. William \(Bill\) Vaughn

    Re: SqlConnection and connection pooling

    William \(Bill\) Vaughn, Nov 14, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    493
    William \(Bill\) Vaughn
    Nov 14, 2003
  4. =?Utf-8?B?UGllcnNvbiBD?=

    Connection Pooling, Dispose/Close/Using

    =?Utf-8?B?UGllcnNvbiBD?=, Oct 14, 2004, in forum: ASP .Net
    Replies:
    9
    Views:
    4,622
    speedy
    Nov 26, 2008
  5. =?Utf-8?B?VmFtJHk=?=

    Connection pooling

    =?Utf-8?B?VmFtJHk=?=, Nov 24, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    492
    Patrice
    Nov 24, 2004
Loading...

Share This Page