[C#] how to query sql server database size

Question

When writing C# code, if I can manage to get a SqlConnection, is there any way to perform a query to get the size of the Database?

I searched the internet, seems "sp_spaceused" can be used, but it is not a table, it is a procedure.

Can I query a procedure?

Answer 1

The following code (taken from a console app testharness) calls the sp_spaceused procedure using ADO.NET and then iterates the resulting datareader to get the db size.

This works, but I can't say that there isn't a more efficient or direct way of achieving what you want.

One alternative implementation would be to wrap the sp_spaceused procedure in your own procedure that gives the exact data your need as a scalar return value.

class Program
{
    static void Main(string[] args)
    {
        string strCount;
        SqlConnection Conn = new SqlConnection
           ("Data Source=ServerName;integrated " +
           "Security=sspi;initial catalog=TestingDB;");
        SqlCommand testCMD = new SqlCommand
           ("sp_spaceused", Conn);

        testCMD.CommandType = CommandType.StoredProcedure;        

        Conn.Open();

        SqlDataReader reader = testCMD.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
               Console.WriteLine("Name: " + reader["database_name"]); 
               Console.WriteLine("Size: " +  reader["database_size"]); 
            }
        }

        Console.ReadLine();
    }
}