I'm trying to get the logical and physical file names for a database (DON'T REDIRECT THIS!)

Question

WHAT I HAVE:
Visual Basic 2019, .NET Framework 4.6+, Entity Framework 6+, WinForms, SQL Server (version unknown)

MY PROBLEM:
I need to obtain the names and types of the logical/physical files for a database whose context is currently open. (GetContextForExistingDatabase gets a context for a database whose catalog name and .mdf file name are specified. Focus only on the code that follows.) My procedure:

Protected Function GetLogicalFiles(ByVal CatalogName As String, _
    ByVal FileName As String) As List(Of LogicalFileEntry)
Dim results As New List(Of LogicalFileEntry)
    Using Context As SocialContactsTracker1 = _
        GetContextForExistingDatabase(CatalogName, FileName)
        '   get database list from master database
        results = _
            Context.Database.SqlQuery(Of LogicalFileEntry) _
                ("SELECT file_id AS " & NameOf(LogicalFileEntry.FileID) _
                        & ", type AS " & NameOf(LogicalFileEntry.FileType) _
                        & ", name AS " & NameOf(LogicalFileEntry.LogicalName) _
                        & ", physical_name AS " & NameOf(LogicalFileEntry.PhysicalName) _
                    & " FROM sys.database_files;").ToList()
            For Each LogicalName As LogicalFileEntry In results
                With LogicalName
                    MessageBox.Show( _
                        .FileID & ControlChars.CrLf & .FileType _
                        & ControlChars.CrLf & """" & .LogicalName & """" _
                        & ControlChars.CrLf & """" & .PhysicalName & """")
                End With
            Next LogicalName
        Return results
    End Using
End Function

The type LogicalFileEntry (which is defined within the larger class containing the above procedure) is defined as follows:

''' <summary>
''' This class returns the data that was retrieved in the
''' T-SQL script of the GetLogicalFiles method 
''' </summary>
Protected Class LogicalFileEntry
    Public FileID As Integer
    Public FileType As Byte
    Public LogicalName As String
    Public PhysicalName As String
End Class

when I list the fields of the 2 entries, they return 0 and "". If I qualify "sys.database_files" as CatalogName & ".sys.database_files", it tells me that the database doesn't exist when doing the query. (Impossible, because the database otherwise opens fine!)

What is wrong with my code?! Is it the T-SQL syntax, is it the way I've specified sys.database_files, is it the field-types for LogicalFileEntry?

Please give me an answer ASAP, in VB.NET, and as simply as possible. Remember, everything needs to be determined at run-time!

Answer 1

To get the logical and physical files:

Protected Function GetDatabaseFiles(ByVal CatalogName As String, _
    ByVal FileName As String) As DatabaseFiles
    Const LogType As Byte = 1, TypeName As String = "type", _
        LogicalName As String = "name", PhysicalName As String = "physical_name"

    Dim df As DatabaseFiles = New DatabaseFiles()
    Using Context As SocialContactsTracker1 = _
            GetContextForExistingDatabase(CatalogName, FileName)
        Using c As SqlConnection = _
                New SqlConnection(Context.Database.Connection.ConnectionString)
            c.Open()
            Using command As SqlCommand = New SqlCommand( _
                    "SELECT * FROM """ & CatalogName & """.sys.database_files;", c)
                Using r As SqlDataReader = command.ExecuteReader()
                    Do While r.Read()
                        If r.GetByte(r.GetOrdinal(TypeName)) = LogType Then
                            df.LogicalLogFile = r.GetString(r.GetOrdinal(LogicalName))
                            df.PhysicalLogFile = r.GetString(r.GetOrdinal(PhysicalName))
                         Else
                            df.LogicalDataFile = r.GetString(r.GetOrdinal(LogicalName))
                            df.PhysicalDataFile = r.GetString(r.GetOrdinal(PhysicalName))
                        End If
                    Loop
                End Using
            End Using
        End Using
    End Using
    Return df
End Function

Defined within the above method's class:

''' <summary>
''' This class returns the data that was retrieved in the GetDataFiles method 
''' </summary>
Protected Class DatabaseFiles
    Public LogicalDataFile As String    
    Public PhysicalDataFile As String
    Public LogicalLogFile As String
    Public PhysicalLogFile As String
End Class