Problem in copying SQLite file in C# code

[mahdaeng]

I have a little Windows application written in C# with a SQLite back-
end. I'm using the System.Data.SQLite provider.

One of the features the application provides is a database back-up,
which just basically copies the S3DB file to a specified location. See
the code below:

//------------------------------------------------
System.IO.File.Copy(srcPath, destPath, true);
//------------------------------------------------

The file is copied without any problems, but when the application
attempts to access the original S3DB file again, the following
exception is thrown:

--------------------------------------------------
System.Data.SQLite.SQLiteException was unhandled
Message="Unable to open the database file"
Source="System.Data.SQLite"
ErrorCode=-2147467259
StackTrace:
....etc...
--------------------------------------------------

If I shut the application down and then start it up again, I can
access the database again with no problems.

Suspicious of what the File.Copy() method might be doing behind the
scenes, I tried using the following approach instead to hopefully
ensure that the files were being released:

//------------------------------------------------
using (FileStream fsSrc = new FileStream(srcPath, FileMode.Open))
{
//Get the source length once so we don't have to keep retrieving it
later
int srcLen = (int)fsSrc.Length;

//Create the destination file (this will overwrite the destination
file if it already exists)
using (FileStream fsDest = File.Create(destPath, srcLen))
{
//Buffer the contents of the source file
Byte[] buffer = new Byte[srcLen];
fsSrc.Read(buffer, 0, srcLen);

//Write buffered contents to the new file
fsDest.Write(buffer, 0, srcLen);
}
}
//------------------------------------------------

Unfortunately, the same problem occurs. I even tried executing the
code in its own thread, but with the same results.

Any ideas why this is happening and how to fix it?

Thank you.

[Andrew Faust]

Are you using the same connection to the SQL Lite database? Or are you
creating new objects?

I would recommend calling Dispose() on your original connection object
before doing the copy to see if it makes a difference.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com
"mahdaeng" <ma******@gmail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...

>I have a little Windows application written in C# with a SQLite back-
end. I'm using the System.Data.SQLite provider.

One of the features the application provides is a database back-up,
which just basically copies the S3DB file to a specified location. See
the code below:

//------------------------------------------------
System.IO.File.Copy(srcPath, destPath, true);
//------------------------------------------------

The file is copied without any problems, but when the application
attempts to access the original S3DB file again, the following
exception is thrown:

--------------------------------------------------
System.Data.SQLite.SQLiteException was unhandled
Message="Unable to open the database file"
Source="System.Data.SQLite"
ErrorCode=-2147467259
StackTrace:
...etc...
--------------------------------------------------

If I shut the application down and then start it up again, I can
access the database again with no problems.

Suspicious of what the File.Copy() method might be doing behind the
scenes, I tried using the following approach instead to hopefully
ensure that the files were being released:

//------------------------------------------------
using (FileStream fsSrc = new FileStream(srcPath, FileMode.Open))
{
//Get the source length once so we don't have to keep retrieving it
later
int srcLen = (int)fsSrc.Length;

//Create the destination file (this will overwrite the destination
file if it already exists)
using (FileStream fsDest = File.Create(destPath, srcLen))
{
//Buffer the contents of the source file
Byte[] buffer = new Byte[srcLen];
fsSrc.Read(buffer, 0, srcLen);

//Write buffered contents to the new file
fsDest.Write(buffer, 0, srcLen);
}
}
//------------------------------------------------

Unfortunately, the same problem occurs. I even tried executing the
code in its own thread, but with the same results.

Any ideas why this is happening and how to fix it?

Thank you.

[mahdaeng]

On Oct 26, 5:46 pm, "Andrew Faust" <[e-mail]:

Are you using the same connection to the SQL Lite database? Or are you
creating new objects?

I would recommend calling Dispose() on your original connection object
before doing the copy to see if it makes a difference.

--
Andrew Faust
andrew[at]andrewfaust.comhttp://www.andrewfaust.com

Thank you for your response, Andrew. I'm instantiating the connection
object on an as-needed basis, and I'm using the "using" keyword when I
do, so there shouldn't be any problems with the connection object.
Just to prove the point, I tried your suggestion and there wasn't any
change in the application's behavior.

At this point, I don't know if the culprit is System.IO or SQLite.

Thank you.

[Andrew Faust]

My gut feel is that it's a bug with the SQLite data provider. It sounds
like their implementation of Dispose isn't cleaning itself up correctly.
Perhaps they forgot to close a file. There are a series of tools you can
download from Microsoft
(http://www.microsoft.com/technet/sys...s/default.mspx) that lets you
delve in and see what's going on.

There is a tool called Filemon that will let you see all file activity in
real time. You can use that to find out what (if any) SQLite files are
still open. If that turns out to be the case you can log an issue in the
SQLite bug tracker. In addition to this, I recommend you actually fix the
issue and submit it back to the SQLite project. It's a good way to ensure
your bug gets fixed, and a way to give something back to the community.

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com
"mahdaeng" <ma******@gmail.comwrote in message
news:11*********************@q5g2000prf.googlegrou ps.com...

On Oct 26, 5:46 pm, "Andrew Faust" <[e-mail]:

>Are you using the same connection to the SQL Lite database? Or are you
creating new objects?

I would recommend calling Dispose() on your original connection object
before doing the copy to see if it makes a difference.

--
Andrew Faust
andrew[at]andrewfaust.comhttp://www.andrewfaust.com

Thank you for your response, Andrew. I'm instantiating the connection
object on an as-needed basis, and I'm using the "using" keyword when I
do, so there shouldn't be any problems with the connection object.
Just to prove the point, I tried your suggestion and there wasn't any
change in the application's behavior.

At this point, I don't know if the culprit is System.IO or SQLite.

Thank you.

[Samuel R. Neff]

The generally recommended practice for backing up a SQLite database is
to first issue a "BEGIN EXCLUSIVE" transaction, do your backup, and
then rollback the transaction.

I haven't tried this with the System.Data.SQLite wrapper but it's
worth a try.

Another thing that could be interfering is connection pooling.. are
you using the latest wrapper with connection pooling turned on? If so
try turning it off.

And if you still have trouble you'll probably get a better response in
the SQLite specific forum here.

http://sqlite.phxsoftware.com/forums/default.aspx

HTH,

Sam

------------------------------------------------------------
We're hiring! B-Line Medical is seeking .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.