Jump to content
  • Advertisement
Sign in to follow this  
allandippy

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

This topic is 4412 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
How big do you expect the table to get? You can probably get away with "SELECT count(1) FROM items" unless it gets pretty massive.

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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!