在ADO.NET中使用事務保護數據的完整性(4)

2024-07-10 13:03:25

字體：大中小

來源：轉載

供稿：網友

實施事務

既然我們已經看了類和成員，讓我們來看一下基本的實施情況．接下來的代碼是一個簡單的情況，使用事務來保證兩個存儲過程－一個從表中刪除庫存，另一個增加庫存在另個表中，或同時執行，或失敗．

using system;
using system.drawing;
using system.collections;
using system.componentmodel;
using system.windows.forms;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;

…public void sptransaction(int partid, int numbermoved, int siteid)
{
// create and open the connection.
sqlconnection conn = new sqlconnection();
string connstring = "server=sqlinstance;database=test;"
+ "integrated security=sspi";
conn.connectionstring = connstring;
conn.open();

// create the commands and related parameters.
// cmddebit debits inventory from the warehouseinventory
// table by calling the debitwarehouseinventory
// stored procedure.
sqlcommand cmddebit =
new sqlcommand("debitwarehouseinventory", conn);
cmddebit.commandtype = commandtype.storedprocedure;
cmddebit.parameters.add("@partid", sqldbtype.int, 0, "partid");
cmddebit.parameters["@partid"].direction =
parameterdirection.input;
cmddebit.parameters.add("@debit", sqldbtype.int, 0, "quantity");
cmddebit.parameters["@debit"].direction =
parameterdirection.input;

// cmdcredit adds inventory to the siteinventory
// table by calling the creditsiteinventory
// stored procedure.
sqlcommand cmdcredit =
new sqlcommand("creditsiteinventory", conn);
cmdcredit.commandtype = commandtype.storedprocedure;
cmdcredit.parameters.add("@partid", sqldbtype.int, 0, "partid");
cmdcredit.parameters["@partid"].direction =
parameterdirection.input;
cmdcredit.parameters.add
("@credit", sqldbtype.int, 0, "quantity");
cmdcredit.parameters["@credit"].direction =
parameterdirection.input;
cmdcredit.parameters.add("@siteid", sqldbtype.int, 0, "siteid");
cmdcredit.parameters["@siteid"].direction =
parameterdirection.input;

// begin the transaction and enlist the commands.
sqltransaction tran = conn.begintransaction();
cmddebit.transaction = tran;
cmdcredit.transaction = tran;

try
{
// execute the commands.
cmddebit.parameters["@partid"].value = partid;
cmddebit.parameters["@debit"].value = numbermoved;
cmddebit.executenonquery();

cmdcredit.parameters["@partid"].value = partid;
cmdcredit.parameters["@credit"].value = numbermoved;
cmdcredit.parameters["@siteid"].value = siteid;
cmdcredit.executenonquery();

// commit the transaction.
tran.commit();
}
catch(sqlexception ex)
{
// roll back the transaction.
tran.rollback();

// additional error handling if needed.
}
finally
{
// close the connection.
conn.close();
}
}

// commit the outer transaction.

tran.commit();

}

catch(oledbexception ex)

{

//roll back the transaction.

tran.rollback();

//additional error handling if needed.

}

finally

{

// close the connection.

conn.close();

}

}

上一篇：使用ADO.NET的最佳實踐

下一篇：在ADO.NET中使用事務保護數據的完整性(3)