ExecuteReader需要一个开放且可用的连接。连接的当前状态为“正在连接”。 [英] ExecuteReader requires an open and available Connection. The connection's current state is Connecting.

问题描述

ExecuteReader需要一个开放且可用的连接。连接的当前状态是正在连接。



该站点在我的本地主机服务器上正常工作，但它有时会在服务器上出现此错误。问题是什么？

ExecuteReader requires an open and available Connection. The connection's current state is Connecting.

The site works fine on my localhost server but it sometimes gives this error on server. what's the problem?

public class Database
   {
       public static string _connectionstring = "Provider=\"Microsoft.Jet.OLEDB.4.0\";Mode=Share Deny None;Data Source=\"" +
          HttpContext.Current.Request.PhysicalApplicationPath + "/App_Data/Borsa_db.mdb\";User ID=Admin;Password=;";

       static OleDbConnection cnn = new OleDbConnection(_connectionstring);

       public static DataSet FillDataSet(string sql, string Tablo)
       {
           DataSet ds = new DataSet();

           OleDbDataAdapter da = new OleDbDataAdapter(sql, cnn);

           da.Fill(ds, Tablo);

           return ds;
       }

       public static int ExecutenonQuery(string sql, bool sonuc)
       {
           int deger = -1;

           if (sql != "")
           {
               OleDbCommand cmd = new OleDbCommand(sql, cnn);

               if (cnn.State == ConnectionState.Closed) cnn.Open();

               try
               {
                   if (sonuc)
                   {
                       cmd.ExecuteNonQuery();
                       OleDbCommand cmd1 = new OleDbCommand("SELECT @@IDENTITY AS SAYI", cnn);
                       deger = Convert.ToInt32(cmd1.ExecuteScalar());
                   }

                   else
                   {
                       deger = cmd.ExecuteNonQuery();
                   }
               }
               finally
               {
                   cnn.Close();
               }
           }

           return deger;
       }

       public static object ExecuteScalar(string sql)
       {
           object deger = null;

           OleDbCommand cmd = new OleDbCommand(sql, cnn);

           if (cnn.State == ConnectionState.Closed) cnn.Open();

           try
           {
               deger = cmd.ExecuteScalar();
           }
           finally
           {
               cnn.Close();
           }

           return deger;
       }

       public static OleDbDataReader DataReader(string sql)
       {
           OleDbCommand cmd = new OleDbCommand(sql, cnn);
           OleDbDataReader dr;

           if (cnn.State == ConnectionState.Closed) cnn.Open();
           try
           {
               dr = cmd.ExecuteReader();
           }
           finally
           {
               cnn.Close();
           }

           return dr;
       }
   }

推荐答案

不要将连接对象存储在静态变量中，尤其是在一个ASP.NET应用程序。这个单一连接将在多个线程的应用程序的每个请求之间共享，这将导致很多难以找到的错误。



相反，每个创建连接你需要的时间，并确保你使用块将其包装在中，以确保它始终正确处理。



您可以使用 | DataDirectory | 占位符来避免 HttpContext.Current.Request.PhysicalApplicationPath 引用。在ASP.NET应用程序中，它始终指向 App_Data 目录。



Don't store connection objects in static variables, particularly in an ASP.NET application. That single connection will be shared between every single request to your application, across multiple threads, which will cause lots of difficult to find errors.

Instead, create the connection each time you need it, and make sure you wrap it in a using block to ensure that it's always disposed of correctly.

You can avoid the HttpContext.Current.Request.PhysicalApplicationPath reference by using the |DataDirectory| placeholder. In an ASP.NET application, this always points to the App_Data directory.

private const string Connectionstring = "Provider=\"Microsoft.Jet.OLEDB.4.0\";Mode=Share Deny None;Data Source=\"|DataDirectory|\\Borsa_db.mdb\";User ID=Admin;Password=;";

public static OleDbConnection CreateConnection()
{
    var result = new OleDbConnection(ConnectionString);
    result.Open();
    return result;
}

您的方法建议您编写的代码容易受到 SQL注入 [ ^ ]。您需要修改它们以接受参数，并更新代码以正确传递参数，而不是使用字符串连接。



你想知道关于SQL注入的一切（但不敢问）特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

SQL注入攻击机制Pluralsight [ ^ ]

Your methods suggest that you are writing code which is vulnerable to SQL Injection[^]. You need to modify them to accept parameters, and update your code to pass the parameters correctly, instead of using string concatenation.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

这篇关于ExecuteReader需要一个开放且可用的连接。连接的当前状态为“正在连接”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！