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) {}