Search code examples
c#sql-serverdatabaseconnection-stringrelative-path

This database cannnot be imported error after changing connection string to relative path

I am working in Visual Studio 2017 , window forms(c#), I have added database to my project, (New item -> new service based database) and I took this connection string:

enter image description here

Then I ran this query and it works fine :

using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Podlaktica40cm\Desktop\WindowsFormsApp1\WindowsFormsApp1\Database1.mdf;Integrated Security=True"))
{
     con.Open();

     using (SqlCommand com = new SqlCommand("insert into Test values(45)", con))
     {
         com.ExecuteNonQuery();
     }
 }

Problem is: when I change my connection string's path to be relative instead of absolute, like this:

 @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = |DataDirectory|\Database1.mdf; Integrated Security = True")

Now when I open database in Visual Studio explorer and try to refresh it, this happens:

enter image description here

So no rows inserted in my database. I have also tried to change copy to output directory property to copy if newer, but still same result.

NOTE: I finished my project and I was working with absolute path, I finished all testing and now I have to change to relative path so it could work on other computers, therefore I made this demo test app to show you what is the error and to simplify (ConfigurationManager, parametrised SQL and so on). After I put |DataDirectory| in my conString and when I close my application, all data which I inserted during runtime is deleted and I'm opening application with empty database. I'm assuming there is some confusion between .mdf file in project directory and mdf in debug folder. I'm really confused so any help or any link would be great!

Solution

  • The problem was occurring because I was accessing and inserting data in mdf file in bin/debug folder , whereas I was opening database in source folder of my project via Server Explorer. So I have added another connection with mdf file in debug and all changes are visible there!