Windows 服务中的 SQL 连接
我编写了一个 Windows 服务，它侦听来自第三方服务的数据，将其保存在内存中一小段时间，并定期将所有新数据刷新到数据库中。
最初，每次需要刷新数据时，我都会打开一个新连接，然后再次关闭它。 （每 5 秒左右）
由于服务器似乎受到重击，我对此进行了更改，因此在应用程序的生命周期中打开并重用了一个连接。
只是想知道这是否是一个坏主意？
我通常做网络工作，其中连接在单个请求的生命周期中打开和关闭。对于需要执行我所描述的操作的 Windows 服务，最佳实践是什么？
我打算建立一个像这样的容错连接：
private SqlConnection _sqlConnection;
public SqlConnection SqlConnection
{
get
{
if (_sqlConnection == null || !_sqlConnection.State.Equals(ConnectionState.Open))
{
var conn = new SqlConnection(_connectionString);
conn.Open();
return conn;
}
return _sqlConnection;
}
}
因此，如果由于某种原因现有连接被关闭或以某种方式出现故障，我们将得到一个新的打开连接，
这是出于某种原因的糟糕设计吗？
I have written a Windows Service which listens for data from a third party service, holds it in memory for a short time and periodically all the new data is flushed to the database.
I was initially opening a new connection each time I needed to flush the data and closing it again afterwards. (Every 5 seconds or so)
As the server seems to be getting hammered I have changed that so there is a single connection opened and reused for the life of the application.
Just wondering if this is a bad idea?
I usually do web stuff where the connection is open and closed over the life of a single request. What is the best practice for a windows service that needs to do the sort of operation I have described?
I was going to make a fault tolerant connection like this:
private SqlConnection _sqlConnection;
public SqlConnection SqlConnection
{
get
{
if (_sqlConnection == null || !_sqlConnection.State.Equals(ConnectionState.Open))
{
var conn = new SqlConnection(_connectionString);
conn.Open();
return conn;
}
return _sqlConnection;
}
}
so if some reason the existing connection is closed or faulted in some way we would get a new open one
is that bad design for any reason?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您是数据库的单个用户，请保持连接。如果没有，您确实可以依靠连接池来为您做到这一点。
我个人每次都会选择打开连接。在 .NET 2.0 中，实现了一项新功能，因此，如果您与 sql server 有一个开放的连接，并且 sql server 重新启动等...您的连接将变得无效，并且这不是我可以拿我的服务冒险的事情。 请参阅我几年前的帖子。
If you are the single user of the database, hold onto the connection. If not you can really rely on connection pooling to do that for you.
I personally would go for opening the connection everytime. In .NET 2.0 a new feature was implemented so that if you have an open connection to a sql server and sql server gets restarted, etc... your connection becomes invalid and that is not something I can risk my service with. See my post from some years ago.
你可以说我保守，但我仍然认为让连接池来管理数据库的物理连接是更好的选择。因此，只需正常打开和关闭连接，然后让池来决定要做什么。我已经在 Web 服务中做到了这一点，没有任何问题，并且您将有更多的连接可用于处理负载。
Call me conservative but I still think that leaving it up to the connection pool to manage the physical connections to the database is a better choice. So just open and close the connection normally, and leave to the pool to decide what to do. I've done that in web services without any problems, and you will have more connections available to handle the load.
我不会尝试保持开放的连接。在很多边缘情况下，连接将变得不可用，并且用于管理连接并确保正确处理旧的连接的代码必须绝对可靠。
我推荐更常见的连接使用模式：打开、使用、关闭/处置。代码将更容易编写和维护。绝对确保在使用完所有命令和连接对象后将其处理掉。使用分析工具监视您的应用程序，并检查服务器上打开的数据库连接的数量，以确保您的代码按您预期的方式工作。
您需要将数据转储到数据库中的频率（以及因此打开/使用/关闭数据库连接）取决于许多因素，例如转储之前内存中有多少数据、数据库服务器消耗的能力数据，以及如果您从 Web 服务接受数据但尚未将其写入数据库并且您的服务或服务器崩溃，则存在丢失数据的风险。
如果您的数据很珍贵，您可能需要考虑采用两个流程。一个进程调用 Web 服务并将接收到的数据安全地存储在消息队列中。另一个进程从队列中读取消息并将消息中的数据放入数据库中。
这种处理过程的方式意味着您可以在数据库暂时关闭时接收数据，并且所有数据最终都将存储在数据库中。
虽然这是一个可靠的解决方案，但根据您的要求，它也很容易被认为是矫枉过正。
I would not try to maintain an open connection. There will be lots of edge cases where the connection will be become unusable and your code for managing the connection and making sure the old duff connection is correctly disposed would have to be absolutely bullet-proof.
I recommend the more common connection use pattern of open, use, close/dispose. The code will be much easier to write and maintain. Be absolutely sure you are disposing of all command and connection objects once you're done with them. Monitor your app with a profiling tool, and keep a check on the number of open database connections at the server to make sure your code is working the way you intended.
How often you need to dump the data into the database (and therefore open/use/close database connections) depends on a number of factors such as how much data will be in-memory before being dumped, the capability of the database server to consume the data, and the risk of losing data if you've accepted it from the web service, but haven't written it to the database and your service or the server crashes.
If your data is precious, you might want to consider having two processes. One process calls the web service and stores the received data securely in a message queue. Another process reads the messages from the queue and puts the data in the message in the database.
This way of handling this process means you can receive data whilst the database is temporarily down, and all the data will eventually be stored in the database.
Whilst this is a solid solution, it could just as easily be considered overkill, depending on your requirements.