Row Count using a SqlDataReader

Question

I am trying to retrieve the row count when data is returned from my datareader. Please take a look at the code.
Thanks

 

try

{

Conn.Open();

SqlDataReader dr = this.sqlCommand1.ExecuteReader();

if (dr.HasRows)

{

while (dr.Read())

{

dgResults.DataSource = dr;

dgResults.DataBind();

dgResults.Visible = true;

}

lblMsg.Text = "Need to display row count here"

dr.Close();

}

else

{

lblMsg.Text = "No notes found. - You must use 2 or more search options.";

dgResults.Visible = false;

conn.Close();

}

}

catch (SqlException ex)

{

// Display any errors...

lblMsg.Text = "ERROR: " + ex.Message;

dgResults.Visible = false;

conn.Close ();

}

finally

{

// Close connection...

conn.Close ();

}

}

Answer 1

The SQLDataReader doesn't have a row count property the easiest way to get the count would be to do this...

int rowCount = 0;

if (dr.HasRows)

{

while (dr.Read())

{               rowCount++;

dgResults.DataSource = dr;

dgResults.DataBind();

dgResults.Visible = true;

}

lblMsg.Text = rowCount.ToString();

dr.Close(); Regards, Jeff

Answer 2

 

SqlDataReader dr = this.sqlCommand1.ExecuteReader();

int count = 0; // <-------- here

if (dr.HasRows)

{

while (dr.Read())

{

count++; // <------------- here

dgResults.DataSource = dr;

dgResults.DataBind();

dgResults.Visible = true;

}

lblMsg.Text = string.Format("Number of rows: {0}", count); // <---- and here.

---

David Morton - http://blog.davemorton.net/

Answer 3

Change

1	if (dr.HasRows) {
2	while (dr.Read()) {
3	dgResults.DataSource = dr;
4	dgResults.DataBind();
5	dgResults.Visible = true;
6	}
7
8	lblMsg.Text = "Need to display row count here"
9	dr.Close();
10	}

to

1	if (dr.HasRows) {
2	int count = 0;
3	while (dr.Read()) {
4	dgResults.DataSource = dr;
5	dgResults.DataBind();
6	dgResults.Visible = true;
7	count++;
8	}
9
10	lblMsg.Text = count.ToString();
11	dr.Close();
12	}

---

Ron Whittle

Answer 4

 I thought about it but I'm not sure on how to do that.

Thanks

Answer 5

lblMsg.Text = "Number of Rows: " + dgResults.RowCount.ToString();

---

David Morton - http://blog.davemorton.net/

Answer 6

Forgot to mention I am still using C# 2003 :-(

Answer 7

Or
dgResults.Rows.Count

It gives number of rows in your DataGridViewRowCollection

Answer 8

chyter said:

Forgot to mention I am still using C# 2003 :-(

Every example given to you will work in .NET 2.0 and above. I assume you're using .NET 2.0, because you've got (apparently) a DataGridView, which was introduced in 2.0.

---

David Morton - http://blog.davemorton.net/

Answer 9

Thanks everyone, finally got it working and please don't laugh were suppose to upgrade to VS 2005 last year....not sure what happened....


Thanks again.

Answer 10

You don't need while loop to fill the grid from SqlDataReader. Just check if reader has rows and set the datasource, so remove while loop. Also there is no property of DataGrid to get data rows directly. As i remember there is to convert it's datasource to DataView and get the view's Rows.Count value.

But why do you use SqlDataReader to fill a grid. It's much obvious to use SqlDataAdapter for that job.

---

MCDBA, MCSD, MCITP http://sharpsource.blogspot.com/

Answer 11

boban.s said:

But why do you use SqlDataReader to fill a grid. It's much obvious to use SqlDataAdapter for that job.

I agree wholeheartedly.

---

David Morton - http://blog.davemorton.net/

Answer 12

 Well Boban & David

I used the SqlDataAdapter first but the results were coming back about five seconds slower than the SqlDataReader that is why I am trying it. But if the SqlDataAdapter is the better way I will go back to that.


Thanks

Answer 13

I worked over the weekend and I changed some things around, would this be better?
Thanks

private void SetParameters()

{

// Add and create data objects

SqlConnection conn = sqlConnection1;

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter("CHTest", conn);

sqlCommand1.CommandType = CommandType.StoredProcedure;

SqlParameter param = da.SelectCommand.Parameters.Add("@FileNumber", SqlDbType.VarChar, 10);

try

{

conn.Open();

da.Fill(ds);

lblMsg.Text = ds.Tables[0].Rows.Count.ToString() + " Rows Found.";

dgResults.DataSource = ds;

dgResults.DataBind();

}

catch (SqlException ex)

{

// Display any errors...

lblMsg.Text = "ERROR: " + ex.Message;

dgResults.Visible = false;

conn.Close ();

da.Dispose();

}

finally

{

// Close connection...

conn.Close();

}

}

private void btnSearch_Click(object sender, System.EventArgs e)

{

SetParameters();

}

}

}

Answer 14

This will be better:

private void SetParameters(string fileNumber)

{

    // Add and create data objects  

    SqlConnection conn = new SqlConnection("your connection string");

 

    SqlCommand cmd = new SqlCommand("CHTest", conn);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@FileNumber", SqlDbType.VarChar, 10).Value = fileNumber;

 

    SqlDataAdapter da = new SqlDataAdapter(cmd);

 

    DataTable results = new DataTable("SearchResults");

    results.Locale = CultureInfo.CurrentCulture;

 

    try

    {

        conn.Open();

        da.Fill(results);

        lblMsg.Text = results.Rows.Count + " Rows Found.";

        dgResults.DataSource = results;

        dgResults.DataBind();

    }

    catch (SqlException ex)

    {

        // Display any errors...  

        lblMsg.Text = "ERROR: " + ex.Message;

        dgResults.Visible = false;

        da.Dispose();

    }

    finally

    {

        // Close connection...  

        conn.Close();

    }

}

 

private void btnSearch_Click(object sender, System.EventArgs e)

{

    SetParameters(textSearchNumber.Text);

} 

---

MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/

Answer 15

I know this is OLD but I thought I would add a very simple way to get the row count

$counter = @($dr.Count) <------this will add every row that it finds to the array $counter, you will see each value as a "1"

$counter.Count <-----------this will provide the total values stored in the array, which will be the number of rows in your table per the filtering, or lack of filtering, you selected.

Answer 16

The SQLDataReader doesn't have a row count property the easiest way to get the count would be to do this...

int rowCount = 0;

if (dr.HasRows)

{

while (dr.Read())

{               rowCount++;

dgResults.DataSource = dr;

dgResults.DataBind();

dgResults.Visible = true;

}

lblMsg.Text = rowCount.ToString();

dr.Close(); Regards, Jeff

this is bad.... unless I'm missing something, your re-binding the grid in every iteration?