Capturing count from an SQL query

C#SqlSql Server

C# Problem Overview

What is the simplest way in C# (.cs file) to get the count from the SQL command

SELECT COUNT(*) FROM table_name

into an int variable?

C# Solutions

Solution 1 - C#

Use SqlCommand.ExecuteScalar() and cast it to an int:

cmd.CommandText = "SELECT COUNT(*) FROM table_name";
Int32 count = (Int32) cmd.ExecuteScalar();

Solution 2 - C#

SqlConnection conn = new SqlConnection("ConnectionString");
conn.Open();
SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM table_name", conn);
Int32 count = (Int32) comm .ExecuteScalar();

Solution 3 - C#

You'll get converting errors with:

cmd.CommandText = "SELECT COUNT(*) FROM table_name";
Int32 count = (Int32) cmd.ExecuteScalar();

Use instead:

string stm = "SELECT COUNT(*) FROM table_name WHERE id="+id+";";
MySqlCommand cmd = new MySqlCommand(stm, conn);
Int32 count = Convert.ToInt32(cmd.ExecuteScalar());
if(count > 0){
	found = true; 
} else {
	found = false; 
}

Solution 4 - C#

Complementing in C# with SQL:

SqlConnection conn = new SqlConnection("ConnectionString");
conn.Open();
SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM table_name", conn);
Int32 count = Convert.ToInt32(comm.ExecuteScalar());
if (count > 0)
{
	lblCount.Text = Convert.ToString(count.ToString()); //For example a Label
}
else
{
	lblCount.Text = "0";
}
conn.Close(); //Remember close the connection

Solution 5 - C#

int count = 0;    
using (new SqlConnection connection = new SqlConnection("connectionString"))
{
    sqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM table_name", connection);
    connection.Open();
    count = (int32)cmd.ExecuteScalar();
}
Categories
Recommended C# Solutions
Recommended Sql Solutions
Recommended Sql Server Solutions

Previous Solution:

What is the best way to unit-test SLF4J log messages?

Unit TestingMockingMockitoSlf4j

Next Solution:

How to cryptographically hash a JSON object?

JsonCryptographyCanonicalization

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSebastjanView Question on Stackoverflow
Solution 1 - C#m.edmondsonView Answer on Stackoverflow
Solution 2 - C#Rami AlshareefView Answer on Stackoverflow
Solution 3 - C#Andrew KralovecView Answer on Stackoverflow
Solution 4 - C#Emmanuel Manzanilla GonzálezView Answer on Stackoverflow
Solution 5 - C#ali sritiView Answer on Stackoverflow