Connection string with relative path to the database file

Question

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.

Are you sure you have a relative path? what you typed "F:\My Documents\Project1\bin\Debug\Data\file.sdf" is also a full path. A relative path would be something like "\data\file.sdf" if your app was already running from "F:\My Documents\Project1\bin\" — Jrud, Dec 02 '09 at 15:35

score 99 · Answer 1 · edited Oct 31 '17 at 12:07

99

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);

edited Oct 31 '17 at 12:07

Roman

4,922
3
22
31

answered Aug 04 '11 at 12:40

Nime Cloud

6,162
14
43
75

2

To learn more about the **DataDirectory** term, have a look at http://msdn.microsoft.com/en-us/library/cc716756.aspx (it's at the end of the document) – Yves M. Mar 14 '14 at 15:34

score 8 · Answer 2 · edited Jun 23 '15 at 07:44

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)

score 4 · Answer 3 · answered Apr 09 '13 at 07:42

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.

score 4 · Answer 4 · edited Sep 29 '16 at 11:54

4

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

edited Sep 29 '16 at 11:54

rory.ap

34,009
10
83
174

answered Dec 02 '09 at 15:36

RC1140

8,423
14
48
71

6

You can't just "append it to the connection string". You'd have to parse the connection string, extract the data source value, prepend the current directory and then rebuild the connection string. This is non-trivial. – Mike Scott Jan 05 '11 at 18:41

score 4 · Answer 5 · answered Dec 03 '16 at 07:56

4

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

answered Dec 03 '16 at 07:56

Sobhan

796
1
9
31

This worked for me. But in the ef core there should be a space after the equal sign and no back slash and space before the data base file name. Otherwise it does not detect the DataDirectory. Example: `"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= |DataDirectory|my_database.accdb;Persist Security Info=False;"` – Reza Shafie Jul 23 '23 at 07:29

score 1 · Answer 6 · answered Aug 02 '15 at 07:21

1

   <?xml version="1.0"?>  
<configuration>  
  <appSettings>  
    <!--FailIfMissing=false -->  
    <add key="DbSQLite" value="data source=|DataDirectory|DB.db3;Pooling=true;FailIfMissing=false"/>  
  </appSettings>  
</configuration>

answered Aug 02 '15 at 07:21

william

13
1

6

do not store connection strings in . They have their own section. – slfan Aug 02 '15 at 07:45

score 0 · Answer 7 · edited Mar 19 '19 at 09:02

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")

score 0 · Answer 8 · answered Apr 03 '17 at 23:24

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");

score 0 · Answer 9 · answered Dec 05 '18 at 15:22

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.

score 0 · Answer 10 · answered Dec 06 '11 at 05:44

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"
};

Connection string with relative path to the database file

10 Answers10

Linked

Related