// 使用事务时使用
public static DataSet GetDataSet(
Oracle.ManagedDataAccess.Client.OracleTransaction tran,
string commandText,
List <SqlParameter> paramsList,
CommandType argCommandType = CommandType.StoredProcedure)
{
DataSet r = new DataSet();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = tran.Connection.CreateCommand();
cmd.Transaction = tran;
cmd.CommandType = argCommandType;
cmd.CommandText = commandText;
if (paramsList != null && paramsList.Count > 0)
{
cmd.Parameters.AddRange(paramsList.ToArray());
}
OracleDataAdapter sda = new OracleDataAdapter(cmd);
sda.Fill(r);
return(r);
}
public static System.Data.DataSet GetDataSet(string connectionString,
string commandText,
List <SqlParameter> paramsList,
bool isRollbackForTest = false,
CommandType argCommandType = CommandType.StoredProcedure
)
{
DataSet r = new DataSet();
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = null;
Oracle.ManagedDataAccess.Client.OracleTransaction tran = null;
if (isRollbackForTest == true) // 实现测试回滚
{
tran = conn.BeginTransaction();
cmd = tran.Connection.CreateCommand();
cmd.Transaction = tran;
}
else // 普通执行模式
{
cmd = conn.CreateCommand();
}
cmd.CommandType = argCommandType;
cmd.CommandText = commandText;
if (paramsList != null && paramsList.Count > 0)
{
cmd.Parameters.AddRange(paramsList.ToArray());
}
OracleDataAdapter sda = new OracleDataAdapter(cmd);
sda.Fill(r);
// 实现测试回滚
if (isRollbackForTest == true)
{
tran.Rollback();
}
}
return(r);
}
/// <summary>
/// 執行多條SQL語句，實現數據庫事務。
/// </summary>
/// <param name="SQLStringList">多條SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl))
{
conn.Open();
Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int num = 0;
for (int i = 0; i < SQLStringList.Count; i++)
{
string SQL = SQLStringList[i].ToString();//獲取SQL語句
if (SQL.Trim().Length > 1)
{
cmd.CommandText = SQL;
num = cmd.ExecuteNonQuery();
}
tx.Commit(); //提交事務
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
}
return(num); //返回執行結果數量
}
catch (Oracle.ManagedDataAccess.Client.OracleException E)
{
tx.Rollback();//事務回滾
throw new Exception(E.Message);
}
}
}
private void IQCReCheck(DataTable dtpub)
{
int m = 0;
string states = "";
if (checktype == "NGToOK")
{
states = "OK";
}
else if (checktype == "OKToNG")
{
states = "NG";
}
else if (checktype == "NGTo特采")
{
states = "OK";
}
string remarks = "", noteid = "";
if (checktype == "NGTo特采")
{
remarks = txtnoteid.Text + checktype + txtreason.Text;
noteid = txtnoteid.Text;
}
else
{
remarks = checktype + txtreason.Text;
}
DataTable dt = dtpub;
DataTable dtalready = new DataTable();
SqlConnection conn = new SqlConnection(DbAccess.connSql);
//OracleConnection orac = new OracleConnection(DbAccess.connOral);
Oracle.ManagedDataAccess.Client.OracleConnection orac = new Oracle.ManagedDataAccess.Client.OracleConnection(DbAccess.connOral);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (orac.State == ConnectionState.Closed)
{
orac.Open();
}
SqlTransaction tran1 = conn.BeginTransaction();
//OracleTransaction oratran = orac.BeginTransaction();
Oracle.ManagedDataAccess.Client.OracleTransaction oratran = orac.BeginTransaction();
try
{
Int32 org_id = 0, item_id = 0, tran_id = 0;
Int64 recepid = 0;
string org_idnew = "";
int instockqty = 0;
int restenableqty = int.Parse(txtqty.Text);
string msg = "", barcodemsg = "", msgcurrent = "", barcodemsgcurrent = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
int alreadyqty = 0;
org_idnew = dt.Rows[j]["ORGANIZATION_CODE"].ToString();
org_id = Int32.Parse(dt.Rows[j]["ship_to_org_id"].ToString());
item_id = Int32.Parse(dt.Rows[j]["INVENTORY_ITEM_ID"].ToString());
tran_id = Int32.Parse(dt.Rows[j]["transaction_id"].ToString());
//recepid = Int32.Parse(dt.Rows[0]["receipt_num"].ToString());
recepid = Int64.Parse(dt.Rows[0]["receipt_num"].ToString());
//判断当前行可检验数量大于界面录入数量,则把所有数量录入到第一笔.
if (int.Parse(dt.Rows[j]["primary_quantity"].ToString()) >= restenableqty)
{
//则把用户输入的检验数量作为此行的检验数
instockqty = restenableqty;
//20150312新增
//x表示还在接口表中的数量
//int x = ERPinterface(tran_id.ToString());
//用临时变量记录此次操作数量
int x = alreadyqty;
//y表示还可处理的数量
int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x;
if (y >= restenableqty)
{
instockqty = restenableqty;
}
else if (y > 0 && y < restenableqty)
{
instockqty = y;
}
else
{
continue;
}
//20150312新增
barcodemsg = barcodemsg + insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, Int32.Parse(dt.Rows[j]["primary_quantity"].ToString()),
Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1);
if (barcodemsg.IndexOf("成功") >= 0)
{
if (states == "OK")
{
msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
else
{
msg = msg + msgcurrent;
txtqty.Text = "0";
break;
}
}
else if (states == "NG")
{
msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
else
{
msg = msg + msgcurrent;
txtqty.Text = "0";
break;
}
}
}
else
{
//防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔
continue;
}
}
//如果当前行的数量小于录入数量,则把当前行先进行检验通过该行的数量,同时把总共要检验的数量-第一笔已录的=作为剩下的可录入数量，再进行下一次循环
else
{
instockqty = int.Parse(dt.Rows[j]["primary_quantity"].ToString());
//20150312新增
//x表示还在接口表中的数量
//int x = ERPinterface(tran_id.ToString());
//用临时变量记录此次操作数量
try
{
DataRow[] rwalready = dtalready.Select(" id='" + tran_id + "'");
if (rwalready.Length > 0)
{
for (int z = 0; z < rwalready.Length; z++)
{
alreadyqty = alreadyqty + int.Parse(rwalready[z]["qty"].ToString());
}
}
}
catch
{
dtalready.Columns.Add("id", typeof(string));
dtalready.Columns.Add("qty", typeof(int));
}
int x = alreadyqty;
//y表示还可处理的数量
int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x;
if (y >= restenableqty)
{
instockqty = restenableqty;
}
else if (y > 0 && y < restenableqty)
{
instockqty = y;
}
else
{
continue;
}
//20150312新增
barcodemsgcurrent = insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, instockqty,
Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1);
barcodemsg = barcodemsg + barcodemsgcurrent;
if (barcodemsgcurrent.IndexOf("成功") >= 0)
{
if (states == "OK")
{
msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
msg = msg + msgcurrent;
restenableqty = restenableqty - instockqty;
txtqty.Text = restenableqty.ToString();
DataRow newRow;
newRow = dtalready.NewRow();
newRow["id"] = tran_id;
newRow["qty"] = instockqty.ToString();
dtalready.Rows.Add(newRow);
continue;
}
else if (states == "NG")
{
msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran);
if (msgcurrent.IndexOf("Error") >= 0)
{
msg = msg + msgcurrent;
break;
}
msg = msg + msgcurrent;
restenableqty = restenableqty - instockqty;
txtqty.Text = restenableqty.ToString();
DataRow newRow;
newRow = dtalready.NewRow();
newRow["id"] = tran_id;
newRow["qty"] = instockqty.ToString();
dtalready.Rows.Add(newRow);
continue;
}
}
else
{
//防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔
continue;
}
}
}
if (msg.IndexOf("Error") >= 0)
{
this.lblinfo.ForeColor = Color.Red;
lblinfo.Text = msg + ";ERP写入失败!";
tran1.Rollback();
oratran.Rollback();
}
else if (barcodemsg.IndexOf("失败") >= 0)
{
lblinfo.ForeColor = Color.Red;
lblinfo.Text = barcodemsg + ";ERP写入失败!";
tran1.Rollback();
oratran.Rollback();
}
else
{
lblinfo.ForeColor = Color.Blue;
lblinfo.Text = "改判成功" + barcodemsg + ";" + msg;
txtqty.Text = "";
this.txtreason.Text = "";
tran1.Commit();
oratran.Commit();
}
}
catch (Exception ex)
{
lblinfo.Text = ex.ToString();
tran1.Rollback();
oratran.Rollback();
}
}
public string insertOracleReject(Int32 tran_id, Int32 pqty, string p_inspection_code, Int32 p_reason_id, string p_comments, string baruser, Oracle.ManagedDataAccess.Client.OracleConnection cn, Oracle.ManagedDataAccess.Client.OracleTransaction tran)
{
Oracle.ManagedDataAccess.Client.OracleCommand sqlCmd;
Oracle.ManagedDataAccess.Client.OracleParameter sqlParm;
sqlCmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "CUX_RCV_TRS_PKG.RCV_REJECT";
sqlCmd.Connection = cn;
sqlCmd.Transaction = tran;
//sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter("x_err_msg", Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, 50);
//sqlParm.Direction = ParameterDirection.Output;
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter("X_ERR_MSG", Oracle.ManagedDataAccess.Client.OracleDbType.NVarchar2, ParameterDirection.Output);
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_rcv_transaction_id";
//sqlParm.DbType = DbType.Int32;
sqlParm.OracleDbType = OracleDbType.Int32;
sqlParm.Value = tran_id;
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_qty";
//sqlParm.DbType = DbType.Int32;
sqlParm.OracleDbType = OracleDbType.Int32;
sqlParm.Value = pqty;
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_inspection_code";
//sqlParm.DbType = DbType.String;
sqlParm.OracleDbType = OracleDbType.NVarchar2;
sqlParm.Value = p_inspection_code;
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_reason_id";
//sqlParm.DbType = DbType.String;
sqlParm.OracleDbType = OracleDbType.Int32;
sqlParm.Value = p_reason_id;
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_comments";
//sqlParm.DbType = DbType.String;
sqlParm.OracleDbType = OracleDbType.NVarchar2;
sqlParm.Value = p_comments;
sqlCmd.Parameters.Add(sqlParm);
sqlParm = new Oracle.ManagedDataAccess.Client.OracleParameter();
sqlParm.Direction = ParameterDirection.Input;
sqlParm.ParameterName = "p_barcode_user";
//sqlParm.DbType = DbType.String;
sqlParm.OracleDbType = OracleDbType.NVarchar2;
sqlParm.Value = baruser;
sqlCmd.Parameters.Add(sqlParm);
sqlCmd.ExecuteNonQuery();
return(sqlCmd.Parameters["X_ERR_MSG"].Value.ToString());
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OracleClient.OracleConnection orlConnection = null;
OracleClient.OracleTransaction orlTransaction = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Start a new transaction.
orlTransaction = orlConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = orlTransaction;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
orlTransaction.Commit();
// Close the database connection.
orlConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (orlTransaction != null)
{
orlTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
