"사용"블록에서 SqlConnection이 반환 또는 예외로 닫혔습니까?

"사용"블록에서 SqlConnection이 반환 또는 예외로 닫혔습니까?

---

첫 번째 질문 :
내가 가지고 있다고

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string storedProc = "GetData";
    SqlCommand command = new SqlCommand(storedProc, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

    return (byte[])command.ExecuteScalar();
}

연결이 닫혔습니까? 기술적 }으로 우리는 return이전 과 다름 없이 끝까지 도달하지 않기 때문입니다.

두 번째 질문 :
이번에는

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        int employeeID = findEmployeeID();

        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();
    }
}
catch (Exception) { /*Handle error*/ }

이제 어딘가에 try오류가 발생하면 오류가 발생합니다. 연결이 여전히 닫혀 있습니까? 다시, 우리는에서 코드의 나머지 부분을 건너 뛰고 명령문 try으로 직접 이동 catch합니다.

using작동 방식에 너무 선형 적으로 생각하고 있습니까? 즉 Dispose(), 우리가 using범위를 벗어날 때 단순히 호출 됩니까 ?

---

1. 예
2. 예.

어느 쪽이든, 사용 블록이 종료되면 (성공적으로 완료되거나 오류로 인해) 닫힙니다.

비록 나중에 지원할 새로운 유지 보수 프로그래머에게조차도 앞으로 일어날 일을 훨씬 쉽게 볼 수 있기 때문에 이렇게 구성하는 것이 더 좋을 것이라고 생각합니다 .

using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
    int employeeID = findEmployeeID();    
    try    
    {
        connection.Open();
        SqlCommand command = new SqlCommand("UpdateEmployeeTable", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));
        command.CommandTimeout = 5;

        command.ExecuteNonQuery();    
    } 
    catch (Exception) 
    { 
        /*Handle error*/ 
    }
}

---

두 질문에 모두 그렇습니다. using 문은 try / finally 블록으로 컴파일됩니다.

using (SqlConnection connection = new SqlConnection(connectionString))
{
}

와 같다

SqlConnection connection = null;
try
{
    connection = new SqlConnection(connectionString);
}
finally
{
   if(connection != null)
        ((IDisposable)connection).Dispose();
}

편집 : 일회용 캐스트 캐스트 http://msdn.microsoft.com/en-us/library/yh598w02.aspx

---

여기 내 템플릿이 있습니다. SQL Server에서 데이터를 선택하는 데 필요한 모든 것. 연결이 닫히고 삭제되고 연결 및 실행 오류가 발생합니다.

string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CompanyServer"].ConnectionString;
string selectStatement = @"
    SELECT TOP 1 Person
    FROM CorporateOffice
    WHERE HeadUpAss = 1 AND Title LIKE 'C-Level%'
    ORDER BY IntelligenceQuotient DESC
";
using (SqlConnection conn = new SqlConnection(connString))
{
    using (SqlCommand comm = new SqlCommand(selectStatement, conn))
    {
        try
        {
            conn.Open();
            using (SqlDataReader dr = comm.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Console.WriteLine(dr["Person"].ToString());
                    }
                }
                else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)");
            }
        }
        catch (Exception e) { Console.WriteLine("Error: " + e.Message); }
        if (conn.State == System.Data.ConnectionState.Open) conn.Close();
    }
}

* 개정 : 2015-11-09 *
NickG가 제안한대로; 너무 많은 괄호로 인해 성가신 경우 다음과 같이 포맷하십시오.

using (SqlConnection conn = new SqlConnection(connString))
   using (SqlCommand comm = new SqlCommand(selectStatement, conn))
   {
      try
      {
         conn.Open();
         using (SqlDataReader dr = comm.ExecuteReader())
            if (dr.HasRows)
               while (dr.Read()) Console.WriteLine(dr["Person"].ToString());
            else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)");
      }
      catch (Exception e) { Console.WriteLine("Error: " + e.Message); }
      if (conn.State == System.Data.ConnectionState.Open) conn.Close();
   }

Then again, if you work for EA or DayBreak games, you can just forgo any line-breaks as well because those are just for people who have to come back and look at your code later and who really cares? Am I right? I mean 1 line instead of 23 means I'm a better programmer, right?

using (SqlConnection conn = new SqlConnection(connString)) using (SqlCommand comm = new SqlCommand(selectStatement, conn)) { try { conn.Open(); using (SqlDataReader dr = comm.ExecuteReader()) if (dr.HasRows) while (dr.Read()) Console.WriteLine(dr["Person"].ToString()); else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)"); } catch (Exception e) { Console.WriteLine("Error: " + e.Message); } if (conn.State == System.Data.ConnectionState.Open) conn.Close(); }

Phew... OK. I got that out of my system and am done amusing myself for a while. Carry on.

---

Dispose simply gets called when you leave the scope of using. The intention of "using" is to give developers a guaranteed way to make sure that resources get disposed.

From MSDN:

A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.

---

Using generates a try / finally around the object being allocated and calls Dispose() for you.

It saves you the hassle of manually creating the try / finally block and calling Dispose()

---

In your first example, the C# compiler will actually translate the using statement to the following:

SqlConnection connection = new SqlConnection(connectionString));

try
{
    connection.Open();

    string storedProc = "GetData";
    SqlCommand command = new SqlCommand(storedProc, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

    return (byte[])command.ExecuteScalar();
}
finally
{
    connection.Dispose();
}

Finally statements will always get called before a function returns and so the connection will be always closed/disposed.

So, in your second example the code will be compiled to the following:

try
{
    try
    {
        connection.Open();

        string storedProc = "GetData";
        SqlCommand command = new SqlCommand(storedProc, connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@EmployeeID", employeeID));

        return (byte[])command.ExecuteScalar();
    }
    finally
    {
        connection.Dispose();
    }
}
catch (Exception)
{
}

The exception will be caught in the finally statement and the connection closed. The exception will not be seen by the outer catch clause.

---

I wrote two using statements inside a try/catch block and I could see the exception was being caught the same way if it's placed within the inner using statement just as ShaneLS example.

     try
     {
       using (var con = new SqlConnection(@"Data Source=..."))
       {
         var cad = "INSERT INTO table VALUES (@r1,@r2,@r3)";

         using (var insertCommand = new SqlCommand(cad, con))
         {
           insertCommand.Parameters.AddWithValue("@r1", atxt);
           insertCommand.Parameters.AddWithValue("@r2", btxt);
           insertCommand.Parameters.AddWithValue("@r3", ctxt);
           con.Open();
           insertCommand.ExecuteNonQuery();
         }
       }
     }
     catch (Exception ex)
     {
       MessageBox.Show("Error: " + ex.Message, "UsingTest", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }

No matter where's the try/catch placed, the exception will be caught without issues.

참고URL : https://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception