Problem accessing localDB when moving application to new folder

Question

Problem accessing localDB when moving application to new folder

Ronny Mees 20

In a CRUD class I have declared the following connection string:

private static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + System.AppDomain.CurrentDomain.BaseDirectory + "\\PlexSeries.mdf;Integrated Security=True;Connect Timeout=30";

And the following GET method:

public DataTable Get()
{

 DataSet dataset = new DataSet();

 using (SqlConnection connection = new SqlConnection(dbConnection))
 {
     try
     {
          SqlDataAdapter adapter = new SqlDataAdapter();
          adapter.SelectCommand = new SqlCommand("SELECT * FROM dbo.Series", connection);
          adapter.Fill(dataset);
          return dataset.Tables["Table"];
     }
     catch (Exception ex)
     {
         MsgBox.Show(ex.Message, "Error", MsgBox.Buttons.OK, MsgBox.Icon.Error, MsgBox.AnimateStyle.FadeIn);
         return null;
     }
 }

}

Upon getting to the line adapter.Fill(dataset), I get the following error:

But when i copy the debug folder to an other location...

Upon getting to the line adapter.Fill(dataset), I get the following error:

Error Image

I tried to solve this by using a relative path to the database file but that didn't solve the problem.

In the previous version of this solution this was not a problem but that was build with Visual Studio 2019 and now I am using Visual Studio 2022.

Any tips on how to solve this?

Accepted answer

1 additional answer

Your answer

Answer 1

Raksha Chourasia 75

The error that you have received shows that the connection string is not able to locate the database file when you are running the application from a different location.

This error is caused because of the path for the database file which is relative to the current directory.

For resolving this you can modify the connected string to an absolute path for the database file

You can use this type of code to set it:

private static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\path\to\your\database\PlexSeries.mdf;Integrated Security=True;Connect Timeout=30";

Also, Replace C:\path\to\your\database with the actual path to the PlexSeries.mdf file on the system.

As well you can use an alternative method for the correct path:

private static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PlexSeries.mdf") + ";Integrated Security=True;Connect Timeout=30";

Also, have a look to match the correct path with PlexSeries.mdf file.

This will help you in establishing the connection.

I hope this will help!!

Ronny Mees 20 Reputation points

2023-07-17T07:22:54.4666667+00:00
Thanks for your help.

What have I done:

Made a folder in my solution named Database en created a new database file:

I changed the property for the files to 'copy to output directory', so on build they are copied to a folder called Database in the debug folder.

Then I changed the connectionstring as follows:

public static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;database="+Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Database\\PlexData.mdf")+";Integrated Security=True;Connect Timeout=30";

When I run the solution I get the following error:

If I change the connectionstring to :

public static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;database='D:\<path to my solution>\plexserie_2\Database\PlexData.mdf';Integrated Security=True;Connect Timeout=30";

All works fine.

Any Idea's ?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-17T08:12:29.11+00:00

A database name that starts with D:\ and ends in .mdf is very odd. Normally a database is just called PlexData, Northwind, or some other simple logical name. SQL Server keeps track of where the database is physically located. That is nothing the application bothers about.
Ronny Mees 20 Reputation points

2023-07-17T08:39:00.0233333+00:00

Thanks for your input.

So now I noticed that when you add a database like I have done that the name it is given is the path to that database file.

So what I have done is changed that name to PlexData and changed my connectionstring to:

public static string dbConnection = @"Data Source=(LocalDB)\MSSQLLocalDB;database=PlexData;Integrated Security=True;Connect Timeout=30";

So now I am able to run the application from what ever folder I want to put it in to, but it always uses the original database in my solution.

So if I would like to move it to an other computer it won't work because the database file will be missing.

What I would like to have is that the solution uses the database file that gets copied upon build , so in the database folder that is in the build folder.

Is that possible?

Or what would be the correct way to make my build solution ready for what ever computer I would like it to run on?

Thanks for the help guys, I am learning a lot from this information!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-17T09:13:50.38+00:00

First of all, I don't know your exact context: are you writing a "real" application, or are you just playing around for training purposes? In the latter case, the requirements may be a little dim, since you don't have clear goal to work against. I'm kind of assuming the latter.

