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?

我建立了广泛的数据录入应用程序，并通过广泛的我的意思是，我只掌握了一些基本设置，其中结合了周围15-25％的整体方案，我有大约15多种形式的有部分设置。 （他们还需要工作）我使用的是SQL精简4.0作为我的后台数据库，我真的不需要因为我不是存储一个MMO的价值数据的更广阔的数据库，在目前这只是一个本地应用程序

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.

然而，在问题的情况下，是如何连接到数据库。我使用2 SQLCE数据库，一个存储是基于服务和工作人员恒定数据，以及第二存储了不断变化的数据，或基于的第一数据库上输入新的数据。我已经看到了关于如何设置这个了许多不同的方法，目前我使用的之一，我有一个基本形式，所有其他形式的继承。内的基本形式我有共有的许多形式从而最小化的正被重复代码量的方法和变量。

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种方法打开要么它们的连接。像这样：

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

然后，每当我需要一个开放的连接，我简单地调用对应于我需要访问，然后关闭它在finally语句打开数据库连接方法。这样的连接是永远不会打开很长，需要的只是当它。当然，这意味着有很多的调用打开连接方法。因此，这是最好的方式来实现这种情景，还是有更好的办法？

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.

推荐答案

连接由.NET汇集，所以重新创建它们通常不是一个昂贵的操作。保持连接打开了很长一段时间，但是，可能会引发问题。

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.

大多数最佳实践告诉我们，并尽快（最后一点后立即关闭它们（右执行任何SQL之前）尽可能晚地打开连接。数据已被提取）

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.

这篇关于C＃数据连接的最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！