Should i go for SqlDataReader or SqlDataAdapter class for returning a datatable

Question

I have written the below code. I want to know if i can improve it any further.

public static DataTable GetDepartments()
{
    DataTable dt = new DataTable();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.CommandText = "proc_GetDepartments";
            command.CommandType = CommandType.StoredProcedure;

            connection.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(command))
            {
                command.Connection = connection;
                da.Fill(dt);
            }
        }
    }
    return dt;

}

Here i have used SqlDataAdapter. What would be the SqlDataReader way to write it. Also which one is better. Any help/guidance is appreciated.

Answer 1

Also which one is better?

As I said in my comment, they are two different things. Apples and oranges..

SqlDataAdapter is using with the a DataTable. It can fill a DataTable with a table from your SQL. SqlDataReader reads database rows one-by-one.

In your case, I don't see any reason to use SqlDataReader since you want to return a DataTable. Go for SqlDataAdapter ;)

..also if u can add the code for SqlDataReader using using.

Sure. But as I said, you can't use DataTable with SqlDataReader. But hey.. Here is how you can get values with SqlDataReader;

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    int firstcell = reader.GetInt32(0);    // I assume your first column is int.
    string secondcell = reader.GetString(1);  // I assume your second column is string.
    string thirdcell = reader.GetString(2); // I assume your third column is string.
    Console.WriteLine("FirstCell = {0}, SecondCell = {1}, ThirdCell = {2}", firstcell, secondcell , thirdcell);
}