Why db not found outside bin/debug folder when data directory is used

Question

I am learning data directory and I have discovered and learnt a lot of people seemed to ask similar questions for example: here, here, here and here. There is one thing that is troubling me. That is once the application has been deployed can the user change the location for there database? For example I am testing my application and the database is stored in bin/Debug folder. I moved the database file to a temp folder so its in C:\A\database1.mdb. When I run the application I received the an error of...

Could not find file 'C:\...\bin\Debug\database1.mdb'.

1) Why do I have to put the db file in bin/Debug folder?

2) How do I overcome the problem for the application to read and access file in C:\A\database1.mdb when I use Data Directory (relative path) instead of hard code?

As repeated above

3) That is once the application has been deployed can the user change the location for there database?

.cs files and .config for Build Action I have selected Compile and for Copy to Output Directory I have selected Copy always

I have a connectionstring that looks like this...

myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= |DataDirectory|\database1.mdb");

I have App.config file and it looks like this...

<connectionStrings>
    <add name="Project1.Properties.Settings.Project1ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\database1.mdb" providerName="System.Data.OleDb"/>
</connectionStrings>

As example

public MainForm()
    {
       InitializeComponent();
       this.WindowState = FormWindowState.Maximized;
       myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\database1.mdb"); 

    }

    OleDbConnection myCon;
    OleDbCommand cmd;

    private void btnInsert_Click(object sender, EventArgs e)
    {

     myCon.Open();
         OleDbCommand cmd = new OleDbCommand();
         cmd.Connection = myCon;

         OleDbCommand cmdCheck = new OleDbCommand();
         cmdCheck.Connection = myCon;   

         cmdCheck.CommandText = "SELECT COUNT(*) FROM Details WHERE ID = ?";
         cmdCheck.Parameters.AddWithValue("@ID", txtID.Text);

         if (Convert.ToInt32(cmdCheck.ExecuteScalar()) == 0)
         {
     cmd.CommandText = (@"INSERT INTO Details (ID, FirstName)
                       VALUES(@ID, @FirstName)")

             cmd.Parameters.AddWithValue("@ID", txtID.Text);
             cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
             cmd.ExecuteNonQuery();
     }
        myCon.Close();
   }

Thanks in advance if anyone can guide me here.

Sudhakar Tillapudi · Accepted Answer · 2013-11-16T12:01:59.343

4

Q1: Why do I have to put the db file in bin/Debug folder?

Answer:

No You don't have to Put the DB File in your Debug/Release Folder. |DataDirectory| is a substitution string so you can configure the location of your database file separately

follow the below step to configure your DB path:

AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\yourrequiredfolder\DB\");

so the above step is to configure the Folder for DB files. so now |DataDirectory| refers to the => C:\yourrequiredfolder\DB\

Q2: How do I overcome the problem for the application to read and access file in C:\A\database1.mdb when I use Data Directory (relative path) instead of hard code?

Ansewer:

if you set the path of your DB file using above step tis problem will be resolved, but make sure that if you are deploying it on client pc you should maintain the same path otherwise you can change using the above step.

Q3: That is once the application has been deployed can the user change the location for there database?

actually no because it is fixed in your code. but if you wish you can do it by following steps:

Step1: provide UI to ask him to get the latest DB Path.

String strUserDBPath=getUserNewDBPath();//get the new path of DB file from user.

Step2: Change the current path of |DataDiretory| from the code as below:

AppDomain.CurrentDomain.SetData("DataDirectory", strUserDBPath);

if you need anything more please let me know.

Sample Code:

public MainForm()
    {
       InitializeComponent();
       this.WindowState = FormWindowState.Maximized;
       AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\A\"); 
       myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\database1.mdb"); 


    }

    OleDbConnection myCon;
    OleDbCommand cmd;

    private void btnInsert_Click(object sender, EventArgs e)
    {

     myCon.Open();
         OleDbCommand cmd = new OleDbCommand();
         cmd.Connection = myCon;

         OleDbCommand cmdCheck = new OleDbCommand();
         cmdCheck.Connection = myCon;   

         cmdCheck.CommandText = "SELECT COUNT(*) FROM Details WHERE ID = ?";
         cmdCheck.Parameters.AddWithValue("@ID", txtID.Text);

         if (Convert.ToInt32(cmdCheck.ExecuteScalar()) == 0)
         {
     cmd.CommandText = (@"INSERT INTO Details (ID, FirstName)
                       VALUES(@ID, @FirstName)")

             cmd.Parameters.AddWithValue("@ID", txtID.Text);
             cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
             cmd.ExecuteNonQuery();
     }
        myCon.Close();
   }

edited Nov 16 '13 at 12:01

answered Nov 15 '13 at 16:52

Sudhakar Tillapudi

25,935
5
37
67

Thank you very much for your reply very useful. From your answer it seems I have to amend path directory and reinstall the application? Then I have another question:- what is the point or difference of using DataDirectory from hard-coding the connection string like myCon.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C\:...database1.mdb") ? – bucketblast Nov 15 '13 at 17:11
Well if you hardcode in your application that is upto your application domain only but if you set it using theSetData() method then it is global so any application which uses |DataDirectory| refers to the same path. – Sudhakar Tillapudi Nov 15 '13 at 17:13
@bucketblast: if you have any doubts please let me know. – Sudhakar Tillapudi Nov 15 '13 at 17:16
I have assigned OleDbConnection to myCon; so how do I assign myCon with AppDomain.CurrentDomain. Thanks Sudhakar – bucketblast Nov 15 '13 at 20:49
I mean I have tried currentDomain.SetData(myCon + "DataDirectory", @"C:\A\database1.mdb"); this is giving error of Object reference not set to an instance of an object. I not am sure on this but looking at it – bucketblast Nov 15 '13 at 20:54
setting DataDirectory is completly different and assigning connection object is different , you don't need to assign the connection object myCon to AppDomain, first you set the DB path and contine with ur program , please share me your code. – Sudhakar Tillapudi Nov 16 '13 at 03:06
I have provided additional information at the top. It's an example of insert method – bucketblast Nov 16 '13 at 11:53
Okay. So i could not see Data Directory path setting statement. where did you write the statement ->AppDomain.CurrentDomain.SetData("DataDirectory", "c:\\path\\"); – Sudhakar Tillapudi Nov 16 '13 at 11:56
@bucketblast: Check My edited answer : here i have added the SetData() function to set the default path(c:\A\) before accessing the Connection string.now your database1.mdb file should be in C:\A\ .please let me know if you need anything more, It Works Perfectly Now. – Sudhakar Tillapudi Nov 16 '13 at 12:05
Thank you very much for your valuable time and effort. It is working and now I have to install this piece of software next week. Hopefully without any problems. – bucketblast Nov 16 '13 at 12:54

Why db not found outside bin/debug folder when data directory is used

1 Answers1