[RESOLVED] ERROR: The process cannot access the file . . .

[Muddy]

Well it's been a while. It's awesome to see this place still thriving after all of these years.
I've got an error I cannot figure out after much online searching. Ive tried all sorts of things but am at a loss.Hoping someone here can help. The error I am getting is this
mscorlib.dll
Additional information: The process cannot access the file 'C:\dbfile.mdf' because it is being used by another process.

I am using Visual Studio 2017 VB.net. My Imports are:

Imports System
Imports System.IO
Imports System.IO.Compression
Imports Microsoft.VisualBasic
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient

The offending code:

 ' NOTE THAT I EDITED THE FILENAME DOWN TO SOMETHING SIMPLE. IT ISNT REALLY IN THE ROOT DIR. 
'I HAVE FULL PERMISSIONS WHERE IT RESIDES AND CAN INDEED COPY/DELETE/ETC IT PROGRAMATICALLY WHERE IT RESIDES
           connectionString = "Data Source=(localdb)\v11.0;Integrated Security=true;AttachDBFileName="C:\dbfile.mdf"
                connection = New System.Data.SqlClient.SqlConnection(connectionString)
                connection.Open()
                    queryString = "SELECT * FROM dbo.myTable;"
                    adapter = New SqlDataAdapter(queryString, connection)
                adapter.UpdateCommand = New SqlCommandBuilder(adapter).GetUpdateCommand()

                runParams = New DataSet()
                    adapter.Fill(RunParams, "RunParams")
                    runParams.Tables(0).Rows(0).Item(3) = 1
                adapter.Update(runParams, "RunParams")

                connection.Close()

                adapter.UpdateCommand.Dispose()
                adapter.Dispose()
                    runParams.Dispose()


                connection.Dispose()
                'ALL WORKS PERFECT TO THIS POINT, BUT AFTER THIS POINT, IF I TRY TO DO ANYTHING WITH THE MDF FILE (COPY,MOVE, WHATEVER) I GET THE ERROR

[Zvoni]

1) Is the "*.mdf" pure happenstance, or why do you access a SQL Server-File directly (instead through the Server-Instance)?
2) The File is in the C-Rootdirectory (where usually no user has any rights whatsoever)?

[jmcilhinney]

That's as it should be. The SqlConnection you create is a high-level object. The actual connection to the database is at a lower level and it remains open for some time after you close the SqlConnection. By doing so, any future connections you make in code are more efficient. If you exit the app or just wait long enough without connecting to the database, the file will be released. Not that you should do so under most circumstances but I think that disabling connection pooling may prevent that happening, although I'm not 100% sure. Without connection pooling, multiple high-level connections will not use the same low-level connection, so I would think that keeping it open would be pointless.

[Muddy]

Thanks Zvoni
1) I am looping through hundreds of mdf files inside a folder and changing a value in each of them. I am very new to vb.net db programming. Is there a better way to do this?
2) No the file isn't really in the C directory. I just edited out the really long path for brevity and privacy

[jmcilhinney]

1) Is the "*.mdf" pure happenstance, or why do you access a SQL Server-File directly (instead through the Server-Instance)?

MDF files can be attached on-demand, which makes them application-specific. It allows you to deploy a data file with your application, rather than having to rely on a database being created separately.

That said, the way that that is usually done is that the MDF file is added to the project via the Solution Explorer and that source file is then copied into the output directory on a build. It's then the copy on the output directory that is accessed by the application at run time or deployed with the application when it's published. This allows you to mess with the test database as much as you like while keeping the source database clean, ready to be copied and deployed with the app. That's how it generally ought to be done, in which case you would use "|DataDirectory|" as the folder path in the connection string.

[Muddy]

Thanks jmcilhinney . . . Im going to try some more things based on your comments.

[jmcilhinney]

I am looping through hundreds of mdf files inside a folder and changing a value in each of them.

If you're connecting to each file once then connection pooling really would be pointless, so you can safely disable it. I think that you can set Pooling to false in the connection string to do that.

[sapator]

I would also try a "USING" rather that opening, closing connections. I'm not sure if that will make any difference but it's seems more appropriate.

[Muddy]

If you're connecting to each file once then connection pooling really would be pointless, so you can safely disable it. I think that you can set Pooling to false in the connection string to do that.

THAT DID IT!!!!!! Much thanks to you, sir!

connectionString = "Data Source=(localdb)\v11.0;Integrated Security=true;Pooling=False;AttachDBFileName=C:\dbfile.mdf"

EDIT TO NOTE: THIS WASNT WORKING EVERY TIME SO I IMPLEMENTED THE FOLLOWING WAIT FUNCTION AFTER CLOSE AND IT SEEMS TO BE STABLE NOW.

Private Sub wait(ByVal seconds As Integer)
    For i As Integer = 0 To seconds * 100
        System.Threading.Thread.Sleep(10)
        Application.DoEvents()
    Next
End Sub

[sapator]

How about try it with "using"?

[Muddy]

good thought sapator . . . I might give that a shot once I get back on my timeline. Thanks much for your suggestion

[jmcilhinney]

How about try it with "using"?

A Using statement is good practice but all that does is implicitly call Dispose on the SqlConnection object. It won't affect the underlying database connection.

[jmcilhinney]

THAT DID IT!!!!!! Much thanks to you, sir!

connectionString = "Data Source=(localdb)\v11.0;Integrated Security=true;Pooling=False;AttachDBFileName=C:\dbfile.mdf"

EDIT TO NOTE: THIS WASNT WORKING EVERY TIME SO I IMPLEMENTED THE FOLLOWING WAIT FUNCTION AFTER CLOSE AND IT SEEMS TO BE STABLE NOW.

Private Sub wait(ByVal seconds As Integer)
    For i As Integer = 0 To seconds * 100
        System.Threading.Thread.Sleep(10)
        Application.DoEvents()
    Next
End Sub

Use Task.Delay for an equivalent to Thread.Sleep that won't freeze the UI.