IDC资讯中心
实施事务
既然我们已经看了类和成员，让我们来看一下基本的实施情况．接下来的代码是一个简单的情况，使用事务来保证两个存储过程－一个从表中删除库存，另一个增加库存在另个表中，或同时执行，或失败．
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();
}
}
