同时在多个方法之间共享单个数据库连接 [英] Sharing single database connection between several methods simultaneously

问题描述

我有一个网页，用户可以在其中插入某些产品的订单。在该页面中还有实时统计信息，每20秒刷新一次，以反映使用asp.net计时器的产品的当前价格。这个活动面板从数据库获取这些价格。

I have a page where a user can insert some order about some product. In that page there are also live statistics which is being refreshed every 20 seconds to reflect the current price of a product using asp.net timer. This live panel fetching these prices from a database.

当有时插入订单时，我得到一个异常，说连接被关闭，插入命令不能继续。我怀疑当给出插入命令时，如果在该时刻正在刷新实时更新，则它们都需要访问数据库，并且它们使用相同的SqlConnection对象。所以当一个完成，连接对象被关闭，即使另一个正在使用相同的连接对象。

When sometimes an order is being inserted, I get an exception saying that the connection is closed and the insert command cannot continue. I suspect that when the insert command is given, if at that exact moment the live update is being refreshed, then they both need to access the database, and they are using the same SqlConnection Object. So when one finishes, the connection object being closed even if the other one is using the same connection object.

在关闭连接之前，如何确保没有其他方法正在使用该连接？

Before closing a connection, how can I be sure that no other methods are using that connection?

我的连接类的代码如下，为了更好的说明 -

The code of my connection class is given below, for better clarification -

using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Specialized;
using System.IO;
using System.IO.Ports;


public class ConnectionHelper
{

#region Private Variables
static string BOconnectionString = "";
static SqlConnection BOcon = null;
static string STRSconnectionString = "";
static SqlConnection STRScon = null;
static SqlTransaction tSTRSTrans = null;

public static SqlTransaction STRSTrans
{
    get { return ConnectionHelper.tSTRSTrans; }
    set { ConnectionHelper.tSTRSTrans = value; }
}
#endregion


#region Constructor
ConnectionHelper() { }
#endregion



#region Public Functions 

public static SqlConnection getSTRSConnection()
{
    STRSconnectionString = ConfigurationManager.ConnectionStrings["STRS_WEB_DB2ConnectionString"].ConnectionString;
    try
    {
        if ((STRScon == null) || (STRScon.State == ConnectionState.Closed))
        {
            STRScon = new SqlConnection(STRSconnectionString);
            STRScon.Open();
            //tSTRSTrans = STRScon.BeginTransaction(IsolationLevel.RepeatableRead);
        }


    }
    catch (Exception ex)
    {
        throw new Exception("Error occurred when Connection is going to be opened" + ex.Message);
    }
    return STRScon;
}
public static void closeSTRSConnection()
{
    if ((STRScon != null) || (STRScon.State == ConnectionState.Open))
    {
        STRScon.Close();
    }        
}

#endregion

}

如果一个方法想要访问数据库，他们是通过以下方式获取和关闭连接 -

If a method want to access the database, they are obtaining and closing the connection in the following way -

con = ConnectionHelper.getSTRSConnection();
........
........
ConnectionHelper.closeSTRSConnection();

推荐答案

我不认为你应该使用这种方法一个Web应用程序，我看到你的意图是利用一个共同的连接，但考虑重构你的代码，所以，而不是试图使用现有的连接，即getSTRSConnection，你简单打开一个连接|做你的工作|并关闭它。作为一般的做法，尽可能晚地打开连接是一个好主意，然后一旦完成工作就关闭连接是另一个好主意。请记住，这些连接是一种昂贵的资源，应该只在需要将信息持久保存到您的数据库的时间内存在。

I don't think you should use this approach for a web application, I see your intent is to utilize a common connection, but consider refactoring your code so that instead of attempting to use an existing connection i.e. "getSTRSConnection", you simple open a connection | do your work | and close it. As a general practice, opening connections as late as possible is a good idea, and then closing the connection as soon as the work is done is another good idea. Remember that these connections are an expensive resource and should only live during the time they are needed to persist information to your database.

这篇关于同时在多个方法之间共享单个数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！