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

  • Article

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

  • Anonymous
    October 28, 2005
    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.

  • Anonymous
    October 28, 2005
    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.

  • Anonymous
    November 07, 2005
    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?

  • Anonymous
    November 23, 2005
    Useful post. I could not find this little bit of information on msdn though!

  • Anonymous
    January 09, 2006
    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

  • Anonymous
    January 16, 2006
    Pragya, Thanks for your feedback.
    -Sushil

  • Anonymous
    January 16, 2006
    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

  • Anonymous
    November 24, 2006
    PingBack from http://www.primetime-software.de/simon.steckermeier/PermaLink,guid,e9648ca4-81e7-4caf-b6d1-400a88a7a598.aspx

  • Anonymous
    September 17, 2007
    free music videos myspace codes

  • Anonymous
    September 17, 2007
    music video codes myspace html

  • Anonymous
    September 17, 2007
    free myspace music background codes video

  • Anonymous
    April 08, 2008
    PingBack from http://drugsmoviesblog.info/data-access-blog-adonet-20-relative-paths-in-connectionstring/

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/445835-how-to-attach-mdf-in

  • Anonymous
    May 31, 2009
    PingBack from http://outdoorceilingfansite.info/story.php?id=22789

  • Anonymous
    June 16, 2009
    PingBack from http://workfromhomecareer.info/story.php?id=8407

  • Anonymous
    June 16, 2009
    PingBack from http://fixmycrediteasily.info/story.php?id=3237