Where my data files at!?

A bit of context: As part of my team’s development workflow, we create/modify our Domain Model in code (entities such as Customer, Product, Order, etc.) and then generate the Database Model via a homegrown console app.  This allows us to  [efficiently]  code our Domain Model without having to worry about applying these changes to the lower layers of the system all the way to the database schema.

The [Schema Export] console app I mentioned automatically generates a fresh database by exploring our Domain Model – NHibernate makes this very easy - for the purpose of comparing and synchronizing with the Database Project , thus bringing our Persistence store up-to-date with our Domain Model.

The Problem: I had a need to find out the physical path was the instance of SQL Server installed on the server [c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\] and thus were the data files for the database are placed [$SqlInstallPath\MSSQL\Data\].

The Solution:

The following T-SQL returned the path where the master database is located:

select physical_name from master.sys.databases dbs inner join master.sys.master_files files on dbs.database_id = files.database_id
where dbs.name = 'master' and type_desc = 'ROWS'

Results:

physical_name
-------------------------------------------------------------------------------
c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf
(1 row(s) affected)

And the following function takes care of removing the filename and fiving you the path:

 private static string GetDbFilePath ( SqlConnection sqlConnection, string databaseName )
        {
            string path = string.Empty;
            try
            {
                string datafilepathQuery =
                    string.Format (
                        @"select physical_name from 
              master.sys.databases dbs inner join master.sys.master_files files 
                on dbs.database_id = files.database_id
                where dbs.name = '{0}' and type_desc = 'ROWS'", databaseName );
                using ( var sqlCmd = new SqlCommand ( datafilepathQuery, sqlConnection ) )
                {
                    object datafilepath = sqlCmd.ExecuteScalar ();
                    path = datafilepath.ToString ();
                    path = path.Substring(0, path.LastIndexOf('\\'));
                }
            }
            catch ( Exception )
            {
            }
            return path;
        }

This allowed me to subsequently run another command to create the database schema at the right location. That is all for today folks!

FYI: I will be writing a post – or series of posts – on how to improve your development workflow and thus adhering to my favorite tenet of the Agile Manifesto : “maximizing the amount of work not done”.