Use SQL in C# to get a Database's Drive Letter

Question

I am trying to find out what drive a database is on, on the server. To further elaborate, there is a sever "MyServer" and three databases on that server. Databases A and B are on the "C:\" Drive of that server and database C is on the "D:\" Drive. I want to know how I can use the connection string to try and get the drive letter so that when a user selects a database from an application it tells them what drive that database is on.

Currently I am trying the following means and not getting the correct response:

Private void GetDriveLetter()
{
   string connectionString = String.Format(DatabaseInfo.CONNECTIONSTRING_TEMPLATE, dbName, server)
   try
   {
     using (SqlConnection cn = new SqlConnection(ConnectionString))
     {
       cn.Open();
       Server srv = new Server(new ServerConnection(cn));
       Database database = new Database(srv, dbName);
       txtDriveLetter.Text = database.PrimaryFilePath;
       cn.Close();
     }
    }
    catch (Exception ex)
    { 
      myException = ex.Message
    }
}

DatabaseInfo is just a class we use for reading SQL Servers and database names, and myException is a function that writes exceptions to a log file. The issue I am having is that no matter what I do I get an exception that says PrimaryFilePath is never set. Am I doing something wrong/missing something, or is this just not the correct way to go about this?

EDIT: Sorry never posted the exception To accomplish this action, set property PrimaryFilePath.

Answer 1

You could always run a query against your sys catalog views inside SQL Server:

SELECT * FROM sys.master_files

This will list your database, it's id, its logical name, and the location of the file on disk.