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, Apr 09 '11 at 11:16
and what about the record 1 which is not displayed? you know any solution for it? — sqlchild, Apr 09 '11 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, Apr 09 '11 at 11:56

score 5 · Accepted Answer · answered Apr 09 '11 at 11:19

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 09 '11 at 11:19

Marc Gravell

1,026,079
266
2,566
2,900

SIR, here also the reader closes automatically, so there's no need to close it explicitly? – sqlchild Apr 09 '11 at 12:39
1

If you are *disposing* it (via the `using` block), then no. But you ***are*** responsible for making sure it gets disposed. – Marc Gravell Apr 09 '11 at 12:53
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? – sqlchild Apr 09 '11 at 12:56
@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. – Marc Gravell Apr 09 '11 at 13:33

score 4 · Answer 2 · edited Mar 20 '14 at 13:27

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

score 1 · Answer 3 · answered Apr 09 '11 at 12:30

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

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

ItemId | ItemName

3 Answers3