C#: Is this the correct way to use the SqlDataReader for DAAB

Question

Hello, I have just started using the Data Access Application Block from microsoft. There are very few instructions on the correct way to use the library. Just wanted to know if this is the correct way to use the data reader.

SqlDataReader reader = SqlHelper.ExecuteReader(config.ConnectionString, CommandType.Text, "select * from category");
List<string> a = new List<string>();
using (reader)
{
     while (reader.Read())
     {
          string t = reader.GetString(1);
          a.Add(t);
     }

     return a;
}

will everything get closed doing it this way? Is there any chance of memory leaks?

Answer 1

HAve a look at

The Enterprise Library Data Access Application Block, Part 2

Answer 2

Put your reader initialization into the using block, and I would avoid using column numbers if you can, as they essentially turn into magic numbers. Unfortunately, that just requires you use the column names, but I have found that column names are less likely to change than column offsets, and you can always put your column names in a configuration file or something. Also, make sure you take into account the possibility for null columns

using(var reader = SqlHelper.ExecuteReader(etc. etc. etc.))
{
    while(reader.read()){
    {
        //Only need to do this if you don't want your string to be null
        //and if the "columnName" column is nullable.
        var stringValue = reader.IsDBNull(reader.GetOrdinal("columnName") 
                        ? "" 
                        : reader.GetString(reader.GetOrdinal("columnName"));
        a.Add(stringValue);
    }
}