Getting Transact SQL query result in C# Application

The command RESTORE FILELISTONLY does return a result set as per the documentation RESTORE Statement - FILELISTONLY. What I did find during testing my code, you need to make sure the user that is running the application has permissions to read the specified file and directory. Otherwise you will run into issues.

For a code example of this:

var command = @"RESTORE FILELISTONLY FROM DISK = N'F:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Scratch.bak'";

using (var sqlConnection = new SqlConnection("Server=localhost;Database=Scratch;Trusted_Connection=True;"))
using (var sqlCommand = new SqlCommand(command, sqlConnection))
{
    sqlConnection.Open();
    var sqlDataReader = sqlCommand.ExecuteReader();

    while (sqlDataReader.Read())
    {
        Console.WriteLine($"Logical Name: {sqlDataReader["LogicalName"]}");
        Console.WriteLine($"Physical Name: {sqlDataReader["PhysicalName"]}");
        Console.WriteLine($"Type: {sqlDataReader["Type"]}");

    }
}

You may also find that when trying to work SQL Server management, using the SqlConnection and SqlCommand objects may be more frustrating then they are worth. I strong recommend using the SQL Server Management Objects (SMO). This is actually what management studio uses when you are work with SQL Server. I have an example on GitHub if you are interested in it. Checkout GitHub: SMO Demo.