How to detach LocalDB file (SQL Server Express) in code

When using LocalDB.mdf files in a deployment, you will often want to move, delete, or archive the database file. It is imperative to separate this file first, because simply deleting it will cause errors, because LocalDB still saves it .

So, how is the LocalDB.mdf file disabled in the code?

+6
source share
3 answers

I had to collect the answer from several places, so I will write it here: Mind, manually deleting the .mdf file from Visual Studio, you can manually delete it before disconnecting, going through the SQL Server Object Explorer.

''' <summary> ''' Detach a database from LocalDB. This MUST be done prior to deleting it. It must also be done after a inadvertent (or ill advised) manual delete. ''' </summary> ''' <param name="dbName">The NAME of the database, not its filename.</param> ''' <remarks></remarks> Private Sub DetachDatabase(dbName As String) Try 'Close the connection to the database. myViewModel.CloseDatabase() 'Connect to the MASTER database in order to excute the detach command on it. Dim connectionString = String.Format("Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True") Using connection As New SqlConnection(connectionString) connection.Open() Dim cmd = connection.CreateCommand '--Before the database file can be detached from code the workaround below has to be applied. 'http://web.archive.org/web/20130429051616/http://gunnalag.wordpress.com/2012/02/27/fix-cannot-detach-the-database-dbname-because-it-is-currently-in-use-microsoft-sql-server-error-3703 cmd.CommandText = String.Format("ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", dbName) cmd.ExecuteNonQuery() '-- '--Now detach cmd.CommandText = String.Format("exec sp_detach_db '{0}'", dbName) cmd.ExecuteNonQuery() '-- End Using Catch ex As Exception 'Do something meaningful here. End Try End Sub 
+8
source

I have the same problem and I was thinking about how to deal with it.

There are 2 approaches.

(1) Disconnect at the end (or during) the database

I did not find a way to close the connection in LinqToSQL, but in fact it is not needed. Just execute the following code:

 var db = @"c:\blablabla\database1.mdf"; using (var master = new DataContext(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True")) { master.ExecuteCommand(@"ALTER DATABASE [{0}] SET OFFLINE WITH ROLLBACK IMMEDIATE", db); master.ExecuteCommand(@"exec sp_detach_db '{0}'", db); } 

and make sure nothing tries to execute the db request after (or you reconnect it).

(2) Disconnect at startup

Before you connect to db , disconnecting will be as simple as:

 var db = @"c:\blablabla\database1.mdf"; using (var master = new DataContext(@"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True")) master.ExecuteCommand(@"exec sp_detach_db '{0}'", db); 

This costume is very suitable for my needs, because I do not care about the delay to launch the application (because in this case I will have to bind to db always), but it will fix any type

System.Data.SqlClient.SqlException (0x80131904): Database 'c: \ blablabla \ database1.mdf' already exists. Choose a different database name.

which occurs if the database file is deleted and you try to create it programmatically

 // DataContext if (!DatabaseExists()) CreateDatabase(); 

Another way

You can also run the sqllocaldb command-line sqllocaldb as follows:

  var start = new ProcessStartInfo("sqllocaldb", "stop v11.0"); start.WindowStyle = ProcessWindowStyle.Hidden; using (var stop = Process.Start(start)) stop.WaitForExit(); start.Arguments = "delete v11.0"; using (var delete = Process.Start(start)) delete.WaitForExit(); 

It will stop the server by canceling all databases. If you have another application using localDB, the next time they will experience a delay when trying to execute the request.

+8
source

Here is my solution for EntityFramework Core 1.0

As you can see, the database name can be used with the full path to the file.

 var dbf = fileDlg.FileName; var options = new DbContextOptionsBuilder(); options.UseSqlServer( $@ "Server=(localdb)\mssqllocaldb;Initial Catalog=master;MultipleActiveResultSets=False;Integrated Security=True"); using (var master = new DbContext(options.Options)) { master.Database.ExecuteSqlCommand($"ALTER DATABASE [{dbf}] SET OFFLINE WITH ROLLBACK IMMEDIATE"); master.Database.ExecuteSqlCommand($"exec sp_detach_db '{dbf}'"); } 
+1
source

Source: https://habr.com/ru/post/943745/


All Articles