I load data from sdf database in winforms App. I use full path to the database file . Example :
conn = new SqlCeConnection
{
ConnectionString ="Data Source=F:\\My Documents\\Project1\\bin\\Debug\\Database.sdf"
};
I d like use a relative path to the database file. For example. I have sdf file in folder F:\My Documents\Project1\bin\Debug\Data\file.sdf and I want use relative path in connection string. Any advice ? Thank you.
Relative path:
ConnectionString = "Data Source=|DataDirectory|\Database.sdf";
Modifying DataDirectory as executable's path:
string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
string path = (System.IO.Path.GetDirectoryName(executable));
AppDomain.CurrentDomain.SetData("DataDirectory", path);
Try this code to the working directory if database file exists like below.
D:\HMProject\DataBase\HMProject.sdf
string Path = Environment.CurrentDirectory;
string[] appPath = Path.Split(new string[] { "bin" }, StringSplitOptions.None);
AppDomain.CurrentDomain.SetData("DataDirectory", appPath[0]);
Connection string for .sdf file
<add name="LocalDB" connectionString="metadata=res://*/Client.HMProject.csdl|res://*/Client.HMProject.ssdl|res://*/Client.HMProject.msl;provider=System.Data.SqlServerCe.4.0;provider connection string="Data Source=|DataDirectory|\Database\HMProjectDB.sdf;Password=HMProject;Persist Security Info=False;"" providerName="System.Data.EntityClient" />
Thanks
ck.Nitin (TinTin)
After several strange errors with relative paths in connectionstring I felt the need to post this here.
When using "|DataDirectory|" or "~" you are not allowed to step up and out using "../" !
Example is using several projects accessing the same localdb file placed in one of the projects.
" ~/../other" and " |DataDirectory|/../other" will fail
Even if it is clearly written at MSDN here the errors it gave where a bit unclear so hard to find and could not find it here at SO.
Relative to what, your application ? If so then you can simply get the applications current Path with :
System.Environment.CurrentDirectory
And append it to the connection string
In your config file give the relative path
ConnectionString = "Data Source=|DataDirectory|\Database.sdf";
Change the DataDirectory to your executable path
string path = AppDomain.CurrentDomain.BaseDirectory;
AppDomain.CurrentDomain.SetData("DataDirectory", path);
If you are using EntityFramework, then you can set the DataDirectory path in your Context class
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|my_database.accdb;Persist Security Info=False;" <?xml version="1.0"?>
<configuration>
<appSettings>
<!--FailIfMissing=false -->
<add key="DbSQLite" value="data source=|DataDirectory|DB.db3;Pooling=true;FailIfMissing=false"/>
</appSettings>
</configuration>
Would you please try with below code block, which is exactly what you're looking for:
SqlConnection conn = new SqlConnection
{
ConnectionString = "Data Source=" + System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Database.sdf"
};
I did this in the web.config file. I added to Sobhan's answer, thanks btw.
<connectionStrings>
<add name="listdb" connectionString="Data Source=|DataDirectory|\db\listdb.sdf"/>
</connectionStrings>
Where "db" becomes my database directory instead of "App_Data" directory.
And opened normally with:
var db = Database.Open("listdb");
I had the same issue trying to specify the relative file path for a database connected to a Windows Forms application. I was able to resolve the issue by following the directions for adding a data source to Windows Forms from Microsoft (e.g., for connecting an Access database).
By using this method, Visual Studio will set the relative file paths to your database for you instead of trying to set it manually. If your database is external to your application, it will create a copy of the database and add it to your application in the proper location. Although you can manually alter you connection string in App.config and/or Settings.settings or within one of your scripts, I've found this method to be error prone. Instead, I've found it best to follow the Microsoft instructions, in general.
This worked for me:
string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ HttpContext.Current.Server.MapPath("\\myPath\\myFile.db")
+ ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
I'm querying an XLSX file so don't worry about any of the other stuff in the connection string but the Data Source.
So my answer is:
HttpContext.Current.Server.MapPath("\\myPath\\myFile.db")
