public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleTransaction txn = null;
try
{
BeginCase("BeginTransaction - connection close");
try
{
txn = con.BeginTransaction();
}
catch (Exception ex) {exp = ex;}
Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName );
exp=null;
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
con.Open();
try
{
BeginCase("BeginTransaction - connection close");
txn = con.BeginTransaction();
Compare(txn == null,false );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection=conn;
OracleTransaction tx=conn.BeginTransaction();
cmd.Transaction=tx;
try
{
for(int n=0;n<SQLStringList.Count;n++)
{
string strsql=SQLStringList[n].ToString();
if (strsql.Trim().Length>1)
{
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch(System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
/// <summary></summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
///
/// UPDATE、INSERT 和 DELETE 语句
public int ExecuteNonQuery(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(-1);
}
int rv = -1;
OracleTransaction oracleTransaction = null;
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
oracleTransaction = oracleConnection.BeginTransaction();
oracleCommand.Transaction = oracleTransaction;
rv = oracleCommand.ExecuteNonQuery();
oracleTransaction.Commit();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
oracleTransaction.Rollback();
rv = -1;
}
return(rv);
}
}
/// <summary>
/// ������̺�
/// </summary>
/// <param name="heatno"></param>
/// <param name="projectid"></param>
/// <param name="spoolname"></param>
/// <param name="materialname"></param>
/// <param name="seqnum"></param>
public static void AddHeatNo(string heatno, string projectid, string spoolname, string materialname, int seqnum)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//���conn����
conn.Open();
string queryString = "SP_AddHeatNo";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("heatno", OracleType.VarChar).Value = heatno;
cmd.Parameters.Add("pid", OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("spname", OracleType.VarChar).Value = spoolname;
cmd.Parameters.Add("material", OracleType.VarChar).Value = materialname;
cmd.Parameters.Add("seqnumber", OracleType.Number).Value = seqnum;
cmd.Transaction = trans;
try
{
//conn.Open();
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "����", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public static int Save(Applicant applicant)
{
int applicantId;
using (OracleConnection oraDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))) {
oraDbConn.Open();
using (OracleTransaction oraTrans =
oraDbConn.BeginTransaction(IsolationLevel.ReadCommitted)) {
try {
applicantId = ApplicantDB.Save(applicant, oraDbConn, oraTrans);
foreach (Loan loan in applicant.Loans) {
loan.ApplicantId = applicantId;
LoanManager.Save(loan, oraDbConn, oraTrans);
}
applicant.Id = applicantId;
oraTrans.Commit();
}
catch (OracleException oraEx) {
oraTrans.Rollback();
throw oraEx;
}
}
}
return applicantId;
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleTransaction txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("Select * From Employees", con);
try
{
BeginCase("check Transaction property - default");
Compare(cmd.Transaction==null , true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
cmd.Transaction = txn;
try
{
BeginCase("check Transaction property");
Compare(cmd.Transaction , txn);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static void UpdatePersonStatus( string queryString, string project, string drawing, string spool, string person, DateTime dt, string frmtxt)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
//string queryString = "SP_UpdateBlankingStatus";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool;
cmd.Parameters.Add("person_in", OracleType.VarChar).Value = person;
cmd.Parameters.Add("datetime_in", OracleType.DateTime).Value = dt;
cmd.Parameters.Add("frmtxt_in", OracleType.VarChar).Value = frmtxt;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK,MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public int ExecSql(string[] sqls)
{
using (OracleConnection conn = new OracleConnection(ConnString))
{
OracleTransaction st;
conn.Open();
st = conn.BeginTransaction();
try
{
foreach (string sql in sqls)
{
OracleCommand scc = new OracleCommand(sql, conn);
scc.Transaction = st;
scc.ExecuteNonQuery();
}
st.Commit();
return 0;
}
catch
{
st.Rollback();
return -1;
}
}
}
public void run()
{
OracleConnection con = null;
OracleTransaction txn;
Exception exp = null;
try
{
BeginCase("OracleTransaction Rollback");
//
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
string Result = "";
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
cmd.ExecuteNonQuery();
txn.Rollback();
//
//
cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con);
Result = cmd.ExecuteScalar().ToString();
Compare(Result,"Last100" );
this.Log(Result);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static void MarkDeletedSpoolRecord(string projectid, string spname, string drawing, string username)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
string queryString = "SP_MarkDeleteSpoolRecord";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("spname_in", OracleType.VarChar).Value = spname;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("username_in", OracleType.VarChar).Value = username;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public static string GetDrawingWithBlock(string projectid, string block)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
string queryString = "SP_GetDrawingWithBlock";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("drawing_out", OracleType.VarChar,100);
cmd.Parameters["drawing_out"].Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("block_in", OracleType.VarChar).Value = block;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
return cmd.Parameters["drawing_out"].Value.ToString();
}
public static void AddTrayORClass(string queryString, string project, string drawing, string spool,string trayorclass)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool;
cmd.Parameters.Add("trayorclass_in", OracleType.VarChar).Value = trayorclass;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
private void btnConfirm_Click(object sender, EventArgs e)
{
if (selection.SelectedCount == 0)
{
MessageBox.Show("没有选择单据");
}
else
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
try
{
for (int i = 0; i < selection.SelectedCount; ++i)
{
int RowIndex = selection.GetSelectedRowIndex(i);
int RowHandle = gridView1.GetRowHandle(RowIndex);
string strXSJSDID = gridView1.GetRowCellValue(RowHandle, colXSJSDID).ToString();
command.CommandText = "insert into Temp_Save_Id (tempid,id) Values (TEMP_SAVE_ID_SEQ.nextval,'" + strXSJSDID + "')";
command.ExecuteNonQuery();
}
selection.ClearSelection();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "JC_C_XSTSD_XD";
command.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.btnConfirm.Tag.ToString();
command.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
transaction.Commit();
string mess = command.Parameters["Message"].Value.ToString();
string alarm = command.Parameters["DescErr"].Value.ToString();
MessageBox.Show(mess + alarm);
}
catch (OracleException ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
this.DialogResult = DialogResult.OK;
this.Close();
}
}
}
}
public void get_cust_id_by_acct_id(int acct_id, out int p_cust_id, out int rc, out string msg)
{
rc = -1;
msg = null;
p_cust_id = -1;
string connStr = getConnectionString();
try
{
log.Info(string.Format("get_cust_id_by_acct_id acct_id={0}", acct_id));
using (OracleConnection objConn = new OracleConnection(connStr))
{
objConn.Open();
OracleTransaction transaction;
transaction = objConn.BeginTransaction();
OracleCommand objCmd = new OracleCommand(schemaPrefixSap + "crmws_functions.get_cust_id_by_acct_id", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
try
{
objCmd.Transaction = transaction;
objCmd.Parameters.Clear();
objCmd.Parameters.Add("p_acct_id", OracleType.Number).Value = dbNlv(acct_id);
objCmd.Parameters.Add("p_cust_id", OracleType.Number).Direction = ParameterDirection.Output;
objCmd.Parameters.Add("p_rc", OracleType.Number).Direction = ParameterDirection.Output;
objCmd.Parameters.Add("p_msg", OracleType.VarChar, 2048).Direction = ParameterDirection.Output;
objCmd.ExecuteNonQuery();
rc = int.Parse(objCmd.Parameters["p_rc"].Value.ToString());
msg = objCmd.Parameters["p_msg"].Value.ToString();
p_cust_id = int.Parse(objCmd.Parameters["p_cust_id"].Value.ToString());
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
rc = -3;
log.Error("get_cust_id_by_acct_id", e);
}
finally
{
objConn.Close();
}
}
}
catch (Exception e)
{
log.Error("get_cust_id_by_acct_id", e);
rc = -1;
}
}
//Creating variables using parameters and assigning them to textboxes to insert into the customer table
public void add_Customer(Customer_Entity_Class customer)
{
/*
* Creating a connection to connect to the database calling Orc class that returns the connection string
*/
OracleConnection connection = new OracleConnection(OrcConnection.connection());
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
OracleTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
cmd.CommandType = CommandType.Text;
cmd.CommandText = " INSERT INTO Customers Values(customer_seq.NEXTVAL,:forename,:surname,:address,:town,:county,:phoneNo,:custType)";
OracleParameter forename = new OracleParameter(":forename", OracleType.VarChar);
forename.Value = customer.Forename;
cmd.Parameters.Add(forename);
OracleParameter surname = new OracleParameter(":surname", OracleType.VarChar);
surname.Value = customer.Surname;
cmd.Parameters.Add(surname);
OracleParameter address = new OracleParameter(":address", OracleType.VarChar);
address.Value = customer.Address;
cmd.Parameters.Add(address);
OracleParameter town = new OracleParameter(":town", OracleType.VarChar);
town.Value = customer.Town;
cmd.Parameters.Add(town);
OracleParameter county = new OracleParameter(":county", OracleType.VarChar);
county.Value = customer.County;
cmd.Parameters.Add(county);
OracleParameter phone_no = new OracleParameter(":phoneNo", OracleType.Number);
phone_no.Value = customer.Phone_No;
cmd.Parameters.Add(phone_no);
OracleParameter cust_Type = new OracleParameter(":custType", OracleType.VarChar);
cust_Type.Value = customer.Cust_Type;
cmd.Parameters.Add(cust_Type);
cmd.Connection = connection;
cmd.ExecuteNonQuery();
transaction.Commit();
connection.Close();
MessageBox.Show("Insert complete successfully");
}
catch (Exception)
{
MessageBox.Show("Insert did not complete Succesfully", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
public void GetAccountByCert(
string CertValue, string CertType,
out string AcctId,
out int rc, out string msg
)
{
rc = -1;
msg = null;
AcctId = "";
try
{
log.Info(string.Format(
"GetAccountByCert CertValue={0} CertType={1}", CertValue, CertType));
using (OracleConnection objConn = new OracleConnection(connStr))
{
objConn.Open();
OracleTransaction transaction;
transaction = objConn.BeginTransaction();
OracleCommand objCmd = new OracleCommand(schemaPrefixSap + "crmws_functions.get_account_by_cert", objConn);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Transaction = transaction;
objCmd.Parameters.Clear();
objCmd.Parameters.Add("p_cert_value", OracleType.VarChar).Value = dbNlv(CertValue);
objCmd.Parameters.Add("p_cert_type", OracleType.VarChar).Value = dbNlv(CertType);
objCmd.Parameters.Add("p_acct_id", OracleType.VarChar, 200).Direction = ParameterDirection.Output;
objCmd.Parameters.Add("p_rc", OracleType.Number).Direction = ParameterDirection.Output;
objCmd.Parameters.Add("p_msg", OracleType.VarChar, 500).Direction = ParameterDirection.Output;
objCmd.ExecuteNonQuery();
rc = int.Parse(objCmd.Parameters["p_rc"].Value.ToString());
msg = objCmd.Parameters["p_msg"].Value.ToString();
AcctId = objCmd.Parameters["p_acct_id"].Value.ToString();
transaction.Commit();
objConn.Close();
}
}
catch (Exception e)
{
rc = -1;
msg = e.Message;
log.Error("GetAccountByCert exception", e);
}
log.Info(string.Format(
"GetAccountByCert finished: rc={0}, msg={1}, AcctId={2}",
rc, msg, AcctId));
}
private void btnSave_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtRoleName.Text.Trim()))
{
MessageBox.Show("请输入角色名称");
txtRoleName.Focus();
}
else
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
connection.Open();
OracleCommand cmd = connection.CreateCommand();
OracleTransaction transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = transaction;
try
{
cmd.CommandText = "insert into sys_role (role_id,role_name,super_id,description) values (seq_sys_role_role_id.nextval,'" + txtRoleName.Text.Trim() + "','0','" + txtRoleDirs.Text.Trim() + "')";
cmd.ExecuteNonQuery();
for (int i = 0; i < treeView2.Nodes.Count;i++ )
{
if (treeView2.Nodes[i].Checked == true)
{
cmd.CommandText = "insert into SYS_ROLE_MODULE (ID,role_id,module_id) values (seq_SYS_ROLE_MODULE_id.nextval,seq_sys_role_role_id.currval,'" + treeView2.Nodes[i].Tag.ToString() + "')";
cmd.ExecuteNonQuery();
for (int j = 0; j < treeView2.Nodes[i].Nodes.Count;j++ )
{
if (treeView2.Nodes[i].Nodes[j].Checked == true)
{
cmd.CommandText = "insert into SYS_ROLE_MODULE_ACTION t" +
" (t.id, t.roleid, t.modeleid, t.actioncode)" +
"values" +
" (seq_action.nextval,seq_sys_role_role_id.currval, '" + treeView2.Nodes[i].Tag.ToString() + "', '" + treeView2.Nodes[i].Nodes[j].Tag.ToString() + "')";
cmd.ExecuteNonQuery();
}
}
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
this.DialogResult = DialogResult.OK;
this.Close();
}
}
}
}
/// <summary>
///保存一个文件
/// </summary>
private void SaveSingle()
{
if (!ISBool)
{
MessageBox.Show("要修改的文件跟加载的文件不一致，请重新加载文件");
return;
}
if (FileList == null)
{
MessageBox.Show("请选择要保存的文件");
return;
}
if (!ValidState(FileList))
{
return;
}
//
OracleTransaction trans = Oracon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
mgr.SetTrans(trans);
string strSql = "select seq_ftp_seq.nextval from dual";
foreach (FtpFile obj in FileList)
{
if (this.myEditType == EditType.Add)
{
obj.PrimaryID = mgr.ReturnOne(Oracon, strSql);
}
obj.LocalDirectory = this.txtFilePath.Text;
if (SaveInfo(obj, trans) <= 0)
{
MessageBox.Show("保存失败");
return;
}
SaveHandle(obj);
}
trans.Commit();
if (cbContinue.Checked)
{
this.Clear();
// string strSql = "select seq_ftp_seq.nextval from dual";
this.lbID.Text = mgr.ReturnOne(Oracon, strSql);
}
else
{
this.Close();
}
}
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 void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
/*********************************************************
* OLEDB Provider for SQL Server does not allow nested transactions
* http://support.microsoft.com/kb/177138/EN-US/
* http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316872&
*/
if ((ConnectedDataProvider.GetDbType(con) == DataBaseServer.SQLServer) ||
(ConnectedDataProvider.GetDbType(con) == DataBaseServer.Oracle) ||
(ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) ||
(ConnectedDataProvider.GetDbType(con) == DataBaseServer.DB2))
{
Log(string.Format("Test skipped, nested transactions are not supported in {0}", ConnectedDataProvider.GetDbType(con)));
return;
}
// How To Implement Nested Transactions with Oracle
// http://support.microsoft.com/kb/187289/EN-US/
OracleTransaction txnOuter = null;
OracleTransaction txnInner = null;
try
{
BeginCase("Check Outer Transaction Isoloation Level");
txnOuter = con.BeginTransaction();
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("Check Inner Transaction Isoloation Level");
Compare(txnOuter.IsolationLevel,IsolationLevel.RepeatableRead);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
private void btnSaveContinue_Click(object sender, EventArgs e)
{
if (tbName.Text == "")
{
MessageBox.Show("请输入员工姓名");
tbName.Focus();
}
else if (tbStaffNum.Text == "")
{
MessageBox.Show("请输入员工编号");
tbStaffNum.Focus();
}
else
{
using (OracleConnection connection = new OracleConnection(FrmLogin.strDataCent))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
if (this.Text == "增加员工")
{
vAddStaff(command, transaction);
btnClear_Click(sender, e);
}
else if (this.Text == "修改员工")
{
vUpdateStaff(command, transaction);
btnClear_Click(sender, e);
}
}
catch (Exception exception)
{
transaction.Rollback();
MessageBox.Show(exception.ToString());
}
finally
{
connection.Close();
}
}
}
}
public void IsolationLevelChaos() {
Exception exp = null;
MonoTests.System.Data.Utils.DataBaseServer dbServer = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleTransaction txn = null;
//supported only in SQLServer
if (dbServer == MonoTests.System.Data.Utils.DataBaseServer.SQLServer) {
try {
BeginCase("IsolationLevel = Chaos");
con.Open();
txn=con.BeginTransaction(IsolationLevel.Chaos);
Compare(txn.IsolationLevel,IsolationLevel.Chaos);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null; if (con.State == ConnectionState.Open) con.Close();}
}
}
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>
/// 分段施工计划
/// </summary>
/// <param name="projectid"></param>
/// <param name="block"></param>
/// <param name="drawing"></param>
/// <param name="plan_pre_start"></param>
/// <param name="plan_pre_finish"></param>
/// <param name="act_pre_start"></param>
/// <param name="act_pre_finish"></param>
/// <param name="plan_ass_start"></param>
/// <param name="plan_ass_finish"></param>
/// <param name="act_ass_start"></param>
/// <param name="act_ass_finish"></param>
/// <param name="username"></param>
/// <param name="remark"></param>
public static void BlockConstructionTab(string projectid, string block,string drawing,DateTime plan_pre_start,DateTime plan_pre_finish, DateTime act_pre_start,DateTime act_pre_finish, DateTime plan_ass_start, DateTime plan_ass_finish, DateTime act_ass_start, DateTime act_ass_finish,string username,string remark)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
string queryString = "SP_BlockConstructionTab";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("projectid_in",OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("block_in", OracleType.VarChar).Value = block;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("plan_pre_start_in", OracleType.DateTime).Value = plan_pre_start;
cmd.Parameters.Add("plan_pre_finish_in", OracleType.DateTime).Value = plan_pre_finish;
cmd.Parameters.Add("act_pre_start_in", OracleType.DateTime).Value = act_pre_start;
cmd.Parameters.Add("act_pre_finish_in",OracleType.DateTime).Value = act_pre_finish;
cmd.Parameters.Add("plan_ass_start_in",OracleType.DateTime).Value = plan_ass_start;
cmd.Parameters.Add("plan_ass_finish_in",OracleType.DateTime).Value = plan_ass_finish;
cmd.Parameters.Add("act_ass_start_in",OracleType.DateTime).Value = act_ass_start;
cmd.Parameters.Add("act_ass_finish_in",OracleType.DateTime).Value = act_ass_finish;
cmd.Parameters.Add("username_in",OracleType.VarChar).Value = username;
cmd.Parameters.Add("remark_in",OracleType.VarChar).Value = remark;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (OracleException ee)
{
trans.Rollback();
MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleTransaction txn = con.BeginTransaction();
try
{
BeginCase("check connection");
Compare(txn.Connection,con);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
//check exception - using a command with the same connection as the transaction
OracleCommand cmd = new OracleCommand("Select * from Employees",con);
//Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.
//The Transaction property of the command has not been initialized.
try
{
BeginCase("Command that uses the transaction connection - exception");
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex){exp=ex;}
Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName);
exp = null;
}
catch(Exception ex) {exp = ex;}
finally {EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
/*
* Setting up parameters and inserting values into the car_class if a new rate needs to be added
* to the car class table
*/
public void addCar_Rate(Car_Rate_Entity_Class rate_Class)
{
/*
* Creating a connection to connect to the database calling Orc class that returns the connection string
*/
OracleConnection connection = new OracleConnection(OrcConnection.connection());
OracleCommand cmd = new OracleCommand();
try
{
connection.Open();
OracleTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO Car_Class VALUES(car_rate_seq.NEXTVAL,:description,:car_rate)";
var description = new OracleParameter(":description", OracleType.VarChar);
description.Value = rate_Class.Description;
cmd.Parameters.Add(description);
var car_rate = new OracleParameter(":car_rate", OracleType.Number);
car_rate.Value = rate_Class.Car_rate;
cmd.Parameters.Add(car_rate);
cmd.Connection = connection;
cmd.ExecuteNonQuery();
transaction.Commit();
connection.Close();
MessageBox.Show("Insert complete successfully");
}
catch (Exception)
{
MessageBox.Show("Insert Did not complete successfully");
}
}
public static bool BuildBatchPackage(string str1, string str2)
{
using (OracleConnection connection = new OracleConnection(SQLHelper.ConnectionString))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction;
// Start a local transaction
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
command.CommandText = new StringBuilder()
.Append("INSERT INTO FX_PACKAGECHECK(PACKAGEID, OPERATOR, MODIFYTIME, DELTF, STATE, NOREG, PACKAGENUM) ")
.Append("VALUES (" + str1 + ")").ToString();
command.ExecuteNonQuery();
command.CommandText = new StringBuilder()
.Append("INSERT INTO FX_PACKAGECHECKLIST (ID, PACKAGEID, KWID, BOOKID, SCALE, ORNAMENT) ")
.Append("VALUES ('" + generate() + "', " + str2 + ")").ToString();
command.ExecuteNonQuery();
transaction.Commit();
return true;
}
catch (Exception ex)
{
transaction.Rollback();
logger.ErrorException("保存包件信息引发异常", ex);
return false;
}
}
}
private void btnDeleteCase_Click(object sender, EventArgs e)
{
bool fgcheckJD = false;
if (this.txtTSDH.Text.ToString() == "")
{
fgcheckJD = false;
MessageBox.Show("您还没有保存结算单，请保存后再进行选单减单操作");
}
else if (selection.SelectedCount == 0)
{
fgcheckJD = false;
MessageBox.Show("选择要减去的单据");
}
else
{
fgcheckJD = true;
}
if (fgcheckJD == true)
{
using (OracleConnection connection = new OracleConnection(StrCon))
{
OracleCommand cmd = connection.CreateCommand();
OracleTransaction transaction;
connection.Open();
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = transaction;
try
{
for (int i = 0; i < selection.SelectedCount; ++i)
{
int RowIndex = selection.GetSelectedRowIndex(i);
int RowHandle = gridView1.GetRowHandle(RowIndex);
string strXSTSDMXID = gridView1.GetRowCellDisplayText(RowHandle, colXSTSDMXID).ToString();
cmd.CommandText = "INSERT INTO TEMP_SAVE_ID (TEMPID, ID) Values (temp_save_id_seq.nextval, '" + strXSTSDMXID + "')";
cmd.ExecuteNonQuery();
}
selection.ClearSelection();
SelectCountClear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Jt_c_XSTSD_JD";
cmd.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.txtTSDH.Tag.ToString().Trim();
cmd.Parameters.Add("Descerr", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
transaction.Commit();
unitOfWork1.DropIdentityMap();
xpServerCollectionSource1.Reload();
MessageBox.Show(cmd.Parameters["Message"].Value.ToString());
}
catch (Exception ex)
{
transaction.Rollback();
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
}
}
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return(new OracleTransaction(con.BeginTransaction(), this));
}
private void btnConfirm_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
string strXSHZDID = "";
if (selection.SelectedCount == 0)
{
MessageBox.Show("请先选择需要确认的记录");
}
else
{
for (int i = 0; i < selection.SelectedCount; ++i)
{
int RowIndex = selection.GetSelectedRowIndex(i);
int RowHandle = gridView1.GetRowHandle(RowIndex);
string strZT = gridView1.GetRowCellValue(RowHandle, colZT).ToString();
if (strZT == "02")
{
string strTemp = gridView1.GetRowCellDisplayText(RowHandle, colXSHZDID);
strXSHZDID += "\'" + strTemp + "\', ";
}
else
{
strXSHZDID = "";
MessageBox.Show("所选记录已被确认，请重新选择！");
break;
}
}
selection.ClearSelection();
vClearSelectSummary();
if (!String.IsNullOrEmpty(strXSHZDID))
{
using (OracleConnection connection = new OracleConnection(FrmLogin.strDataCent))
{
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
int index = strXSHZDID.LastIndexOf("'");
strXSHZDID = strXSHZDID.Substring(0, index + 1).Trim();
try
{
string strSQL = "update jc_c_xshzd set zt = '15', czrq = sysdate where XSHZDID in (" + strXSHZDID + ")";
command.CommandText = strSQL;
command.ExecuteNonQuery();
transaction.Commit();
unitOfWork1.DropIdentityMap();
xpServerCollectionSource1.Reload();
MessageBox.Show("所选记录已确认成功");
}
catch (Exception exception)
{
transaction.Rollback();
MessageBox.Show(exception.ToString());
}
finally
{
connection.Close();
}
}
}
}
}
/// <summary>
/// 开始事务
/// </summary>
/// <param name="conn">OracleConnection连接对象</param>
public static void BeginTransaction(OracleConnection connObjection)
{
if (Transaction == null||Transaction.Connection != connObjection)
{
Transaction = connObjection.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
/// <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;
}
/// <summary>
/// 通过事务执行SQL语句命令
/// </summary>
/// <param name="connection"></param>
/// <param name="SQLStringList"></param>
public static void ExecuteSQLTransaction(OracleConnection connection, List<string> SQLStringList)
{
connection.Open();
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleTransaction transaction = connection.BeginTransaction();
if (transaction != null)
{
//command.Transaction = transaction;
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string str = SQLStringList[i].ToString();
if (str.Trim().Length > 1)
{
command.CommandText = str;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (OracleException e)
{
transaction.Rollback();
string stringlist = "";
foreach (var str in SQLStringList)
{
stringlist += str.ToString() + "\r\n";
}
LogHelper.WriteLog("MsOracle.ExecuteSQLTransaction", stringlist, e);
throw new System.Exception(e.Message);
}
finally
{
connection.Close();
}
}
//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"));
}
}
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");
}
