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
'in the form declaration
Private myLC As LoginCredientials
Public Sub New(ByRef lc As LoginCredientials)
InitializeComponent()
myLC = lc
End Sub
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.