的SqlCommand，SqlConnection的使用问题处置 [英] SqlCommand-SqlConnection Using Disposing issue

问题描述

根据MSDN如果
嵌套内部using语句的IDisposable的资源包含外使用
语句的资源，嵌套资源的Dispose方法释放
包含的资源。

MSDN（的http：/ /msdn.microsoft.com/en-us/library/ms182334.aspx ）=>

例嵌套using语句（使用Visual Basic中）可能会导致
侵犯CA2202警告。如果
嵌套内部使用语句IDisposable的资源包含外使用
语句的资源，嵌套资源的Dispose方法释放
包含的资源。当这种情况发生时，$ B $的Dispose方法B外using语句试图出售其资源为
第二次。在下面的例子中，创建
在外部using语句Stream对象在该StreamWriter对象是
的Dispose方法内使用
语句的结束被释放包含流目的。在使用外部语句的结束，
流对象被释放了第二次。第二个版本是
违反CA2202的。

但如果我尝试这段代码的代码仍然有效，并返回数计数插入到表中。这是对MSDN的解释自相矛盾。我希望的代码，因为conn变量是第一个使用内部语句后设置在cmd.ExecuteScalar（）调用崩溃。
这是为什么还在工作，为什么不设置在第一内使用？

 静态无效后conn变量主要（字串[] args）
 {
变种数= DoItGetIt（）; 
}用
 
私有静态诠释DoItGetIt（）
 {
（VAR康恩=新的SqlConnection（数据源= BestmixSql;初始目录=测试;集成安全性=真））
 {
 conn.Open（）; 
使用（VAR CMD =新的SqlCommand（））
 {
 cmd.Connection =康恩; 
 cmd.CommandText =。INSERT INTO [测试] [DBO] [TABEL] VALUES（666）; 
 cmd.ExecuteNonQuery（）; 
}使用
 
（VAR CMD =新的SqlCommand（））
 {
 cmd.Connection =康恩; 
 cmd.CommandText =SELECT COUNT（*）FROM [测试] [DBO] [TABEL]。 
 
变种数= cmd.ExecuteScalar（）; 
 
返回Convert.ToInt32（计数）; 
} 
} 
} 
  

解决方案

您的谈话对象MSDN的例子是专门谈论到内部对象的需要的外部对象的posession场景;例如，的StreamWriter 可以假设为流责任。在的该方案下，布置所述内对象还导致要设置外部对象 - 但这不是在一般情况下真

在特别是，命令不承担处置连接的责任。有趣的是，数据读取器的可以的承担起责任，而只是通过一个可选的标志。

许多这样的对象提供的标志，让来电者决定是否内部对象应承担处置外部对象的责任。例如，的StreamWriter 现在还提供了一个构造函数重载使用布尔leaveOpen 参数。如果你传递这是真正的的StreamWriter 做的不可以级联的Dispose（）。

这是全部内部对象，当它的实现细节的专门编写要做到这一点的。这不是使用模式的默认行为。

---

附注：我想说的MSDN简直就是错在这里。在正确的实施为两使用 与第一个样本。第二个例子是不直观，并且容易发生不正确执行。不要使用它。如有必要，使用 leaveOpen 要提出的是明确的，但坦率地说，通常没有这个工作得很好，如果你大概要处理它。

According to MSDN If the IDisposable resource of the nested inner using statement contains the resource of the outer using statement, the Dispose method of the nested resource releases the contained resource.

MSDN (http://msdn.microsoft.com/en-us/library/ms182334.aspx) =>

Example Nested using statements (Using in Visual Basic) can cause violations of the CA2202 warning. If the IDisposable resource of the nested inner using statement contains the resource of the outer using statement, the Dispose method of the nested resource releases the contained resource. When this situation occurs, the Dispose method of the outer using statement attempts to dispose its resource for a second time. In the following example, a Stream object that is created in an outer using statement is released at the end of the inner using statement in the Dispose method of the StreamWriter object that contains the stream object. At the end of the outer using statement, the stream object is released a second time. The second release is a violation of CA2202.

But if i try this piece of code the code still works and returns the number count inserted into the table. Which is contradictory towards the MSDN explanation. I would expect the code to crash on the cmd.ExecuteScalar() call because the conn variable is disposed after the first inner using statement. Why is this still working and why isn't the conn variable disposed after the first inner using?

static void Main(string[] args)
{
    var numbers= DoItGetIt();
}

private static int DoItGetIt()
{
    using (var conn = new SqlConnection("Data Source=BestmixSql;Initial Catalog=Test;Integrated Security=True"))
    {
        conn.Open();
        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO [Test].[dbo].[Tabel] VALUES (666)";
            cmd.ExecuteNonQuery();
        }

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "SELECT COUNT(*) FROM [Test].[dbo].[Tabel]";

            var count = cmd.ExecuteScalar();

            return Convert.ToInt32(count);
        }
    }
}

解决方案

The MSDN example you are talking to is specifically talking to the scenario where the inner object takes posession of the outer object; for example, a StreamWriter can assume responsibility for a Stream. In that scenario, disposing the inner object also causes the outer object to be disposed - but this is not true in the general case.

In particular, a command does not assume responsibility for disposing a connection. Interestingly, a data-reader can assume responsibility, but only via an optional flag.

Many such objects offer flags to let the caller determine whether the inner object should assume responsibility for disposing the outer object. For example, StreamWriter also now offers a constructor-overload with a bool leaveOpen parameter. If you pass this as true, the StreamWriter does not cascade the Dispose().

This is all implementation details of the inner object, when it is specifically written to do this. It is not a default behaviour of the using pattern.

---

Side note: I would say MSDN is simply wrong here. The correct implementation is the first sample with two using. The second example is non-intuitive, and prone to incorrect implementation. Don't use it. If necessary, use leaveOpen to make is explicit, but frankly it usually works fine without this, if you are about to dispose it anyway.

这篇关于的SqlCommand，SqlConnection的使用问题处置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！