NullReferenceException on closing datareader

Question

I am just learning to work with ADO.NET and I seem to have a problem.What I am trying to do is get the data from a table and insert it into a DataTable.Here is my code:

public DataTable GetCategories()
    {
        SqlConnection connection = null;
        SqlDataReader reader = null;
        DataTable categories = new DataTable();

        try {
            connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            connection.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetCategories";
            reader = cmd.ExecuteReader();

            categories.Columns.Add("Id", typeof(int));
            categories.Columns.Add("CategoryName", typeof(int));

            while (reader.Read()) {
                int categoryId = (int)reader["Id"];
                string categoryName = (string)reader["CategoryName"];
                categories.Rows.Add(categoryId , categoryName);
            }

        }catch(Exception e){
            DataTable error = new DataTable();
            error.Columns.Add("Error");
            error.Rows.Add(e.Message);
            return error;
        }finally{
            connection.Close();
            reader.Close();
        }
        return categories;
    }

Here is my SQL query :

 CREATE PROCEDURE [dbo].[GetCategories]
    AS
        SELECT Id , CategoryName
        FROM Categories

Where I run this method I get back on reader.Close() an exception that says NullRefferenceException.

What am I doing wrong?

EDIT

I just noticed that reader = cmd.ExecuteReader(); throws an InvalidOperationException.Is this because of the query?

Answer 1

The way you have your code written means that if there's an error creating or connecting to the SqlConnection, your finally block will try to close a reader that hasn't been set yet.

Either check for a null value in the finally block or re-structure your code.