execute a select count sql in c#

Maria

I am trying to execute a simple sql select statement using C# and I want to return the number of rows. What is the correct syntax.

SELECT Count(*) FROM InvData

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari4y ago
You can use below code.
SqlConnection conn = new SqlConnection("ConnectionString");
conn.Open();
SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM InvData", conn);
Int32 count = (Int32) comm .ExecuteScalar();
geetha velusamy4y ago
The SQL COUNT() is a function that returns the number of records of the table in the output.

SQL QUERY:

SELECT COUNT(*) FROM emp

Here I attached my screenshot:
Jignesh Kumar4y ago
Hi Maria,

Please refer this link which will help you out,
https://www.aspsnippets.com/questions/616597/Count-number-of-rows-in-a-table-using-C-and-VBNet-in-ASPNet/
Vinitha T4y ago
public static int Count(string connectionString)
{
string query = "Select Count(*) From InvData";
int count="";
using (SqlConnection con = new SqlConnection(Your data source ConnectionString))
{?SqlCommand cmd = new SqlCommand(query, con);?cmd.CommandType = CommandType.Text;?con.Open();?count = cmd.ExecuteScalar();?}
return count;?}

Here, Using statement is used so no need to close the connection, it will close automatically.
ExecuteScalar is used to retrieve single value from database, so for counting the data ExecuteScalar can be used.
Ananth G9y ago
1. using System.Data.Odbc
2. public static string ReadData(string connectionString)
3. {
4. string objQuery= "SELECT count(*) from INVData";
5. string output="";
6. using (OdbcConnection objConn= new OdbcConnection(connectionString))
7. {
8. OdbcCommand objCmd= new OdbcCommand(objQuery, objConn);
10. objConn.Open();
11. OdbcDataReader objReader= objCmd.ExecuteReader();
12. while (objReader.Read())
13. {
14. output=objReader[0].ToString();
16. objReader.Close();
17. }
18. objConn.Close();
19. }
20. return output;
21. }

u can use like this

Ifour Hiren9y ago

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

 above code helps you to make it working.

Ryan Alford18y ago

this is more of an edit to Jeff's post. Not really necessary to use a DataReader object if just returning one column.

public static void ReadData(string connectionString)
{
    string queryString = "SELECT count(*) from INV";

    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        OdbcCommand command = new OdbcCommand(queryString, connection);

        connection.Open();

        int count = command.ExecuteScalar();

        Console.WriteLine("Count is: {0}", count);
    }
}

"ExecuteScalar()" returns the first column of the first record. Since we are only return one column of one record, it is the perfect solution.

Also, since you didn't state with database you were using, we chose to use the ODBC connection. If you are using SQL Server, you can use "SqlConnection" and "SqlCommand" instead.

Ron18y ago

Try this. private void button2_Click(object sender, EventArgs e) { SqlConnection myConnectionAdd = null; DataSet myDataSetAdd = new DataSet(); myConnectionAdd = new SqlConnection("Data Source=YourSource Catalog=YourCatalog;Integrated Security=True"); string mySqlInsert = "SELECT count(*) FROM dbo.Customers"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = new SqlCommand(mySqlInsert, myConnectionAdd); mySqlDataAdapter.Fill(myDataSetAdd, "count(*)"); dataGridView1.DataSource = myDataSetAdd.Tables["count(*)"]; myConnectionAdd.Close(); }

Jeff Johnson18y ago

Try the snippet below, you may need to research some of the other classes to get your connectionString (DSN= etc), but this worked for me.

using System.Data.Odbc

public static void ReadData(string connectionString)
{
    string queryString = "SELECT count(*) from INV";

    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        OdbcCommand command = new OdbcCommand(queryString, connection);

        connection.Open();

        // Execute the DataReader and access the data.
        OdbcDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine("CustomerID={0", reader[0]);
        

        // Call Close when done reading.
        reader.Close();