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

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!

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

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.

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.  

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

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

Just 2 connection, 12 commands and 12 open...close sections, right?

Just tried it and it works great.  Thanks for the help!

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

davidlars99,

Even better!

Todd

glad I can help...  :)