?
Solved

What is the best way to determine the row count of a SQL Server database table so that I can display it on a web page

Posted on 2005-03-28
12
Medium Priority
?
236 Views
Last Modified: 2010-04-07
I am building a 'control panel' for our system which includes a complex database structure.  I am going to have a diagram of the system and I want to have the number of rows in each table listed on the diagram on the web page.

What is the best way to determine the number of rows in a SQL Server table so that I can display it on a web page?
0
Comment
Question by:Todd_Anderson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 4

Expert Comment

by:imperial_p79
ID: 13645511
if you query is something like select count(*) as totalcount from tablename1;

then in the code behind

ds.fill(dataadapter)
ds.Tables(0).Rows(0)("totalcount") will give you the table count.

Hope this helps!
0
 
LVL 8

Expert Comment

by:thrill_house
ID: 13645537
This will return the number of rows...  It is a SQL query that you can do.
Select count(*)
From TableName

You can also return the results of a query, and put the data into a data table.  Then you can just do this:
myDataTable.Rows.Count
0
 
LVL 13

Accepted Solution

by:
davidlars99 earned 500 total points
ID: 13646489
or the fastest way (I think...)  :)

Dim cn As OleDbConnection = New OleDbConnection("database connection")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Count(*) FROM table", cn)

cn.Open()
Dim status As Integer = cmd.ExecuteScalar()
cn.Close()


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Todd_Anderson
ID: 13646542
So, I guess you have to have a connection, a data adapter and so on to do it this way.  Some of them are very large.  Seems like it would take a lot of time and memory to load all of these table into memory just to count the number of rows.

I was hoping that there would be an easier way.  I have about a dozen tables in my database that I need to do this for.  Could a stored procedure or a user function in SQL Server be called from ASP.NET that could return the counts for all of the tables in one shot (say in a list)?  I'm pretty new to all this so I am just brainstorming.
0
 
LVL 8

Expert Comment

by:thrill_house
ID: 13646555
You aren't loading any data into memory if you do select count(*) from YourTableName as a query

You can basically just do davidlars99's code, and that will do it.  
0
 
LVL 13

Expert Comment

by:davidlars99
ID: 13646688
what I showed you is a minimum and easiest you can do, I don't think there's any other solution simpler, easier and faster then that
0
 

Author Comment

by:Todd_Anderson
ID: 13646714
davidlars99,

Your comment came in while I was typing mine.

It looks great.  I have 2 databases with 6 tables in each that I need to get the row count for.  I'll just use your code 12 time right?

Todd
0
 

Author Comment

by:Todd_Anderson
ID: 13646729
Just 2 connection, 12 commands and 12 open...close sections, right?
0
 

Author Comment

by:Todd_Anderson
ID: 13646849
Just tried it and it works great.  Thanks for the help!
0
 
LVL 13

Expert Comment

by:davidlars99
ID: 13646853
yes and no... :)

YES=you can use it sure, but will be lot better if you put it in the shared function like so

Public Shared Function GetCount(ByVal table As String, ByVal strConn As String)
      Dim cn As OleDbConnection = New OleDbConnection(strCon)
      Dim cmd As OleDbCommand = New OleDbCommand("SELECT Count(*) FROM " & table, cn)

      cn.Open()
      Dim status As Integer = cmd.ExecuteScalar()
      cn.Close()

      Return status
End Function


NO=you can do it better by creating stored procedure which returns multiple comma separated values for different tables
0
 

Author Comment

by:Todd_Anderson
ID: 13646900
davidlars99,

Even better!

Todd
0
 
LVL 13

Expert Comment

by:davidlars99
ID: 13646911
glad I can help...  :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question