正确使用C#和MySQL连接 [英] Correct use of connections with C# and MySQL

问题描述

C#/MySQL交互的一个典型示例涉及这样的代码(为简单起见，我跳过了try/catches和错误检查):

A typical example for C#/MySQL interaction involves code like this (I'm skipping try/catches and error checking for simplicity's sake):

conn = new MySqlConnection(cs);
conn.Open();
string stm = "SELECT * FROM Authors";
MySqlCommand cmd = new MySqlCommand(stm, conn);
rdr = cmd.ExecuteReader();

该方案是用于独立应用程序和WCF Web服务的类库.因此，每次查询时都应该打开连接吗?还是应该在打开程序时将其打开一次?

The scenario is a class library for both a standalone application and a WCF web-service. So, should I open a connection whenever I make a query? Or should I open it once when I open the program?

推荐答案

您通常希望每个打开一个连接工作单元. MySQL ADO.NET驱动程序可以为您缓冲连接，但是，我建议不要让每个查询都打开连接.可能发生的事情是，您开始使用几种方法来服务一项业务事务，并且由于每种方法都在打开连接，因此您最终可能会完成一项业务事务而使可用的连接陷入饥饿.这当然会导致超时，性能下降等.

You typically want to open one connection per unit of work. The MySQL ADO.NET driver can pool connections for you, however, I would recommend against having each query open a connection. What can happen is you start using several methods in order to service one business transaction, and since each is opening a connection, you can end up that one business transaction starving your available connections. This of course can lead to timeouts, poor performance, etc.

相反，请考虑定义一个IUnitOfWork/UnitOfWork API，该API创建并打开一个连接和事务，并让您的数据访问层请求当前的IUnitOfWork，它将提供当前的连接和事务.

Instead, consider defining an IUnitOfWork/UnitOfWork API that creates and opens a connection and transaction, and having your data access layer request the current IUnitOfWork, which will provide the current connection and transaction.

当然，诀窍是知道工作单元的开始时间和结束时间.它们与完整，有意义的操作(业务交易")相关联.例如，当您为其中一个WCF服务上的方法请求提供服务时.服务实例启动时，应响应请求，应创建一个工作单元.然后，您的DAL组件可以要求使用当前的工作单元.然后，当请求完成时，应该提交工作单元(应该提交事务并关闭连接).

The trick, of course, is knowing when a unit of work begins and when it ends. They are associated with a complete, meaningful action (a "business transaction"). For example, when you are servicing a request to a method on one of your WCF services. When the service instance starts, in response to a request, a unit of work should be created. Then you DAL components can ask for the current unit of work and use it. Then when the request is completed, the unit of work should be committed (which should commit the transaction and close the connection).

这篇关于正确使用C#和MySQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！