ASP.NET 中的 SqlConnection 问题

Question

我创建了一个 3tier 应用程序..我想在其中调用连接数据库并相应更新记录的更新方法。

下面是我的数据库访问层。

public class DataLogic
{
    public DataLogic()
    {
    }

    public SqlConnection ConnectDatabase
    {
        get { return new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString); }
    }

    public int UpdateArticle(BusinessLogic b, int ArticleId)
    {
        int updateExecuted = -1;
        StringBuilder formParamString = new StringBuilder();
        formParamString.Append("IsArticlePaging=" + b.IsPagingEnable + " ");
        string updateString = "update crossarticle_article set " + formParamString.ToString() + "where id = " + ArticleId + "";
        try
        {
            using (SqlCommand comUpdateArticle = new SqlCommand(updateString, ConnectDatabase))
            {
                ConnectDatabase.Open();
                updateExecuted = comUpdateArticle.ExecuteNonQuery();
            }
        }
        catch (Exception ex) { HttpContext.Current.Response.Write(ex.Message); }
        finally { ConnectDatabase.Close(); }
        return updateExecuted;
    }
}

下面是我的业务逻辑层，

public class BusinessLogic
{
    DataLogic dLogic = new DataLogic();
    public BusinessLogic()
    {

    }
    private bool _IsPagingEnable;

    public bool IsPagingEnable
    {
        get { return _IsPagingEnable; }
        set { _IsPagingEnable = value; }
    }
    private int _articleID;
    public int ArticleID
    {
        get { return _articleID; }
        set { _articleID = value; }
    }

    public int UpdateExtraFieldArticle()
    {
        return dLogic.UpdateArticle(this, ArticleID);
    }
}

当我创建 BusinessLogic 对象并调用更新方法时，它会按预期调用 DataLogic 的更新方法，但在更新数据库之前它会抛出错误，指出 ExecuteNonQuery 需要打开且可用的连接。但我已经打开了连接。

请任何人帮我解决有关 sqlconnection 的问题。

Answer 1

问题是您正在打开两个单独的连接。请尝试以下操作：

using (sqlConnection connection = ConnectDatabase)
{
    using (SqlCommand comUpdateArticle = new SqlCommand(updateString, connection))
    {
        connection.Open();
        updateExecuted = comUpdateArticle.ExecuteNonQuery();
    }
}

Answer 2

您的 ConnectDatabase 始终返回一个新的连接：

public SqlConnection ConnectDatabase
{
 get { return new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString); }
}

您应该使用局部变量并从工厂方法初始化一次：

public SqlConnection CreateConnection
{
    return new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString);
}

例如：

using (SqlConnection con = CreateConnection())
{
   using (SqlCommand comUpdateArticle = new SqlCommand(updateString, con))
   {
      con.Open();
      updateExecuted = comUpdateArticle.ExecuteNonQuery();
   }
}