Anyway, if you want to run an application against the same from multiple computers, you typically put the database on a server, which can be reached by multiple clients. And multiple users.

But you may have a single-user application, so that basically each computer running the application has its own database. The schema will be the same, and in the install package for your application, you would install a new empty database. "Empty" here meaning that tables, stored procedures etc are in place, but no user data. (There can still be lookup data and reference data that comes with your application.) In this particular scenario, you could use the AttachDBFilename connection keyword, but personally, I would prefer to use the RESTORE command to restore a backup of the database.

Now, in the single-user case, there may be the situation where you have bought a new laptop and want to move things over. For this scenario, I would use BACKUP/RESTORE.

Generally, while it is possible to move/copy a database by working with the physical files, I think BACKUP/RESTORE is better. A backup is a single file, only one item to keep track of, whereas there at least two database files - which means there are more chances for things to go wrong.
Ronny Mees 20 Reputation points

2023-07-18T05:53:00.3366667+00:00

Many thanks for making the effort trying to help me out!

It is a 'real' application that I have reworked (new version with better UI and functionality). It is a single-user application that runs on my PC, but my son also uses it (he lives in his own house, so no shared folders...).

So I am not a professional programming but really like to learn all I can.

Until now I always have used the .exe and .dll files you get from building the solution.

In the previous version I could place the database files with the application files in a folder of my choice and it would run. But now that won't work for some reason.

What I know is what i could read on the internet and have tested and implemented, I can imagen that not always leads to the 'correct way' to go for some things...

I have never used the publish option to make an install package for the application that also creates the 'empty' database file.

Could you help me out with that?

Is there some course or tutorial you know off that could get me started with that?

If I need to use a backup/restore method, where can I find info on that?

Many thanks!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-18T09:08:02.71+00:00

It is a 'real' application that I have reworked (new version with better UI and functionality). It is a single-user application that runs on my PC, but my son also uses it (he lives in his own house, so no shared folders...).

Ok, so this is not a commercial application, but for your personal use. This is may not be the typical scenario that SQL Server is designed for.

You say your son also uses the application on is his PC. I assume that his has his own database with his own data?
Ronny Mees 20 Reputation points

2023-07-18T09:57:18.1066667+00:00

It is not a commercial application, only for personal use.

Indeed the application of my son has his own database with his own data.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-18T10:15:25.43+00:00
So there are exactly two "production" instances of this application. And they are already live. Then it is certainly over the top of writing an installation program that deploys the initial version of the database. The only case to consider is when you or your son gets a new computer and wants to move the application and the database to the new box.

In this case you need to install LocalDB and move the database. BACKUP/RESTORE is good for that, and you should have backups running anyway, in case something bad happens to the database, like corruption.

To back up a database you say:

BACKUP DATABASE db TO DISK = 'C:\somepath\db.bak'

To observe is that if you take multiple backup to the same path, the new backup will not be replace the old one, but be appended to the file. That is, a .bak file can include multiple files. This can cause some confusion when you restore, since RESTORE will default to the first and oldest file. Therefore, it is common to make sure that backup files have unique names with a timestamp in the file name.

To restore a database, you say in the simple case

RESTORE DATABASE db FROM DISK = 'C:\somepath\db.bak'

RESTORE will then restore the database to the same path as the original database. It is possible to relocate the database with the MOVE option, but I'm saving that for another day. If you are moving to a new laptop, I would expect the path to be the same. I should add that if the database does not exist, RESTORE will effectively create it.
Ronny Mees 20 Reputation points

2023-07-19T13:05:45.17+00:00

I ended up with adding a helperclass with the methodes checkDbIsAttached, DbAttach, DbBacup.

Upon startup of the app I check if the database is attached, if not it gets attached tot localDB.

Before closing the app I make a backup of the database with a datetimestamp in the name.

All is well, thanks for all your help!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-19T20:20:42.5666667+00:00

Seems like you have something going! I'm only happy to have helped!

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

So why do you use AttachDBFilename in the connection string in the first place? This is a very odd parameter, that I have never understood the point with. The normal connection-string keyword to specify the database is Database or Initial Catalog. (The two are equivalent.)

There error you get is exactly because you dabble with AttachDBFilename.

Share via

Problem accessing localDB when moving application to new folder

1 additional answer

Your answer