public static DataTable GetPrivateLawyerDetailsTable(double PID) { GetConnection GetConn = new GetConnection(); try { string selectSQL = " SELECT DISTINCT P_ID,LAWYER_TYPE_ID,lawyer_type_description,LICENSE_NO,P_FROM_DATE,UNIT_ID,unit_name FROM vw_lawyer_info_details " + " WHERE P_FROM_DATE IS NOT NULL AND UNIT_ID IS NOT NULL "; if (PID > 0) { selectSQL += " AND P_ID = " + PID; } OracleConnection DBConn = GetConn.GetDbConn(Module.LJMS); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, selectSQL); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static DataTable GetLibraryNameListTable() { GetConnection GetConn = new GetConnection(); try { //string SelectSQL = "select * from library"; string SelectSQL = "select distinct Library_ID,Library_Name,org_id from vw_library_info order by library_Name ASC"; //OracleConnection DBConn = GetConn.GetDbConn("LIS_ADMIN", "LIS_ADMIN"); OracleConnection DBConn = GetConn.GetDbConn(Module.LIS); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, SelectSQL); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static DataTable GetDocumentsType(int?docTypeId) { GetConnection GetCon = new GetConnection(); OracleConnection DBConn; OracleCommand Cmd; try { DBConn = GetCon.GetDbConn(); Cmd = new OracleCommand(); string SelectSql = "SP_GET_DOCUMENTS_TYPE"; OracleParameter[] ParamArray = new OracleParameter[2]; ParamArray[0] = Utilities.GetOraParam(":p_DOC_TYPE_ID", docTypeId, OracleDbType.Int64, ParameterDirection.Input); ParamArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, SelectSql, ParamArray); return((DataTable)ds.Tables[0]); } catch (OracleException oex) { PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError(); throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message)); } catch (Exception ex) { throw ex; } }
/// <summary> /// Edit author object to database /// </summary> /// <param name="obj">ATTAuthor object</param> /// <returns>return bool</returns> public static bool EditAuthor(ATTAuthor obj, Previlege pobj) { string EditSP; EditSP = "SP_EDIT_AUTHOR"; //Preparing oracle parameter with value OracleParameter[] paramArray = new OracleParameter[2]; paramArray[0] = Utilities.GetOraParam(":p_AUTHOR_ID", obj.AuthorID, OracleDbType.Int64, ParameterDirection.InputOutput); paramArray[1] = Utilities.GetOraParam(":p_AUTHOR_NAME", obj.AuthorName, OracleDbType.Varchar2, ParameterDirection.Input); GetConnection getConn = new GetConnection(); try { OracleConnection DBConn = getConn.GetDbConn(Module.LIS); if (Previlege.HasPrevilege(pobj, DBConn) == false) { throw new ArgumentException(Utilities.PreviledgeMsg + " update Author."); } SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, EditSP, paramArray); return(true); } catch (Exception ex) { throw ex; } finally { getConn.CloseDbConn(); } }
public static bool DeletePost(int PostID) { GetConnection Conn = new GetConnection(); OracleConnection DBConn; OracleTransaction Tran; string DeletePostSql = "SP_DEL_POST "; try { DBConn = Conn.GetDbConn(Module.DLPDS); Tran = DBConn.BeginTransaction(); OracleParameter[] ParamArray = new OracleParameter[1]; ParamArray[0] = Utilities.GetOraParam(":p_POST_ID", PostID, OracleDbType.Int64, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeletePostSql, ParamArray); Tran.Commit(); return(true); } catch (Exception ex) { throw ex; } }
//public static DataTable SearchDocumentListTable(int? orgID, int? unitID, int? docID, string docName,string status,int queryID) public static DataTable SearchDocumentListTable(int?orgID, int?unitID, int?docID, string docName, string status) { GetConnection GetConn = new GetConnection(); try { //QueryID = queryID; string SearchSQL; SearchSQL = QueryBuilder(orgID, unitID, docID, docName, status); OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, SearchSQL); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static DataTable GetDesignationLevelTable() { string SelectDL; SelectDL = "SP_GET_LEVEL"; OracleParameter[] paramArray = new OracleParameter[1]; //paramArray[0] = Utilities.GetOraParam(":Level_ID", levelID, OracleDbType.Int64, ParameterDirection.InputOutput); //paramArray[1] = Utilities.GetOraParam(":Level_Name", levelName, OracleDbType.Varchar2, ParameterDirection.Output); paramArray[0] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); GetConnection conn = new GetConnection(); try { OracleConnection DBconn = conn.GetDbConn(Module.PMS); SqlHelper.ExecuteNonQuery(DBconn, CommandType.StoredProcedure, SelectDL, paramArray); OracleDataReader rdr = ((OracleRefCursor)paramArray[0].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(rdr, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw ex; } finally { conn.CloseDbConn(); } }
public static DataTable GetMeetingResponseTable(ATTMeetingResponse objMResponse) { //string SelectSQL = "SP_GET_PART_RESPONSE"; string SelectSQL = "SP_GET_MINUTE_NOTE"; OracleParameter[] paramArray = new OracleParameter[3]; paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objMResponse.OrgID, OracleDbType.Int16, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_MEETING_ID", objMResponse.MeetingID, OracleDbType.Int16, ParameterDirection.Input); //paramArray[2] = Utilities.GetOraParam(":p_PARTICIPANT_ID",objMResponse.ParticipantID, OracleDbType.Int16, ParameterDirection.Input); //paramArray[3] = Utilities.GetOraParam(":p_RESPONSE_ID",objMResponse.ResponseID, OracleDbType.Int16, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); GetConnection GetConn = new GetConnection(); try { OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray); OracleDataReader reader = ((OracleRefCursor)paramArray[2].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(reader, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static bool DeleteInstitution(int InstitutionID) { GetConnection Conn = new GetConnection(); OracleConnection DBConn = Conn.GetDbConn(); OracleTransaction Tran = DBConn.BeginTransaction(); string DeleteInstitutionSql = "SP_DEL_INSTITUTIONS"; try { OracleParameter[] ParamArray = new OracleParameter[1]; ParamArray[0] = Utilities.GetOraParam(":p_INSTITUTION_ID", InstitutionID, OracleDbType.Int64, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeleteInstitutionSql, ParamArray); Tran.Commit(); return(true); } catch (Exception ex) { throw ex; } finally { Conn.CloseDbConn(); } }
public static DataTable GetEmployeePostingHistory(int empID, int?orgID) { GetConnection GetConn = new GetConnection(); try { string selectSQL = " SELECT EMP_ID,ORG_ID,ORG_NAME,DES_ID,DES_NAME,POSTING_TYPE_ID,POSTING_TYPE_NAME,FROM_DATE,CREATED_DATE,POST_ID " + " FROM VW_EMP_POSTING_HISTORY WHERE EMP_ID=" + empID; if (orgID != null) { selectSQL = selectSQL + " AND ORG_ID=" + orgID; } OracleConnection DBConn = GetConn.GetDbConn(Module.PMS); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, selectSQL); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static bool SaveMeetingResponse(ATTMeetingResponse objMResponse) { string SaveSQL = "SP_UPDATE_MINUTE_NOTE"; OracleParameter[] paramArray = new OracleParameter[5]; paramArray[0] = Utilities.GetOraParam(":P_ORG_ID", objMResponse.OrgID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":P_MEETING_ID", objMResponse.MeetingID, OracleDbType.Int64, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":P_PART_ID", objMResponse.ParticipantID, OracleDbType.Int64, ParameterDirection.Input); //paramArray[3] = Utilities.GetOraParam(":P_RESPONSE_ID", null, OracleDbType.Int64, ParameterDirection.InputOutput); paramArray[3] = Utilities.GetOraParam(":P_NOTE", objMResponse.Response, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[4] = Utilities.GetOraParam(":P_IS_AGREE", objMResponse.IsAgree, OracleDbType.Varchar2, ParameterDirection.Input); GetConnection GetConn = new GetConnection(); OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); OracleTransaction Tran = DBConn.BeginTransaction(); try { SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, SaveSQL, paramArray); Tran.Commit(); return(true); } catch (Exception ex) { Tran.Rollback(); throw (ex); } finally { GetConn.CloseDbConn(); } }
public static DataTable GetEmployeeWithPostingListTable(int?orgID) { GetConnection GetConn = new GetConnection(); try { string selectSQL = " SELECT ORG_ID,P_ID,FIRST_NAME,MID_NAME,SUR_NAME,DES_ID,DES_NAME " + " FROM vw_employee_with_posting"; OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, selectSQL); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static string GetEmployeeCurrentPosting(double empId) { try { GetConnection Conn = new GetConnection(); OracleConnection DBConn; DBConn = Conn.GetDbConn(Module.PMS); object obj = DBConn; string SelectSql = "SP_GET_EMP_CURRENT_POST"; OracleParameter[] ParamArray = new OracleParameter[3]; ParamArray[0] = Utilities.GetOraParam(":p_EMP_ID", empId, OracleDbType.Double, ParameterDirection.Input); ParamArray[1] = Utilities.GetOraParam(":P_DES_ID", null, OracleDbType.Int64, ParameterDirection.InputOutput); ParamArray[2] = Utilities.GetOraParam(":P_DES_NAME", null, OracleDbType.Varchar2, ParameterDirection.InputOutput); ParamArray[2].Size = 100; SqlHelper.ExecuteNonQuery((OracleConnection)obj, CommandType.StoredProcedure, SelectSql, ParamArray); int DesgID = int.Parse(ParamArray[1].Value.ToString()); string DesName = ParamArray[2].Value.ToString(); return(DesgID.ToString() + " " + DesName); } catch (Exception ex) { throw ex; } }
public static DataTable SearchEmployee(ATTOrgUnitHead objOrgUnitHead) { try { string strSelect = ""; strSelect = "SELECT * FROM VW_EMP_ORG_UNIT_HEAD WHERE 1=1 "; List <OracleParameter> ParamList = new List <OracleParameter>(); if (objOrgUnitHead.OrgID > 0) { strSelect += "AND ORG_ID = :OrgID"; ParamList.Add(Utilities.GetOraParam(":OrgID", objOrgUnitHead.OrgID, OracleDbType.Int64, ParameterDirection.Input)); } strSelect += " ORDER BY ORG_ID"; GetConnection conn = new GetConnection(); OracleConnection obj = conn.GetDbConn(Module.PMS); DataSet ds = SqlHelper.ExecuteDataset(obj, CommandType.Text, strSelect, ParamList.ToArray()); return((DataTable)ds.Tables[0]); } catch (Exception ex) { throw ex; } }
public static DataTable GetEmpAttendance(int orgid, int desid, string yearmonth) { GetConnection conn = new GetConnection(); OracleConnection obj = conn.GetDbConn(Module.PMS); string SelectSP = "SP_GET_EMP_ATTENDANCE"; List <OracleParameter> paramArray = new List <OracleParameter>(); paramArray.Add(Utilities.GetOraParam("P_ORG_ID", orgid, OracleDbType.Int32, ParameterDirection.Input)); if (desid == 0) { paramArray.Add(Utilities.GetOraParam("P_DES_ID", null, OracleDbType.Int32, ParameterDirection.Input)); } else if (desid > 0) { paramArray.Add(Utilities.GetOraParam("P_DES_ID", desid, OracleDbType.Int32, ParameterDirection.Input)); } paramArray.Add(Utilities.GetOraParam("P_YEARMONTH", yearmonth, OracleDbType.Varchar2, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("P_RC", null, OracleDbType.RefCursor, ParameterDirection.InputOutput)); try { return(SqlHelper.ExecuteDataset(CommandType.StoredProcedure, SelectSP, Module.PMS, paramArray.ToArray()).Tables[0]); } catch (Exception ex) { throw ex; } }
public static DataTable GetEmployeeLeave(int?empID, string REQ_REC_APP) { try { GetConnection conn = new GetConnection(); OracleConnection obj = conn.GetDbConn(Module.PMS); string GetLeaveTypeEmployeeSQL = ""; if (REQ_REC_APP == "REQ") { GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_REQ"; } else if (REQ_REC_APP == "REC") { GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_REC"; } else if (REQ_REC_APP == "APP") { GetLeaveTypeEmployeeSQL = "SP_GET_EMP_LEAVE_APP"; } OracleParameter[] ParamArray = new OracleParameter[2]; ParamArray[0] = Utilities.GetOraParam(":P_EMP_ID", empID, OracleDbType.Int64, ParameterDirection.Input); ParamArray[1] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); DataSet ds = SqlHelper.ExecuteDataset(obj, CommandType.StoredProcedure, GetLeaveTypeEmployeeSQL, ParamArray); return((DataTable)ds.Tables[0]); } catch (Exception ex) { throw ex; } }
public static bool UpdateMaterialCategory(ATTMaterialCategory objMC, Previlege pobj) { string UpdateSQL; UpdateSQL = "SP_EDIT_MATERIAL_CATEGORY"; GetConnection GetConn = new GetConnection(); try { OracleConnection DBConn = GetConn.GetDbConn(Module.LIS); if (Previlege.HasPrevilege(pobj, DBConn) == false) { throw new ArgumentException(Utilities.PreviledgeMsg + " update Material Category."); } OracleParameter[] paramArray = new OracleParameter[3]; paramArray[0] = Utilities.GetOraParam(":p_CATEGORY_ID", objMC.CategoryID, OracleDbType.Int16, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_CATEGORY_NAME", objMC.CategoryName, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_CATEGORY_DESCRIPTION", objMC.CategoryDescription, OracleDbType.Varchar2, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, UpdateSQL, paramArray); return(true); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static object GetLoginEmpType(int orgID, int subjectID, double empID) { string SQL = "SP_GET_LOGIN_EMP_TYPE"; List <OracleParameter> paramArray = new List <OracleParameter>(); paramArray.Add(Utilities.GetOraParam("p_org_id", orgID, OracleDbType.Int16, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_sub_id", subjectID, OracleDbType.Int16, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_emp_id", empID, OracleDbType.Double, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_type", "m", OracleDbType.Varchar2, ParameterDirection.InputOutput)); paramArray[3].Size = 10; GetConnection DBConn = new GetConnection(); try { OracleConnection Conn = DBConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SQL, paramArray.ToArray()); object obj = paramArray[3].Value; return(obj); } catch (Exception ex) { throw ex; } finally { DBConn.CloseDbConn(); } }
//public static int QueryID; public static DataTable GetDocumentNameListTable(int?docID, int?unitID, int?orgID) { string SelectSQL = "SP_GET_DOCUMENT"; OracleParameter[] paramArray = new OracleParameter[4]; paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int16, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_UNIT_ID", unitID, OracleDbType.Int16, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_DOC_ID", docID, OracleDbType.Int16, ParameterDirection.Input); paramArray[3] = Utilities.GetOraParam(":p_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); GetConnection GetConn = new GetConnection(); try { OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray); OracleDataReader reader = ((OracleRefCursor)paramArray[3].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(reader, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw (ex); } finally { GetConn.CloseDbConn(); } }
public static int CheckLoginUserIsChannelMember(int orgId, int subjectID, double empID) { string SQL = "SP_CHK_LOGIN_USER_IS_CHNL_PER"; List <OracleParameter> paramArray = new List <OracleParameter>(); paramArray.Add(Utilities.GetOraParam("p_org_id", orgId, OracleDbType.Int16, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_subject_id", subjectID, OracleDbType.Int16, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_emp_id", empID, OracleDbType.Double, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam("p_count", null, OracleDbType.Int32, ParameterDirection.InputOutput)); paramArray[3].Size = 4; GetConnection DBConn = new GetConnection(); try { OracleConnection Conn = DBConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, SQL, paramArray.ToArray()); object obj = paramArray[3].Value; return(int.Parse(obj.ToString())); } catch (Exception ex) { throw ex; } finally { DBConn.CloseDbConn(); } }
public static bool UpdateResourcePersonMarks(List <ATTSessionCourseMember> lst) { string UpdateSP = ""; foreach (ATTSessionCourseMember obj in lst) { UpdateSP = "SP_UPDATE_RP_MARKS"; OracleParameter[] paramArray = new OracleParameter[7]; paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", obj.OrgID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_PROGRAM_ID", obj.ProgramID, OracleDbType.Int64, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_SESSION_ID", obj.SessionID, OracleDbType.Int64, ParameterDirection.Input); paramArray[3] = Utilities.GetOraParam(":p_COURSE_ID", obj.CourseID, OracleDbType.Int64, ParameterDirection.Input); paramArray[4] = Utilities.GetOraParam(":p_FACULTY_ID", obj.FacultyID, OracleDbType.Int64, ParameterDirection.Input); paramArray[5] = Utilities.GetOraParam(":p_PID", obj.PID, OracleDbType.Int64, ParameterDirection.Input); paramArray[6] = Utilities.GetOraParam(":p_MARKS_OBTAINED", obj.MarksObtained, OracleDbType.Int64, ParameterDirection.Input); GetConnection GetConn = new GetConnection(); try { OracleConnection DBConn = GetConn.GetDbConn(Module.DLPDS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, UpdateSP, paramArray); } catch (Exception ex) { throw ex; } } return(true); }
public static DataTable GetMelMilaapKartaaOath(int orgID, double mmkID) { GetConnection GetConn = new GetConnection(); OracleConnection DBConn = GetConn.GetDbConn(Module.CMS); string SelectSQL = "SP_GET_MM_KARTAA_OATH"; try { List <OracleParameter> paramArray = new List <OracleParameter>(); paramArray.Add(Utilities.GetOraParam(":P_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_PERSON_ID", mmkID, OracleDbType.Double, ParameterDirection.Input)); //paramArray.Add(Utilities.GetOraParam(":P_FROM_DATE", attMMK.FromDate, OracleDbType.Varchar2, ParameterDirection.Input)); //paramArray.Add(Utilities.GetOraParam(":P_JUDGE_ID", attMMK.PID, OracleDbType.Double, ParameterDirection.Input)); paramArray.Add(Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output)); DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray.ToArray()); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; return(tbl); } catch (Exception ex) { throw ex; } finally { GetConn.CloseDbConn(); } }
public static DataTable GetItemCategory(int?itemCategoryID, string itemCatActive) { string SelectSP; SelectSP = "sp_inv_get_items_category"; //p_items_category_id, p_active, p_rc OracleParameter[] paramArray = new OracleParameter[3]; paramArray[0] = Utilities.GetOraParam(":p_items_category_id", itemCategoryID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_active", itemCatActive, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_rc", null, OracleDbType.RefCursor, ParameterDirection.Output); GetConnection getConn = new GetConnection(); try { OracleConnection DBConn = getConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSP, paramArray); OracleDataReader reader = ((OracleRefCursor)paramArray[2].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(reader, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw ex; } finally { getConn.CloseDbConn(); } }
public static int CheckExistsOrgItems(ATTInvOrgItems objInvOItems) { try { GetConnection GetConn = new GetConnection(); OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); string sql = "SELECT CHECK_EXISTS_INV_ORG_ITEM(" + objInvOItems.OrgID + "," + objInvOItems.ItemsCategoryID + "," + objInvOItems.ItemsSubCategoryID + ",'" + objInvOItems.ItemsID + "')" + "FROM DUAL"; DataSet ds = SqlHelper.ExecuteDataset(DBConn, CommandType.Text, sql); DataTable tbl = new DataTable(); tbl = (DataTable)ds.Tables[0]; int existsOrgItems = int.Parse(tbl.Rows[0][0].ToString()); return(existsOrgItems); } catch (Exception ex) { throw (ex); } }
public static DataTable GetItemsSubCategory(int?itemCategoryID, string active) { List <OracleParameter> param = new List <OracleParameter>(); param.Add(Utilities.GetOraParam(":p_items_category_id", itemCategoryID, OracleDbType.Int64, ParameterDirection.Input)); param.Add(Utilities.GetOraParam(":p_active", active, OracleDbType.Varchar2, ParameterDirection.Input)); param.Add(Utilities.GetOraParam(":p_rc", null, OracleDbType.RefCursor, ParameterDirection.Output)); GetConnection getConn = new GetConnection(); try { OracleConnection DBConn = getConn.GetDbConn(Module.OAS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, "sp_inv_get_items_sub_category", param.ToArray()); OracleDataReader reader = ((OracleRefCursor)param[2].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(reader, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw ex; } finally { getConn.CloseDbConn(); } }
public static bool SaveSubmissionDetails(ATTSubmissionDetails objSD) { GetConnection Conn = new GetConnection(); try { string SaveSQL = "SP_ADD_SUBMISSIONDETAILS"; //OracleConnection DBConn = Conn.GetDbConn("PMS_ADMIN", "PMS_ADMIN"); OracleConnection DBConn = Conn.GetDbConn(Module.PMS); OracleParameter[] paramArray = new OracleParameter[4]; paramArray[0] = Utilities.GetOraParam(":P_EMP_ID", objSD.EmpID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_SUB_DATE", objSD.SubmissionDate, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":p_SUB_OFFICE", objSD.SubmissionOffice, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[3] = Utilities.GetOraParam("::p_SUB_OFFICE_PLACE", objSD.SubmissionPlace, OracleDbType.Varchar2, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SaveSQL, paramArray); return(true); } catch (Exception ex) { throw (ex); } finally { Conn.CloseDbConn(); } }
public static DataTable GetLibraryTable(int orgID, int?libraryID) { string SelectSQL = "SP_GET_LIBRARY"; OracleParameter[] paramArray = new OracleParameter[3]; paramArray[0] = Utilities.GetOraParam(":p_LIBRARY_ID", libraryID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_ORG_ID", orgID, OracleDbType.Int64, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":P_RC", null, OracleDbType.RefCursor, ParameterDirection.Output); GetConnection GetConn = new GetConnection(); try { OracleConnection DBConn = GetConn.GetDbConn(Module.LIS); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SelectSQL, paramArray); OracleDataReader reader = ((OracleRefCursor)paramArray[2].Value).GetDataReader(); DataTable tbl = new DataTable(); tbl.Load(reader, LoadOption.OverwriteChanges); return(tbl); } catch (Exception ex) { throw ex; } finally { GetConn.CloseDbConn(); } }
public static bool DeleteAppointmentEvents(ATTAppointment objAppointment) { string deleteSQL = "SP_DEL_APPOINTMENT"; int countAppointee = objAppointment.LstAppointee.Count; OracleParameter[] paramArray = new OracleParameter[2]; paramArray[0] = Utilities.GetOraParam(":p_ORG_ID", objAppointment.OrgID, OracleDbType.Int64, ParameterDirection.Input); paramArray[1] = Utilities.GetOraParam(":p_APPOINTMENT_ID", objAppointment.AppointmentID, OracleDbType.Int64, ParameterDirection.InputOutput); GetConnection GetConn = new GetConnection(); OracleConnection DBConn = GetConn.GetDbConn(Module.OAS); OracleTransaction Tran = DBConn.BeginTransaction(); try { if (countAppointee > 0) { DLLAppointee.DeleteAppointee(objAppointment, null, Tran); } SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, deleteSQL, paramArray); Tran.Commit(); return(true); } catch (Exception ex) { Tran.Rollback(); throw (ex); } finally { GetConn.CloseDbConn(); } }
public static bool DeleteLeaveType(int LeaveTypeID) { GetConnection Conn = new GetConnection(); OracleConnection DBConn; OracleTransaction Tran; string DeleteLeaveTypeSql = "SP_DEL_LEAVE_TYPES"; try { DBConn = Conn.GetDbConn(Module.PMS); Tran = DBConn.BeginTransaction(); OracleParameter[] ParamArray = new OracleParameter[1]; ParamArray[0] = Utilities.GetOraParam(":p_LEAVE_TYPE_ID", LeaveTypeID, OracleDbType.Int64, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, DeleteLeaveTypeSql, ParamArray); Tran.Commit(); return(true); } catch (Exception ex) { throw ex; } finally { Conn.CloseDbConn(); } }
public static bool SavePropertyCategoryCols(List <ATTPropertyCategoryColumns> lstPCCols) { GetConnection Conn = new GetConnection(); try { string SaveSQL = "SP_ADD_PROPERTY_CAT_COL"; OracleConnection DBConn = Conn.GetDbConn(Module.PMS); foreach (ATTPropertyCategoryColumns objPCCols in lstPCCols) { OracleParameter[] paramArray = new OracleParameter[5]; paramArray[0] = Utilities.GetOraParam(":P_PCAT_ID ", objPCCols.PCategoryID, OracleDbType.Int64, ParameterDirection.InputOutput); paramArray[1] = Utilities.GetOraParam(":P_COL_NAME ", objPCCols.ColName, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[2] = Utilities.GetOraParam(":P_COL_NO ", objPCCols.ColNo, OracleDbType.Int64, ParameterDirection.Input); paramArray[3] = Utilities.GetOraParam(":P_COL_DATA_TYPE ", objPCCols.ColDataType, OracleDbType.Varchar2, ParameterDirection.Input); paramArray[4] = Utilities.GetOraParam(":P_ACTIVE ", objPCCols.Active, OracleDbType.Varchar2, ParameterDirection.Input); SqlHelper.ExecuteNonQuery(DBConn, CommandType.StoredProcedure, SaveSQL, paramArray); } return(true); } catch (Exception ex) { throw (ex); } finally { Conn.CloseDbConn(); } }