/// <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>
/// 执行一个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];
}
private void vUpdateStaff(OracleCommand command, OracleTransaction transaction)
{
string strUpdate = "update BASE_OPERATOR set OPERATORNO = :OPERATORNO, OPERATORNAME = :OPERATORNAME, "
+ "FASTCODE = :FASTCODE, SEX = :SEX, BIRTHDAY = :BIRTHDAY, EMAIL = :EMAIL, CONTACTADDRESS = :CONTACTADDRESS, "
+ "TELEPHONE = :TELEPHONE, MOBILETELEPHONE = :MOBILETELEPHONE, DEPARTID = :DEPARTID "
+ "where OPERATORID = '" + m_sStaff.strOPERATORID + "'";
command.CommandText = strUpdate;
command.Parameters.Add(new OracleParameter("OPERATORNAME", OracleType.VarChar)).Value = tbName.Text;
command.Parameters.Add(new OracleParameter("OPERATORNO", OracleType.VarChar)).Value = tbStaffNum.Text;
command.Parameters.Add(new OracleParameter("FASTCODE", OracleType.VarChar)).Value = tbFastCode.Text;
command.Parameters.Add(new OracleParameter("SEX", OracleType.VarChar)).Value = cbGender.Text;
command.Parameters.Add(new OracleParameter("BIRTHDAY", OracleType.DateTime)).Value = dateTimePicker1.Value.ToString();
command.Parameters.Add(new OracleParameter("EMAIL", OracleType.VarChar)).Value = tbEmail.Text;
command.Parameters.Add(new OracleParameter("CONTACTADDRESS", OracleType.VarChar)).Value = tbAddress.Text;
command.Parameters.Add(new OracleParameter("TELEPHONE", OracleType.VarChar)).Value = tbTel.Text;
command.Parameters.Add(new OracleParameter("MOBILETELEPHONE", OracleType.VarChar)).Value = tbMobile.Text;
command.Parameters.Add(new OracleParameter("DEPARTID", OracleType.VarChar)).Value = sleDepart.EditValue ?? "";
command.ExecuteNonQuery();
transaction.Commit();
MessageBox.Show("修改成功！");
}
public static string GetLotNo(string userID, double productLOID, OracleTransaction zTrans)
{
bool LetClose = false;
string code = "";
OracleConnection zConn = null;
if (zTrans == null)
{
LetClose = true;
zConn = OracleDB.GetConnection();
zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
ProductBarcodeDAL _bDAL = new ProductBarcodeDAL();
if (_bDAL.GetDataByLOID(productLOID, zTrans))
{
ProductMasterDAL _dal = new ProductMasterDAL();
if (_dal.GetDataByLOID(_bDAL.PRODUCTMASTER, zTrans))
{
if (_dal.YEAR == (DateTime.Now.Year + 543).ToString().Substring(2))
{
if (_dal.RUNNING == "9")
{
_dal.RUNNING = "1";
switch (_dal.LOTNO)
{
case "A": _dal.LOTNO = "B"; break;
case "B": _dal.LOTNO = "C"; break;
case "C": _dal.LOTNO = "D"; break;
case "D": _dal.LOTNO = "E"; break;
case "E": _dal.LOTNO = "F"; break;
case "F": _dal.LOTNO = "G"; break;
case "G": _dal.LOTNO = "H"; break;
case "H": _dal.LOTNO = "I"; break;
case "I": _dal.LOTNO = "J"; break;
case "J": _dal.LOTNO = "K"; break;
case "K": _dal.LOTNO = "L"; break;
case "L": _dal.LOTNO = "M"; break;
case "M": _dal.LOTNO = "N"; break;
case "N": _dal.LOTNO = "O"; break;
case "O": _dal.LOTNO = "P"; break;
case "P": _dal.LOTNO = "Q"; break;
case "Q": _dal.LOTNO = "R"; break;
case "R": _dal.LOTNO = "S"; break;
case "S": _dal.LOTNO = "T"; break;
case "T": _dal.LOTNO = "U"; break;
case "U": _dal.LOTNO = "V"; break;
case "V": _dal.LOTNO = "W"; break;
case "W": _dal.LOTNO = "X"; break;
case "X": _dal.LOTNO = "Y"; break;
case "Y": _dal.LOTNO = "Z"; break;
case "Z": _dal.LOTNO = "A"; break;
default: _dal.LOTNO = "A"; break;
}
}
else
{
_dal.RUNNING = (Convert.ToInt32(_dal.RUNNING == "" ? "0" : _dal.RUNNING) + 1).ToString().Trim();
}
}
else
{
_dal.LOTNO = "A";
_dal.RUNNING = "1";
_dal.YEAR = (DateTime.Now.Year + 543).ToString().Substring(2);
}
if (!_dal.UpdateCurrentData(userID, zTrans))
{
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
throw new ApplicationException(_dal.ErrorMessage);
}
else
{
code = (_dal.CODE.Length > 3 ? _dal.CODE.Substring(0, 3) : _dal.CODE) + " " + _dal.LOTNO + _dal.RUNNING + _dal.YEAR;
}
}
}
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
return code.ToUpper();
}
/// <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;
}
private void vAddStaff(OracleCommand command, OracleTransaction transaction)
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "BASE_OPERATOR_INSERT";
command.Parameters.Add("ls_OPERATORNO", OracleType.VarChar).Value = tbStaffNum.Text;
command.Parameters.Add("ls_OPERATORNAME", OracleType.VarChar).Value = tbName.Text;
command.Parameters.Add("ls_FASTCODE", OracleType.VarChar).Value = tbFastCode.Text;
command.Parameters.Add("ls_SEX", OracleType.VarChar).Value = cbGender.Text;
command.Parameters.Add("ls_BIRTHDAY", OracleType.VarChar).Value = dateTimePicker1.Value.ToString();
command.Parameters.Add("ls_EMAIL", OracleType.VarChar).Value = tbEmail.Text;
command.Parameters.Add("ls_CONTACTADDRESS", OracleType.VarChar).Value = tbAddress.Text;
command.Parameters.Add("ls_TELEPHONE", OracleType.VarChar).Value = tbTel.Text;
command.Parameters.Add("ls_MOBILETELEPHONE", OracleType.VarChar).Value = tbMobile.Text;
command.Parameters.Add("ls_DEPARTID", OracleType.VarChar).Value = sleDepart.EditValue ?? "";
command.Parameters.Add("descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("ls_OPTID", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
transaction.Commit();
MessageBox.Show(command.Parameters["Message"].Value.ToString());
}
public override void Commit()
{
trx.Commit();
}
public static string GetRunningCode(DateTime docDate, string RunningItem, OracleTransaction zTrans)
{
string tablename = "RUNNINGDAY";
string code = "";
string day = docDate.ToString("yyMMdd");
bool LetClose = false;
OracleConnection zConn = null;
if (zTrans == null)
{
LetClose = true;
zConn = GetConnection();
zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
string loid = "";
string lastValue = "";
string sqlz = "SELECT LOID, RUNNING, DAY, VALUE FROM " + tablename + " WHERE DAY = '" + day + "' AND RUNNING = '" + RunningItem + "' ";
OracleDataReader zRd = ExecQueryCmd(sqlz, zTrans);
if (zRd.Read())
{
loid = zRd["LOID"].ToString();
lastValue = zRd["VALUE"].ToString();
lastValue = (Convert.ToDouble(lastValue) + 1).ToString("000");
zRd.Close();
sqlz = "UPDATE " + tablename + " SET VALUE = '" + lastValue + "' WHERE LOID = " + loid;
ExecNonQueryCmd(sqlz, zTrans);
}
else
{
lastValue = "001";
loid = GetLOID("RUNNINGDAY", zTrans).ToString();
sqlz = "INSERT INTO " + tablename + " (LOID, DAY, VALUE, RUNNING) VALUES (" + loid + ", '" + day + "', '" + lastValue + "', '" + RunningItem + "') ";
ExecNonQueryCmd(sqlz, zTrans);
}
code = day + lastValue;
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
return code;
}
/// <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;
}
private void vUpdateDepart(OracleCommand command, OracleTransaction transaction)
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "jt_j_dwxx_update";
command.Parameters.Add("ls_DWID", OracleType.VarChar).Value = m_sDWXX.strDWID;
command.Parameters.Add("ls_DWBH", OracleType.VarChar).Value = teDWBH.Text;
command.Parameters.Add("ls_DWMC", OracleType.VarChar).Value = teDWMC.Text;
command.Parameters.Add("ls_DWJC", OracleType.VarChar).Value = teDWJC.Text;
command.Parameters.Add("ls_ZJM", OracleType.VarChar).Value = teZJM.Text;
command.Parameters.Add("ls_SJDWID", OracleType.VarChar).Value = sleDWXX.EditValue ?? "";
command.Parameters.Add("ls_DWSX", OracleType.VarChar).Value = cbDWSX.SelectedValue;
command.Parameters.Add("ls_BMLXID", OracleType.VarChar).Value = cbBMLX.SelectedValue;
command.Parameters.Add("ls_GYSLXID", OracleType.VarChar).Value = cbGYSLX.SelectedValue;
command.Parameters.Add("ls_KHLXID", OracleType.VarChar).Value = cbKHLX.SelectedValue;
command.Parameters.Add("ls_KFLXID", OracleType.VarChar).Value = cbKFLX.SelectedValue;
command.Parameters.Add("ls_YINSDWLXID", OracleType.VarChar).Value = cbYSCLX.SelectedValue;
command.Parameters.Add("ls_CBSLXID", OracleType.VarChar).Value = cbCBSLX.SelectedValue;
command.Parameters.Add("ls_YSDWLXID", OracleType.VarChar).Value = cbYSDWLX.SelectedValue;
command.Parameters.Add("ls_BMZTID", OracleType.VarChar).Value = sleZTBM.EditValue ?? "";
command.Parameters.Add("ls_GZLID", OracleType.VarChar).Value = "";
command.Parameters.Add("ls_DWJB", OracleType.Number).Value = teDWJB.EditValue ?? 0;
command.Parameters.Add("ls_DWFR", OracleType.VarChar).Value = teDWFR.Text;
command.Parameters.Add("ls_SFID", OracleType.VarChar).Value = sleSFBM.EditValue ?? "";
command.Parameters.Add("ls_DQID", OracleType.VarChar).Value = sleDQ.EditValue ?? "";
command.Parameters.Add("ls_YZBM", OracleType.VarChar).Value = teYZBM.Text;
command.Parameters.Add("ls_TXDZ", OracleType.VarChar).Value = teTXDZ.Text;
command.Parameters.Add("ls_DH", OracleType.VarChar).Value = teDH.Text;
command.Parameters.Add("ls_CZ", OracleType.VarChar).Value = teCZ.Text;
command.Parameters.Add("ls_LXR", OracleType.VarChar).Value = teLXR.Text;
command.Parameters.Add("ls_KHYH", OracleType.VarChar).Value = teKHYH.Text;
command.Parameters.Add("ls_ZH", OracleType.VarChar).Value = teZH.Text;
command.Parameters.Add("ls_SH", OracleType.VarChar).Value = teSH.Text;
command.Parameters.Add("ls_EMAIL", OracleType.VarChar).Value = teEMAIL.Text;
command.Parameters.Add("ls_WZ", OracleType.VarChar).Value = teWZ.Text;
command.Parameters.Add("ls_JSFSID", OracleType.VarChar).Value = teJSFS.Text;
command.Parameters.Add("ls_TSSX", OracleType.Number).Value = teTSSX.EditValue ?? 0;
command.Parameters.Add("ls_TSXX", OracleType.Number).Value = teTSXX.EditValue ?? 0;
command.Parameters.Add("ls_SDSX", OracleType.Number).Value = teSDSX.EditValue ?? 0;
command.Parameters.Add("ls_SDXX", OracleType.Number).Value = teSDXX.EditValue ?? 0;
command.Parameters.Add("ls_ZKSX", OracleType.Number).Value = teZKSX.EditValue ?? 0;
command.Parameters.Add("ls_ZKXX", OracleType.Number).Value = teZKXX.EditValue ?? 0;
command.Parameters.Add("ls_JSDWID", OracleType.VarChar).Value = sleJSDW.EditValue ?? "";
command.Parameters.Add("ls_DJSDBZ", OracleType.VarChar).Value = cbeDJSDBZ.Text;
command.Parameters.Add("ls_TSJSDBZ", OracleType.VarChar).Value = cbeTSJSDBZ.Text;
command.Parameters.Add("ls_KHZZYFBZ", OracleType.Number).Value = teZZYFBZ.EditValue ?? 0;
command.Parameters.Add("ls_KHZZBZFBZ", OracleType.Number).Value = teZZBZFBZ.EditValue ?? 0;
command.Parameters.Add("ls_KHZZQTFYBZ", OracleType.Number).Value = teZZQTFYBZ.EditValue ?? 0;
command.Parameters.Add("ls_CGJSZQ", OracleType.Number).Value = teCGJSZQ.EditValue ?? 0;
command.Parameters.Add("ls_TSJSZQ", OracleType.Number).Value = teXSJSZQ.EditValue ?? 0;
command.Parameters.Add("ls_SHDZ", OracleType.VarChar).Value = teSHDZ.Text;
command.Parameters.Add("ls_KJDZ", OracleType.VarChar).Value = teKJDZ.Text;
command.Parameters.Add("ls_MJDZ", OracleType.VarChar).Value = teMJDZ.Text;
command.Parameters.Add("ls_YSFSID", OracleType.VarChar).Value = cbYSFS.SelectedValue;
command.Parameters.Add("ls_YSFXID", OracleType.VarChar).Value = teYSFX.Text;
command.Parameters.Add("ls_ZZDBZ", OracleType.VarChar).Value = teZZDBZ.Text;
command.Parameters.Add("ls_YZDBH", OracleType.VarChar).Value = teYZDBH.Text;
command.Parameters.Add("ls_EZDBH", OracleType.VarChar).Value = teEZDBH.Text;
command.Parameters.Add("ls_KFDZ", OracleType.VarChar).Value = teKFDZ.Text;
command.Parameters.Add("ls_TDYZS", OracleType.Number).Value = teTTDYZS.EditValue ?? 0;
command.Parameters.Add("ls_DWTJBH", OracleType.VarChar).Value = teTJBH.Text;
command.Parameters.Add("ls_CGJSYXJB", OracleType.Number).Value = teCGJSYXJ.EditValue ?? 0;
command.Parameters.Add("ls_XSJSYXJB", OracleType.Number).Value = teXSJSYXJ.EditValue ?? 0;
command.Parameters.Add("ls_PHYXJB", OracleType.Number).Value = tePHYXJ.EditValue ?? 0;
command.Parameters.Add("ls_XTYXJB", OracleType.Number).Value = teXTYXJ.EditValue ?? 0;
command.Parameters.Add("ls_JTYXJB", OracleType.Number).Value = teJTYXJ.EditValue ?? 0;
command.Parameters.Add("ls_CGYXJB", OracleType.Number).Value = teCGYXJ.EditValue ?? 0;
command.Parameters.Add("ls_WEBYH", OracleType.VarChar).Value = teWEBYH.Text;
command.Parameters.Add("ls_WEBMM", OracleType.VarChar).Value = teWEBMM.Text;
command.Parameters.Add("ls_ZT", OracleType.VarChar).Value = cbeZT.Text;
command.Parameters.Add("ls_CJR", OracleType.VarChar).Value = FrmLogin.getUserName;
command.Parameters.Add("ls_TYR", OracleType.VarChar).Value = "";
command.Parameters.Add("ls_BZ", OracleType.VarChar).Value = teBZ.Text;
command.Parameters.Add("ls_SFZT", OracleType.VarChar).Value = cbSFZT.SelectedValue;
command.Parameters.Add("ls_CWXTID", OracleType.VarChar).Value = "";
command.Parameters.Add("descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
transaction.Commit();
MessageBox.Show(command.Parameters["Message"].Value.ToString());
}
/// <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");
}
public static double GetLOID(string tablename, OracleTransaction zTrans)
{
bool LetClose = false;
OracleConnection zConn = null;
if (zTrans == null)
{
LetClose = true;
zConn = GetConnection();
zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
string lastid;
string sqlz = "SELECT SQ" + tablename.ToUpper() + ".NEXTVAL AS RUNNINGNUMBER FROM DUAL";
try
{
lastid = ExecSingleCmd(sqlz, zTrans).ToString();
}
catch (Exception ex)
{
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
throw new ApplicationException(ex.Message);
}
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
return Convert.ToDouble(lastid);
}
/// <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>
/// <param name="transaction">事务对象</param>
/// <param name="connection">连接对象</param>
public static void Commit(OracleTransaction transaction, OracleConnection connection)
{
transaction.Commit();
connection.Close();
}
/// <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;
}
}
public static string GetRunningCode(string RunningName, string RunningItem, OracleTransaction zTrans)
{
string tablename = "RUNNING";
bool LetClose = false;
OracleConnection zConn = null;
if (zTrans == null)
{
LetClose = true;
zConn = GetConnection();
zTrans = zConn.BeginTransaction(IsolationLevel.ReadCommitted);
}
string loid = "";
string code = "";
string lastValue = "";
string year = "";
string month = "";
string sqlz = "SELECT LOID, CODE, YEAR, MONTH, VALUE FROM " + tablename + " WHERE RUNNING = '" + RunningName + "' AND ITEM = '" + RunningItem + "' ";
OracleDataReader zRd = ExecQueryCmd(sqlz, zTrans);
if (zRd.Read())
{
int length = 0;
loid = zRd["LOID"].ToString();
year = zRd["YEAR"].ToString();
month = zRd["MONTH"].ToString();
lastValue = zRd["VALUE"].ToString();
length = lastValue.Length;
if (month != "" && year != "")
{
if ((DateTime.Now.Year +543).ToString().Substring(4 - year.Length) != year || DateTime.Now.Month.ToString("00") != month)
{
year = (DateTime.Now.Year + 543).ToString().Substring(4 - year.Length);
month = DateTime.Now.Month.ToString("00");
lastValue = "0";
}
}
else if (year != "")
{
if (year != (DateTime.Now.Year + 543).ToString().Substring(4 - year.Length) && year != "")
{
year = (DateTime.Now.Year + 543).ToString().Substring(4 - year.Length);
lastValue = "0";
}
}
//if (year != DateTime.Now.Year.ToString().Substring(4 - year.Length) && year != "")
//{
// year = DateTime.Now.Year.ToString().Substring(4 - year.Length);
// lastValue = "0";
//}
lastValue = "00000000000000000000" + (Convert.ToDouble(lastValue) + 1).ToString();
lastValue = lastValue.Substring(lastValue.Length - length);
code = zRd["CODE"].ToString() + year + month + lastValue;
zRd.Close();
sqlz = "UPDATE " + tablename + " SET MONTH = '" + month + "', YEAR = '" + year + "', VALUE = '" + lastValue + "' WHERE LOID = " + loid;
ExecNonQueryCmd(sqlz, zTrans);
}
else
{
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
throw new ApplicationException("äÁèÊÒÁÒöÍèÒ¹¤èÒ running ä´é");
}
if (LetClose)
{
zTrans.Commit();
zConn.Close();
}
return code;
}