Q:

How to programmatically check if a SQL database/server supports compression in C#?

Building a tool to backup a database that I'm testing locally first. I'm trying to check if compression is supported and change my SQL query based on the value returned. Running it against an actual live server returns 0 or 1, but running the query itself against a local instance doesn't give a value. As a result I don't believe the if statements ever run to change the CommandText and my final WriteLine test returns the initial compression query instead of one of the backup commands.
I tried changing the if to check for a null, however the else should catch any other values besides '1'
string compressionQuery = "SELECT VALUE FROM sys.configurations WHERE name = 'backup compression default'";
SqlCommand sqlCmd = new SqlCommand(compressionQuery, newConn);

SqlDataReader reader = sqlCmd.ExecuteReader();

while (reader.Read()) //while the data reader is checking the records
{
    Interface.WriteLine(reader.GetInt32(0).ToString()); //print the specified record(row) to the console
    canCompress = reader.GetInt32(0);

    // Backup the database.
    if (canCompress == 1)
    {
        sqlCmd.CommandText = "BACKUP DATABASE [" + connBuilder.InitialCatalog + "] "
            + "TO DISK = '" + backupPath + "' "
            + "WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT, "
            + "NAME = '" + backupName + "', "
            + "SKIP, REWIND, NOUNLOAD, STATS = 10";
        Interface.WriteLine("1");
    }
    else 
    {
        sqlCmd.CommandText = "BACKUP DATABASE [" + connBuilder.InitialCatalog + "] "
            + "TO DISK = '" + backupPath + "' "
            + "WITH COPY_ONLY, NOFORMAT, NOINIT, "
            + "NAME = '" + backupName + "', "
            + "SKIP, REWIND, NOUNLOAD, STATS = 10";
        Interface.WriteLine("0");
    }
}

reader.Close(); //stop reading records

Interface.WriteLine(sqlCmd.CommandText.ToString()); //Should print one of the backup queries
sqlCmd.ExecuteNonQuery();

It should return one of the nested backup commands. Right now it simply writes the initial compression query.

A:

Backup compression is not available in all editions of SQL Server. So in my sqlexpress the value is not even in the table, it is probably your case too. reader.Read() never reads anything, so you do not get into the if part at all. You can restructuralize you code
bool canCompress = false;

using (SqlDataReader reader = sqlCmd.ExecuteReader())
{
    while (reader.Read())
    {
        canCompress = reader.GetInt32(0) == 1;                                      
    }
}

if (canCompress)
{
    ...
}
else
{
    ...
}

And you can even simplify the reading like this
bool canCompress = (int?)sqlCmd.ExecuteScalar() == 1;

