Vb.net Question
Best way to pass a connection object among forms?
Background: I'm rewriting a VB6 app that used MS Access for data storage to one that uses VB.NET and MS SQL Server.
I'm curious as to the best way to pass a connection among the different forms in my application that need a connection to the database. Right now I've built a class to manage the connection string to pass that between forms in a secure manner:
Public Class LoginCredientials
Private uname As String
Private password_hash() As Byte = {0}
Private server_name As String 'not used in access style databases
Private dbname As String
Private st As ServerType 'enum that would allow for different connections
Private tdes As TripleDES 'encryption class to encrypt password in memory
Public Sub New()
uname = ""
server_name = ""
dbname = ""
st = ServerType.stNotDefined
End Sub
Public Sub New(ByVal Username As String, _
ByVal Password As String, _
ByVal ServerName As String, _
ByVal DatabaseName As String, _
ByVal ServType As ServerType)
tdes = New TripleDES
uname = Username
password_hash = tdes.Encrypt(Password)
server_name = ServerName
dbname = DatabaseName
st = ServType
tdes = Nothing
End Sub
Public ReadOnly Property Server_Type() As ServerType
Get
Return st
End Get
End Property
Public ReadOnly Property CompanyName() As String
Get
Return dbname.Remove(0, 4)
End Get
End Property
Public Property UserName() As String
Get
Return uname
End Get
Set(ByVal value As String)
uname = value
End Set
End Property
Public Property Password() As String
Get
tdes = New TripleDES
Return tdes.Decrypt(password_hash)
tdes = Nothing
End Get
Set(ByVal value As String)
tdes = New TripleDES
password_hash = tdes.Encrypt(value)
tdes = Nothing
End Set
End Property
Public Property ServerName() As String
Get
Return server_name
End Get
Set(ByVal value As String)
server_name = value
End Set
End Property
Public Property DatabaseName() As String
Get
Return dbname
End Get
Set(ByVal value As String)
dbname = value
End Set
End Property
Public Function GetConnectionString() As String
Dim cstring As String = ""
tdes = New TripleDES
Select Case st
Case ServerType.stSQLServer
cstring = "User ID=" & uname & ";" & _
"Password=" & tdes.Decrypt(password_hash) & ";" & _
"Initial Catalog=" & dbname & ";" & _
"Data Source=" & server_name
End Select
tdes = Nothing
Return cstring
End Function
End Class
I had been passing a reference to my object to any of my forms that needed a connection to the database like so:
'in the form declaration
Private myLC As LoginCredientials
Public Sub New(ByRef lc As LoginCredientials)
InitializeComponent()
myLC = lc
End Sub
And then I would create a new connection object, did what I needed to do, and then closed the connection and destroyed the connection object. When I've done this before long ago in ADO with VB6, the process created by the connection was killed when the connection object was destroyed, but that doesn't appear to be the case anymore. Now every time I create a new connection object and connect to my server, a new process is created and then put to sleep when I close my connection. After a while the server will start refusing connections until I log in and kill all the processes my app created. Obviously this isn't being done right, and I would like to learn the right way.
Would it be better to simply pass the same connection object by reference (or inside a wrapper class) among my forms, leaving the connection object open?
What is the correct way to close my connection so that I don't eventually get a bunch of sleeping processes on my SQL server? Is there a setting in SQL server I can adjust to automatically kill processes after a certain period of inactivity?
Would you consider encrypting the password in runtime memory overkill?
Thank you for any help. :)
Answer Source
You should NOT pass the connection object among forms. Basically, the pattern when using a connection to SQL Server is to create the connection, open it, perform your operation, then close the connection.
To that end, you should have a public static method somewhere which will generate your SqlConnection which you would use in a Using statement, like so:
Using connection As SqlConnection = GetConnection
/* Use connection here.*/
End Using
That should prevent the processes from stacking up on the server.