/// <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();
}
}
/// <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();
}
}
}