Solved

DBConnection in VB.net for www users

Posted on 2004-10-28
485 Views
Last Modified: 2010-08-05
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
0
Question by:mSchmidt
    9 Comments
     
    LVL 5

    Expert Comment

    by:Hammadian2
    make a global instance of DBConnection only once and use it wherever u want
    no need to re-create the same object
    0
     
    LVL 48

    Accepted Solution

    by:
    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
     
    LVL 11

    Expert Comment

    by:pratap_r
    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
     
    LVL 48

    Expert Comment

    by:Julian Hansen
    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
     
    LVL 11

    Expert Comment

    by:pratap_r
    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
     
    LVL 48

    Expert Comment

    by:Julian Hansen
    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
     
    LVL 11

    Expert Comment

    by:pratap_r
    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
     
    LVL 1

    Expert Comment

    by:Amrik27
    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
     
    LVL 11

    Expert Comment

    by:pratap_r
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    Problem to refer to cell 8 58
    How to copy an image file into clipboard C/C++? 1 95
    changePi Challenge 15 37
    nestparen challenge 4 13
    Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now