public bool CheckPk(OracleTransaction trans)
{
OracleConnection con = trans.Connection;
OracleCommand checkCmd = new OracleCommand();
checkCmd.Connection = con;
checkCmd.Transaction = trans;
checkCmd.CommandText = "SELECT ROWID FROM RBGZ WHERE TRUNC(SJ)=:RQ AND GAOLU=:GAOLU AND BC=:BC";
checkCmd.Parameters.Add(":RQ", OracleType.DateTime).Value = this.SJ;
checkCmd.Parameters.Add(":GAOLU", OracleType.Int32).Value = this.GAOLU;
checkCmd.Parameters.Add(":BC", OracleType.VarChar, 6).Value = this.BC;
OracleDataReader dr = checkCmd.ExecuteReader();
bool result = false;
if (dr.Read())
{
OracleString rowid = dr.GetOracleString(0);
if (!rowid.IsNull)
{
this.RowId = rowid;
result = true;
}
}
dr.Close();
return(result);
}
public ProductCard_Status GetProductCardStatus(string serialNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard_Status pcCard = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_VER.GET_PC_STATUS"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard_Status();
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.MC_NO = OraDataReader.Instance.GetString("MC_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.STATUS = OraDataReader.Instance.GetString("STATUS");
if (!OraDataReader.Instance.IsDBNull("PROCESS_DATE"))
{
pcCard.PROCESS_DATE = OraDataReader.Instance.GetDateTime("PROCESS_DATE");
}
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.NG_QTY = OraDataReader.Instance.GetInteger("NG_QTY");
pcCard.REP_QTY = OraDataReader.Instance.GetInteger("REP_QTY");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
pcCard = null;
throw ex;
}
return(pcCard);
}
public MaterialCard ScanMat_Stock(string serialno, out string resultMsg)
{
resultMsg = string.Empty;
MaterialCard mtl = null;
try
{
ProcParam procPara = new ProcParam(3)
{
ProcedureName = "SCANNER_MAT_PACK.SCAN_MAT_STOCK"
};
procPara.AddParamRefCursor(0, "IO_CURSOR");
procPara.AddParamInput(1, "strSERIAL_NO", serialno);
procPara.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 30);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
if (OraDataReader.Instance.OraReader.HasRows)
{
OraDataReader.Instance.OraReader.FetchSize = OraDataReader.Instance.OraReader.RowSize;
while (OraDataReader.Instance.OraReader.Read())
{
mtl = new MaterialCard();
mtl.PARTY_ID = OraDataReader.Instance.GetString("PARTY_ID");
mtl.PARTY_NAME = OraDataReader.Instance.GetString("PARTY_NAME");
mtl.MTL_CODE = OraDataReader.Instance.GetString("MTL_CODE");
mtl.MTL_NAME = OraDataReader.Instance.GetString("MTL_NAME");
mtl.MTL_GRADE = OraDataReader.Instance.GetString("MTL_GRADE");
mtl.MTL_COLOR = OraDataReader.Instance.GetString("MTL_COLOR");
mtl.QTY = OraDataReader.Instance.GetDecimal("QTY");
mtl.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
mtl.MIN_QTY = OraDataReader.Instance.GetDecimal("MIN_QTY");
mtl.MAX_QTY = OraDataReader.Instance.GetDecimal("MAX_QTY");
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
OracleString resultDB = (OracleString)procPara.ReturnValue(2);
if (!resultDB.IsNull)
{
resultMsg = resultDB.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(mtl);
}
public ProductCard GetProductCardInfo(string serialNo, string mode, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard pcCard = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_VER.GET_PC_INFO_PROCESS"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamInput(2, "strPROCESS", mode);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard();
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.PROD_SEQ_NO = OraDataReader.Instance.GetString("PROD_SEQ_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.MTL_TYPE = OraDataReader.Instance.GetString("MTL_TYPE");
pcCard.JOB_NO = OraDataReader.Instance.GetString("JOB_NO");
pcCard.SHIFT = OraDataReader.Instance.GetString("SHIFT");
pcCard.LINE_NO = OraDataReader.Instance.GetInteger("LINE_NO");
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.BOX_SCANNED = OraDataReader.Instance.GetInteger("NO_OF_BOX");
pcCard.BOX_QTY = OraDataReader.Instance.GetInteger("TOT_BOX");
pcCard.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString resultDB = (OracleString)procPara.ReturnValue(3);
if (!resultDB.IsNull)
{
resultMessage = resultDB.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pcCard);
}
public string DeleteDeliveryOrder(string doNo, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam paramHDR = new ProcParam(3)
{
ProcedureName = "DO_PACK.DO_HDR_DEL"
};
paramHDR.AddParamInput(0, "strDO_NO ", doNo);
paramHDR.AddParamInput(1, "strUSER_ID", userid);
paramHDR.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)paramHDR.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
// Implementation of IOracleCustomType.ToCustomObject()
public virtual void ToCustomObject(OracleConnection con, object pUdt)
{
// Convert from the Oracle Object to a Custom Type
// Get the "NAME" attribute
// If the "NAME" attribute is NULL, then null will be returned
m_name = (string)OracleUdt.GetValue(con, pUdt, "NAME");
// The "NAME" attribute can also be accessed by specifying index 0
// m_name = (string)OracleUdt.GetValue(con, pUdt, 0);
// Get the "ADDRESS" attribute
// If the "ADDRESS" attribute is NULL, then OracleString.Null will be returned
m_address = (OracleString)OracleUdt.GetValue(con, pUdt, "ADDRESS");
// The "NAME" attribute can also be accessed by specifying index 1
// m_address = (OracleString)OracleUdt.GetValue(con, pUdt, 1);
// Get the "AGE" attribute
// If the "AGE" attribute is NULL, then null will be returned
m_age = (int?)OracleUdt.GetValue(con, pUdt, "AGE");
// The "AGE" attribute can also be accessed by specifying index 2
// m_age = (int?)OracleUdt.GetValue(con, pUdt, 2);
}
public string DeleteMTLType(string mtlTypeNo, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(3);
param.ProcedureName = "MASTER_PACK.M_MTL_TYPE_DEL";
param.AddParamInput(0, "strSEQ_NO", mtlTypeNo);
param.AddParamInput(1, "strUSER_ID", userid);
param.AddParamOutput(2, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(2);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
/// <summary>
/// Get XMLType data as Oracle Type
/// </summary>
/// <param name="dr"></param>
public static void GetOraType(OracleDataReader dr)
{
try
{
// Get the XMLType column as an OracleXmlType
OracleXmlType xml = dr.GetOracleXmlType(0);
// Print out the xml data in the OracleXmlType object
Console.WriteLine("Get XMLType column using GetOracleXmlType(i)");
Console.WriteLine("Value: " + xml.Value);
Console.WriteLine(String.Empty);
xml.Dispose();
// Get the XMLType column as an OracleString
OracleString ostr = dr.GetOracleString(0);
// Print out the xml data in the OracleString object
Console.WriteLine("Get XMLType column using GetOracleString(i)");
Console.WriteLine(ostr);
Console.WriteLine(String.Empty);
// Get the XMLType column as an Oracle type
Object obj = dr.GetOracleValue(0);
// Print the type name of the Oracle type object
Console.WriteLine("Get XMLType column using GetOracleValue(i)");
Console.WriteLine("TypeName: " + obj.GetType().AssemblyQualifiedName);
Console.WriteLine(String.Empty);
}
catch (Exception e)
{
Console.WriteLine("GetOraType Error: {0}", e.Message);
}
}
public bool IsAuthenticationPass(string login, string password)
{
bool isValid = false;
try
{
ProcParam procPara = new ProcParam(3)
{
ProcedureName = "ADM_PKG.CHECK_AUTHENTICATION_USER"
};
procPara.AddParamReturn(0, "ReturnValue", Oracle.DataAccess.Client.OracleDbType.Varchar2, 30);
procPara.AddParamInput(1, "strLogin", login);
procPara.AddParamInput(2, "strPwd", password);
GlobalDB.Instance.DataAc.ExecuteNonQuery(procPara);
if (GlobalDB.Instance.DataAc.LastException != null)
{
throw GlobalDB.Instance.DataAc.LastException;
}
OracleString result = (OracleString)procPara.ReturnValue(0);
if (!result.IsNull)
{
isValid = !string.IsNullOrEmpty(result.Value);
}
}
catch (Exception ex)
{
throw ex;
}
return(isValid);
}
public void GetOracleStringWithContentReturnsContent()
{
var oraString = new OracleString("Foo");
var result = OracleValueConverter.Convert <string>(oraString);
result.Should().Be("Foo");
}
public void GetOracleStringAsNullReturnsNull()
{
var oraString = new OracleString();
var result = OracleValueConverter.Convert <string>(oraString);
result.Should().BeNull();
}
public string UpdateMaterial(Material material, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(16)
{
ProcedureName = "MASTER_PACK.M_MATERIAL_UPD"
};
param.AddParamInput(0, "strMTL_SEQ_NO", material.MTL_SEQ_NO);
param.AddParamInput(1, "strMTL_CODE", material.MTL_CODE);
param.AddParamInput(2, "strMTL_NAME", material.MTL_NAME);
param.AddParamInput(3, "strMTL_GRADE", material.MTL_GRADE);
param.AddParamInput(4, "strMTL_COLOR", material.MTL_COLOR);
param.AddParamInput(5, "strUNIT", material.UNIT);
param.AddParamInput(6, "strSTD_QTY", material.STD_QTY);
param.AddParamInput(7, "strMIN_QTY", material.MIN_QTY);
param.AddParamInput(8, "strMAX_QTY", material.MAX_QTY);
param.AddParamInput(9, "strPARTY_ID", material.PARTY_ID);
if (material.MTL_IMAGE != null)
{
param.AddParamBLOBInput(10, "strMTL_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(material.MTL_IMAGE));
}
else
{
param.AddParamInput(10, "strMTL_IMAGE", DBNull.Value);
}
param.AddParamInput(11, "strLOCATION_ID", material.LOCATION_ID);
param.AddParamInput(12, "strREMARK", material.REMARK);
param.AddParamInput(13, "strUSER_ID", userid);
param.AddParamInput(14, "strREC_STAT", (material.REC_STAT ? "Y" : "N"));
param.AddParamOutput(15, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(15);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
private string GetBomRevisionSeq(string fileid, string userid)
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(3)
{
ProcedureName = "INTERFACE_PACK.UP_BOM_REVISION"
};
param.AddParamReturn(0, "ReturnValue", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255);
param.AddParamInput(1, "strFILE_ID", fileid);
param.AddParamInput(2, "strUser_id", userid);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)param.ReturnValue(0);
if (!resultDB.IsNull)
{
result = resultDB.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string UpdateProduct(Product prod, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(17)
{
ProcedureName = "MASTER_PACK.M_PRODUCT_UPD"
};
param.AddParamInput(0, "strPROD_SEQ_NO", prod.PROD_SEQ_NO);
param.AddParamInput(1, "strPRODUCT_NO", prod.PRODUCT_NO);
param.AddParamInput(2, "strPRODUCT_NAME", prod.PRODUCT_NAME);
param.AddParamInput(3, "strMATERIAL_TYPE", prod.MATERIAL_TYPE);
param.AddParamInput(4, "strPRODUCTION_TYPE", prod.PRODUCTION_TYPE);
param.AddParamInput(5, "strMC_NO", prod.MC_NO);
param.AddParamInput(6, "strBOX_QTY", prod.BOX_QTY);
param.AddParamInput(7, "strUNIT", prod.UNIT);
if (prod.PROD_IMAGE != null)
{
param.AddParamBLOBInput(8, "strPROD_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(prod.PROD_IMAGE)); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
else
{
param.AddParamInput(8, "strPROD_IMAGE", DBNull.Value); //this.BitmapToByteArray(prod.PROD_IMAGE)
}
//param.AddParamBLOBInput(8, "strPROD_IMAGE", OracleDbType.Blob, this.BitmapToByteArray(prod.PROD_IMAGE));
param.AddParamInput(9, "strREMARK", prod.REMARK);
param.AddParamInput(10, "strREC_STAT", (prod.REC_STAT ? "Y" : "N"));
param.AddParamInput(11, "strUSER_ID", userid);
//add new parameter on 07-Jun-2011
param.AddParamInput(12, "strCUST_PROD_NO", prod.CUST_PROD_NO);
param.AddParamInput(13, "strBUYER", prod.BUYER_PRICE);
param.AddParamInput(14, "strSELLING", prod.SELLING_PRICE);
param.AddParamInput(15, "strCOST", prod.COST_PRICE);
param.AddParamOutput(16, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(16);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public void GetOracleString2StringArray()
{
var oraArray = new OracleString[] { "Foo", "Bar" };
var result = OracleValueConverter.Convert <string[]>(oraArray);
result.Should().BeOfType <string[]>();
result.Should().HaveCount(2);
}
public ProductCard Horizontal_GetUpdatePC_QCInfo(string serialNo, string qcStatus, string userid, out string resultMessage)
{
resultMessage = string.Empty;
ProductCard pcCard = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_HOZ.GET_UPD_PC_QC"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strSERIAL_NO", serialNo);
procPara.AddParamInput(2, "strQC_STATUS", qcStatus);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pcCard = new ProductCard();
pcCard.JOB_NO = OraDataReader.Instance.GetString("JOB_NO");
pcCard.LINE_NO = OraDataReader.Instance.GetInteger("LINE_NO");
pcCard.JOB_LOT = OraDataReader.Instance.GetString("JOB_LOT");
pcCard.PROD_SEQ_NO = OraDataReader.Instance.GetString("PROD_SEQ_NO");
pcCard.PRODUCT_NO = OraDataReader.Instance.GetString("PRODUCT_NO");
pcCard.PRODUCT_NAME = OraDataReader.Instance.GetString("PRODUCT_NAME");
pcCard.MTL_TYPE = OraDataReader.Instance.GetString("MTL_TYPE");
pcCard.SERIAL_NO = OraDataReader.Instance.GetString("SERIAL_NO");
pcCard.QTY = OraDataReader.Instance.GetInteger("QTY");
pcCard.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(3);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pcCard);
}
public Pallet GetUpdatePalletLoading(string loadNo, string palletno, string userid, out string resultMessage)
{
resultMessage = string.Empty;
Pallet pallet = null;
try
{
ProcParam procPara = new ProcParam(5)
{
ProcedureName = "SCANNER_PACK_FG.GET_UPD_PALLET_LOADING"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strPALLET_NO", palletno);
procPara.AddParamInput(2, "strLOADING_NO", loadNo);
procPara.AddParamOutput(3, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(4, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pallet = new Pallet();
pallet.PALLET_NO = OraDataReader.Instance.GetString("PALLET_NO");
pallet.SO_NO = OraDataReader.Instance.GetString("SO_NO");
pallet.PALLET_SEQ = OraDataReader.Instance.GetInteger("PALLET_SEQ");
pallet.PALLET_TOTAL = OraDataReader.Instance.GetInteger("PALLET_TOTAL");
pallet.PALLET_STATUS = OraDataReader.Instance.GetString("PALLET_STATUS");
pallet.PALLET_BOX = OraDataReader.Instance.GetInteger("PALLET_BOX");
pallet.PALLET_PCS = OraDataReader.Instance.GetInteger("PALLET_PCS");
pallet.PARTY_NAME = OraDataReader.Instance.GetString("PARTY_NAME");
pallet.ETA = OraDataReader.Instance.GetString("ETA");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(3);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pallet);
}
public static void insertTest(string connStr)
{
// string sql = @"
//
//select t1_seq.CurrVal FROM DUAL
//";
//string sql = @"BEGIN SELECT 12 INTO :1 FROM DUAL; END;";
string sql = "BEGIN insert INTO t1 (id1) values (t1_seq.NextVal);\n select t1_seq.CurrVal INTO :1 FROM DUAL; END;";
#if ORACLE_PROVIDER
//Oracle provider - download from http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft_11gbeta.html
//Oracle provider (check via TNSPING): "User Id=Scott;Password=tiger;Data Source=orcl9i"
connStr = "User Id=Northwind;Password=linq2;Data Source=XE";
#else
//Microsoft provider: System.Data.OracleClient
//see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1055859&SiteID=1
#endif
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
//OracleCommand cmd = new OracleCommand(sql, conn);
//cmd.CommandType = System.Data.CommandType.Text;
OracleCommand cmd = conn.CreateCommand();
OracleParameter p1 = new OracleParameter("1", OracleType.Number);
p1.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p1);
OracleString os = new OracleString();
try
{
//conn.Open();
try
{
//cmd.CommandText = "begin dbms_output.enable; end;";
cmd.CommandText = sql;
//cmd.ExecuteOracleNonQuery(out os);
object obj1 = cmd.ExecuteScalar();
Type tt2 = obj1.GetType();
}
catch (OracleException ex)
{
//MessageBox.Show(ex.Message, "Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
Console.WriteLine("Failed: " + ex);
}
}
catch (Exception ex)
{
Console.WriteLine("Failed: " + ex);
}
//int result = cmd.ExecuteNonQuery();
//object result = cmd.ExecuteOracleScalar();
object result = cmd.ExecuteScalar();
Type tt = result.GetType();
//result++;
}
public PickQty GetPickInfo(string pickNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
PickQty pickQty = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_FG.GET_PICK_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strPICK_NO", pickNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
pickQty = new PickQty();
pickQty.PICK_NO = OraDataReader.Instance.GetString("PICK_NO");
pickQty.QTY = OraDataReader.Instance.GetInteger("QTY");
pickQty.PICKED_QTY = OraDataReader.Instance.GetInteger("PICKED_QTY");
pickQty.UNIT_ID = OraDataReader.Instance.GetString("UNIT_ID");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(pickQty);
}
public string InsertParty(ref Party party, string userid)
{
string resultMsg = string.Empty;
try
{
ProcParam param = new ProcParam(15);
param.ProcedureName = "MASTER_PACK.M_PARTY_INS";
param.AddParamInOutput(0, "strPARTY_ID", OracleDbType.Varchar2, 100, party.PARTY_ID);
param.AddParamInput(1, "strPARTY_NAME", party.PARTY_NAME);
param.AddParamInput(2, "strPARTY_TYPE", party.PARTY_TYPE);
param.AddParamInput(3, "strADD1", party.ADD1);
param.AddParamInput(4, "strADD2", party.ADD2);
param.AddParamInput(5, "strADD3", party.ADD3);
param.AddParamInput(6, "strADD4", party.ADD4);
param.AddParamInput(7, "strTEL", party.TEL);
param.AddParamInput(8, "strFAX", party.FAX);
param.AddParamInput(9, "strEMAIL", party.EMAIL);
param.AddParamInput(10, "strPIC", party.PIC);
param.AddParamInput(11, "strREMARK", party.REMARK);
param.AddParamInput(12, "strREC_STAT", (party.REC_STAT ? "Y" : "N"));
param.AddParamInput(13, "strUSER_ID", userid);
param.AddParamOutput(14, "RESULTMSG", OracleDbType.Varchar2, 255);
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(14);
if (!result.IsNull)
{
resultMsg = result.Value;
OracleString resultPartyID = (OracleString)param.ReturnValue(0);
if (!resultPartyID.IsNull)
{
party.PARTY_ID = resultPartyID.Value;
}
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
static void Main(string[] args)
{
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
// Tạo một đối tượng Command để gọi hàm Get_Emp_No.
OracleCommand cmd = new OracleCommand("Get_Emp_No", conn);
// Kiểu của Command là StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
// ** Chú ý: Với Oracle, tham số trả về phải được thêm vào đầu tiên.
// Tạo một đối tượng Parameter, lưu trữ kết quả trả về của hàm (Varchar2(50)).
OracleParameter resultParam = new OracleParameter("@Result", OracleDbType.Varchar2, 50);
// ReturnValue
resultParam.Direction = ParameterDirection.ReturnValue;
// Thêm vào danh sách tham số.
cmd.Parameters.Add(resultParam);
// Thêm tham số @p_Emp_Id và sét giá trị của nó = 100.
cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value = 100;
// Gọi hàm.
cmd.ExecuteNonQuery();
string empNo = null;
if (resultParam.Value != DBNull.Value)
{
Console.WriteLine("resultParam.Value: " + resultParam.Value.GetType().ToString());
OracleString ret = (OracleString)resultParam.Value;
empNo = ret.ToString();
}
Console.WriteLine("Emp No: " + empNo);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.WriteLine(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
public LoadQty FGPress_GetLoadInfo(string loadNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
LoadQty loadQty = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_FG_PRESS.GET_LOADING_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strLOADING_NO", loadNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
loadQty = new LoadQty();
loadQty.LOADING_NO = OraDataReader.Instance.GetString("LOADING_NO");
loadQty.LOADED_BOX = OraDataReader.Instance.GetInteger("LOADED_BOX");
loadQty.LOADED_QTY = OraDataReader.Instance.GetInteger("LOADED_QTY");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(loadQty);
}
public void TypeCast()
{
// Note that method BLToolkit.Common.Convert.ToDecimal(OracleNumber p) does not exist.
//
decimal d = ConvertTo <decimal> .From(new OracleDecimal(123));
Assert.AreEqual(123.0m, d);
// Method BLToolkit.Common.Convert.ToOracleString(string p) does not exist too.
//
OracleString s = ConvertTo <OracleString> .From("test");
Assert.AreEqual("test", s.Value);
}
private void button9_Click(object sender, EventArgs e)
{
String _connstring = "Data Source=localhost/NEWDB;User Id=EDZEHOO;Password=PASS123;";
try
{
OracleConnection _connObj = new OracleConnection(_connstring);
_connObj.Open();
OracleCommand _cmdObj = _connObj.CreateCommand();
_cmdObj.CommandText = "ProductsPackage.proc_GetAllProductNames";
_cmdObj.CommandType = CommandType.StoredProcedure;
//Create an output parameter
OracleParameter _NameParam = new OracleParameter();
_NameParam.ParameterName = "ProdNames";
_NameParam.OracleDbType = OracleDbType.Varchar2;
_NameParam.Direction = ParameterDirection.Output;
_NameParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
//You must explicitly define the number of elements to return
_NameParam.Size = 10;
//Because you are retrieving an object with a variable size, you need to
//define the size of the string returned. This size must be specified for
//each element in the output result
int[] intArray = new int[10];
int _counter;
for (_counter = 0; _counter < 10; _counter++)
{
intArray[_counter] = 255;
}
_NameParam.ArrayBindSize = intArray;
//Execute the stored procedure
_cmdObj.Parameters.Add(_NameParam);
_cmdObj.ExecuteNonQuery();
//For VARCHAR2 data types, an array of OracleString objects is returned
String _result = "";
OracleString[] stringArray = (OracleString[])_NameParam.Value;
for (_counter = 0; _counter <= stringArray.GetUpperBound(0); _counter++)
{
OracleString _outputString = stringArray[_counter];
_result = _result + _outputString.Value + "\n";
}
MessageBox.Show("Product names are:\n" + _result);
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public QCReturn GetQcReturnInfo(string qcReturnNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
QCReturn qcReturn = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_RETRUN_PACK.GET_FG_RETURN"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strRT_NO", qcReturnNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
qcReturn = new QCReturn();
qcReturn.WH_ID = OraDataReader.Instance.GetString("WH_ID");
qcReturn.RT_NO = OraDataReader.Instance.GetString("RT_NO");
qcReturn.NO_OF_LABEL = OraDataReader.Instance.GetInteger("NO_OF_LABEL");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(qcReturn);
}
public TransferOrderInfo GetTOInfo(string toNo, string userid, out string resultMessage)
{
resultMessage = string.Empty;
TransferOrderInfo tOrder = null;
try
{
ProcParam procPara = new ProcParam(4)
{
ProcedureName = "SCANNER_PACK_MTST_VER.GET_TO_INFO"
};
procPara.AddParamRefCursor(0, "io_cursor");
procPara.AddParamInput(1, "strTO_NO", toNo);
procPara.AddParamOutput(2, "resultmsg", OracleDbType.Varchar2, 255);
procPara.AddParamInput(3, "strUser_id", userid);
OraDataReader.Instance.OraReader = GlobalDB.Instance.DataAc.ExecuteDataReader(procPara);
this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
if (OraDataReader.Instance.OraReader.HasRows)
{
while (OraDataReader.Instance.OraReader.Read())
{
tOrder = new TransferOrderInfo();
tOrder.TO_NO = OraDataReader.Instance.GetString("TO_NO");
}
}
else
{
OracleString result = (OracleString)procPara.ReturnValue(2);
if (!result.IsNull)
{
resultMessage = result.Value;
}
}
// always call Close when done reading.
OraDataReader.Instance.Close();
}
catch (Exception ex)
{
throw ex;
}
return(tOrder);
}
public string InsertTransferOrder(ref TransferOrderHdr TO_HDR, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam paramHDR = new ProcParam(10);
paramHDR.ProcedureName = "TO_PACK.TO_HDR_INS";
paramHDR.AddParamInOutput(0, "strTO_NO", OracleDbType.Varchar2, 255, TO_HDR.TO_NO);
paramHDR.AddParamInput(1, "strTO_DATE ", TO_HDR.TO_DATE);
paramHDR.AddParamInput(2, "strREF_NO", TO_HDR.REF_NO);
paramHDR.AddParamInput(3, "strPROD_TYPE", TO_HDR.PROD_TYPE);
paramHDR.AddParamInput(4, "strTO_DEST", TO_HDR.TO_DEST);
paramHDR.AddParamInput(5, "strDELIVERY_DATE", TO_HDR.DELIVERY_DATE);
paramHDR.AddParamInput(6, "strREMARK", TO_HDR.REMARK);
paramHDR.AddParamInput(7, "strREC_STAT", (TO_HDR.REC_STAT ? "Y" : "N"));
paramHDR.AddParamInput(8, "strUSER_ID", userid);
paramHDR.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)paramHDR.ReturnValue(0);
OracleString result = (OracleString)paramHDR.ReturnValue(9);
if (!result.IsNull)
{
TO_HDR.TO_NO = resultDB.Value;
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string InsertLoadingOrder(ref LoadingOrder loadingOrd, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam paramHDR = new ProcParam(10)
{
ProcedureName = "LOADING_PACK.LOADING_HDR_INS"
};
paramHDR.AddParamInOutput(0, "strLOADING_NO", OracleDbType.Varchar2, 255, loadingOrd.LOADING_NO);
paramHDR.AddParamInput(1, "strLOADING_DATE ", loadingOrd.LOADING_DATE);
paramHDR.AddParamInput(2, "strDELIVERY_DATE", loadingOrd.DELIVERY_DATE);
paramHDR.AddParamInput(3, "strTRUCK_NO", loadingOrd.TRUCK_NO);
paramHDR.AddParamInput(4, "strCONTAINER_NO", loadingOrd.CONTAINER_NO);
paramHDR.AddParamInput(5, "strWH_ID", loadingOrd.WH_ID);
paramHDR.AddParamInput(6, "strREMARK", loadingOrd.REMARK);
paramHDR.AddParamInput(7, "strREC_STAT", (loadingOrd.REC_STAT ? "Y" : "N"));
paramHDR.AddParamInput(8, "strUSER_ID", userid);
paramHDR.AddParamOutput(9, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(paramHDR);
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)paramHDR.ReturnValue(0);
OracleString result = (OracleString)paramHDR.ReturnValue(9);
if (!result.IsNull)
{
loadingOrd.LOADING_NO = resultDB.Value;
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public string UpdateUser(User user, string userid)
{
string result = string.Empty;
try
{
ProcParam param = new ProcParam(13);
param.ProcedureName = "ADMINISTRATOR_PACK.USER_UPD";
param.AddParamInput(0, "strUSER_ID", user.USER_ID);
param.AddParamInput(1, "strUSER_NAME", user.USER_NAME);
param.AddParamInput(2, "strWARE_ID", user.WARE_ID);
param.AddParamInput(3, "strLOGIN", user.LOGIN);
param.AddParamInput(4, "strPWD", user.PWD);
param.AddParamInput(5, "strEMPLOYEE_ID", user.EMPLOYEE_ID);
param.AddParamInput(6, "strROLE_ID", user.ROLE_ID);
param.AddParamInput(7, "strREMARK", user.REMARK);
param.AddParamInput(8, "strREC_STAT", (user.REC_STAT ? "Y" : "N"));
param.AddParamInput(9, "strEMAIL", user.EMAIL);
param.AddParamInput(10, "strCOMP_ID", user.COMP_ID);
param.AddParamInput(11, "strUSER_ID", userid);
param.AddParamOutput(12, "RESULTMSG", OracleDbType.NVarchar2, 255, "OK");
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
////this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString resultDB = (OracleString)param.ReturnValue(12);
if (!resultDB.IsNull)
{
result = resultDB.Value;
}
else
{
throw GlobalDB.Instance.DataAc.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string UpdatePostTO(string tono, string postRef, string userid)
{
string resultMsg = string.Empty;
try
{
#region "Transaction Header"
ProcParam param = new ProcParam(4)
{
ProcedureName = "TO_PACK.TO_POSTREF_UPD"
};
param.AddParamInput(0, "strSO_NO", tono);
param.AddParamInput(1, "strPOST_REF ", postRef);
param.AddParamInput(2, "strUSER_ID", userid);
param.AddParamOutput(3, "RESULTMSG", OracleDbType.Varchar2, 255);
#endregion
GlobalDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalDB.Instance.LastException != null)
{
throw GlobalDB.Instance.LastException;
}
//this.executionTime = GlobalDB.Instance.DataAc.ExecuteTime;
OracleString result = (OracleString)param.ReturnValue(3);
if (!result.IsNull)
{
resultMsg = result.Value;
}
}
catch (Exception ex)
{
throw ex;
}
return(resultMsg);
}
public static OracleString op_Addition(OracleString x, OracleString y) {}
public static OracleBoolean GreaterThan(OracleString x, OracleString y) {}
public static OracleString Concat(OracleString x, OracleString y) {}
public static OracleBoolean NotEquals(OracleString x, OracleString y) {}
public static OracleBoolean LessThan(OracleString x, OracleString y) {}
public int ExecuteOracleNonQuery(out OracleString& rowid) {}
public static string op_Explicit(OracleString x) {}
public static OracleBoolean op_Inequality(OracleString x, OracleString y) {}
public static OracleBoolean op_GreaterThanOrEqual(OracleString x, OracleString y) {}
public static OracleBoolean op_LessThanOrEqual(OracleString x, OracleString y) {}
