ADO.Net 2.0: Relative paths in ConnectionString [Sushil Chordia]


In SqlClient Managed Provider, there has always existed (right from ADO.Net 1.0 days) a keyword in the connection string called AttachDbFileName. The user could have the location of the database file assigned to this keyword in the connection string and when opening a connection, this file gets attached as a database on the server. Here is an example:
    SqlConnection c = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;”+
                     @”AttachDbFilename=C:\TestApplication\Company.mdf;Initial Catalog=Company”);

   c.Open();
Result: The above code attaches file C:\TestApplication\Company.mdf as DataBase company on the server


Default Relative Paths:
Now say you were writing an application that references the database file. It’s not a good idea to have a hard reference to the location of the DB file in your code, but instead use relative paths. In ADO.Net 2.0, we have introduced the concept of substitution string (aka. |DataDirectory|) so that you can get the absolute path of the DB file to attach at run time. By default, |DataDirectory| gets substituted with base directory that the application(/assembly) is running from. Lets see the above example changed to use the substitution string.
   SqlConnection c = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;”+
                    @”AttachDbFilename=|DataDirectory|\Company.mdf;Initial Catalog=Company”);

   c.Open();
Result: When run in the C:\TestApplication directory, the above code attaches file C:\TestApplication\Company.mdf as DataBase company on the server


Custom Relative Paths:
You can reset the value to be substituted for |DataDirectory| to be anything you want. This can be done as follows;
   AppDomain.CurrentDomain.setData(“DataDirectory”,”C:\newPath\”);
   SqlConnection c = new SqlConnection (@“Data Source=.\SQLEXPRESS; Integrated Security=True;”+
                     @”AttachDbFilename=|DataDirectory|\Company.mdf;Initial Catalog=Company”);
   c.Open();
Result: The above code attaches file C:\newpath\Company.mdf as DataBase company on the server


Note:The above strings are hardcoded in the application above to just illustrate the concept of Subsititution strings in ADO.Net. In reality, these connection strings should be stored into and retrieved from the configuration files. The same applies to the strings stored in the config files.


Other providers:
Consider, you want to have relative paths to the UDL file in OleDb or the DSN file in ODBC. Yes, this is also supported; the syntax is similar to the AttachDbFileName example above. There are a few restrictions in using the substitution string:
      SqlClient Managed Provider: can be used only for the AttachDbFileName keyword 
      OleDb Managed Provider: can be used for any keyword other than user id, password and servername.
      ODBC Managed Provider: can be used for any keyword other than user id, password and servername.
      Oracle Managed Provider: not valid for any keyword


Conclusion: The above is a very simple feature that will help in deploying custom applications on client machines. Do send in your comments or feedback.


Sushil Chordia, ADO.Net team, Microsoft.
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights

Comments (16)

  1. Mayoor Patel says:

    I don’t know how it is going to be useful, as in your example, you still hardcoded the path.

    >AppDomain.CurrentDomain.setData(“DataDirectory”,”C:newPath”);

    Rather in such cases it is preferable to use AppSettings.

    SqlConnection c = new SqlConnection (@“Data Source=.SQLEXPRESS; Integrated Security=True;"+

    e.g. @"AttachDbFilename=" AppSettings("MyDataFilePath") + "Company.mdf;Initial Catalog=Company");

    c.Open();

    where MyDataFilePath is defined either in "AppSettings" section of App.Config or Web.Config.

  2. MSDN Archive says:

    Mayoor, Thanks for the feedback on the post. The ideal approach would be to put one single connection string (with the substitution string)in your configuration files and then change the Substitution strings in your code based on custom logic. The example above was to just illustrate how to add custom paths, I agree that it is a good idea to have the path defined in some other config file to reuse in you code. In doing so, you wont have to change your connection string with the above example.

  3. Sam says:

    what would happen if you use |DataDirectory| connecting to a different computer – for example the program could be running on a xp comp named ‘kurt’, and connects to a SBS 2003 running the SQL Server named ‘karin’ – the SBS 2003 server can’t store the database on the xp client, so where will it go, pathwise?

  4. Pragya says:

    Useful post. I could not find this little bit of information on msdn though!

  5. Kevin Jones says:

    Sushil,

    can you confirm that AppDomain.CurrentDomain.SetData("DataDirectory", "path-to-dir") still works in the RTM bits?

    I’ve had this working but it’s now stopped and I’ve seen other blog entries claiming this doesn’t work. I wonder if there was some change between a late beta/CTP and the release

  6. Pragya, Thanks for your feedback.

    -Sushil

  7. Kevin, I still see the function (AppDomain::SetData(str, object) being public in the RTM bits. This method has been public since 1.1 days. Here is a link to its documentation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemappdomainclasssetdatatopic.asp

    Hope that helps,

    Sushil Chordia

  8. free myspace music background codes video