Sign in to follow this  
allandippy

How do i know the number of rows of a table w/o returning the data (SQL)?

Recommended Posts

I have some code below that would retrieve data from a database table. I'm just wondering how could I get just the total number of rows of a table w/o getting the row data. I'm using C#. public void FillDataSet(ref DataSet ds, ref SqlDataAdapter da, SqlConnection conn, String sel, String tbl) { SqlCommand comm = conn.CreateCommand(); SqlCommandBuilder commbldr = new SqlCommandBuilder(da); comm.CommandText = sel; da.SelectCommand = comm; conn.Open(); da.Fill(ds, tbl); conn.Close(); } ... I will use it like: FillDataSet(ref ds, ref da, conn, "SELECT * FROM Items", "Items") Could anyone help...

Share this post


Link to post
Share on other sites
Probably, the table could contain >1000 rows. I know that I can use "SELECT COUNT(*) FROM Items". What I would like know is how to store the number of rows in a variable after opening the connection...

Share this post


Link to post
Share on other sites
I think count is the only way? If you are getting the data at somepoint, like your sample shows, then use what da.Fill(...) returns, it will be the # of rows returned from that database( well altered in the dataset, but if the set is new then it should return them all . . i think :) )

Share this post


Link to post
Share on other sites
I was looking for a solution since yesterday and I think I just found it. Thanks for the replies anyway...

// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Items", conn);

conn.Open();
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
conn.Close();
MessageBox.Show(count.ToString());


.. found it at http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

Share this post


Link to post
Share on other sites
Indeed, that's what everyone said :-).

Depending on your SQL implementation, you might be better off with count(1) rather than count(*), but in most cases, that gets optimized away anyway.

Also, depending on what you're using, the analysis tools will give you a good estimate of the number of rows when they are run. But if we're talking on the order of 1000, you have nothing to worry about with using COUNT.

Share this post


Link to post
Share on other sites

Hi,

With a little more work and providing your database supports triggers, you could denormalize a tad and add a column that holds the number of rows in a table. You could then setup a trigger that executes on a DML statement (provided it's insert or delete).

Regards,

GCS584

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this