none
Getting number of rows in a table RRS feed

  • Question

  • Hi,

    Can someone tell me how I get all the rows in a SQL table and return the number of rows as an integer?

    Thanks.
    Friday, May 22, 2009 12:24 PM

Answers

  • Jimmy,
    there are several ways to do it.
    The SQL query would be:

    select count(*) from mytable;

    You can implement it like this (assuming no parameters):

    private int CountRows () {
      using (SqlConnection connection = new SqlConnection (myConnectionString)) {
        connection.Open ();
        SqlCommand query = new SqlCommand ("select count(*) from mytable", connection);
        return (int) query.ExecuteScalar ();
      }
    }


    HTH
    --mc

    Friday, May 22, 2009 12:32 PM

  • String connectionString = "connectionStringgoeshere";

    using(SqlConnection conn = new SqlConnection(connectionString) {
       SqlCommand cmd = new SqlCommand("Select Count(*) from tablename", conn);
       int rowCount = (int)cmd.ExecuteScalar();
    }

    Sorry. Forgot refresh my browser. :P


    Thanks,
    A.m.a.L
    .Net Goodies
    Remember to click "mark as answered" when you get a correct reply to your question
    Friday, May 22, 2009 12:33 PM

All replies

  • Jimmy,
    there are several ways to do it.
    The SQL query would be:

    select count(*) from mytable;

    You can implement it like this (assuming no parameters):

    private int CountRows () {
      using (SqlConnection connection = new SqlConnection (myConnectionString)) {
        connection.Open ();
        SqlCommand query = new SqlCommand ("select count(*) from mytable", connection);
        return (int) query.ExecuteScalar ();
      }
    }


    HTH
    --mc

    Friday, May 22, 2009 12:32 PM
  • That's great, thanks Mario.
    Friday, May 22, 2009 12:33 PM

  • String connectionString = "connectionStringgoeshere";

    using(SqlConnection conn = new SqlConnection(connectionString) {
       SqlCommand cmd = new SqlCommand("Select Count(*) from tablename", conn);
       int rowCount = (int)cmd.ExecuteScalar();
    }

    Sorry. Forgot refresh my browser. :P


    Thanks,
    A.m.a.L
    .Net Goodies
    Remember to click "mark as answered" when you get a correct reply to your question
    Friday, May 22, 2009 12:33 PM
  • Hi,

     I know its already answered but that is with ADO.NET. If you are trying to get this information by using Entity Framework. You can directly get from entities Count() method.

    Beware, Use DbSet<T> instead of DbSet. As, DbSet<T> will return IQueryable.

    Sample code below

    public int GetTableRowCount<T>(DbContext dbContext, DbSet<T> myEntity)
    {
        int rowCount = myEntity.Count();
        return rowCount;
    }


    Sai Kumar K (MCP)
    Blog: Sai's Stuff.
    WebSite: SantoshTechnologies.

    Thursday, August 16, 2018 9:18 AM