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 I'm not mistaken opening and closing a connection often causes a bit of overhead. I'd suggest working in "disconnected mode" as I think it's referred to, or to simply just leave the connection open during the programs life.

 

When your application exits there's an event that fires (even if your app exits on an error). It's under the Application object.. Application.ApplicationExit. I'd 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 you're 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.

Edited February 21, 2003 by mhsueh001

[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.

 

There's 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, I'm 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?

 

Here's what I've tried, but I think I'm 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

Edited February 24, 2003 by mhsueh001