Datareader has rows when loading it to datatable I am getting row count = 0

Question

try
        {
            String empid1 = Request.QueryString["MyText"];

            int empid = int.Parse(empid1);
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["EmployeeDatabase"].ConnectionString;
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            SqlCommand cmd = new SqlCommand("ReportingManagers", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@EmpID", SqlDbType.Int, 0).Value = empid;

            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            LinkButton3.Text = string.Empty;
            int i = 1;
             while(dr.Read())
            {


                TreeNode parentNode = new TreeNode("L" + i++ + "Manager : " + dr["Emp_Name"].ToString());
                parentNode.Value = dr["Emp_ID"].ToString();
                TreeView1.Nodes.Add(parentNode);
                parentNode.ChildNodes.Add(new TreeNode("Short ID : " + dr["Short_ID"].ToString()));
                parentNode.ChildNodes.Add(new TreeNode("EmpID : " + dr["Emp_ID"].ToString()));
              //  LinkButton3.Text = "Reporting Managers";  
            }

             DataTable dt = new DataTable();
             dt.Load(dr);
             if (dt.Rows.Count > 1)
             {
                 LinkButton3.Text = "Reporting Managers";  
             }

            dr.Close();
            con.Close();
        }

Please explain me why my if loop is not getting executed in the above code. I can see that in datatable dt I got row count '0' but my dr is showing true for has rows..I dont wnat to use sql adapter in this scenerio.

How can i capture row count of data table in this scenario?

score 1 · Accepted Answer · answered Jan 31 '13 at 13:45

You can't load a datatable with a datareader that has already been used to the end of its data.
Try to change the order of your code execution in this way

SqlDataReader dr = cmd.ExecuteReader();
LinkButton3.Text = string.Empty;
int i = 1;
DataTable dt = new DataTable();
dt.Load(dr);
if (dt.Rows.Count > 1)
{
    LinkButton3.Text = "Reporting Managers";  
    foreach(DataRow r in dt.Rows)
    {
        TreeNode parentNode = new TreeNode("L" + i++ + "Manager : " + r["Emp_Name"].ToString());
        parentNode.Value = r["Emp_ID"].ToString();
        TreeView1.Nodes.Add(parentNode);
        parentNode.ChildNodes.Add(new TreeNode("Short ID : " + r["Short_ID"].ToString()));
        parentNode.ChildNodes.Add(new TreeNode("EmpID : " + r["Emp_ID"].ToString()));
    }
}

Thank you...I got the logic. – user1912987 Jan 31 '13 at 14:56 — user1912987, Jan 31 '13 at 14:56

score 0 · Answer 2 · answered Jan 31 '13 at 13:40

0

Because you've read through your reader already when loading it into the TreeNode structure (the while loop that reads the entire reader). DataReaders are forward only so you can't just 'reset' the reader either.

answered Jan 31 '13 at 13:40

Eli Gassert

9,745
3
30
39

Is it something like i cannot load the reader in datatable once after reading it in while loop?? – user1912987 Jan 31 '13 at 13:43
Yes that's basically what I'm saying. Once read, there's no going back. – Eli Gassert Jan 31 '13 at 14:50

Datareader has rows when loading it to datatable I am getting row count = 0

2 Answers2