错误，因为连接字符串属性尚未初始化

Question

为什么我会遇到此错误：

连接状态尚未进行Intialized

当我在另一种方法中使用一种方法？

这是我的DbConnectio.cs：

public class DbContext
{
        public SqlConnection sqlconn = null;
        public SqlConnection DbConnection 
        {
            get { return sqlconn; }
            set { value = sqlconn; }
        }
        public DbContext()
        {
            string cs = ConfigurationManager.ConnectionStrings["CTXDB"].ConnectionString;
            sqlconn = new SqlConnection(cs);
        }
}

web.config：

<add name="CTXDB" 
     connectionString="Data Source=Md;Initial Catalog=Md;User ID=sa;Password=123;MultipleActiveResultSets=true" 
     providerName="System.Data.SqlClient" />

这是我的回购 - 我正在实施我的业务逻辑：

DbContext db = new DbContext();
public Employee FindEmpById(int key)
{
        SqlConnection conn = db.DbConnection;
        try
        {
            var employee = new Employee();
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand("Sp_GetEmployeeById", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmpId", key);
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.HasRows == true)
            {
                while (rdr.Read())
                {
                    employee.Emp_Id = Convert.ToInt32(rdr["Emp_Id"]);
                    employee.EmpName = rdr["EmpName"].ToString();
                    employee.Email = rdr["Email"].ToString();
                    employee.Psw = rdr["Psw"].ToString();
                }
            }
            return employee;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (conn != null)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                    conn.Dispose();
            }
        }
}

这个 FindEmpById我在 DeleteEmpById函数

中致电

public void DeleteEmpById(int Key)
{
            SqlConnection Con = db.DbConnection;
            var x = FindEmpById(Key);
            if (x != null)
            {
                if (Con.State != ConnectionState.Open)
                {
                    Con.Open();
                }
                SqlCommand cmd = new SqlCommand("sp_DeleteById", Con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmpId", Key);
                cmd.ExecuteNonQuery();
            }
}

Answer 1

FindEmpById处理连接 conn.Dispose();。因此，当您之后尝试使用它时，它不再有效。

不要尝试重复使用连接。每次您需要一个新连接。在内部，物理连接会自动汇总，即，在可能的情况下将重复使用相同的物理连接。使用new SqlConnection创建新连接很轻。

而不是做

SqlConnection conn = db.DbConnection; // WRONG!
// All your try catch finally and testing for conn.State    --- WRONG!

做

// OK!
using (SqlConnection conn = db.CreateConnection()) {
    conn.Open();
    ...
} // Automatically closed and disposed here.

CreateConnection在每个呼叫中创建新的SqlConnection。这也容易得多，直截了当。将您的DbContext类更改为

public class DbContext
{
    private static readonly string _connectionString;
    static DbContext()
    {
        _connectionString = ConfigurationManager.ConnectionStrings["CTXDB"].ConnectionString;
    }
    public SqlConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

---

您的大修FindEmpById方法变为

public Employee FindEmpById(int key)
{
    using (SqlConnection conn = db.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("Sp_GetEmployeeById", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@EmpId", key);
        var employee = new Employee();
        conn.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader()) {
            if (rdr.Read()) {
                employee.Emp_Id = Convert.ToInt32(rdr["Emp_Id"]);
                employee.EmpName = rdr["EmpName"].ToString();
                employee.Email = rdr["Email"].ToString();
                employee.Psw = rdr["Psw"].ToString();
            }
        }
        return employee;
    }
}

顺便说一句：删除之前，您无需致电FindEmpById(Key)。只是删除。在SQL中删除0个记录不是错误。

public void DeleteEmpById(int Key)
{
    using (SqlConnection conn = db.CreateConnection())
    using (SqlCommand cmd = new SqlCommand("sp_DeleteById", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@EmpId", Key);
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}