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

Could you edit the title to state what the code functionally does, rather than technically? — dfhwze
– dfhwze, Commented Oct 9, 2019 at 16:10
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." — BCdotWEB
– BCdotWEB, Commented Oct 10, 2019 at 14:08
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. — AppDeveloper
– AppDeveloper, Commented Oct 10, 2019 at 14:11

dfhwze · Accepted Answer · 2019-10-09 17:09:34Z

2

  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.

edited Oct 9, 2019 at 17:09

dfhwze

14.2k3 gold badges40 silver badges101 bronze badges

answered Oct 9, 2019 at 16:45

user73941

\$\begingroup\$ Thanks, for using (SqlCommand command, are we missing braces? \$\endgroup\$

AppDeveloper
– AppDeveloper

2019-10-09 16:50:18 +00:00
Commented Oct 9, 2019 at 16:50
1

\$\begingroup\$ @AppDeveloper: No, we're allowed to "stack" using statements as shown :-) \$\endgroup\$

user73941
– user73941

2019-10-09 16:51:55 +00:00
Commented Oct 9, 2019 at 16:51
\$\begingroup\$ cool beans, very articulate. Can you refer me to any of your comprehensive insert, update or delete posts just for the sake of review. \$\endgroup\$

AppDeveloper
– AppDeveloper

2019-10-09 16:55:32 +00:00
Commented Oct 9, 2019 at 16:55
\$\begingroup\$ @AppDeveloper: I'm not sure if I understand your question? \$\endgroup\$

user73941
– user73941

2019-10-09 17:17:31 +00:00
Commented Oct 9, 2019 at 17:17
2

\$\begingroup\$ Asking for advice on code yet to be written or implemented is off-topic for this site. Even in a sneaky comment. \$\endgroup\$

dfhwze
– dfhwze

2019-10-09 17:23:30 +00:00
Commented Oct 9, 2019 at 17:23

Add a comment |

Stack Exchange Network

database connection, fetching rows and handling error in csharp

1 Answer 1

You must log in to answer this question.

Hot Network Questions

database connection, fetching rows and handling error in csharp

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions