Search content within the blog

Friday, May 29, 2009

Refactoring code in c#

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.

BEFORE

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();
}
}
}

AFTER


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;
}
}
}
}

No comments:

Post a Comment