C# 数据连接最佳实践？

C# Data Connections Best Practice?

提问by Josh C.

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.

我正在构建一个广泛的数据输入应用程序，广泛的意思是我只有基本设置，其中包含整个程序的 15-25% 左右，我有大约 15 个部分设置的表单。（他们仍然需要工作）我使用 SQL Compact 4.0 作为我的后端数据库，我真的不需要更广泛的数据库，因为我没有存储 MMO 的数据，目前这只是一个本地应用程序.

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.

然而，有问题的场景是如何连接到数据库。我正在使用 2 个 SQLCE 数据库，一个存储基于服务和人员的常量数据，另一个存储不断变化的数据或基于第一个数据库输入的新数据。我已经看到了许多关于如何设置它的不同方法，目前我正在使用一种方法，其中我有一个所有其他表单都继承自的 BaseForm。在 BaseForm 中，我有许多表单通用的方法和变量，从而最大限度地减少了重复的代码量。

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

这包括到两个数据库的连接字符串，以及打开到其中任何一个连接的 2 种方法。像这样：

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?

然后，每当我需要打开连接时，我只需调用与我需要访问的数据库相对应的打开连接方法，然后在 finally 语句中关闭它。通过这种方式，连接永远不会打开很长时间，只在需要时打开。当然，这意味着有很多对开放连接方法的调用。那么这是实现这种场景的最佳方法，还是有更好的方法？

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.

在表单加载后立即打开连接，然后在表单关闭时关闭它是否更好？我有一次打开多个表单的实例，每个表单都可能需要一个与数据库的打开连接，所以如果一个关闭它，那么其他表单就会被搞砸，对吗？或者我应该在应用程序启动时打开与两个数据库的连接？任何输入将不胜感激。谢谢。

采纳答案by D Stanley

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.

连接由 .NET 池化，因此重新创建它们通常不是一项昂贵的操作。但是，长时间保持连接打开可能会导致问题。

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).

大多数“最佳实践”告诉我们尽可能晚地打开连接（就在执行任何 SQL 之前）并尽快关闭它们（在提取最后一位数据之后）。

An effective way of doing this automatically is with usingstatements:

自动执行此操作的有效方法是使用using语句：

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.

简而言之，在应用程序打开或每个表单打开时打开连接可能不是最好的方法。

回答by Danny

I think it's better to just open them upon the application launching, since you need stuff from your database right?I'm not an expert in this, it's just my opinion... I programmed some similar applications and made the connection at the start of the main form. The only form where i created a separate connection was the login form.

我认为最好在应用程序启动时打开它们，因为您需要数据库中的东西，对吗？我不是这方面的专家，这只是我的意见......我编写了一些类似的应用程序并在开始时建立了连接的主要形式。我创建单独连接的唯一表单是登录表单。