/// <summary> /// To get All Category details from database /// </summary> /// <returns></returns> public BudgetEstimationList getAllCategory() { OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString); OracleCommand cmd; string proc = "USP_MST_SELECTALLCATEGORY"; cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("Sp_recordset", Oracle.DataAccess.Client.OracleDbType.RefCursor).Direction = ParameterDirection.Output; cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); BudgetEstimationBO objBudgetEstimation = null; BudgetEstimationList BudgetEstimationList = new BudgetEstimationList(); while (dr.Read()) { objBudgetEstimation = new BudgetEstimationBO(); objBudgetEstimation.CategoryID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("BGT_CATEGORYID"))); objBudgetEstimation.CategoryName = dr.GetString(dr.GetOrdinal("BGT_CATEGORYNAME")); BudgetEstimationList.Add(objBudgetEstimation); } dr.Close(); return(BudgetEstimationList); }
/// <summary> /// To get SubCategory ByCategoryID /// </summary> /// <param name="CategoryID"></param> /// <returns></returns> public BudgetEstimationList getSubCatByCatID(int CategoryID) { OracleConnection cnn = new OracleConnection(AppConfiguration.ConnectionString); OracleCommand cmd; string proc = "USP_MST_GET_SUBCATBYID"; cmd = new OracleCommand(proc, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("CategoryID_", CategoryID); cmd.Parameters.Add("Sp_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; cmd.Connection.Open(); OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); BudgetEstimationBO BudgetEstimationObj = null; BudgetEstimationList BudgetEstimationList = new BudgetEstimationList(); while (dr.Read()) { BudgetEstimationObj = new BudgetEstimationBO(); if (ColumnExists(dr, "BGT_SUBCATEGORYNAME") && !dr.IsDBNull(dr.GetOrdinal("BGT_SUBCATEGORYNAME"))) { BudgetEstimationObj.SubCategoryName = dr.GetString(dr.GetOrdinal("BGT_SUBCATEGORYNAME")); } if (ColumnExists(dr, "BGT_SUBCATEGORYID") && !dr.IsDBNull(dr.GetOrdinal("BGT_SUBCATEGORYID"))) { BudgetEstimationObj.SubCategoryID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("BGT_SUBCATEGORYID"))); } if (ColumnExists(dr, "BGT_CATEGORYID") && !dr.IsDBNull(dr.GetOrdinal("BGT_CATEGORYID"))) { BudgetEstimationObj.CategoryID = Convert.ToInt32(dr.GetValue(dr.GetOrdinal("BGT_CATEGORYID"))); } BudgetEstimationList.Add(BudgetEstimationObj); } dr.Close(); return(BudgetEstimationList); }