/// <summary>
/// 更新类对象到数据库中
/// </summary>
/// <typeparam name="T">类对象ID段类型</typeparam>
/// <param name="objTable">类对象</param>
/// <param name="tr">事务,null表示不启用事务</param>
/// <returns>更新记录成功，返回记录ID号，否则返回默认值(0，或者空串)</returns>
public T Update <T>(ITable objTable, Transaction tr)
{
Type type = typeof(object);
string pkField = objTable.GetPrimayField(ref type);
string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":update";
string sqlstr = "";
string strParamList = "";
if (paramCache[hashKey] == null)
{
string str1 = "";
foreach (string strkey in objTable.GetFieldList().Split(new char[] { ',' }))
{
if (pkField != strkey)
{
str1 += "," + strkey + "=" + BuildParamFlag(strkey);
strParamList += "," + strkey;
}
}
str1 = str1.Remove(0, 1);
sqlstr = "UPDATE " + objTable.GetTableName() + " set " + str1 + " where " + pkField + "=" + BuildParamFlag(pkField);
strParamList = strParamList.Remove(0, 1) + "," + pkField;
paramCache.Add(hashKey, sqlstr);
paramCache.Add(hashKey + ":param", strParamList);
}
sqlstr = paramCache[hashKey].ToString();
strParamList = paramCache[hashKey + ":param"].ToString();
Hashtable htParam = objTable.GetFields();
return(Execute(objTable.GetTableName(), "update", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T));
}
public void TestBeginTransactionChaos() {
DataBaseServer dbType = ConnectedDataProvider.GetDbType(con);
// not supported on DB2 and Oracle and Sybase
if (dbType != DataBaseServer.Oracle && dbType != DataBaseServer.DB2 && dbType != DataBaseServer.Sybase) {
con.Close();
con.Open();
try {
BeginCase("BeginTransaction - IsolationLevel Chaos");
tran = con.BeginTransaction(IsolationLevel.Chaos);
Compare(tran == null, false);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
/* not supported by MSSQL,DB2,Oracle
con.Close();
con.Open();
try
{
BeginCase("BeginTransaction - IsolationLevel Unspecified");
tran = con.BeginTransaction(IsolationLevel.Unspecified );
Compare(tran == null, false);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
*/
}
public bool UpdateQCResult(double loid, DateTime duedate, double qcqty1, double qcqty2, double qcqty3, string qcresult, string qcremark, string userID, string status, OracleTransaction zTrans)
{
string sql = "UPDATE PDPRODUCT SET QCRESULT = '" + qcresult + "', ";
sql += "QCQTY1 = " + qcqty1 + ", ";
sql += "QCQTY2 = " + qcqty2 + ", ";
sql += "QCQTY3 = " + qcqty3 + ", ";
sql += "QCREMARK = '" + qcremark + "', ";
sql += "PRODSTATUS = '" + status + "', ";
sql += "QCDUEDATE = " + OracleDB.QRDateTime(duedate) + ", ";
sql += "UPDATEBY = '" + userID + "', ";
sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
sql += "WHERE LOID = " + loid + " ";
//+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : ""));
bool ret = true;
try
{
ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
return ret;
}
public bool CreateTransaction()
{
bool ret = true;
try
{
if (_Conn == null) { _Conn = OracleDB.GetConnection(); }
_Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted);
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
if (!ret)
{
try
{
if (_Conn != null) { _Conn.Close(); }
_Conn.Open();
_Trans = _Conn.BeginTransaction(IsolationLevel.ReadCommitted);
ret = true;
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
}
return ret;
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OracleCommand to be prepared</param>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
command.CommandTimeout = 200000;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public static int Save(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans)
{
if(loan.Id == null){
return Insert(loan, oraDbConn, oraTrans);
} else {
return Update(loan);
}
}
public static int Save(Applicant applicant, OracleConnection oraDbConn, OracleTransaction oraTrans)
{
if (applicant.Id == null) {
return Insert(applicant, oraDbConn, oraTrans);
} else {
return Update(applicant);
}
}
public OracleCommandSet(OracleConnection connection, OracleTransaction transaction)
{
this._usedParameterNames = new Hashtable(StringComparer.OrdinalIgnoreCase);
this._commandList = new ArrayList();
this._batchCommand = new OracleCommand();
this.Connection = connection;
this.Transaction = transaction;
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="trans">an existing database transaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Get Data List of This Table
/// </summary>
/// <param name="whereCause"></param>
/// <param name="zTrans">Transaction, set to null if no transaction provided</param>
/// <returns></returns>
public DataTable GetDataList(string whereCause, OracleTransaction zTrans)
{
string sql = "SELECT PT.* , ";
sql += "CASE PT.TYPE WHEN '" + Constz.ProductType.Type.FG.Code + "' THEN '" + Constz.ProductType.Type.FG.Rank + "' ";
sql += "WHEN '" + Constz.ProductType.Type.WH.Code + "' THEN '" + Constz.ProductType.Type.WH.Rank + "' ";
sql += "WHEN '" + Constz.ProductType.Type.Others.Code + "' THEN '" + Constz.ProductType.Type.Others.Rank + "' ";
sql += "ELSE '' END AS RANK FROM PRODUCTTYPE PT ORDER BY RANK,PT.CODE";
return OracleDB.ExecListCmd(sql);
}
public override void Commit()
{
try
{
ts.Commit();
}
finally
{
ts = null;
}
}
public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx)
{
moreResults = -1;
preparedStatement = null;
CommandText = commandText;
Connection = connection;
Transaction = tx;
CommandType = CommandType.Text;
UpdatedRowSource = UpdateRowSource.Both;
DesignTimeVisible = true;
parameters = new OracleParameterCollection ();
}
public void BeginTrans()
{
if (_conn != null)
{
if (_trans != null)
{
throw new Exception("Transition already began! Please commit it before begin a new one.");
}
_trans = _conn.BeginTransaction();
_isInTransaction = true;
}
}
public FrmSupplierCorrection(OracleConnection Conn, OracleTransaction Trans, string strGYSMC)
{
string strSQL = "select DWID, DWMC, DWBH, ZJM from JT_J_DWXX";
OracleDataAdapter ada = new OracleDataAdapter(strSQL, Conn);
ada.SelectCommand.Transaction = Trans;
DataSet ds = new DataSet();
ada.Fill(ds, "JT_J_DWXX");
InitializeComponent();
jTJDWXXBindingSource.DataSource = ds;
jTJDWXXBindingSource.DataMember = "JT_J_DWXX";
teOldSupplier.Text = strGYSMC;
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
tr = con.BeginTransaction();
cmd = new OracleCommand("", con, tr);
dbServerType = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
Assert.AreEqual("Setup", "Setup");
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
}
public bool UpdateQCStockinItem(double stockin, string userID, string status, OracleTransaction zTrans)
{
string sql = "UPDATE STOCKINITEM ";
sql += "SET STATUS = '" + status + "', ";
sql += "UPDATEBY = '" + userID + "', ";
sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
sql += "WHERE STOCKIN = " + stockin + " ";
bool ret = true;
try
{
ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
return ret;
}
public bool UpdateQCStockin(double loid, string anacode, DateTime anadate, string userID, string status, OracleTransaction zTrans)
{
string sql = "UPDATE STOCKIN SET ANACODE = '" + anacode + "', ";
sql += "STATUS = '" + status + "', ";
sql += "ANADATE = " + OracleDB.QRDateTime(anadate) + ", ";
sql += "UPDATEBY = '" + userID + "', ";
sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
sql += "WHERE LOID = " + loid + " ";
bool ret = true;
try
{
ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
return ret;
}
public bool UpdatePDOrder(double loid, string userID, string status, OracleTransaction zTrans)
{
string sql = "UPDATE PDORDER SET ";
sql += "STATUS = '" + status + "', ";
sql += "UPDATEBY = '" + userID + "', ";
sql += "UPDATEON = " + OracleDB.QRDateTime() + " ";
sql += "WHERE LOID = " + loid + " ";
//+ (status == Constz.Requisition.Status.Approved.Code ? "AND STATUS = '" + Constz.Requisition.Status.Waiting.Code + "' " : (status == Constz.Requisition.Status.Void.Code ? "AND STATUS = '" + Constz.Requisition.Status.Approved.Code + "' " : ""));
bool ret = true;
try
{
ret = (OracleDB.ExecNonQueryCmd(sql, zTrans) > 0);
if (!ret) throw new ApplicationException(OracleDB.Err_NoUpdate);
}
catch (Exception ex)
{
ret = false;
_error = ex.Message;
}
return ret;
}
internal static bool ExecuteNonQuery(string sql, OracleTransaction trans)
{
bool ret = false;
try
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = trans.Connection;
cmd.Transaction = trans;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 240;
cmd.ExecuteNonQuery();
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
public static int ExecNonQueryCmd(string sqlz, OracleTransaction zTrans)
{
OracleCommand zCommand = new OracleCommand();
int retval;
if (zTrans != null)
{
BuildzCommand(zCommand, zTrans.Connection, zTrans, CommandType.Text, sqlz, null);
retval = zCommand.ExecuteNonQuery();
}
else
{
using (OracleConnection zConn = new OracleConnection(ConnectionString))
{
BuildzCommand(zCommand, zConn, zTrans, CommandType.Text, sqlz, null);
retval = zCommand.ExecuteNonQuery();
}
}
return retval;
}
public void SetUp()
{
Exception exp = null;
BeginCase("Setup");
try
{
// prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
// transaction use was add for PostgreSQL
tr = con.BeginTransaction();
cmd = new OracleCommand("", con, tr);
Compare("Setup" ,"Setup");
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <param name="tr">事务,null表示不启用事务</param>
/// <returns>是否执行成功</returns>
public bool ExecSQL(string sqlstr, Transaction tr)
{
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
dbCommand.Transaction = tr;
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
dbCommand.ExecuteNonQuery();
return(true);
}
catch (Exception e)
{
WriteLogInfo("errorn on " + this.GetType().Name + " ExecSQL:" + sqlstr + "\r\n" + e.Message);
return(false);
}
}
private static int Insert(Loan loan, OracleConnection oraDbConn, OracleTransaction oraTrans)
{
int LoanId = 0;
using (OracleCommand insertLoanCommand = new OracleCommand()) {
insertLoanCommand.CommandType = CommandType.StoredProcedure;
insertLoanCommand.CommandText = "LoansPKG.insertLoan";
insertLoanCommand.Connection = oraDbConn;
insertLoanCommand.Transaction = oraTrans;
insertLoanCommand.Parameters.AddWithValue("AppId", loan.ApplicantId);
insertLoanCommand.Parameters.AddWithValue("LoanType", loan.Type);
insertLoanCommand.Parameters.AddWithValue("LoanAmount", loan.Amount);
OracleParameter outputLoanId = new OracleParameter("LoanId", OracleType.Number);
outputLoanId.Direction = ParameterDirection.Output;
insertLoanCommand.Parameters.Add(outputLoanId);
insertLoanCommand.ExecuteNonQuery();
LoanId = Convert.ToInt32(outputLoanId.Value);
}
return LoanId;
}
/// <summary>
/// 插入类对象到数据库中
/// </summary>
/// <typeparam name="T">类对象ID段类型</typeparam>
/// <param name="objTable">类对象</param>
/// <param name="CreateNewId">是否生成新的ID号</param>
/// <param name="tr">事务,null表示不启用事务</param>
/// <returns>插入记录成功，返回记录ID号，否则返回默认值(0，或者空串)</returns>
public T Insert <T>(ITable objTable, bool CreateNewId, Transaction tr)
{
Type type = typeof(object);
string pkField = objTable.GetPrimayField(ref type);
string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":insert";
string sqlstr = "";
string strParamList = "";
if (paramCache[hashKey] == null)
{
string str1 = "";
string str2 = "";
foreach (string strkey in objTable.GetFieldList().Split(new char[] { ',' }))
{
str1 += "," + strkey;
str2 += "," + BuildParamFlag(strkey);
}
str1 = str1.Remove(0, 1);
str2 = str2.Remove(0, 1);
sqlstr = "INSERT INTO " + objTable.GetTableName() + " (" + str1 + ")VALUES(" + str2 + ")";
strParamList = str1;
paramCache.Add(hashKey, sqlstr);
paramCache.Add(hashKey + ":param", strParamList);
}
sqlstr = paramCache[hashKey].ToString();
strParamList = paramCache[hashKey + ":param"].ToString();
Hashtable htParam = objTable.GetFields();
if (CreateNewId)
{
htParam[pkField] = GetSerialNo <T>(objTable.GetTableName(), pkField);
}
return(Execute(objTable.GetTableName(), "insert", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T));
}
internal static DataTable ExecuteTable(string sql, OracleTransaction trans)
{
OracleCommand cmd = new OracleCommand();
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
try
{
cmd.Connection = trans.Connection;
cmd.Transaction = trans;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 240;
adapter.Fill(dt);
adapter.Dispose();
}
catch (Exception ex)
{
dt = new DataTable();
}
return dt;
}
/// <summary>
/// 删除数据库中指定ID的数据记录
/// </summary>
/// <typeparam name="T">类对象ID段类型</typeparam>
/// <param name="objTable">类对象</param>
/// <param name="id">待删除的记录ID</param>
/// <param name="tr">事务,null表示不启用事务</param>
/// <returns>更新删除成功，返回记录ID号，否则返回默认值(0，或者空串)</returns>
public T Delete <T>(ITable objTable, T id, Transaction tr)
{
Type type = typeof(object);
string pkField = objTable.GetPrimayField(ref type);
string hashKey = this.mDataSource + ":table:" + objTable.GetTableName() + ":delete";
string sqlstr = "";
string strParamList = "";
if (paramCache[hashKey] == null)
{
sqlstr = "DELETE FROM " + objTable.GetTableName() + " where " + pkField + "=" + BuildParamFlag(pkField);
strParamList = pkField;
paramCache.Add(hashKey, sqlstr);
paramCache.Add(hashKey + ":param", strParamList);
}
sqlstr = paramCache[hashKey].ToString();
strParamList = paramCache[hashKey + ":param"].ToString();
Hashtable htParam = objTable.GetFields();
htParam[pkField] = id;
return(Execute(objTable.GetTableName(), "delete", sqlstr, htParam, strParamList, tr) ? (T)htParam[pkField] : default(T));
}
/// <summary>
/// 执行一个存储过程，没有返回数据集，只返回影响记录数
/// 这个方法并没有提供存取到输出参数或存储过程的返回值参数。
/// </summary>
/// <remarks>
/// 这种方法并没有提供存取到输出参数或存储过程的返回值参数
///
/// 例如：
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="spName">存储过程名称</param>
/// <param name="parameterValues"> params object[]</param>
/// <returns>影响记录数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
OracleParameter[] commandParameters = MsOracleProviderParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
/// <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>
/// <remarks>
/// 例如：
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
/// </remarks>
/// <param name="transaction">有效的 OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或 PL/SQL</param>
/// <returns>影响记录数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteNonQuery(transaction, commandType, commandText, (OracleParameter[])null);
}
/// <summary>
///打开(如果必要的话),指定一个命令、连接、事务、参数类型和参数
/// </summary>
/// <param name="command">OracleCommand</param>
/// <param name="connection">OracleConnection</param>
/// <param name="transaction">OracleTransaction 或 'null'</param>
/// <param name="commandType"> CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">OracleParameter[]</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
//command.Transaction = transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
else
{
if (command.Parameters.Count > 0)
command.Parameters.Clear();
}
return;
}
public OracleCommand(string commandText, OracleConnection connection, OracleTransaction tx) : this()
{
this.CommandText = commandText;
this.Connection = connection;
this.Transaction = tx;
}
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");
}
/// <summary>
/// 提交事务
/// </summary>
public static void CommitTransaction()
{
try
{
if (Transaction != null)
{
Transaction.Commit();
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
conn = null;
comm = null;
adpter = null;
Transaction = null;
}
}
}
catch (OracleException e)
{
Transaction.Rollback();
Close();
Transaction = null;
LogHelper.WriteLog("MsOracle.CommitTransaction",null, e);
throw new System.Exception(e.Message);
}
finally
{
Close();
Transaction = null;
}
}
/**
* Initializes a new instance of the OracleCommand class with the text of the query, a SqlConnection, and the Transaction.
* @param cmdText The text of the query.
* @param connection A SqlConnection that represents the connection to an instance of SQL Server.
* @param transaction The SqlTransaction in which the OracleCommand executes.
*/
public OracleCommand(
String cmdText,
OracleConnection connection,
OracleTransaction transaction)
: base(cmdText, connection, transaction) {
}
public OracleTransaction(System.Data.OracleClient.OracleTransaction trx, OracleConnection con)
{
this.trx = trx;
this.con = con;
}
/// <summary>
/// 开始事务
/// </summary>
/// <param name="conn">OracleConnection连接对象</param>
public static void BeginTransaction(OracleConnection connObjection)
{
if (Transaction == null||Transaction.Connection != connObjection)
{
Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
//public ActionResult Search()
//{
// return View("Index");
//}
//[HttpPost]
public ActionResult Search(string BondLicenseNo)
{
if (!(System.Web.HttpContext.Current.User.IsInRole("Bonder")))
{
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo select b.BONDERSLNO).SingleOrDefault();
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 = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO ";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
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);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
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"));
}
return(RedirectToAction("Index"));
}
else
{
USERPERMISSION permission = session.getStoredUserPermission();
var bonderName = permission.BONDER.BONDERNAME;
var bonderslno = (from b in db.BONDERs where b.BONDLICENSENO == BondLicenseNo && b.BONDERNAME == bonderName select b.BONDERSLNO).SingleOrDefault();
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 = "select STATUS,BSDATE,SUBMITTEDBYNM,REMARKS from BONDSTATUS where BONDERSLNO=:BONDERSLNO";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("BONDERSLNO", bonderslno);
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);
}
try
{
return(View(getRenewalInfo(bonderslno)));
}
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"));
}
return(RedirectToAction("Index"));
}
}
/// <summary>
/// 开始事务
/// </summary>
public static void BeginTransaction()
{
if (conn == null)
{
conn = GetOracleConnection(ConnectionString);
}
if (Transaction.Connection != conn)
{
Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
//private Transaction CreateTransaction()
//{
// Transaction tr=mConn.BeginTransaction();
// tr.Commit();
//}
#endregion
#endregion
#region 公开方法：存贮过程相关
#region CallProc
public bool CallProc(IStoredProc objProc, Transaction tr)
{
#region 创建命令
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = objProc.GetProcName();
dbCommand.Transaction = tr;
#endregion
#region 参数赋值
#if DT_ORACLE
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), false);
#elif DT_MSSQL
DbParameter[] dbparams = this.DeriveParameters(objProc.GetProcName(), true);
#elif DT_OLEDB
DbParameter[] dbparams = new DbParameter[0];
this.WriteLogInfo(this.GetType().Name + "OLEDB方式不支持取存贮过程参数");
return(false);
#endif
Hashtable htParam = objProc.GetFields();
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Input || dbparam.Direction == ParameterDirection.InputOutput)
{
dbparam.Value = htParam[dbparam.ParameterName.ToUpper()];
}
else
{
dbparam.Value = DBNull.Value;
}
dbCommand.Parameters.Add(dbparam);
}
#endregion
#region 执行
DataSet mDataSet = new DataSet();
using (DataAdapter mDataAdapter = new DataAdapter(dbCommand))
{
try
{
mDataAdapter.Fill(mDataSet);
}
catch (Exception e)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n" + e.Message);
return(false);
}
}
#endregion
#region 读取参数
foreach (DbParameter dbparam in dbparams)
{
if (dbparam.Direction == ParameterDirection.Output || dbparam.Direction == ParameterDirection.InputOutput)
{
htParam[dbparam.ParameterName.ToUpper()] = dbparam.Value;
}
}
if (objProc.GetCursorFieldList() != "")
{
string[] cursorFields = objProc.GetCursorFieldList().Split(new char[] { ',' });
if (mDataSet.Tables.Count != cursorFields.Length)
{
WriteLogInfo("error on " + this.GetType().Name + " " + objProc.GetProcName() + ".excute:\r\n 返回的游标数量与参数不匹配："
+ "\r\n\t游标参数:" + objProc.GetCursorFieldList()
+ "\r\n\t游标返回数:" + mDataSet.Tables.Count);
return(false);
}
int idx = 0;
foreach (string cursorField in cursorFields)
{
htParam[cursorField.ToUpper()] = mDataSet.Tables[idx++];
}
}
#endregion
return(true);
}
/// <summary>
/// 回滚事务
/// </summary>
public static void RollbackTransaction(OracleConnection connObjection)
{
try
{
if (Transaction != null && Transaction.Connection != null)
{
Transaction.Rollback();
Close(connObjection);
Transaction = null;
if (connObjection != null)
{
if (connObjection.State == ConnectionState.Open)
{
Close(connObjection);
}
}
}
else
{
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>
/// 执行SQL语句
/// </summary>
/// <param name="tablename"></param>
/// <param name="optype"></param>
/// <param name="sqlstr"></param>
/// <param name="htParam"></param>
/// <param name="strParamList"></param>
/// <param name="tr"></param>
/// <returns></returns>
private bool Execute(string tablename, string optype, string sqlstr, Hashtable htParam, string strParamList, Transaction tr)
{
#region 创建命令
DbCommand dbCommand = new DbCommand();
dbCommand.Connection = mConn;
dbCommand.CommandText = sqlstr;
dbCommand.Transaction = tr;
#endregion
#region 设置参数（对于OLEDB方式而言，参数的顺序必须与SQL语句中一致）
foreach (string strkey in strParamList.Split(new char[] { ',' }))
{
#if DT_OLEDB
if (htParam[strkey].GetType() != typeof(DateTime))
{
dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey]));
}
else
{
//OleDB连Access时，这个日期类型必须特别指定；连Oracle不需要；SQL没测过
dbCommand.Parameters.Add(new DbParameter(strkey, System.Data.OleDb.OleDbType.Date)).Value = htParam[strkey];
}
#else
dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey]));
#endif
}
#endregion
#region 提交命令
try
{
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
}
dbCommand.ExecuteNonQuery();
//DisConnection();
return(true);
}
catch (Exception e)
{
this.WriteLogInfo("error on " + this.GetType().Name + " " + tablename + ".Execute." + optype + ":\r\nsql:" + sqlstr + "\r\n" + e.Message);
return(false);
}
#endregion
}
