OleDb delays commits and unlocks?

  • Thread starter Thread starter John W.
  • Start date Start date
J

John W.

I've seen other posts with similar problems, but no solutions. I have two
hair-pulling dilemmas that have been frustrating me for months now. Is there
no solution? (C#.NET, Framework 2.0, Jet OleDb 4.0)

The general goal is to read one MDB file and copy relevant info into another
MDB file (copied from an empty template), replacing the old file with the new
file. DataAdapters are used only to read (or re-read) the data from the file
into the in-memory DataTable. All data INSERTs and UPDATEs are done with
OleDbCommand.ExecuteNonQuery().

Problem 1: To retain the original version of the file, the process requires
two rename operations. The first renames the original file. The second
renames the new file to have the original file's name. Half the files I
process do this OK. The other half fail on one of the renames, claiming that
the file is still locked by another process. Checking the directory just
prior to the rename attempt confirms that the corresponding LDB file still
exists when the rename fails and doesn't exist when it succeeds. I've gone
so far as to put all the data copying into a separate Main and invoking it
with a Process.Start() so that the locking process has to be terminated
before I attempt the rename. No dice. Aaaaargh!

Problem 2: In the last-called method of the copy process, one new record is
written to each of two empty tables. After that, a message is written to a
log file, the method returns, and the OleDbConnection is closed. All of that
occurs in about a dozen lines of code. This works almost all the time,
except on one of my test files. With that file, the first inserted row
appears in the output mdb file, but the second one does not! There is
nothing particularly unusual about this file; other test files have identical
structures. The tables involved are new tables, not present in the source
data file, and every test file requires that these two rows be created. No
errors are reported by the INSERTs or any other statement; it just doesn't
get committed to the table. Double-Aaaaargh!!
 
Johh,

Why not simple use a DataAdapter to update as you tells that it is a single
non related table than it should be a piece of cake.

(You can then probably simple use the commandbuilder)

Cor
 
I avoided using DataAdapters since (1) the operations I needed were so basic,
(2) I don't feel very comfortable with them, and (3) with the exception of
two tables, I don't need to have the updated tables in memory--only in the
file. (Those two tables are used to reconcile several foreign key columns in
various tables.) CommandBuilder lost favor with me a long time ago when I
found that it couldn't handle half of what I wanted it to do. (I don't
remember the specifics, but I do remember the disappointment.) The two tables
in Problem 2 should be among the simplest to handle. One of the new records
is always the same, and the other varies only slightly. Two INSERT commands
and I'm done, without the overhead of the DataAdapter.
 
John,

From your description I was not sure if you had to update datarows or only
to insert.
As you state it now probably a simple merge can do the job (as long as you
use the same tablenames and the same columnnames).

Why avoid simple tools for simple jobs.

Like you write it, it seems to me that you want to use simple tools for
difficult problems and more basic tools for difficult jobs, as I try to say
it in an anology.

It looks that you are using a stappler to build a house and a hammer with
nails to put some papers together.

Cor
 
I accept that rebuke; your assessment may be true, but I have
left significant complexity out of this discussion because of its
tendency to hide the real problem that I am fighting. (Case in
point: the table names and column names are not the same
between the files.)

Yesterday, we "stumbled" onto evidence that convinces me my
two problems are just different manifestations of the same i
ssue: the lazy write characteristic of the JET engine. If I can turn
that off while this process runs, both problems will go away. I
can find VB examples of ways to do it, but they don't seem to have
C# counterparts in the OleDb family of classes. The
JRO.JetEngineClass.ResetCache() method is available for
refreshing the read cache, but doesn't have an equivalent for the
write cache--which would also solve my problem. Got any
suggestions on this?
 
Back
Top