✔ 最佳答案
你是問了兩個問題,1. 轉用 Access mdb,2. 程式的意思。
問題 1
轉用 OleDb namespace 後所有 SqlClient的ADO.Net objects 都要改為 OleDb
OleDbConnection
OleDbTransaction
OleDbDataAdapter
OleDbCommand
OleDbParameter
OleDbDataReader
OleDbCommandBuilder 等等
OleDbConnection 也支援 EnlistTransaction 方法,絶大部份SqlClient 有的物件、方法、屬性、事件 OleDb 也有,當然 connection string 就要改為
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\mydb.mdb
Access 不可以執行太復雜的 sql statement,你列出的程式應該可以執行,不過也要試過至知。
問題 2
PurchaseMasterTableAdapter, PurchaseDetailTableAdapter 應該不是 DataAdapter, DataAdapter 是沒有 Connection 這屬性的,它們大概是 Command。
EnlistTransaction 用於 distributed transaction,你的程式開了三個 connection(也可能是同一個 connection),如果這三個 connection 分別有三個 update Command,要確保三個 update都可以完成才寫落 Database 就會用上 distributed transaction。
2008-06-26 04:14:56 補充:
一個簡單方法是用 OleDbTransaction 而不用 EnlistTransaction,例子如下:
Dim conn As OleDbConnection = New OleDbConnection(connection_string)
conn.Open()
Dim txn As OleDbTransaction = conn.BeginTransaction()
Dim cmd As OleDbCommand = conn.CreateCommand()
cmd.Transaction = txn
cmd.CommandText = sql_statement_1
2008-06-26 04:15:05 補充:
Try
cmd.ExecuteNonQuery()
cmd.CommandText = sql_statement_2
cmd.ExecuteNonQuery()
txn.Commit()
Catch eX As System.Exception
txn.Rollback()
End Try
conn.Close()
2008-06-26 04:31:16 補充:
EnlistTransaction 方法用於多個 connection,例子如下:
Dim txn As CommittableTransaction = New CommittableTransaction()
Dim conn1 As OleDbConnection = New OleDbConnection(connection_string_1)
Dim conn2 As OleDbConnection = New OleDbConnection(connection_string_2)
2008-06-26 04:31:40 補充:
conn1.Open()
conn1.EnlistTransaction(txn)
Dim cmd1 As OleDbCommand = conn1.CreateCommand()
cmd1.CommandText = sql_statement_1
conn2.Open()
conn2.EnlistTransaction(txn)
Dim cmd2 As OleDbCommand = conn2.CreateCommand()
cmd2.CommandText = sql_statement_2
2008-06-26 04:31:55 補充:
Try
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
txn.Commit()
Catch eX As System.Exception
txn.Rollback()
End Try
conn1.Close()
conn2.Close()