C# File + DB Transaction

[=?Utf-8?B?Q2hyaXMgRmluaw==?=]

I have the following requirements and am looking to make this into a c#
transaction.

1. Read from a file into a memory stream
2. Insert the stream into a DB
3. Delete the file

Is it possible to perform a transaction on both a File action and a DB
insert?

A transaction will ensure that if an error occurs in step 3, the insert will
not commit, etc. All three steps must succeed, otherwise the transaction
should rollback and notify of an error.

Any assistance is appreciated.

Thanks

[*Lysander*]

Chris Fink schrieb:

1. Read from a file into a memory stream
2. Insert the stream into a DB
3. Delete the file

If you want to have all of that in ONE transaction...
You can use the transaction-object provided by your database provider
and wrap the commit()/rollback() into a try/catch.

With the Firebird-Provider it would look similar to this:

(Conn01 is a valid and open connection...)

FBTransaction Transaction1 = Conn01.BeginTra nsaction();

try
{
... read file into memory stream
... Insert the stream into DB
... Delete the file
Transaction1.Co mmit()
}
catch
{
Transaction1.Ro llback()
... show errormessages
}
I would consider, though, to put at least the "read file into stream"-
part outside the database transaction. Why bother the database with a
transaction if you probably can't read or even find the file.

[=?UTF-8?B?QXJuZSBWYWpow7hq?=]

Chris Fink wrote:

I have the following requirements and am looking to make this into a c#
transaction.

1. Read from a file into a memory stream
2. Insert the stream into a DB
3. Delete the file

Is it possible to perform a transaction on both a File action and a DB
insert?

A transaction will ensure that if an error occurs in step 3, the insert will
not commit, etc. All three steps must succeed, otherwise the transaction
should rollback and notify of an error.

BEGIN
INSERT
if delete file ok then
COMMIT
else
ROLLBACK
end if

may be good enough if you are sufficient sure that the DB transaction
will commit.

But if you really need a single transaction, then you will need:
- a file system that supports XA transactions
- a transaction manager to do 2PC

I would seriously consider the third option of having logic that
checks whether the file has already been inserted before doing
INSERT and then just do your 3 steps after that.

Arne