Sql数据访问方法oracle
本文关键字:oracle 方法 访问 数据 Sql | 更新日期: 2023-09-27 18:11:45
我有一个基本的dll,其中包含常见的数据库方法,如getconnectionstring,executescalar,executenonquery, doselect等等。我正在工作(vb.net或c#)和oracle。现在我需要为一个事务写一个方法,我可能有2-3个sql,只有当它们都工作时才应该提交。我想知道该怎么做。我需要sql1返回某种类型的唯一字段,用于插入到sql2和/或sql3。
private sub executeTransaction(byval sql1 as string,byval sql2 as string,byval sql3 as string)
code to begin transaction
execute sql1 returning unique id to a local field 'since this id may be different based on sql, how to handle this?
execute sql2
execute sql3 optional
if exception rollback
commit on finally block and then close the connection object
end sub
我正在寻找建议/最佳实践来编写上述方法。
模式为:
- 从您的连接创建一个事务
- 将事务分配给每个命令
- 完成后提交/回滚
using(OracleConnection conn = new OracleConnection())
{
using(DbTransaction transaction = conn.BeginTransaction())
{
try
{
// .... create a command
cmd1 = new OracleCommand({sql to get a value});
cmd1.Transaction = transaction;
cmd1.ExecuteScalar();
// .... create another command
cmd1 = new OracleCommand({sql to update a value});
cmd2.Transaction = transaction;
cmd2.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception err)
{
transaction.Rollback();
throw err; // or just throw; to preserve the deeper stack trace
}
}
}