sql - C# Data Connections Best Practice?

Question

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?

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.

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.

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.

This includes the connection strings to both databases, and 2 methods that open a connection to either of them. Like so:

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);
            }
        }

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?

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.

Answer 1

Connections are pooled by .NET, so re-creating them generally isn't an expensive operation. Keeping connections open for long periods of time, however, can cause issues.

Most "best practices" tell us to open connections as late as possible (right before executing any SQL) and closing them as soon as possible (right after the last bit of data has been extracted).

An effective way of doing this automatically is with using statements:

using (SqlConnection conn = new SqlConnection(...))
{
    using(SqlCommand cmd = new SqlCommand(..., conn))
    {
        conn.Open();
        using(DataReader dr = cmd.ExecuteReader())  // or load a DataTable, ExecuteScalar, etc.    
        {
             ...
        {
    }
}

That way, the resources are closed and disposed of even if an exception is thrown.

In short, opening a connection when the app opens or when each form opens is probably not the best approach.