我应该为每个查询打开和关闭数据库吗？
我正在使用旧式 ADO.net 和 C#，因此有很多此类代码。是为每个查询创建一个函数并每次打开和关闭数据库更好，还是使用同一连接对象运行多个查询更好？以下只是一个查询，仅供示例之用。
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectMain"].ConnectionString))
{
// Add user to database, so they can't vote multiple times
string sql = " insert into PollRespondents (PollId, MemberId) values (@PollId, @MemberId)";
SqlCommand sqlCmd = new SqlCommand(sql, connection);
sqlCmd.Parameters.Add("@PollId", SqlDbType.Int);
sqlCmd.Parameters["@PollId"].Value = PollId;
sqlCmd.Parameters.Add("@MemberId", SqlDbType.Int);
sqlCmd.Parameters["@MemberId"].Value = Session["MemberId"];
try
{
connection.Open();
Int32 rowsAffected = (int)sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
}
}
I am using old school ADO.net with C# so there is a lot of this kind of code. Is it better to make one function per query and open and close db each time, or run multiple queries with the same connection obect? Below is just one query for example purpose only.
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectMain"].ConnectionString))
{
// Add user to database, so they can't vote multiple times
string sql = " insert into PollRespondents (PollId, MemberId) values (@PollId, @MemberId)";
SqlCommand sqlCmd = new SqlCommand(sql, connection);
sqlCmd.Parameters.Add("@PollId", SqlDbType.Int);
sqlCmd.Parameters["@PollId"].Value = PollId;
sqlCmd.Parameters.Add("@MemberId", SqlDbType.Int);
sqlCmd.Parameters["@MemberId"].Value = Session["MemberId"];
try
{
connection.Open();
Int32 rowsAffected = (int)sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
好吧，你可以测量；但只要您
使用连接（因此即使您遇到异常，它们也会被释放），并且启用了池化（对于 SQL Server，默认情况下启用），这并不重要；关闭（或处置）只是将底层连接返回到池中。两种方法都有效。抱歉，这没有多大帮助；p只是在执行其他冗长的非数据库工作时不要保持打开的连接。关闭并重新打开；您实际上可能会得到相同的底层连接，但其他人（另一个线程）可能已经使用了它，而您没有。
Well, you could measure; but as long as you are
usingthe connections (so they are disposed even if you get an exception), and have pooling enabled (for SQL server it is enabled by default) it won't matter hugely; closing (or disposing) just returns the underlying connection to the pool. Both approaches work. Sorry, that doesn't help much ;pJust don't keep an open connection while you do other lengthy non-db work. Close it and re-open it; you may actually get the same underlying connection back, but somebody else (another thread) might have made use of it while you weren't.
对于大多数情况，打开和关闭每个查询的连接是正确的方法（正如 Chris Lively 指出的那样）。但是，在某些情况下，您会使用此解决方案遇到性能瓶颈。
例如，当处理大量依赖于先前结果的相对快速执行的查询时，我可能建议在单个连接中执行多个查询。在对数据进行批处理或出于报告目的进行数据调整时，您可能会遇到这种情况。
不管您遵循哪种模式，请始终确保使用“using”包装器以避免内存泄漏。
For most cases, opening and closing a connection per query is the way to go (as Chris Lively pointed out). However, There are some cases where you'll run into performance bottlenecks with this solution though.
For example, when dealing with very large volumes of relatively quick to execute queries that are dependent on previous results, I might suggest executing multiple queries in a single connection. You might encounter this when doing batch processing of data, or data massaging for reporting purposes.
Always be sure to use the 'using' wrapper to avoid mem leaks though, regardless of which pattern you follow.
如果方法的结构使得单个命令在单个方法中执行，则“是”：实例化并处置每个命令的连接。
如果方法的结构使得您在同一代码块中执行多个命令，则外部块需要是连接的 using 子句。
ADO 在连接池方面非常出色，因此命令对象的实例化和处理将非常快并且不会影响性能。
例如，我们有几个页面将执行 50 个查询的更新以组成页面。因为有分支代码来确定要运行的查询，所以我们将每个查询都用自己的
using (connection...)子句包装起来。我们曾经将它们撕下来并获取一个连接对象并将其传递给各个方法。这使得性能改进为零，同时使代码变得非常复杂，每个地方都有所有异常子句以确保连接在最后得到正确处理。测试结束时，我们将代码回滚到之前的状态。更清楚地知道正在发生什么以及何时使用连接。
If the methods are structured such that a single command is executed within a single method, then Yes: instantiate and dispose of the connection for each command.
If the methods are structured such that you have multiple commands executed in the same block of code, then the outer block needs to be the using clause for the connection.
ADO is very good about connection pooling so instantiating and disposing of the command object is going to be extremely fast and really won't impact performance.
As an example, we have a few pages that will execute update to 50 queries in order to compose the page. Because there is branching code to determine the queries to run, we have each of them wrapped with their own
using (connection...)clauses.We once ripped those out and grabbed one connection object and passed it to the individual methods. This had exactly zero performance improvement while complicating the hell out of the code with all the exception clauses every where to ensure the connection was properly disposed at the end. At the end of the test, we rolled back the code to how it was before. Much cleaner to know exactly what was going on and when a connection was being used.
嗯，一如既往，这取决于。如果您在同一个方法调用中要进行 5 个数据库调用，则您可能应该使用单个连接。
然而，从可扩展性的角度来看，通常不建议在没有发生任何事情时保持连接。
Well, as always, it depends. If you have 5 database call to make within the same method call, you should probably use a single connection.
However, holding onto connection while nothing is happening isn't usually advised from a scalability standpoint.
ADO.NET 现在已经过时了吗？哇，你让我感觉自己老了。对我来说，在 Windows 3.1 上使用 Borland C++ 的 Rogue Wave ODBC 是老式的。
要回答这个问题，通常您需要了解数据驱动程序的工作原理。了解连接池等概念，并学习分析与连接/断开连接和执行查询相关的事务成本。然后将这些知识应用于您的情况。
ADO.NET is old school now? Wow, you just made me feel old. To me Rogue Wave ODBC using Borland C++ on Windows 3.1 is old school.
To answer, in general you want to understand how your data drivers work. Understand such concepts as connection pooling and learn to profile the transaction costs associate with connecting / disconnecting and executing queries. Then take that knowledge and apply it it your situation.