Where should I declare & instansiate Connection Obj?

  • Thread starter Thread starter al
  • Start date Start date
A

al

Greetings All,

Where should I declare & instansiate Connection object? That is,
should I do it in class or main module.
I'm really confused, if connction obj. is decl. and insta. before
connection string gets servername and database from registry this will
causes an error? Any doc or codes is greatly appcreciated!!!

MTIA,
Grawsha
 
Grawsha,

You can change the connection string after the InitializeComponent();
without a problem. The Connection object will not give you an error until
it is used. This way you can go ahead and decl. and insta. it in the
InitializeComponent(); routine.

Here is some sample code that works for me.

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

String connectStr = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry
Path=;Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Jet
OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=False;persist security info=False;Extended
Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy
Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk
Transactions=1;Data Source=";

// String X = @"....""....";

if (File.Exists(mySettings.DataBaseName))

{

connectStr += "\"" + mySettings.DataBaseName + "\"";

}

else

{

openFileDialog1.Title = "Find the Pacer97.mdb file!";

openFileDialog1.InitialDirectory = "c:\\";

openFileDialog1.Filter = "Access Files (*.mdb)|*.mdb";

openFileDialog1.FilterIndex = 2;

if (openFileDialog1.ShowDialog() == DialogResult.OK)

{

connectStr += "\"" + openFileDialog1.FileName + "\"";

mySettings.DataBaseName = openFileDialog1.FileName;

}

}

cn.ConnectionString = connectStr;
 
Grawsha,

I'd suggest a class and instantiating it the first time it is used with code
like the following (as you said "module" I assumed VB .NET) (aircode):

Imports System

Private Shared mConn As Data.SQLClient.SQLConnection

Public Shared Readonly Property Connection As Data.SQLClient.SQLConnection
Get
If mConn Is Nothing Then
mConn = New Data.SQLClient.SQLConnection
' Set connection string here and possibly test it
End If
Return mConn
End Get
End Property

If you are managing multiple connections, you may not want to use the
Shared approach.

Kathleen
 
Kathleen Dollard said:
Grawsha,

I'd suggest a class and instantiating it the first time it is used with code
like the following (as you said "module" I assumed VB .NET) (aircode):

Imports System

Private Shared mConn As Data.SQLClient.SQLConnection

Public Shared Readonly Property Connection As Data.SQLClient.SQLConnection
Get
If mConn Is Nothing Then
mConn = New Data.SQLClient.SQLConnection
' Set connection string here and possibly test it
End If
Return mConn
End Get
End Property

If you are managing multiple connections, you may not want to use the
Shared approach.

Kathleen
Click to expand...

Thank you all for yo kind attension and help. The problem i'm facing
is that I have a Sub Main (in class module) as the startup object for
my app. This sub checks (at the startup of the app) for connection
properties (server name and database) in the registry and then creates
a sub key if there is no properties there (in fact, it prompts the
user to enter such settings) and if there is such things, it loads the
main form. Now, having connection propertis at the startup forces me
to have the connection propertis to be placed in the sub main (because
app checks everytime for those properties in the sub main and have to
have them before loading of main form. I prompts the user if there is
nothing). This will give no chance to have the connection properties
in a class(unless I want to create another connection which i don't
want to have, OR may be have the sub main in the classe)
 
Thank you all for yo kind attension and help. The problem i'm facing
is that I have a Sub Main (in class module) as the startup object for
my app. This sub checks (at the startup of the app) for connection
properties (server name and database) in the registry and then creates
a sub key if there is no properties there (in fact, it prompts the
user to enter such settings) and if there is such things, it loads the
main form. Now, having connection propertis at the startup forces me
to have the connection propertis to be placed in the sub main (because
app checks everytime for those properties in the sub main and have to
have them before loading of main form. I prompts the user if there is
nothing). This will give no chance to have the connection properties
in a class(unless I want to create another connection which i don't
want to have, OR may be have the sub main in the classe)
Click to expand...

IMHO, user settings are the perfect place for a Singleton pattern
(google on "Singleton Pattern" to find a ton of examples). Initiate the
settings class in your Sub Main, then you can access those settings from
anywhere else in the program. Essentially...

private shared _instance as UserSettings

Private Sub new() ' Note the private access modifier
' Load the settings from the registry
End Sub

Public Shared Instance() as UserSettings
If _instance is nothing then
_instance = new UserSettings
End If
return _instance
End Sub

Public Property ConnectionString as String
Get
return SomeConnectionStringLoadedPreviously
End Get
....

In Sub Main, call the UserSettings.Instance() function to get the user
settings, and if there are no settings in the registry you can set them
there. After that, anywhere in the program, you can call
UserSettings.Instance().ConnectionString
to get your connection string.
 
Al,

In addition to what David said, you can store the connection string in the
class at startup, or you can call a method of the class at startup.
Something like

Private Shared mConn As Data.SQLClient.SQLConnection
Private Shared mConnectionString as string

Public Shared Sub SetConnectionString(s as string)
mConnectionString = s
End

Public Shared Readonly Property Connection As Data.SQLClient.SQLConnection
Get
If mConn Is Nothing Then
mConn = New Data.SQLClient.SQLConnection(mConnectionString)
End If
Return mConn
End Get
End Property

You can call what you need to from your startup code.
 
Back
Top