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?
Add a comment
|
5 Answers
Use SqlCommand.ExecuteScalar() and cast it to an int:
cmd.CommandText = "SELECT COUNT(*) FROM table_name";
Int32 count = (Int32) cmd.ExecuteScalar();
8 Comments
SeaDrive
In this case, there will always be a return value of type int. In the more general case, you might get a return value of DBNull, e.g. "select ssn from table1 where company_id = '112233'". Since you can't cast the DBNull to your return datatype, you have test and change it, either in the SQL or in your app.
m.edmondson
@SearchForKnowledge Check the value being returned is a value that can be converted into an Int32. There's a good chance you're trying to convert a word.
Willz
@SearchForKnowledge Try using Convert.ToInt32(cmd.ExecuteScalar()) instead of the cast.
EKanadily
use long instead of int as a return type
|
SqlConnection conn = new SqlConnection("ConnectionString");
conn.Open();
SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM table_name", conn);
Int32 count = (Int32) comm .ExecuteScalar();
2 Comments
m.edmondson
Is this not identical to my answer?
erikric
Just for clarity, a connection string is usually a string with information about which database you are connection to. An example: "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;" This will log you on to the database with the same credentials you are running on your computer.
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;
}
1 Comment
Bishoy Frank
I have a question ... what if I want to where on a string using a textbox e.g :- string stm = "SELECT COUNT(*) FROM table_name WHERE name="+Name.Text .Trim()+";" ;
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
Comments
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();
}
