Transaction in Text files using OdbcConnection

[billk]

How can i use transaction when accessing simple text files with ADO.NET?

For example i have the following code: (of course is not working

----------------------------------------------------------------------------
------------
Dim conn As OdbcConnection = New OdbcConnection("DSN=testtext")

conn.Open()

Dim myCommand As OdbcCommand = conn.CreateCommand()

Dim myTrans As OdbcTransaction

' Start a local transaction

myTrans = conn.BeginTransaction '<----ERROR: transaction not supported in
text files

' Assign transaction object for a pending local transaction

'myCommand.Transaction = myTrans

Try

myCommand.CommandText = "Update Orders.txt Set ShipCity='Athens' where
customerid ='ALFKI'" '<----ERROR: UPDATE not supported in text files

myCommand.ExecuteNonQuery()

myTrans.Commit()

Catch ex1 As Exception

Try

myTrans.Rollback()

Catch ex As OdbcException

If Not myTrans.Connection Is Nothing Then

End If

End Try

Finally

conn.Close()

End Try

----------------------------------------------------------------------------
--------------------------------------

From what i know INSERT/UPDATE/DELETE are not supported with the text ODBC
driver, so how can i use INSERT/UPDATE/DELETE or Transaction with ADO.NET
when working with text files ??

Any samples or URL'S are welcome.

Thanks

Bill

 

[Paul Clement]

¤ How can i use transaction when accessing simple text files with ADO.NET?
¤
¤ For example i have the following code: (of course is not working
¤
¤ ----------------------------------------------------------------------------
¤ ------------
¤ Dim conn As OdbcConnection = New OdbcConnection("DSN=testtext")
¤
¤ conn.Open()
¤
¤ Dim myCommand As OdbcCommand = conn.CreateCommand()
¤
¤ Dim myTrans As OdbcTransaction
¤
¤ ' Start a local transaction
¤
¤ myTrans = conn.BeginTransaction '<----ERROR: transaction not supported in
¤ text files
¤
¤ ' Assign transaction object for a pending local transaction
¤
¤ 'myCommand.Transaction = myTrans
¤
¤ Try
¤
¤ myCommand.CommandText = "Update Orders.txt Set ShipCity='Athens' where
¤ customerid ='ALFKI'" '<----ERROR: UPDATE not supported in text files
¤
¤ myCommand.ExecuteNonQuery()
¤
¤ myTrans.Commit()
¤
¤ Catch ex1 As Exception
¤
¤ Try
¤
¤ myTrans.Rollback()
¤
¤ Catch ex As OdbcException
¤
¤ If Not myTrans.Connection Is Nothing Then
¤
¤ End If
¤
¤ End Try
¤
¤ Finally
¤
¤ conn.Close()
¤
¤ End Try
¤
¤ ----------------------------------------------------------------------------
¤ --------------------------------------
¤
¤ From what i know INSERT/UPDATE/DELETE are not supported with the text ODBC
¤ driver, so how can i use INSERT/UPDATE/DELETE or Transaction with ADO.NET
¤ when working with text files ??
¤
¤ Any samples or URL'S are welcome.
¤
¤ Thanks

Unfortunately you cannot make modifications to a Text file directly using the Text ISAM driver. Your
only option would be to import to an intermediate format, such as an Access database or Microsoft
Excel Workbook, perform any modifications and then export the data back out to a Text file.

The alternative to ADO.NET would be to use File I/O (System.IO).


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Michael D. Long]

And you expected text files to have transaction support?

You have a few options:

1. Write a Resource Manager for the file system, and the associated ODBC
Driver or OLE DB Provider with transaction support.

2. Perform the following steps:

a) write a new copy of the existing file with your changes, closing both
old and new

b) rename the original file (such as "testtext.old")

c) rename the new file to "testtext"

d) remove "testtext.old" if step c works, otherwise throw an error and
"rollback" your changes