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

Marc Gravell · Accepted Answer · 2019-01-08 11:01:20Z

5

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

answered Jan 8, 2019 at 11:01

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cristi Priciu Over a year ago

Thank you, it works after I removed the loop. I had no ideea about the "Load" thing. This function should return a list of 2 strings that become dataset and labels for a Chart.js chart. This is the first time i hear of Dapper. It seems shorter and simpler. Thank you again, Marc! :)

Marc Gravell Over a year ago

@CristiPriciu "dapper" is the tool that we use (and wrote) here at Stack Overflow - it is designed to be very easy and obvious to use (unlike raw ADO.NET), and be very fast and efficient at execution :) - available on NuGet: nuget.org/packages/Dapper

Outman · Accepted Answer · 2019-01-08 11:26:28Z

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.

Collectives™ on Stack Overflow

SqlDataReader closed for unknown reason

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related