Database connection 1

[brd24gor]

I am relatively new to C# and OOP, and I have a question about good programming practice regarding database connections. I am writing a program in which multiple forms will be connecting to a singular Access database. I have the OleDbConnection setup and working.

My question is: What is the best way to handle the database connection between forms?

1. Should I pass the connection as a parameter between the forms? If so, is it OK to leave it open?

-or-

2. Should I create a new connection each time a form is created that uses the database?

Thanks!

--Brad

 

[obislavu]

One ideea is to create a connection object and pass it to each form. Each form will check the connection state. If the state is not open, then will open the connection.
If there is no need to be kept open then the connection could be closed only but not disposed.
The connection object will be in a separate .dll as well the authentication and other database features specific to your application,table data gateways e.g. all SQL code for accessing a single table or query for MSAccess.

public class DBLibrary
{
   private System.Data.OleDb.OleDbConnection.OleDbConnection m_SQLConnection;
   private string m_ConnectionString="";
   
	public DBLibrary()
	{
	}
	public bool Disconnect()
	{
		try
		{
		       //...
			m_SQLConnection.Close(); 
			m_SQLConnection=null;
			return(true);
		}
		catch(Exception vException)
		{
			MessageBox.Show("Error: Disconnect() - Unable to disconnect from database. Please verify ...","Unable to disconnect from Database",MessageBoxButtons.OK,MessageBoxIcon.Stop  ); 
			return(false);
		}
	}
	public bool Connect()
	{
		try
		{
			m_SQLConnection= new SqlConnection(m_ConnectionString); 
			//..
			return(true);
		}
		catch(Exception vException)
		{
			MessageBox.Show("Error: Connect() - Unable to connect to database. Please verify server, database, user and password information and try again.","Unable to connect to Database",MessageBoxButtons.OK,MessageBoxIcon.Stop  ); 
			return(false);
		}
	}
	public System.Data.SqlClient.SqlConnection SQLConnection
	{
		get{return m_SQLConnection;}
		set{m_SQLConnection=value;}
	}
	//...
}

Next, you should have a main form with two menu items, Connect and Disconnect.
When Connect, the LogOnForm will get the db , user, password to connect, create the Connection object and pass it to other forms.
On other forms check the State of the connection object which is part of the m_DBLibrary object:

public void SetDateClosed(DateTime vDateTime)
{
	
 if (m_DBLibrary.SQLConnection.State==System.Data.ConnectionState.Closed) 
     m_DBLibrary.SQLConnection.Open();
     //..
}

obislavu

 

[brd24gor]

Thanks, this is exactly the type of logic I was working for.