使用事务时关闭连接 [英] Close connection when using a Transaction

问题描述

我有以下使用事务的方法.

I have the following method which uses a transaction.

private string getDocumentDetailsByNumber(string DocNumber)
    {
       SqlTransaction transaction = DALDBConnection.SqlConnection.BeginTransaction();

            try
            {
                DataSet DocNum = new DataSet();

                string sDocNumber = "";
                string[] taleNamesDoc = new string[1];
                taleNamesDoc[0] = "docnumber";
                SqlParameter[] paramDoc = new SqlParameter[1];
                paramDoc[0] = new SqlParameter("@DocumentNumber", DocNumber.ToString().Trim());

                SqlHelper.FillDataset(transaction, CommandType.StoredProcedure, "spGetDocumentDetailsByNumber", DocNum, taleNamesDoc, paramDoc);
                string docTitle = DocNum.Tables["docnumber"].Rows[0][0].ToString();


                transaction.Commit();

                return docTitle;
            }
            catch (Exception ex)
            {

                transaction.Rollback();
                throw ex;
            }

    }

多次运行该方法后，用户最终收到以下错误消息.

after running the method several times, user ended up getting the error message below.

从连接获得连接之前超时时间已过游泳池

the timeout period elapsed prior to obtaining a connection from the pool

发生错误是因为我没有关闭连接并且连接池已经溢出.

Error occurred because I haven't closed the connection and the connection pool has over flown.

我尝试在提交事务之前关闭连接.

I tried to close the connection before committing the transaction.

transaction.Connection.Close();  
transaction.Commit();

然后得到以下错误.

这个SqlTransaction已经完成；它不再可用

This SqlTransaction has completed; it is no longer usable

如何关闭连接以避免错误?

How can I close the connection to avoid the error?

推荐答案

如前所述，您应该正确处理连接.我已经修改了您的代码以进行演示.请注意，您需要将连接字符串替换为您的连接字符串.

As mentioned, you should dispose of the connection properly. I've modified your code to demonstrate. Please note you will need to substitute the connection string with yours.

private string getDocumentDetailsByNumber(string DocNumber)
{
    using (var connection = new SqlConnection("My Connection String"))
    {
        SqlTransaction transaction = connection.BeginTransaction();

        DataSet DocNum = new DataSet();

        string sDocNumber = "";
        string[] taleNamesDoc = new string[1];
        taleNamesDoc[0] = "docnumber";
        SqlParameter[] paramDoc = new SqlParameter[1];
        paramDoc[0] = new SqlParameter("@DocumentNumber", DocNumber.ToString().Trim());

        SqlHelper.FillDataset(transaction, CommandType.StoredProcedure, "spGetDocumentDetailsByNumber", DocNum, taleNamesDoc, paramDoc);
        string docTitle = DocNum.Tables["docnumber"].Rows[0][0].ToString();

        transaction.Commit();

        return docTitle;
    }  // Connection is disposed and cleaned up. 
}

打开新的连接很便宜，不应该被拒绝.你调用数据库的每一个你都应该像这样打开一个新的.通过维护连接而不是处理它，您也将资源从数据库中带走.它的池中没有可以同时使用的无限数量的连接.

Opening new connections are cheap and should not be frowned upon. Each one you call the database you should open a new one like this. By maintaining a connection and not disposing of it, you are taking resources away from the database as well. It does not have an infinite amount of connections in its pool that can be used at once.

编辑

删除了评论中提到的 try/catch.如果在 using 块中抛出异常，则会发生回滚并将异常向上传递堆栈.

Removed try/catch as mentioned in comments. If an exception is thrown while in the using block, a rollback will occur and the exception passed up the stack.

这篇关于使用事务时关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！