1) Надо установить Microsoft OLE DB Provider for Visual FoxPro 9.0 (vfpoledb.exe)
с микрософта

2) Пример кода ниже. Все дбф в C:\MACIR\Data, имена соотв. именам таблиц.

Dim conn As New OleDbConnection("Provider=VFPOLEDB.1; Data Source=C:\MACIR\Data;")
Dim Command As New OleDbCommand
Command.Connection = conn
Command.CommandText = "SELECT ID,FirstName,LastName,Company,Address,City,State,ZipCode,Country,Phone,Fax " + _
"FROM Subs WHERE FirstName='" & UCase(cAdr.FirstName) & "' AND LastName='" & UCase(cAdr.LastName) & "'"
conn.Open()
Dim DBFReader As OleDbDataReader
DBFReader = Command.ExecuteReader()
Dim MACIRAdr As Address
If DBFReader.HasRows Then
While DBFReader.Read()
MACIRAdr.id = Trim(DBFReader.GetValue(0))
MACIRAdr.FirstName = Trim(DBFReader.GetString(1))
MACIRAdr.LastName = Trim(DBFReader.GetString(2))))
MACIRAdr.Zip = Trim(DBFReader.GetString(7))
If MACIRAdr.FirstName = UCase(cAdr.FirstName) _
And MACIRAdr.LastName = UCase(cAdr.LastName) _
And MACIRAdr.Zip = UCase(cAdr.Zip) Then
DBFReader.Close()
Return MACIRAdr.id 'Same user
End If
End While
DBFReader.Close()
Else
'create user in MACIR
'get next SUBID to create new subscriber
DBFReader.Close()
Command.CommandText = "Select ID FROM SUBID"
DBFReader = Command.ExecuteReader()
DBFReader.Read()
Dim SUBID As Integer = Trim(DBFReader.GetValue(0))
DBFReader.Close()
'increment SUBID
Command.CommandText = "UPDATE SUBID SET ID=" & SUBID + 1
Command.ExecuteNonQuery()
'insert into SUBS
If cAdr.Country = "CA" Then
cAdr.Country = "CAN"
End If
Command.CommandText = "Insert into SUBS (" + _
"ID,SALUTE,FirstName,LastName,TITLE,Company," + _
"COMPCODE,MAILSTOP,Address,City,State," + _
"ZipCode,Country,Phone,Fax," + _
"FSA,COMMENTS,BADADDR,RENTLIST) VALUES " + _
"(" & SUBID & ",'','" & cAdr.FirstName & "','" & cAdr.LastName & "','','" & cAdr.Company & "'," + _
"'','','" & cAdr.Address1 & cAdr.Address2 & "','" & cAdr.City & "','" & cAdr.State & "'," + _
"'" & cAdr.Zip & "','" & cAdr.Country & "','" & cAdr.Phone & "','" & cAdr.Fax & "'," + _
"0,'','',?)"
Dim logical As Object
Command.Parameters.Add("@RENTLIST", logical).Value = True
Try
Command.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.ToString)
End Try
...

DBF с OleDbProvider=VFPOLEDB.1
После "успешного" отката захожу в SUBID.DBF и вижу там неоткатившееся 1111, вместо исходного 1756. Может кто знает в чем дело.

Огромное спасибо!

Dim myConnection As New OleDbConnection("Provider=VFPOLEDB.1;" + _
"Data Source=C:\MACIR\Data;")
myConnection.Open()
Dim myCommand As OleDbCommand = myConnection.CreateCommand()
Dim myTrans As OleDbTransaction
' Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)
' Assign transaction object for a pending local transaction
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
Try
myCommand.CommandText = "UPDATE SUBID SET ID=1111"
myCommand.ExecuteNonQuery()
'Изначально ошибочная команда для выполнения Rollback:
myCommand.CommandText = "Insert into SUBID (ID) VALUES ('103')"
myCommand.ExecuteNonQuery()
myTrans.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As OleDbException
If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Console.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data.")
Console.WriteLine("Neither record was written to database.")
Finally
myConnection.Close()
End Try