Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DBConnection in VB.net for www users

Posted on 2004-10-28
9
Medium Priority
?
520 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
Comment
Question by:mSchmidt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 5

Expert Comment

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

Accepted Solution

by:
Julian Hansen earned 2000 total points
ID: 12437480
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
ID: 12442744
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Julian Hansen
ID: 12442865
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
ID: 12442914
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 59

Expert Comment

by:Julian Hansen
ID: 12443100
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
ID: 12443250
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
ID: 12452119
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
ID: 12452145
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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Progress
Starting up a Project

610 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