C # command 'select count' sql incorrectly returns null rows from sql server

Question

I am trying to return the number of rows from a SQL Server table. Several sources in "net" show below as a workable method, but it continues to return "0 lines". When I use this query in the management studio, it works fine and returns a string correctly. I tried this simply with a simple table name, as well as with the full qualifications that management studios like.

using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = sqlConnectionString; cn.Open(); SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM [LBSExplorer].[dbo].[myTable]", cn); countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar()); Console.WriteLine("Starting row count: " + countStart.ToString()); } 

Any suggestions on what could be causing this?

Answer 1

Set CommandType text to text

 command.CommandType = CommandType.Text 

More details from Damien_The_Unbeliever , regarding whether, by default, .NET by default SqlCommandTypes prints text.

If you separate the getter for CommandType into SqlCommand, you will find that there is a strange special shell in which, if the value is currently 0, it lies and says that Text / 1 is used instead (similarly, from the / design component, the default value is indicated as 1). But the actual internal value remains equal to 0.

Answer 2

Here's how I write it:

 using (SqlConnection cn = new SqlConnection(sqlConnectionString)) { cn.Open(); using (SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM [LBSExplorer].[dbo].[myTable]", cn)) { commandRowCount.CommandType = CommandType.Text; var countStart = (Int32)commandRowCount.ExecuteScalar(); Console.WriteLine("Starting row count: " + countStart.ToString()); } } 

Answer 3

You can use this best query:

 SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 AND OBJECT_NAME(OBJECT_ID)=N'YOUR_TABLE_NAME'