CODEWITHSUNDEEP
c#asp.netdatatablesqldatareader
user1912987
user1912987

Reputation: 115

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

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?

Upvotes: 0

Views: 4217

Answers (2)

Steve
Steve

Reputation: 216253

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

Upvotes: 1

Eli Gassert
Eli Gassert

Reputation: 9763

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.

Upvotes: 0

Related Questions