DatagridView in Winforms , Is SqlDataReader automatically closed on end of loop?

Question

int a ;

SqlCommand cmd = new SqlCommand (" Select * From items order by ItemID ", conn );

SqlDataReader reader = cmd.ExecuteReader();

while(reader.Read())
        {
            a = reader.GetInt32(0);

            if (reader.HasRows == false)
            {
                dataGridView1.Visible = false;
            }

            else
            {
                dataGridView1.Visible = true;

                DataTable dt = null;

                dt = new DataTable();

                dt.Load(reader);

                dataGridView1.DataSource = dt;

                if (reader.IsClosed == true)
                {
                        break;
                }   
            }

I want to ask that is the reader closed automatically, because here am not using reader.Close() and still it is closed? also , in my items table i have the first record as

ItemId | ItemName

 1       Bag
 2       Laptop
 8       Weighing Machine 

But, when this data is displayed in the datagridview then the row 1 , that is, the itemname "BAG" is not displayed. why so?

Solution 1

By calling Read(), you have already "claimed" the first row (hence why Bag isn't showing - because you aren't doing anything with it); and yet dt.Load is also going to do a while(reader.Read()). I expect you want (note I'm not calling Read here, and have no while loop):

if(reader.HasRows)
{
   // load via dt.Load() and show
}
else
{
     // hide
}

The reason it is exiting is that once you've called Load you've already read all the data, so there is nothing else to read. I honestly don't know whether getting to the end of the TDS stream implicitly closes the reader, but you should be using:

using(var cmd = new SqlCommand (" Select * From items order by ItemID ", conn))
using(var reader = cmd.ExecuteReader()) 
{
     // everything else in here
} 

Solution 2

You are loading the reader into the DataTable so the while(reader.Read()) loop is not required.

The first record is not displaying because reader.Read() has taken the first record and dt.Load() is starting from the second record.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = connection.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM teams";
        connection.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                dataGridView.Visible = true;
                DataTable dt = new DataTable();
                dt.Load(reader);
                dataGridView.DataSource = dt;
            }
            else
            {
                dataGridView.Visible = false;
            }
        }
    }
}

Solution 3

Even this too works fine for me

string strcon = ConfigurationSettings.AppSettings["Constring"].ToString();
MySqlConnection conn = new MySqlConnection(strcon);
MySqlCommand cmd = new MySqlCommand("Select * From items order by ItemID", conn);
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
   dataGridView1.Visible = true;
   DataTable dt = null;
   dt = new DataTable();
   dt.Load(reader);
   dataGridView1.DataSource = dt;
}