/// <summary>
/// 执行一个OracleCommand(返回一个结果OracleDataReader)
/// </summary>
/// <param name="connection">有效的 OracleConnection</param>
/// <param name="transaction">有效的 OracleTransaction, or 'null'</param>
/// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">以一个数组的形式返回OracleParameters </param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleProvider</param>
/// <returns></returns>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
OracleDataReader dr = null;
try
{
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
// dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
dr = cmd.ExecuteReader();
}
if (transaction != null)
{
transaction.Commit();
}
return (OracleDataReader)dr;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
}
/// <summary>
///执行一个命令，没有返回数据集，只返回影响记录数
/// </summary>
/// <remarks>
/// 例如：
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">OracleParameter[]</param>
/// <returns>影响记录数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
int returnInt = cmd.ExecuteNonQuery();
if (transaction != null)
{
transaction.Commit();
}
return returnInt;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
return 0;
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollbackTransaction(OracleConnection connObjection)
{
if (Transaction != null && Transaction.Connection == connObjection)
{
try
{
Transaction.Rollback();
Close(connObjection);
Transaction = null;
if (connObjection != null)
{
if (connObjection.State == ConnectionState.Open)
{
Close(connObjection);
}
}
}
catch (OracleException e)
{
Transaction.Rollback();
Close(connObjection);
Transaction = null;
LogHelper.WriteLog("MsOracle.RollbackTransaction(OracleConnection connObjection)", null, e);
throw new System.Exception(e.Message);
}
}
}
/// <summary>
/// 执行一个OracleCommand命令并返回结果集
/// </summary>
/// <remarks>
/// 例如：
/// DataTable ds = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataTable ExecuteTable(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
OracleDataAdapter da;
DataSet ds = new DataSet();
try
{
da = new OracleDataAdapter(cmd);
da.Fill(ds);
if (transaction != null)
{
transaction.Commit();
}
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
}
//return the dataset
return ds.Tables[0];
}
/// <summary>
/// 执行一个OracleCommand命令并返回结果集;执行完成没有关闭OracleConnection连接,需要手动关闭
/// </summary>
/// <remarks>
/// 例如：
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">命令类型:SQL语句或存储过程</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
OracleDataAdapter da;
DataSet ds = new DataSet();
try
{
da = new OracleDataAdapter(cmd);
da.Fill(ds);
if (transaction != null)
{
transaction.Commit();
}
}
catch (System.Exception e)
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
throw new System.Exception(e.Message, e);
}
//return the dataset
return ds;
}
/// <summary>
///返回结果集的第一行第一列
/// </summary>
/// <remarks>
/// 例如：
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程T-OleDb的名称或命令</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回结果集的第一行第一列</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
object obj = cmd.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
}
return obj;
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
return null;
}
}
/// <summary>
/// 回滚事务
/// </summary>
/// <param name="transaction">事务对象</param>
/// <param name="connection">连接对象</param>
public static void Rollback(OracleTransaction transaction, OracleConnection connection)
{
transaction.Rollback();
connection.Close();
}
public override void Rollback()
{
trx.Rollback();
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回DataSet</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da;
DataSet ds = new DataSet();
try
{
da = new OracleDataAdapter(cmd);
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
if (transaction != null)
{
transaction.Commit();
}
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
}
//return the dataset
return ds;
}
/// <summary>
///返回结果集的第一行第一列
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-OleDb command</param>
/// <param name="commandParameters">params OracleParameter[]</param>
/// <returns>返回结果集的第一行第一列</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
try
{
//execute the command & return the results
object obj = cmd.ExecuteScalar();
if (transaction != null)
{
transaction.Commit();
}
return obj;
}
catch
{
if (transaction != null)
{
// Rollback the transaction
transaction.Rollback();
}
return null;
}
}
public ActionResult Renewinfo(RenewalViewModel rvm, string[] doc, string[] rgno, string[] issuedate, string[] expdate, HttpPostedFileBase[] files)
{
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
System.Data.OracleClient.OracleConnection conn = ConnectBMS.Connection();
cmd.Connection = conn;
System.Data.OracleClient.OracleTransaction bmsTransaction = conn.BeginTransaction();
cmd.Transaction = bmsTransaction;
cmd.CommandText = "update BONDSTATUS set STATUS=:STATUS,BSDATE=:BSDATE,SUBMITTEDBYNM=:SUBMITTEDBYNM,REMARKS=:REMARKS"
+ " where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("STATUS", Request["STATUS"]);
if (!String.IsNullOrEmpty(Request["ApplicationSubmissionDate"]))
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = Request["ApplicationSubmissionDate"];
}
else
{
cmd.Parameters.Add(new OracleParameter(":BSDATE", OracleType.DateTime)).Value = DBNull.Value;
}
cmd.Parameters.AddWithValue("SUBMITTEDBYNM", Request["SubmittedBy"]);
cmd.Parameters.AddWithValue("REMARKS", Request["Remarks"]);
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
if (Request["STATUS"] == "Cm")
{
cmd.CommandText = "update BONDAPPLICATIONPROGRESS set READYFORAPP=:READYFORAPP where BONDERSLNO=:BONDERSLNO and BSNO=:BSNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
cmd.Parameters.AddWithValue("BSNO", BondInfo.BSNoToRenew);
cmd.Parameters.AddWithValue("READYFORAPP", "Y");
try
{
try
{
bmsTransaction = conn.BeginTransaction();
}
catch { }
cmd.Transaction = bmsTransaction;
cmd.ExecuteNonQuery();
bmsTransaction.Commit();
}
catch
{
bmsTransaction.Rollback();
}
}
int p = 1;
if (doc != null)
{
try
{
for (int i = 0; i < doc.Length; i++)
{
if (doc[i] != null && doc[i] != "")
{
DOCUMENTATTACHMENT D = new DOCUMENTATTACHMENT();
var path = "";
D.ATTCHSLNO = (Int16)p;
//foreach (var outitem in rvm)
//{
// foreach (var item in outitem.Bonder)
// {
D.BONDERSLNO = BondInfo.bondSlNoToRenew;
//}
//foreach (var item in outitem.Bondstatus)
//{
D.BSNO = BondInfo.BSNoToRenew;
// }
//}
if (files[i] != null)
{
var filename = Path.GetFileName(files[i].FileName);
path = Path.Combine(Server.MapPath(Url.Content("~/Uploads/")), filename);
files[i].SaveAs(path);
D.ATTACHFILENM = path;
}
D.DOCHEADINGNAME = doc[i];
D.RGATTCHNAME = rgno[i];
//D.ISSUEDATE = Convert.ToDateTime(issuedate[i], CultureInfo.CurrentCulture);
//D.EXPDATE = Convert.ToDateTime(expdate[i], CultureInfo.CurrentCulture);
if (!String.IsNullOrEmpty(issuedate[i]))
{
D.ISSUEDATE = DateTime.ParseExact(issuedate[i], "dd/MM/yyyy", null);
}
if (!String.IsNullOrEmpty(expdate[i]))
{
D.EXPDATE = DateTime.ParseExact(expdate[i], "dd/MM/yyyy", null);
}
db.DOCUMENTATTACHMENTs.Add(D);
p++;
db.SaveChanges();
}
}
ViewBag.Message = "Successfully Inserted";
//return View(renewalviewmodel);
//return Search(BIMS.CommonAppSet.BondInfo.bondLicenseNoToRenew);
//return View("Search",rvm);
//return View("RenewalForm");
}
catch (DbEntityValidationException dbEx)
{
foreach (var validationErrors in dbEx.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
}
}
ViewBag.Message = "Insertion Failed";
return(View("RenewalForm"));
}
}
cmd.CommandText = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", BondInfo.bondSlNoToRenew);
System.Data.OracleClient.OracleDataReader drBondStatus = cmd.ExecuteReader();
if (drBondStatus.HasRows)
{
drBondStatus.Read();
ViewBag.STATUS = drBondStatus.GetValue(0);
ViewBag.BSDATE = drBondStatus.GetValue(1);
ViewBag.SUBMITTEDBYNM = drBondStatus.GetValue(2);
ViewBag.REMARKS = drBondStatus.GetValue(3);
}
return(View("Search", getRenewalInfo(BondInfo.bondSlNoToRenew)));
//return View("RenewalForm");
}