DBConnection in VB.net for www users

Hi i have currently the following connection setup and use it this way

Imports System.Web
Public Class DBConnection
    Private Shared con As DBConnection
    Private Shared DBCon As ADODB.Connection
    Private Sub New()
        DBCon = New ADODB.Connection
        DBCon.Open("driver={SQL Server};server=agportal;database=agportal;")
    End Sub
    Public Shared Function getConnection() As ADODB.Connection
        Return DBCon
    End Function
    Public Shared Function getInstance() As ADODB.Connection
        If con Is Nothing Then con = New DBConnection
        Return DBCon
    End Function
    Public Shared Function closeInstance()
        con = Nothing
        DBCon.Close()
    End Function
End Class

    Public Function getHelpDeskList() As Collection
        Dim tCol As New Collection
        With rs
            .Open("select helpdesk_name from helpdesk", DBConnection.getInstance)
            While Not .EOF
                tCol.Add(.Fields("helpdesk_name").Value)
                .MoveNext()
            End While
            .Close()
        End With
        Return tCol
    End Function

This however.. causes major slowdowns when the getInstance method is called multiple times... can someone tell me a better way of preserving the connection ???
Iam programming VB.net and it is being compiled to a DLL for use with WebParts for Sharepoint Portal Server
mSchmidtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hammadian2Commented:
make a global instance of DBConnection only once and use it wherever u want
no need to re-create the same object
0
Julian HansenCommented:
Hammadian2 is right

Try this

Imports System.Web
Public Class DBConnection
    Private Shared con As DBConnection
'    Private Shared DBCon As ADODB.Connection  <--- Delete this
    Private Sub New()
        DBCon = New ADODB.Connection
        DBCon.Open("driver={SQL Server};server=agportal;database=agportal;")
    End Sub
    Public Shared Function getConnection() As ADODB.Connection
        Return DBCon
    End Function
    Public Shared Function getInstance() As ADODB.Connection
        If DBCon Is Nothing Then DBCon = New DBConnection  <--- Change This
        Return DBCon
    End Function
    Public Shared Function closeInstance()
'        con = Nothing        <---- Delete This
        DBCon.Close()
    End Function
End Class

    Private DBConnection myConnection <-- declare this global within your class.

    Public Function getHelpDeskList() As Collection
        Dim tCol As New Collection
        With rs
            .Open("select helpdesk_name from helpdesk", myConnection.getInstance)   <--- Change this to use your global instance of DBConnection
            While Not .EOF
                tCol.Add(.Fields("helpdesk_name").Value)
                .MoveNext()
            End While
            .Close()
        End With
        Return tCol
    End Function

Haven't tested it but it should be close to what you want.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pratap_rCommented:
sharing a single connection object will lead to problems when the hit count increases and further i dont see anything wrong with the implementation of the connection code. the connection object should persist as long as closeInstance is not called and i dont see it being called anywhere..

you might want to consider connection pooling rather than just using one connection. ADO.Net has built in support for this

and try using the application cache for storing the connection objects rather than the session object.

have fun,
Pratap
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Julian HansenCommented:
pratap_r,

Could you elaborate a bit on why you say that a single connection object will lead to problems. Surely this will only be the case if you have multiple threads trying to access the database through the same connection. If you have only one thread where you would be executing only 1 query at a time why would having additional connections make any difference. Connection pooling is good but I always thought it was to provide a number of pooled connections could be shared amongst many processes - saving time on setting up the connection each time. However, muliple processes is the same scenario as multiple threads and if you only have one process you only need one connection. I am just trying to fill out my knowlege here.

thanks
0
pratap_rCommented:
hmm.. you have a valid point julianH.. but what you say is applicable for a standalone application probably a windows app.. but i was explaining for a web scenario... there will be multithreading.. :-) and the question is also for a web scenario!

Have fun,
Pratap
0
Julian HansenCommented:
Ok - accepted. Next question - given it is a web application then connection pooling would still be your only option right - I mean there is still no argument for creating multiple connections within the app because each instance of the app is single threaded. It only becomes "multi-threaded" when considered from the perspective of the Web Server - the parent process that creates the instances of the app - which leaves only connection pooling as an option rather than multiple connections within the app - am I correct?

mSchmidt - appologies for hijacking your thread somewhat. pratap_r raised some interesting points on some stuff I am a bit hazy on so I thought I would try and find out a bit more.
0
pratap_rCommented:
right.. but connection pooling deals with multiple connections.. except that there is an upper limit.. the connections are served on a FCFS basis.. if there is no connection object available then the application thread is made to wait till one is available. the number of connections will impact the speed of the response.. too low and you have too many threads locked up.. too high and it is equivalent to having a single connection for every thread.. yes connection pooling would be the way to do it.

Have fun,
Pratap
0
Amrik27Commented:
U cannot use connection pooling bcos this is not a web application.
You must use a global  connection variable.
Open this connection once and then use is as and when needed.
Dont open it again and again as it creates overhead.
There is no point in using connection pooling if u are not creating a web application.

Amrik
0
pratap_rCommented:
Amrik, the title of this question says Question Title: DBConnection in VB.net for www users and the code has a Imports System.Web, so we assumed this is a web application.... please do correct us if we missed something..

anyways.. connection pooling can be used in a non web application also, especially in case of a multithreaded application

mSchmidt, please close this question if you are satisfied with the answers

Have fun,
Pratap
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.