CODEWITHSUNDEEP
c#asp.netsqldatabase
Eyalse
Eyalse

Reputation: 997

How to count the number of rows from sql table in c#?

How to count the number of rows from sql table in c#? I need to extract some data from my database...

Upvotes: 10

Views: 104659

Answers (7)

Joe Carter
Joe Carter

Reputation: 69

I used this method in my own application to count the number of active users within the program. This can be easily manipulated for your own use.

        con.open();
        string ActiveUsers = "SELECT * FROM Devices WHERE Status='" + "Online" + "'";
        SqlCommand cmd = new SqlCommand(ActiveUsers, con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        con.Close();

        Users.Text = ds.Tables[0].Rows.Count.ToString();

Upvotes: 0

Sajas Mohammed
Sajas Mohammed

Reputation: 341

Use this its working

        string strQuery = "SELECT * FROM staff WHERE usertype='lacturer'";

        connect.Open();

        SqlCommand cmd = new SqlCommand(strQuery, connect);
        SqlDataAdapter OleDbDa = new SqlDataAdapter(cmd);
        DataSet dsData = new DataSet();
        OleDbDa.Fill(dsData);

        connect.Close();
        
        std.Text = dsData.Tables[0].Rows.Count.ToString();

Upvotes: 0

FelixAVeras
FelixAVeras

Reputation: 1384

This works for me

using (var context = new BloggingContext())
{
   var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
}

For more information consult: https://learn.microsoft.com/es-es/ef/ef6/querying/raw-sql?redirectedfrom=MSDN

Upvotes: 1

David Fawzy
David Fawzy

Reputation: 1076

You can make global function that you can use all the time as

    public static int GetTableCount(string tablename, string connStr = null)
    {
        string stmt = string.Format("SELECT COUNT(*) FROM {0}", tablename);
        if (String.IsNullOrEmpty(connStr))
            connStr = ConnectionString;
        int count = 0;
        try
        {

            using (SqlConnection thisConnection = new SqlConnection(connStr))
            {
                using (SqlCommand cmdCount = new SqlCommand(stmt, thisConnection))
                {
                    thisConnection.Open();
                    count = (int)cmdCount.ExecuteScalar();
                }
            }
            return count;
        }
        catch (Exception ex)
        {
            VDBLogger.LogError(ex);
            return 0;
        }
    }

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

You may try like this:

select count(*) from tablename where columname = 'values'

C# code will be something like this:-

public int A()
{
   string stmt = "SELECT COUNT(*) FROM dbo.tablename";
   int count = 0;

   using(SqlConnection thisConnection = new SqlConnection("Data Source=DATASOURCE"))
   {
       using(SqlCommand cmdCount = new SqlCommand(stmt, thisConnection))
       {
           thisConnection.Open();
           count = (int)cmdCount.ExecuteScalar();
       }
   }
   return count;
}

Upvotes: 42

zey
zey

Reputation: 6103

Do you means likes this ? 

SELECT COUNT(*) 
FROM yourTable 
WHERE ....

Upvotes: 3

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

You need to make a database connection from c# first. Then, you need to pass below query as commandText.

Select count(*) from TableName

Use ExecuteScalar/ExecuteReader to get the returned count.

Upvotes: 4

Related Questions