?
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
?
238 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
  • 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
Technology Partners: 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

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!

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses
Course of the Month9 days, 12 hours left to enroll

609 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