database connection, fetching rows and handling error in csharp 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&lt;Department&gt; FindAllDepartment()
{
    List&lt;Department&gt; rows = new List&lt;Department&gt;();
    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();
        }
    }
}
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&lt;Department&gt; 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&lt;Department&gt; rows = new List&lt;Department&gt;();
      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.