VB.Net CommittableTransaction

2008-06-20 7:27 pm
我買Visual Basic 2005 資料庫程式設計暨進銷存會計系統實作, 書用MS SQL SERVER作為資料庫, 但我想用ACESS MDB, 請問如何更改, 及以下程式是什麼意思:
Dim tsExplicit As New CommittableTransaction

cnKINGS.EnlistTransaction(tsExplicit)
Me.PurchaseMasterTableAdapter.Connection. _
EnlistTransaction(tsExplicit)
Me.PurchaseDetailTableAdapter.Connection. _
EnlistTransaction(tsExplicit)

tsExplicit.Commit()

sExplicit.Rollback()

回答 (1)

2008-06-21 12:46 pm
✔ 最佳答案
你是問了兩個問題,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()


收錄日期: 2021-04-13 15:43:13
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20080620000051KK00633

檢視 Wayback Machine 備份