A simple connection question

[Creative2]

Hi guys,

I am 8, 10 subs and I call them in different click events, they query a sql database, so my question is that it is better to open the connection once and use it, or open and close connection everytime a different sub is called.

Thank you guys for your help.

 

[wyrd]

If Im not mistaken opening and closing a connection often causes a bit of overhead. Id suggest working in "disconnected mode" as I think its referred to, or to simply just leave the connection open during the programs life.

When your application exits theres an event that fires (even if your app exits on an error). Its under the Application object.. Application.ApplicationExit. Id suggest putting the connection.Close() method under that event if you choose to keep the connection open during the apps lifetime (the consturctor would be the best place to open it).

 

[Robby]

If youre using Datasets then open late and close early, easy enough.

When it comes to other types of connections, it depends...
If you have 10 subs all calling each other through a single user action, in other words the user clicks one button and all 10 subs are triggered, then by all means leave the connection open until all 10 are complete.

I would have a hard time leaving a connection open during the life of an application. But then again it depends of the app.

 

[mhsueh001]

Similar question

Ok, in VB6 I left the connection object open. I declared my command and record set object locally, but had a global connection in my program.

Question, is it better to use a global connection for the entire program or better to use a "Static" connection in a class?

Thanks

 

[Robby]

Did you read what wyrd and I had to say?

 

[Heiko]

I say "static", but that may be personal bias.

 

[mhsueh001]

To robby

I read your answer, did you read my question?

I need the application to stay connected.

The question was "Is it better to stay connected by using a global connection object (ie in a module) which can be accessed throughout the entire program", or to some how set up a connection object in a particular workspace? or class? using a "Shared" connection. (I used the word static in my other message from VC# command I was reading and appologize for any confusion there)

VB.NET is new and has some different advantages that I would like to persue.

How is the best way to set up a global variable in a particular workspace so that variable can be accessed by all memebers of that workspace but not from outside that workspace?

Thanks in advance for your time.

 

[Heiko]

re: global variable ...

I recommend setting up an object that follows the "Singleton" design pattern. In this way you will have a sort of "global object" to work with.

Theres an example in the msdn.

 

[Heiko]

Cut from my Code

Public Class UserContext

#Region "Member Variables"

    Private m_FullName As String
    Private m_Identity As System.Security.Principal.WindowsIdentity

    Shared myInstance As UserContext
#End Region

    Private Sub New()

        m_Identity = System.Security.Principal.WindowsIdentity.GetCurrent()
        m_FullName = m_Identity.Name

    End Sub

    Public Shared Function GetInstance() As UserContext
        If myInstance Is Nothing Then
            myInstance = New UserContext()
        End If
        Return myInstance
    End Function

#Region "Properties"
    Public ReadOnly Property FullUsername() As String
        Get
            Return m_FullName
        End Get
    End Property

#End Region

End Class

As you might have noticed, this class has no public new() method,
so consequently it can not be instantiated freely. Instead the caller must always use the classes "getInstance" Method.

Dim aUC as UserContext

aUC = UserContext.getInstance()

 

[mhsueh001]

Thanks Heiko

But, Im having a problem following your example and setting up a class where I need to instantiate a connection object.

I need a Shared function which can pass out an SQLConnection only after I have a userid and password.

How do I set this up?

Heres what Ive tried, but I think Im way off.

I have the problem "Cannot refer to an instance member of a class from within a shared method or shared memeber initializer without an explicit instance of the class."

Thanks in advance for your help.


Option Explicit On
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class ServerConnection

#Region "Member Variables"
Private mstrUserID As String
Private mstrPassword As String

Private adoConnection As SqlConnection

Shared adoConnect As ServerConnection
#End Region

#Region "Properties"
Public WriteOnly Property UserID() As String
Set(ByVal Value As String)
If Len(Value) > 0 Then
mstrUserID = Value
Else
MessageBox.Show("Invalid User ID", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End If
End Set
End Property
Public WriteOnly Property Password() As String
Set(ByVal Value As String)
If Len(Value) > 0 Then
mstrPassword = Value
Else
MessageBox.Show("Invalid Password", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End If
End Set
End Property
#End Region

Private Sub ConnectToServer()
Dim strConnect As String
Dim strCatalog As String = "Solution"

strConnect = "Data Source = SQLSERVER; " & _
"Initial Catalog = " & strCatalog & "; " & _
"Persist Security Info = True; " & _
"User Id = " & mstrUserID & "; " & _
"Password = " & mstrPassword & "; " & _
"Packet Size = 4096"

adoConnection = New SqlConnection(strConnect)
MessageBox.Show("Connected")
End Sub

Public Shared Function Connect() As ServerConnection
If adoConnect Is Nothing Then
ERROR IS HERE!
ConnectToServer()
adoConnect = New ServerConnection()
End If
Return adoConnect
End Function
End Class

 

[mhsueh001]

Oh

And this is how I originally had it designed, but your method looked more elegant, can you help make this similar to your example.

Option Explicit On 
Imports System.Windows.Forms
Imports System.Data.SqlClient

Public Class ServerConnection

#Region "Member Variables"
    Private mstrUserID As String
    Private mstrPassword As String

    Shared adoConnection As SqlConnection
#End Region

#Region "Properties"
    Public WriteOnly Property UserID() As String
        Set(ByVal Value As String)
            If Len(Value) > 0 Then
                mstrUserID = Value
            Else
                MessageBox.Show("Invalid User ID", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
            End If
        End Set
    End Property
    Public WriteOnly Property Password() As String
        Set(ByVal Value As String)
            If Len(Value) > 0 Then
                mstrPassword = Value
            Else
                MessageBox.Show("Invalid Password", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
            End If
        End Set
    End Property
#End Region

    Public Function ConnectToServer() As SqlConnection
        If adoConnection Is Nothing Then
            Dim strConnect As String
            Dim strCatalog As String = "Solution"

            strConnect = "Data Source = SQLSERVER; " & _
                            "Initial Catalog = " & strCatalog & "; " & _
                            "Persist Security Info = True; " & _
                            "User Id = " & mstrUserID & "; " & _
                            "Password = " & mstrPassword & "; " & _
                            "Packet Size = 4096"

            adoConnection = New SqlConnection(strConnect)
            Try
                adoConnection.Open()
            Catch
                adoConnection = Nothing
                MessageBox.Show("Either a bad userid or password")
                Return adoConnection
            End Try
            MessageBox.Show("Connected")
        End If
        Return adoConnection
    End Function

    Public Sub Disconnect()
        adoConnection = Nothing
        MessageBox.Show("Disconnected")
    End Sub
End Class