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?

yes sir, i debugged it and found that it automatically closes ? but i read on msdn that it should be explicitly closed. — sqlchild
– sqlchild, Commented Apr 9, 2011 at 11:16
and what about the record 1 which is not displayed? you know any solution for it? — sqlchild
– sqlchild, Commented Apr 9, 2011 at 11:17
dataGridView1.DataSource = dt; ---after this line , i checked by using a messagebox and displaying the reader.IsClosed property , it gives out True — sqlchild
– sqlchild, Commented Apr 9, 2011 at 11:56

Marc Gravell · Accepted Answer · 2011-04-09 11:19:06Z

5

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
}

answered Apr 9, 2011 at 11:19

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

4 Comments

sqlchild Over a year ago

SIR, here also the reader closes automatically, so there's no need to close it explicitly?

Marc Gravell Over a year ago

If you are disposing it (via the using block), then no. But you are responsible for making sure it gets disposed.

sqlchild Over a year ago

sir, when i wasn't using "using" ,that is, i used your code which you specified in the starting of your answer , then also it was automatically closed?

Marc Gravell Over a year ago

@sqlchild - the two things are part of the same answer. The only way I can really answer that is to say again: if you dispose it, you do not also have to close it. You should always dispose it, so do that.

Frank Rem · Accepted Answer · 2014-03-20 13:27:39Z

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

Developer · Accepted Answer · 2011-04-09 12:30:29Z

1

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

answered Apr 9, 2011 at 12:30

Developer

8,69743 gold badges137 silver badges254 bronze badges

Collectives™ on Stack Overflow

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

ItemId | ItemName

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

ItemId | ItemName

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related