CODEWITHSUNDEEP
c#stringconnectionsql-server-ce
Martin
Martin

Reputation: 511

Connection string with relative path to the database file

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.

Upvotes: 51

Views: 128894

Answers (10)

Tyler
Tyler

Reputation: 19848

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

Upvotes: 0

Billy Raseman
Billy Raseman

Reputation: 311

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.

Upvotes: 0

Nime Cloud
Nime Cloud

Reputation: 6389

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

Upvotes: 100

pat
pat

Reputation: 1

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

Upvotes: 0

Sobhan
Sobhan

Reputation: 816

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

Upvotes: 4

RC1140
RC1140

Reputation: 8653

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

Upvotes: 4

william
william

Reputation: 13

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

Upvotes: 1

Ck.Nitin
Ck.Nitin

Reputation: 181

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=&quot;Data Source=|DataDirectory|\Database\HMProjectDB.sdf;Password=HMProject;Persist Security Info=False;&quot;" providerName="System.Data.EntityClient" />

Thanks

ck.Nitin (TinTin)

Upvotes: 8

tomg
tomg

Reputation: 369

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.

Upvotes: 4

Elias Hossain
Elias Hossain

Reputation: 4469

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

Upvotes: 0

Related Questions