database connection, fetching rows and handling error in csharp

Question

Fetching database records and displaying as JSON. Asking for revision to ensure if everything's ok, i.e. connections are properly handled and closed in case of error.

public class Department
{
 public Department(int id, String name)
 {
 this.Id = id;
 this.Name = name;
 }
 public int Id { get; set; }
 public String Name { get; set; }
}
public List<Department> FindAllDepartment()
{
 List<Department> rows = new List<Department>();
 using (SqlConnection sqlConnection = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=P@ssw0rd;pooling=true"))
 {
 SqlCommand command = new SqlCommand("SELECT * FROM Employees.dbo.Department", sqlConnection);
 try
 {
 sqlConnection.Open();
 using (SqlDataReader sqlDataReader = command.ExecuteReader())
 {
 while (sqlDataReader.Read())
 rows.Add(new Department(sqlDataReader.GetInt32(0), sqlDataReader.GetString(1)));
 }
 return rows;
 }
 finally
 {
 sqlConnection.Close();
 }
 }
}

• dfhwze: Could you edit the title to state what the code functionally does, rather than technically?
• BCdotWEB: Don't write ADO.NET code, use the likes of Dapper. "if you’re writing ADO.Net code by hand, you’re stealing from your employer or client."
• AppDeveloper: I don't want to give my employer or client something that has 300+ outstanding issues, github.com/StackExchange/Dapper/issues plain vanilla ADO, just crack it down and learn to code the right way and just repeat.

Answer 1

 using (SqlConnection sqlConnection = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=P@ssw0rd;pooling=true"))
 {
 ...
 }
 finally
 {
 sqlConnection.Close();
 }
 }

You're correctly using a using statement for the connection, which will both close and dispose the connection when its scope finishes. So no need to explicit call Close().

Almost (if not) every database related object implements IDisposable - including SqlCommand, so you should encapsulate that in a using as well.

All in all, your method should look like something like this:

public List<Department> FindAllDepartment()
{
 using (SqlConnection sqlConnection = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=P@ssw0rd;pooling=true"))
 {
 sqlConnection.Open();
 using (SqlCommand command = new SqlCommand("SELECT * FROM Employees.dbo.Department", sqlConnection))
 using (SqlDataReader sqlDataReader = command.ExecuteReader())
 {
 List<Department> rows = new List<Department>();
 while (sqlDataReader.Read())
 {
 rows.Add(new Department(sqlDataReader.GetInt32(0), sqlDataReader.GetString(1)));
 }
 return rows;
 }
 }
}

where the using statements handle the clean up - even if an exception is thrown.

• AppDeveloper: Thanks, for using (SqlCommand command, are we missing braces?
• user73941: @AppDeveloper: No, we're allowed to "stack" using statements as shown :-)
• AppDeveloper: cool beans, very articulate. Can you refer me to any of your comprehensive insert, update or delete posts just for the sake of review.
• user73941: @AppDeveloper: I'm not sure if I understand your question?
• dfhwze: Asking for advice on code yet to be written or implemented is off-topic for this site. Even in a sneaky comment.