C# 正确的 SqlConnection 声明

Correct SqlConnection declaration

提问by LoneXcoder

The problem is that I have used my SqlConnectionas a public static connection, thinking this might be the problem causing form time to time an error :

问题是我已将 mySqlConnection用作公共静态连接，认为这可能是导致表单不时出现错误的问题：

*the connection is not open or the connection was already open

*连接未打开或连接已打开

So is it ok to use one statement of SqlConnectionin a static class?

那么可以SqlConnection在静态类中使用一个语句吗？

So that I could Declareit only once, I know I could use connectionStringin web.config

这样我就可以只声明一次，我知道我可以connectionString在web.config

ConfigurationManager.ConnectionStrings["conn"].ConnectionString ...

but I like it to stay unrelated to web.configsettings or servers name.

但我喜欢它与web.config设置或服务器名称无关。

• ReEdit :

• 重新编辑：

as it is realy two methods within same class theres also another class in that main class but this is not what's important rather than using same connection for all executions ! so you say that even though i re edited with right code of my helprer class this is wrong ?

因为它确实是同一个类中的两个方法，该主类中还有另一个类，但这不是重要的，而不是对所有执行使用相同的连接！所以你说即使我用我的助手类的正确代码进行了编辑，这也是错误的吗？

public static class myDBhelper
{
 public static SqlConnection Conn = new SqlConnection ("server=(local);Initial Catalog=dbName;Integrated Security=True");

        public static int ExecSQLint(string TblintSQL)
        {
            int anIntValue=0;
            SqlCommand TblintCMD = new SqlCommand(TblintSQL, Conn);
            try
            {
                Conn.Open();
                anIntValue = Convert.ToInt32(TblintCMD.ExecuteScalar());
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }
          return anIntValue;
        }



        public static string ExecSQLstring(string TblStrSQL)
        {
          string strValue="";
          SqlCommand TblStrCMD = new SqlCommand(TblStrSQL, Conn);
            try
            {
                Conn.Open();
                strValue = TblStrCMD.ExecuteScalar().ToString();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }

            return strValue;
        }


}

The main issue I suspect is those two options :

我怀疑的主要问题是这两个选项：

SqlConnection Conn = new SqlConnection("Data Source=(local);Integrated Security=True;database=dbName")

in my DBhelperclass I was using this declaration

在我的DBhelper课堂上，我使用了这个声明

SqlConnection Conn = new SqlConnection("server=(local);Initial Catalog=dbName;Integrated Security=True");

could that be unstable or error prone ?

那会不稳定或容易出错吗？

p.s.: I am executing commands via try catch

ps：我正在通过 try catch 执行命令

            try
            {
                Conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception("No Can Do: " + ex.Message);
            }
            finally
            {
                Conn.Close();
            }

Is Usingstatement more appropriate? Although it is not my problem I was thinking... if I am already trying to do it 'by the book'...

是Using语句比较合适？虽然这不是我的问题，但我在想……如果我已经在尝试“按书”去做……

Is any method here actually wrongamong those ?

其中有什么方法实际上是错误的吗？

采纳答案by cuongle

Keeping Connection as static is not a common wayto use connection to database. It could lead to exception as you mentioned when application is working on web or multi-thread environment.

将连接保持为静态并不是使用数据库连接的常用方法。当应用程序在 Web 或多线程环境中工作时，它可能会导致您提到的异常。

Image that thread 1 executing command 1 is the same connection with thread 2 executing command 2. Your ex: TblintCMD and TblStrCMD. When thread 1 finishs, it closed connection, meanwhile thread 2 is still executing command on close connection

想象一下，线程 1 执行命令 1 与线程 2 执行命令 2 的连接相同。您的前任：TblintCMD 和 TblStrCMD。当线程 1 完成时，它关闭连接，同时线程 2 仍在执行关闭连接的命令

Your two options are not the problem.

你的两个选项不是问题。

The best way is to use usingkeyword and create connection when needed:

最好的方法是使用using关键字并在需要时创建连接：

using (var connection = new SqlConnection("yourConnectionString"))
{
    connection.Open();
    ...
}

usingis similar with:

using类似于：

var connection =  new SqlConnection("connectionString");
try
{
    connection.Open();
    ....
}
finally
{
    connection.Close();
}

So, you don't need to know when to close Connection.

因此，您无需知道何时关闭 Connection。

Behind the scene, ADO.NET uses connection poolto manage connections for you automatically, so you should not care much how many connections open.

在幕后，ADO.NET 使用连接池自动为您管理连接，因此您不必太在意打开了多少连接。

回答by Kishore Kumar

using(var conn=new. SqlConnection( "server=(local);Initial Catalog=dbName;Integrated Security=True"))
{
    conn.Open();
}

回答by Luqman Cheema

public SqlConnection GetSqlConnection()
{
    SqlConnection sql = new SqlConnection();
    sql.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SchoolContext"].ToString();
    return sql;
}