CODEWITHSUNDEEP
c#sqlsql-server
Sebastjan
Sebastjan

Reputation: 1147

Capturing count from an SQL query

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?

Upvotes: 68

Views: 196734

Answers (5)

m.edmondson
m.edmondson

Reputation: 30862

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

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

Upvotes: 131

Emmanuel Manzanilla González
Emmanuel Manzanilla González

Reputation: 31

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

Upvotes: 3

Andrew Kralovec
Andrew Kralovec

Reputation: 501

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; 
}

Upvotes: 11

ali sriti
ali sriti

Reputation: 47

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();
}

Upvotes: 0

Rami Alshareef
Rami Alshareef

Reputation: 7140

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

Upvotes: 27

Related Questions