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