Using Sql Management Objects (SMO) to connect to SQL, how can I find out if a view has any rows?

Question

I know I can find the Table Rowcount using SMO, but i'm having trouble getting the View Rowcount.. I dont really need exact rowcount (it is a bonus), but i do need to know if the view is "empty" (0 records).

Here is my attempt so far:

 public Int64 GetRowCount()
{
    SqlConnection sqlConnection = new SqlConnection(SqlConnectionString);
    ServerConnection serverConnection = new ServerConnection(sqlConnection);
    Server server = new Server(serverConnection);
    if (server == null)
        throw new InvalidDataException(
            string.Format(
                "Could not connect to server {0}. Check that it exists and that the current user has access to it.",
                SqlServerName));
    Database db = server.Databases[SqlDatabaseName];
    if (db == null)
        throw new InvalidDataException(
            string.Format(
                "Could not connect to database {0} on server {1}. Check that it exists and that the current user has access to it.",
                SqlDatabaseName, SqlServerName));

    db.DefaultSchema = SqlSchemaName;

    if (db.Tables.Contains(SqlTableName))
    {
        Table tbl = db.Tables[SqlTableName];
        return tbl.RowCount;
    }
    if (db.Views.Contains(SqlTableName))
    {
        View view = db.Views[SqlTableName];
        try
        {
            view.ReCompileReferences();
        }
        catch
        {
            // ignored
        }
        return view.RowCount;  ************************** MAGIC GOES HERE
    }

    throw new InvalidDataException(string.Format(    "The SQL table/view {0} does not exist in database {1}, or is not accessible by the current user.",
SqlTableName, SqlDatabaseName));
}

Answer 1

I think the answer is "no" unless the view is an indexed (or materialized) view. I say that because the SMO appears to be looking at the metadata in sys.partitions to determine row count and no row will exist there unless the data that underlies the view is persisted to disk. That only happens when the view is indexed.