在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)