DATA ACCESS LAYER METHOD (GET,INSERT,UPDATE,DELETE)

public  class HousingAllowanceSP : DBConnection
    {
        SqlCommand cmd = null;
        SqlTransaction transaction = null;
        public SqlConnection Connection
        {

            set
            {
                sqlcon = value;
            }
        }

        public HousingAllowanceSP(SqlTransaction trans)
        {
            transaction = trans;
        }

        public HousingAllowanceSP()
        {
           
        }

        #region ALL STORED PROCEDURE INITIALIZED

        // Allowance  SP
        private string storedProcedure_HousingAllowance_INSERT = "sproc_HousingAllowance_INSERT";
        private string storedProcedure_HousingAllowance_UPDATE = "sproc_HousingAllowance_UPDATE";
        private string storedProcedure_HousingAllowance_LIST = "sproc_HousingAllowance_LIST";

 #region INSERT Allowance Contract
        /// <summary>
        /// INSERT Allowance  and check for exist record if found then return -2 ELSE insert the record
        /// Insert Allowance  State History based on Allowance  Id
        /// </summary>
        /// <param name="Allowance "></param>
        /// <returns></returns>
        public int HousingAllowance_INSERT(HousingAllowance housingAllowance)
        {

            int returnParam = 0;
            try
            {

                if (sqlcon.State == ConnectionState.Closed)
                {
                    sqlcon.Open();
                }
                // lets begin a transaction here
                transaction = sqlcon.BeginTransaction();
                cmd = new SqlCommand(storedProcedure_HousingAllowance_INSERT, sqlcon);
                cmd.CommandType = CommandType.StoredProcedure;
                // assosiate this command with transaction
                cmd.Transaction = transaction;
                cmd.Parameters.AddWithValue("@AllowanceNumber", housingAllowance.AllowanceNumber);
                cmd.Parameters.AddWithValue("@AllowanceValue", housingAllowance.AllowanceValue);
                cmd.Parameters.AddWithValue("@HousingAllowanceStatusId", housingAllowance.HousingAllowanceStatusId);
                cmd.Parameters.AddWithValue("@StartDate", housingAllowance.strStartDate);
                cmd.Parameters.AddWithValue("@BeneficiaryId", housingAllowance.BeneficiaryId);
                cmd.Parameters.AddWithValue("@HousingAllowanceRequestId", housingAllowance.HousingAllowanceRequestId);
                cmd.Parameters.AddWithValue("@AllowanceContractFileName", housingAllowance.AllowanceContractFileName);
                cmd.Parameters.AddWithValue("@ObservationReportFilename", housingAllowance.ObservationReportFilename);
                cmd.Parameters.AddWithValue("@ReturnParam", 34).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                returnParam = Convert.ToInt32(cmd.Parameters["@ReturnParam"].Value);

                if (returnParam > 0)
                {
                    transaction.Commit();
                    try
                    {
                        AuditSP auditSP = new AuditSP();
                        auditSP.SaveAudit(MOACommons.AuditUtils.GetAuditData(housingAllowance, (int)DBOperations.Insert, DBConstant.CONST_Allowance, returnParam));
                    }
                    catch (Exception ex)
                    {
                        MOACommons.Utils.ErrorLog.WriteLog(ex, this.GetType().ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name);
                    }
                }
                else
                {
                    transaction.Rollback();
                    returnParam = -2;
                }


            }
            catch (Exception ex)
            {
                transaction.Rollback();
                returnParam = 0;

                MOACommons.Utils.ErrorLog.WriteLog(ex, this.GetType().ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
            finally
            {
                sqlcon.Close();
            }

            return returnParam;
        }
        #endregion


   #region Allowance  List
        public IList<HousingAllowance> AllowanceList(int? BeneficiaryId, int? RequestStateId, int? GovernorateId,
            System.DateTime? AllocationDate, float? AllowanceValue, int? AllocationNumber, int? RequestNumber, int PAGESIZE, int PAGENUMBER, string BeneficiaryCivilId)
        {

            List<HousingAllowance> result = new List<HousingAllowance>();

            try
            {
                sqlcon.Open();
                SqlCommand sqlCmd = new SqlCommand(storedProcedure_HousingAllowance_LIST, sqlcon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                if (BeneficiaryCivilId == null)
                    sqlCmd.Parameters.AddWithValue("@BeneficiaryCivilId", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@BeneficiaryCivilId", BeneficiaryCivilId);

                if (RequestStateId == 0 || RequestStateId == null)
                    sqlCmd.Parameters.AddWithValue("@RequestStateId", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@RequestStateId", RequestStateId);

                if (BeneficiaryId == 0 || BeneficiaryId == null)
                    sqlCmd.Parameters.AddWithValue("@BeneficiaryId", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@BeneficiaryId", BeneficiaryId);

                if (AllocationDate == null)
                    sqlCmd.Parameters.AddWithValue("@AllocationDate", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@AllocationDate", AllocationDate);

                if (AllowanceValue == 0 || AllowanceValue== null)
                    sqlCmd.Parameters.AddWithValue("@AllowanceValue", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@AllowanceValue", AllowanceValue);

                if (AllocationNumber == 0 || AllocationNumber==null)
                    sqlCmd.Parameters.AddWithValue("@AllocationNumber", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@AllocationNumber", AllocationNumber);

                if (RequestNumber == 0 || RequestNumber==null)
                    sqlCmd.Parameters.AddWithValue("@RequestNumber", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@RequestNumber", RequestNumber);

                if (GovernorateId == 0 || GovernorateId==null)
                    sqlCmd.Parameters.AddWithValue("@GovernorateId", DBNull.Value);
                else
                    sqlCmd.Parameters.AddWithValue("@GovernorateId", GovernorateId);

                sqlCmd.Parameters.AddWithValue("@PAGESIZE", PAGESIZE);
                sqlCmd.Parameters.AddWithValue("@PAGENUMBER", PAGENUMBER);

             

                SqlDataReader reader = sqlCmd.ExecuteReader();

                while (reader.Read())
                {
                    HousingAllowance req = new HousingAllowance();
                    req.HousingAllowanceId = (int)reader["HousingAllowanceId"];
                    req.AllowanceValue = Convert.ToDouble(reader["AllowanceValue"].ToString());
                    req.AllowanceNumber = Convert.ToInt32(reader["AllowanceNumber"].ToString());
                    req.BeneficiaryId = (int)reader["BeneficiaryId"];
                    req.HousingAllowanceRequestId = (int)reader["HousingAllowanceRequestId"];

                    if (reader["LastPaidMonth"] != DBNull.Value)
                    {
                        req.LastPaidMonth = reader["LastPaidMonth"] as string;
                    }
                    else
                    {
                        req.LastPaidMonth=null;
                    }
                 
                    //AllocationDate
                    DateTime hAllocationDate;
                    string strAllocationDate;
                    if (reader["AllocationDate"] != DBNull.Value)
                    {
                        hAllocationDate = Convert.ToDateTime(reader["AllocationDate"]);
                        strAllocationDate = hAllocationDate.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        strAllocationDate = null;
                    }
                    req.strAllocationDate = strAllocationDate;

                    req.HousingAllowanceRequest = new HousingAllowanceRequest();
                    req.HousingAllowanceRequest.AllowanceRequestNumber = (decimal)reader["AllowanceRequestNumber"];
                 
                   //Beneficiary
                    req.Beneficiary = new Beneficiary();
                    req.Beneficiary.BeneficiaryId = (int)reader["BeneficiaryId"];
                    req.Beneficiary.BeneficiaryName = reader["BeneficiaryName"] as string;

                    //Status
                    req.HousingAllowanceStatu = new HousingAllowanceStatu();
                    req.HousingAllowanceStatu.HousingAllowanceStatusId = (int)reader["HousingAllowanceStatusId"];
                    req.HousingAllowanceStatu.StatusName = reader["StatusName"] as string;

                    req.TotalRecordsCount = (int)reader["TotalRecordsCount"];

                    result.Add(req);
                }
            }
            catch (Exception ex)
            {
                MOACommons.Utils.ErrorLog.WriteLog(ex, this.GetType().ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
            finally
            {
                sqlcon.Close();
            }

            return result;
        }
        #endregion