c# - 无法在另一个 SqlConnection 的 'using' 子句中打开 SqlConnection？

Question

我很好奇为什么会这样。我今天早些时候遇到了这种情况

using (SqlConnection oConn = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("IC_Expense_InsertCycle", oConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PortalId", portalId);
        cmd.Parameters.AddWithValue("@Description", description);
        cmd.Parameters.AddWithValue("@StartDate", start);
        cmd.Parameters.AddWithValue("@EndDate", end);

        try
        {
            oConn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw ex;

        }
    }
}

//Get the new set of ExpenseCycles for binding
ExpenseCycle cycle = new ExpenseCycle(ConnectionString);
return cycle.GetExpenseCycles(portalId);

// ^^ this works just fine. The GetExpenseCycles call will basically set up the structure above with using SqlConnection and using SqlCommand

using (SqlConnection oConn = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("IC_Expense_InsertCycle", oConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PortalId", portalId);
        cmd.Parameters.AddWithValue("@Description", description);
        cmd.Parameters.AddWithValue("@StartDate", start);
        cmd.Parameters.AddWithValue("@EndDate", end);

        try
        {
            oConn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw ex;

        }

        //Get the new set of ExpenseCycles for binding
        ExpenseCycle cycle = new ExpenseCycle(ConnectionString);
        return cycle.GetExpenseCycles(portalId);

        //This didn't work. The INSERT statement was successful, but it was bringing back old entries, and did not include the newest one that was just inserted
    }
}

底部的代码块最初是我的，我的测试环境的返回计数只有 1，但数据库中有 2 条记录。它没有获取新插入的记录。

GetExpenseCycles 的基本代码如下:

using (SqlConnection oConn = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("IC_Expense_GetExpenseCyclesByPortal",oConn))
    {
        oConn.Open();
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            //Read List<expensecycle> here
        }
    }
}

有什么想法吗？没有抛出异常。

Answer 1

没有抛出异常所以没有错误...我怀疑连接上的隔离级别

在第一种情况下，连接不重叠。

ExpenseCycle() 使用连接字符串，我可以安全地假设它启动了一个新连接。

在第二个示例(问题案例)中，连接确实重叠:

如果隔离级别例如是读取提交的，并且“封闭”连接尚未稳定其写入(提交)，则新连接不会获取更改，在这种情况下是插入。

可能的解决方案或尝试的事情:1.检查连接上的隔离级别2. 将连接而不是连接字符串传递给 ExpenseCycle()(恕我直言，这也是一种更好的做法)