sqlconnection_为什么using语句比尖锐的用法更好，以及SqlConnection重构示例

sqlconnection

A friend of mine sent me some code just now where he was experimenting with Close and Dispose on SqlConnection.  Reflectoring into SqlConnection shows it closes open connections in Dispose().  So, here's the before and after code.  I think it shows good examples on why the using statement exists, and when to avoid (hide) superfluous try/catches.  I also changed a few nits for readability by using certain overloaded constructors as well as String.Format().

我的一个朋友刚刚给我发送了一些代码，他在SqlConnection上尝试使用Close and Dispose。 反射到SqlConnection中显示它关闭了Dispose()中打开的连接。 因此，这是之前和之后的代码。 我认为它显示了很好的示例，说明了using语句为何存在以及何时避免(隐藏)多余的try / catching。 我还通过使用某些重载的构造函数以及String.Format()更改了一些可读性。

BEFORE - The code I was given:

之前-给我的代码是：

private void RunScriptOnDB(string filename,string DB)
{
    SqlConnection sqlcon = new SqlConnection();
    sqlcon.ConnectionString ="Persist Security Info=False;Integrated Security=SSPI;Initial Catalog="+DB+";Data Source=(local);";
    SqlCommand com = new SqlCommand();
    com.Connection = sqlcon;
    try
    {
        StreamReader sr = Utility.GetStreamOfFile(filename);
        com.CommandText = sr.ReadToEnd();
        sr.Close();
    }
    catch(FileNotFoundException fileex)
    {
        msg.Text = fileex.Message;
        return;
    }
    try
    {
        sqlcon.Open();
        com.ExecuteNonQuery();
        msg.Text = "Successful";
    }
    catch( SqlException sqlex)
    {
        msg.Text = sqlex.Message;
    }
    finally
    {
        if(closingMethod.SelectedValue == "c") //SDH: He's trying different closing methods based on a Radio Button, this won't be needed in a refactor        {
            sqlcon.Close();
        }
        else if(closingMethod.SelectedValue == "d")
        {
            sqlcon.Dispose();
        }
        else
        {
            sqlcon.Close();
            sqlcon.Dispose();
        }
    }
}

私有void RunScriptOnDB(字符串文件名，字符串DB)
{
SqlConnection sqlcon =新的SqlConnection();
sqlcon.ConnectionString = “持久性安全信息= False;集成安全性= SSPI;初始目录=” + DB + “;数据源=(本地);” ;
SqlCommand com =新的SqlCommand();
com.Connection = sqlcon;
尝试
{
StreamReader sr = Utility.GetStreamOfFile(filename);
com.CommandText = sr.ReadToEnd();
sr.Close();
}
捕获(FileNotFoundException fileex)
{
msg.Text = fileex.Message;
回报;
}
尝试
{
sqlcon.Open();
com.ExecuteNonQuery();
msg.Text = “成功” ；
}
抓(SqlException sqlex)
{
msg.Text = sqlex.Message;
}
最后
{
if (closingMethod.SelectedValue == “ c” ) // SDH：他正在尝试基于单选按钮的其他关闭方法，因此在重构中不需要{
sqlcon.Close();
}
否则，如果(closingMethod.SelectedValue == “ d” )
{
sqlcon.Dispose();
}
其他
{
sqlcon.Close();
sqlcon.Dispose();
}
}
}

AFTER - My quickie refactor/clean:

之后-我的快速重构/清理：

private void RunScriptOnDB(string filename, string database)
{
   string commandText = String.Empty;
   try
   {
      using (StreamReader sr = Utility.GetStreamOfFile(filename))
      {
         commandText = sr.ReadToEnd();
      }
   }
   catch (FileNotFoundException fileEx)
   {
      msg.Text = fileEx.Message;
      return;
   }
   using (SqlConnection connection = new SqlConnection(String.Format("Persist Security Info=False;Integrated Security=SSPI;Initial Catalog={0};Data Source=(local);",database))
   {
      using (SqlCommand command = new SqlCommand(commandText, connection))
      {
         try
         {
            connection.Open();
            command.ExecuteNonQuery();
            msg.Text = "Successful";
         }
         catch (SqlException sqlEx)
         {
            msg.Text = sqlEx.Message;
         }
      }
   }
}

私有void RunScriptOnDB(字符串文件名，字符串数据库)
{
字符串commandText = String.Empty;
尝试
{
使用(StreamReader sr = Utility.GetStreamOfFile(filename))
{
commandText = sr.ReadToEnd();
}
}
捕获(FileNotFoundException fileEx)
{
msg.Text = fileEx.Message;
回报;
}
使用(SqlConnection连接=新的SqlConnection(String.Format( “ Persist Security Info = False;集成安全性= SSPI; Initial Catalog = {0} ; Data Source =(local);” ，数据库))
{
使用(SqlCommand命令=新的SqlCommand(commandText，连接))
{
尝试
{
connection.Open();
command.ExecuteNonQuery();
msg.Text = “成功” ；
}
捕获(SqlException sqlEx)
{
msg.Text = sqlEx.Message;
}
}
}
}

翻译自: https://www.hanselman.com/blog/why-the-using-statement-is-better-than-a-sharp-stick-in-the-eye-and-a-sqlconnection-refactoring-example

sqlconnection