SqlDataReader closed for unknown reason

Question

C# noob here. I am trying to get some data from a SQL Data Base using the code below, however i get the error: System.InvalidOperationException: 'Invalid attempt to call Read when reader is closed.'

After searching for the answer online, i found nothing, maybe someone can see what is wrong?

The error happens at: while (rdr.Read())

Thank you!

internal List<string> GetInflationByYearData(string Country, int StartYear, 
int EndYear)
        {
            string CS = 
ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("SpCountryData", con)
                {
                    CommandType = System.Data.CommandType.StoredProcedure
                };
                cmd.Parameters.AddWithValue("@act", 
"getInflationByYearData");
                cmd.Parameters.AddWithValue("@Country", Country);
                cmd.Parameters.AddWithValue("@startYear", StartYear);
                cmd.Parameters.AddWithValue("@endYear", EndYear);
                var table = new DataTable();
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    {
                        table.Load(rdr);
                    };
                }
                //con.Close();
                List<string> labelList = new List<string>();
                List<string> valueList = new List<string>();
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        string label = row["Year"].ToString();
                        string value = row["Percentage"].ToString();
                        labelList.Add(label);
                        valueList.Add(value);
                    }
                }
                List<string> list = new List<string>();
                StringBuilder sbLabels = new StringBuilder();
                foreach (string lbl in labelList)
                {
                    sbLabels.Append(lbl + ",");
                }
                StringBuilder sbValues = new StringBuilder();
                foreach (string val in valueList)
                {
                    sbValues.Append(val + ",");
                }
                list.Add(sbLabels.ToString().Substring(0, sbLabels.Length - 
1));
                list.Add(sbValues.ToString().Substring(0, sbValues.Length - 
1));
                return list;
            }
        }

Answer 1

Load consumes the reader to completion; you should either have a while (rdr.Read()) loop, or call table.Load(rdr), but: not both. Load basically does that same loop internally.

So: remove the loop - just use Load.

However! If all you want is the data as List<string>, loading it into a DataTable seems like an unnecessary step (DataTable is not lightweight) - it seems like you could do that in the reader, or perhaps use other tools (like "dapper") to remove that.

It isn't clear what data types Year and Percentage are, but as a "dapper" example...

class MyRowThing {
    public int Year {get;set;}
    public decimal Percentage {get;set;}
}

var rawData = con.Query<MyRowThing>("SpCountryData",
    new { // params here
        act = "getInflationByYearData", Country,
       startYear = StartYear, endYear = EndYear
    }, commandType: CommandType.StoredProcedure).AsList();

// now loop over rawData, which is a List<MyRowThing> with the data

Answer 2

Replacing:

  var table = new DataTable();
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    {
                        table.Load(rdr);
                    };
                }
                //con.Close();
                List<string> labelList = new List<string>();
                List<string> valueList = new List<string>();
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        string label = row["Year"].ToString();
                        string value = row["Percentage"].ToString();
                        labelList.Add(label);
                        valueList.Add(value);
                    }
                }

With:

    con.Open();

    List<string> labelList = new List<string>();
    List<string> valueList = new List<string>();
    SqlDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
            labelList.Add(rdr["Year"].ToString());
            valueList.Add(rdr["Percentage"].ToString());
    }

Will get rid of the DataTable for a more optimized code.