title: C# Data Connections Best Practice?
body: <p>Ok, so this is one of those kind of opinionated topics, but based on your knowledge, opinion, and current practice, what is the best way to set the following scenario up?</p>
  
  <p>I'm building an extensive data entry application, and by extensive I mean I've only got the basics setup which incorporates around 15-25% of the overall program and I have about 15 forms that are partially setup. (They still need work)  I'm using SQL Compact 4.0 as my backend database, I don't really need a more expansive database as I'm not storing an MMO's worth of data, and for the moment this is only a local application.</p>
  
  <p>I would love to be able to set it up to be displayed as a single window that just changes to various different pages based on a menu system, but I can't seem to find a good tutorial on how that would be accomplished, so if anyone knows of any, please enlighten me.</p>
  
  <p>The scenario in question however, is how to connect to the databases.  I'm using 2 SQLCE databases, one that stores constant data that is based on services and staff, and a second that stores the constantly changing data or new data that's entered based on the first database.  I have seen many different methods on how to set this up and currently I am using one in which I have a BaseForm that all other forms inherit from.  Within the BaseForm I have methods and variables that are common to many forms thus minimizing the amount of code that is being repeated.</p>
  
  <p>This includes the connection strings to both databases, and 2 methods that open a connection to either of them.  Like so:</p>
  
  <pre><code>internal SqlCeConnection dataConn = new SqlCeConnection(@"Data Source = |DataDirectory|\opi_data.sdf");
  internal SqlCeConnection logConn = new SqlCeConnection(@"Data Source = |DataDirectory|\opi_logs.sdf");
  internal SqlCeCommand command;
  
  internal void openDataConnection() // Opens a connection to the data tables 
          {
              try
              {
                  if(dataConn.State == ConnectionState.Closed)
                      dataConn.Open();
              }
              catch(SqlCeException ex)
              {
                  MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
              }
          }
  
          internal void openLogConnection() // Opens a connection to the log tables
          {
              try
              {
                  if(logConn.State == ConnectionState.Closed)
                      logConn.Open();
              }
              catch (SqlCeException ex)
              {
                  MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
              }
          }
  </code></pre>
  
  <p>Then whenever I need an open connection I simply call the open connection method that corresponds to the database I need access to and then close it in a finally statement.  In this way a connection is never open for very long, just when it's needed.  Of course this means there are a lot of calls to the open connection methods.  So is this the best way to implement this sort of scenario, or are there better ways?</p>
  
  <p>Is it better to just open a connection as soon as a form loads and then close it when the form closes?  I have instances where multiple forms are open at a time and each one would probably need an open connection to the databases so if one closes it then the others would be screwed right?  Or should I open a connection to both databases upon the application launching?  Any input would be appreciated.  Thanks.</p>