3个地点的交易范围
本文关键字：交易 范围 3个 | 更新日期: 2023-09-27 18:28:48
我看过很多事务范围示例，大多只显示了2个位置示例。如果我有3个位置，我应该如何将范围放在一起。我试着如下，这是正确的吗？我不明白的另一个问题是，为什么第二个connectString2必须在第一个connectString1之下？
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
using (SqlConnection connection2 = new SqlConnection(connectString2))
{
}
using (SqlConnection connection3 = new SqlConnection(connectString3))
{
}
}
}
设置个人交易
int backUpCentralCopy = 0, backUpCentral = 0;
int rollbackBoolean = 0;
MySqlTransaction transactionLocal = null;
MySqlConnection connectionLocal = null;
transactionConnectionLocal1 callTransactionConnectionLocal1 = null;
try
{
callTransactionConnectionLocal1 = new transactionConnectionLocal1();
connectionLocal = callTransactionConnectionLocal1.localConnection1;
connectionLocal.Open();
transactionLocal = connectionLocal.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection (Local Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection (Local Server Is Down) " + ex.Message);
}
globalConnectionLocal1 myConnect1 = null;
MySqlDataReader myReader1 = null;
MySqlTransaction transactionCentralCopy = null;
MySqlConnection connectionCentralCopy = null;
transactionConnectionCentralCopy1 callTransactionConnectionCentralCopy1 = null;
try
{
myConnect1 = new globalConnectionLocal1();
myConnect1.command.CommandText = "Select " +
"tblUpdateCentralCopy.updateCentralCopyID " +
"From tblUpdateCentralCopy ";
myReader1 = myConnect1.command.ExecuteReader();
if (myReader1.HasRows == true)
{
backUpCentralCopy = 1;
}
else
{
try
{
callTransactionConnectionCentralCopy1 = new transactionConnectionCentralCopy1();
connectionCentralCopy = callTransactionConnectionCentralCopy1.centralCopyConnection1;
connectionCentralCopy.Open();
transactionCentralCopy = connectionCentralCopy.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
backUpCentralCopy = 1;
MessageBox.Show("Error From Database Connection (Central C Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From Database Connection (Central C Is Down) " + ex.Message);
}
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error From UCC Check " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From UCC Check " + ex.Message);
}
finally
{
myReader1.Close();
myConnect1.command.Dispose();
myConnect1.connection1.Close();
}
本节我从数据网格中读取每个值，并相应地进行插入和更新操作
for (int j = 0; j < gridTransfer.RowCount; j++)
{
String mySelectQuery6 = "Select tblProduct.productTotalStock, " +
"tblProduct.productTotalAmount, " +
"tblProduct.productPrice " +
"From tblProduct " +
"Where tblProduct.productID=" + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString());
MySqlDataReader myReader8 = null;
MySqlCommand myCommandLocal11 = new MySqlCommand(mySelectQuery6);
myCommandLocal11.Connection = connectionLocal;
int chTSICBefore = 0;
double chTAICBefore = 0.00, chACICBefore = 0.00;
try
{
myReader8 = myCommandLocal11.ExecuteReader();
while (myReader8.Read())
{
chTSICBefore = Convert.ToInt16(myReader8.GetValue(0).ToString());
chTAICBefore = Convert.ToDouble(myReader8.GetValue(1).ToString());
chACICBefore = Convert.ToDouble(myReader8.GetValue(2).ToString());
}
if (chTSICBefore <= 0 || chTAICBefore <= 0.00 || chACICBefore <= 0.00)
{
MessageBox.Show("Error From Before chTSICBefore = " + chTSICBefore + " And chACICBefore = " + chACICBefore + " And chACICBefore = " + chACICBefore + " For pID = " + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString()));
rollbackBoolean = 1;
break;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal11 mySelectQuery6 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal11 mySelectQuery6 " + ex.Message);
}
finally
{
myReader8.Close();
myCommandLocal11.Dispose();
}
if (chTSICBefore > 0 && chTAICBefore > 0.00 && chACICBefore > 0.00)
{
String myInsertQuery3 = "Insert into tblTransferDetails " +
"Set transferDetailsID = " + transferDetailsID + ", " +
"transferID=" + transferID + ", " +
"outletID = " + globalSettings.settingOutletID + ", " +
"stockID = " + Convert.ToInt32(this.gridTransfer[3, j].Value.ToString()) + ", " +
"productID= " + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString()) + ", " +
"productType = '" + this.gridTransfer[2, j].Value.ToString() + "', " +
"stockQuantity = 1, " +
"stockSIQ = '" + this.gridTransfer[10, j].Value.ToString() + "', " +
"costPrice = " + Convert.ToDouble(this.gridTransfer[12, j].Value.ToString()) + ", " +
"transferPrice = " + Convert.ToDouble(this.gridTransfer[13, j].Value.ToString());
MySqlCommand myCommandLocal12 = new MySqlCommand(myInsertQuery3);
try
{
myCommandLocal12.Connection = connectionLocal;
myCommandLocal12.Transaction = transactionLocal;
myCommandLocal12.ExecuteNonQuery();
totalCost = totalCost + Convert.ToDouble(this.gridTransfer[12, j].Value.ToString());
totalTransferAmount = totalTransferAmount + Convert.ToDouble(this.gridTransfer[14, j].Value.ToString());
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal12 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal12 myInsertQuery3" + ex.Message);
}
finally
{
myCommandLocal12.Dispose();
}
if (backUpCentralCopy == 0)
{
MySqlCommand myCommandCentralCopy7 = new MySqlCommand(myInsertQuery3);
try
{
myCommandCentralCopy7.Connection = connectionCentralCopy;
myCommandCentralCopy7.Transaction = transactionCentralCopy;
myCommandCentralCopy7.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy7 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy7 myInsertQuery3" + ex.Message);
}
finally
{
myCommandCentralCopy7.Dispose();
}
}
else
{
String myInsertQueryReplace3 = myInsertQuery3.Replace("'", "''");
MySqlCommand myCommandCentralCopy7 = new MySqlCommand("Insert into tblUpdateCentralCopy SET updateCentralCopyQuery='" + myInsertQueryReplace3 + "'");
try
{
myCommandCentralCopy7.Connection = connectionLocal;
myCommandCentralCopy7.Transaction = transactionLocal;
myCommandCentralCopy7.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy7 myInsertQueryReplace3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy7 myInsertQueryReplace3" + ex.Message);
}
finally
{
myCommandCentralCopy7.Dispose();
}
}
if (backUpCentral == 0)
{
MySqlCommand myCommandCentral4 = new MySqlCommand(myInsertQuery3);
try
{
myCommandCentral4.Connection = connectionCentral;
myCommandCentral4.Transaction = transactionCentral;
myCommandCentral4.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentral4 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentral4 myInsertQuery3" + ex.Message);
}
finally
{
myCommandCentral4.Dispose();
}
}
else
{
String myInsertQueryReplace3 = myInsertQuery3.Replace("'", "''");
MySqlCommand myCommandCentral4 = new MySqlCommand("Insert into tblUpdateCentral SET updateCentralQuery='" + myInsertQueryReplace3 + "'");
try
{
//myCommandCentralDB3.CommandText = "Insert into tblUpdateCentral SET updateCentralQuery='" + myInsertQuery1 + "'";
myCommandCentral4.Connection = connectionLocal;
myCommandCentral4.Transaction = transactionLocal;
myCommandCentral4.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentral4 myInsertQueryReplace3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentral4 myInsertQueryReplace3" + ex.Message);
}
finally
{
myCommandCentral4.Dispose();
}
}
TransactionScope与当前执行的线程有上下文关系，直到它被释放。它应该在最外层的using语句中（您已正确指出）。
在释放作用域之前，可以在当前线程上的任何位置创建/释放连接。这包括其他方法甚至其他类和程序集中的声明。
因此，应该将using连接封装在要实例化和处理它们的位置。using块的用途不会因为您将其封装在TransactionScope中而改变。
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
}
using (SqlConnection connection2 = new SqlConnection(connectString2))
{
}
using (SqlConnection connection3 = new SqlConnection(connectString3))
{
}
scope.Complete(); // call this otherwise the transaction will be rolled back
}
请注意，您也可以合法地嵌套作用域（尽管这可能很快变得令人困惑）。
如果您还没有注意到，一旦在同一范围内创建多个连接，事务就会自动升级为使用分布式事务协调器（DTC）。DTC很容易配置。
使用DependentTransaction也可以进行多线程事务。