How to share a database connection between different classes

[cnall5]

I would like to share the same database connection between two Forms. I would like to use the same connection to associate OleCommands that are executed in each form/class.
My main form creates my second form (a modal dialog) that I want to use to insert user input to the database.
How do I make my database connection global to be used by all classes in a given namespace? I need to associate my OleCommands with a connection. I would like this connection to be the same throughout my program.

Thank you!

 

[alexboly]

You could use a class named like ApplicationSettings that keeps static members with such variables, like the connection to the database etc.
I am not sure however that you can do database transactions with the same connection... It seemed strange to me too and I am not 100% sure, but it may be possible not to be able to use a connection for different transactions... Maybe you will clear it up and show us how you do it.

 

[cnall5]

Ok! The static member of a class works! Here is what I used. I created this class in the namespace of my main Form after all declarations etc... of the main form class. I tried to put it before the class fclForm1{ class stuff ...} but this caused some rescource errors.

public class DataConnection
{
// this class creates a static connection that can acessed by any class within
// this classes namespace. Be certain to close as needed

public const string DB_CONN_STRING = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=WS_SMS.mdb";
private static System.Data.OleDb.OleDbConnection DB_CONN = new OleDbConnection(DB_CONN_STRING);



public static System.Data.OleDb.OleDbConnection GetDBConnection()
{
if(DB_CONN.State == ConnectionState.Closed)
{
DB_CONN.Open();
}
return DB_CONN;
}

}



Then in my modal dialog:

private System.Data.OleDb.OleDbConnection m_projConn;


//Get the global data base connection for the
//PROJECT Record insert
try
{
m_projConn = DataConnection.GetDBConnection();
this.cmdInsertProject.Connection = m_projConn;
}
catch(Exception ex)...


m_projConn.Close(); when the modal dialog is closed


But getting the Global connection allowed me to associate my cmdInsertProject to this connection. I can also omit the local connection m_projConn, and directly associate my command: cmdInsertProject.Connection = DataConnection.GetDBConnection();

 

[alexboly]

Ok, I'm glad it worked and I'm glad that I found out something new.Thanks!

 

[xloop]

You could also easily create a new Form reference in your second form and associate that with you parent form. Thus giving you access to your main forms variables (as long as they're public, of course.

 

[chiph]

You could also easily create a new Form reference in your second form and associate that with you parent form. Thus giving you access to your main forms variables (as long as they're public, of course.

Yes, you could, but I would consider that to be bad design. A form *uses* a database connection, but it doesn't *own* a database connection.

Your DB connection info should be contained in a third object that just does database stuff. You can make it visible with a public static member (as above) or make it an instance member (if you need connections to two or more databases at the same time). The choice depends on your needs.

Chip H.

 

[cnall5]

Yes, The second Form only needs the connection, and does not need to know anything from form1. Therefore, the static member implementation worked well. I am connecting to Access but I hope that migrating to SQLServer in the future will not be too difficult if I have >1 connection. I don't think it should. ...But you never know....til ya get there.
Thanks for your insights.