SQL CE database size

Question

I am looking for C# code for the compact framework to determine the current size of a database table (within a SDF file). Table name "LogTable"

Does anyone have any code examples to determine how this can be achieved. I cannot use a stored procedure as the code throws an exception as this is not supported :-(

I have tried the code below::

using (SqlCeCommand cmd = sql_conn.Connection.CreateCommand()) {

            cmd.CommandText = "exec sp_spaceused";

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 60;               
            cmd.ExecuteNonQuery();
        }

Also tried cmd.CommandText = "sp_spaceused"; and got an exception that this is not supported.

Thanks :-)

Answer 1

According to this KB Article, it looks like SQL CE table size can only be estimated.

Answer 2

SQLCE / SQL Mobile 2005 limits row sizes to 8 Kb. If you know your column definition, you can estimate your row size and multiply by the number of rows to determine your table size. SQLCE / SQL Mobile 2005 does not support stored procedures (as you have already figured out!)

You can also read and modify .sdf files with SQL Management Studio 2005 and probably determine table size easily through that application unless you want to do it programmatically.