[c#] 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.

This question is related to c# string connection sql-server-ce

The answer is


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)


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

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


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

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

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.


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


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


Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to string

How to split a string in two and store it in a field String method cannot be found in a main class method Kotlin - How to correctly concatenate a String Replacing a character from a certain index Remove quotes from String in Python Detect whether a Python string is a number or a letter How does String substring work in Swift How does String.Index work in Swift swift 3.0 Data to String? How to parse JSON string in Typescript

Examples related to connection

Apache Server (xampp) doesn't run on Windows 10 (Port 80) "Proxy server connection failed" in google chrome Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES) "The underlying connection was closed: An unexpected error occurred on a send." With SSL Certificate Login to Microsoft SQL Server Error: 18456 How do I start Mongo DB from Windows? java.rmi.ConnectException: Connection refused to host: 127.0.1.1; mySQL Error 1040: Too Many Connection org.apache.http.conn.HttpHostConnectException: Connection to http://localhost refused in android What is the functionality of setSoTimeout and how it works?

Examples related to sql-server-ce

How to change column width in DataGridView? Creating stored procedure with declare and set variables How to connect to SQL Server from another computer? how to open *.sdf files? How do you open an SDF file (SQL Server Compact Edition)? Connection string with relative path to the database file How do you create a foreign key relationship in a SQL Server CE (Compact Edition) Database?