//Created By : Jessica //Created Date : 19/02/16 ///<summary> ///Method to Get List of Active or Inactive Hostels /// </summary> /// <param name="argEn">HostelStructure Entity is an Input. Code,Block and Status are Input Properties.</param> /// <returns>Returns List of HostelStructre</returns> public List <HostelStructEn> GetHostelStructList(HostelStructEn argEn) { List <HostelStructEn> loEnList = new List <HostelStructEn>(); argEn.RoomTYpe = argEn.RoomTYpe.Replace("*", "%"); argEn.Code = argEn.Code.Replace("*", "%"); argEn.Block = argEn.Block.Replace("*", "%"); string sqlCmd = "select * from SAS_HostelStruct WHERE SAHS_Code <> '0'"; if (argEn.RoomTYpe.Length != 0) { sqlCmd = sqlCmd + " and SAHB_RoomTYpe like '" + argEn.RoomTYpe + "'"; } if (argEn.Code.Length != 0) { sqlCmd = sqlCmd + " and SAHB_Code like '" + argEn.Code + "'"; } if (argEn.Block.Length != 0) { sqlCmd = sqlCmd + " and SAHB_Block like '" + argEn.Block + "'"; } //if (argEn.Status == true) sqlCmd = sqlCmd + " and SAFS_Status = 1"; if (argEn.Status == true) { sqlCmd = sqlCmd + " and SAFS_Status = true"; } //if (argEn.Status == false) sqlCmd = sqlCmd + " and SAFS_Status = 0 "; if (argEn.Status == false) { sqlCmd = sqlCmd + " and SAFS_Status = false "; } try { if (!FormHelp.IsBlank(sqlCmd)) { HostelStrDetailsDAL loHostelStrDet = new HostelStrDetailsDAL(); using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType, DataBaseConnectionString, sqlCmd).CreateDataReader()) { while (loReader.Read()) { HostelStructEn loItem = LoadObject(loReader); loItem.lstHFeeWithAmt = loHostelStrDet.GetHostelDetailsAmtList(loItem); loEnList.Add(loItem); } loReader.Close(); } } } catch (Exception ex) { throw ex; } return(loEnList); }
/// <summary> /// Method to Delete HostelStructure /// </summary> /// <param name="argEn">HostelStructure Entity is an Input.</param> /// <returns>Returns Boolean</returns> public bool Delete(HostelStructEn argEn) { bool lbRes = false; string sqlCmd = "DELETE FROM SAS_HostelStruct WHERE SAHS_Code = @SAHS_Code"; try { if (!FormHelp.IsBlank(sqlCmd)) { DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString); _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_Code", DbType.String, argEn.HostelStructureCode); _DbParameterCollection = cmd.Parameters; HostelStrDetailsEn loHstrDet = new HostelStrDetailsEn(); HostelStrDetailsDAL loHstrDetDal = new HostelStrDetailsDAL(); HostelStrAmountEn loHstrAmt = new HostelStrAmountEn(); HostelStrAmountDAL loHstAmtDal = new HostelStrAmountDAL(); loHstrAmt.HSCode = argEn.HostelStructureCode; loHstrDet.HSCode = argEn.HostelStructureCode; loHstAmtDal.Delete(loHstrAmt); loHstrDetDal.Delete(loHstrDet); int liRowAffected = _DatabaseFactory.ExecuteNonQuery(Helper.GetDataBaseType, cmd, DataBaseConnectionString, sqlCmd, _DbParameterCollection); if (liRowAffected > -1) { lbRes = true; } else { throw new Exception("Deletion Failed! No Row has been updated..."); } } } catch (Exception ex) { throw ex; } return(lbRes); }
/// <summary> /// Method to Insert HostelStructure /// </summary> /// <param name="argEn">HostelStructure Entity is an Input.</param> /// <returns>Returns Boolean</returns> //public bool Insert(HostelStructEn argEn) //{ // bool lbRes = false; // int iOut = 0; // string sqlCmd = "Select count(*) as cnt From SAS_HostelStruct WHERE SAHS_Code != @SAHS_Code and SAHB_Code=@SAHB_Code and SAHB_Block=@SAHB_Block and SAHB_RoomTYpe=@SAHB_RoomTYpe"; // try // { // if (!FormHelp.IsBlank(sqlCmd)) // { // DbCommand cmdSel = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString); // _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHS_Code", DbType.String, argEn.HostelStructureCode); // _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_Block", DbType.String, argEn.Block); // _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_Code", DbType.String, argEn.Code); // _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_RoomTYpe", DbType.String, argEn.RoomTYpe); // _DbParameterCollection = cmdSel.Parameters; // using (IDataReader dr = _DatabaseFactory.GetIDataReader(Helper.GetDataBaseType, cmdSel, // DataBaseConnectionString, sqlCmd, _DbParameterCollection).CreateDataReader()) // { // if (dr.Read()) // iOut = clsGeneric.NullToInteger(dr["cnt"]); // if (iOut > 0) // throw new Exception("Record Already Exist"); // } // } // if (iOut == 0) // { // sqlCmd = "INSERT INTO SAS_HostelStruct(SAHS_Code,SAHB_Code,SAHB_Block,SAHB_RoomTYpe,SAHS_EffectFm,SAFS_Status,SAHS_UpdatedUser,SAHS_UpdatedDtTm) VALUES (@SAHS_Code,@SAHB_Code,@SAHB_Block,@SAHB_RoomTYpe,@SAHS_EffectFm,@SAFS_Status,@SAHS_UpdatedUser,@SAHS_UpdatedDtTm) "; // if (!FormHelp.IsBlank(sqlCmd)) // { // argEn.HostelStructureCode = GetAutoNumber("HostelCode"); // DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_Code", DbType.String, argEn.HostelStructureCode); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_Code", DbType.String, argEn.Code); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_Block", DbType.String, argEn.Block); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_RoomTYpe", DbType.String, argEn.RoomTYpe); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_EffectFm", DbType.String, argEn.EffectFm); // _DatabaseFactory.AddInParameter(ref cmd, "@SAFS_Status", DbType.Boolean, argEn.Status); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_UpdatedUser", DbType.String, argEn.UpdatedUser); // _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_UpdatedDtTm", DbType.String, argEn.UpdatedDtTm); // _DbParameterCollection = cmd.Parameters; // int liRowAffected = _DatabaseFactory.ExecuteNonQuery(Helper.GetDataBaseType, cmd, // DataBaseConnectionString, sqlCmd, _DbParameterCollection); // int i = 0; // int j = 0; // HostelStrDetailsDAL loHstrDal = new HostelStrDetailsDAL(); // HostelStrAmountDAL loHstrAmDal = new HostelStrAmountDAL(); // while (i < argEn.lstHFeeSD.Count) // { // //inserting new hostel feeDetails // argEn.lstHFeeSD[i].HSCode = argEn.HostelStructureCode; // loHstrDal.Insert(argEn.lstHFeeSD[i]); // while (j < argEn.lstHFeeSD[i].ListFeeAmount.Count) // { // //inserting new hostel feeAmounts // argEn.lstHFeeSD[i].ListFeeAmount[j].HSCode = argEn.HostelStructureCode; // loHstrAmDal.Insert(argEn.lstHFeeSD[i].ListFeeAmount[j]); // j = j + 1; // } // i = i + 1; // } // if (liRowAffected > -1) // lbRes = true; // else // throw new Exception("Insertion Failed! No Row has been updated..."); // } // } // } // catch (Exception ex) // { // throw ex; // } // return lbRes; //} /// <summary> /// Method to Insert HostelStructure /// </summary> /// <param name="argEn">HostelStructure Entity is an Input.</param> /// <returns>Returns Boolean</returns> public bool Insert(HostelStructEn argEn, bool GenerateAutoNumber = true) { bool lbRes = false; int iOut = 0; //DO NOT USE SAHS_CODE TO CHECKING, CHECK COMPOSITE KEY ( SAHB_Code, SAHB_Block, SAHB_RoomTYpe) //string sqlCmd = "Select count(*) as cnt From SAS_HostelStruct WHERE SAHS_Code = @SAHS_Code and SAHB_Code=@SAHB_Code and SAHB_Block=@SAHB_Block and SAHB_RoomTYpe=@SAHB_RoomTYpe"; string sqlCmd = "Select count(*) as cnt From SAS_HostelStruct WHERE SAHB_Code=@SAHB_Code and SAHB_Block=@SAHB_Block and SAHB_RoomTYpe=@SAHB_RoomTYpe"; try { if (!FormHelp.IsBlank(sqlCmd)) { DbCommand cmdSel = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString); // _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHS_Code", DbType.String, argEn.HostelStructureCode); _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_Block", DbType.String, argEn.Block); _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_Code", DbType.String, argEn.Code); _DatabaseFactory.AddInParameter(ref cmdSel, "@SAHB_RoomTYpe", DbType.String, argEn.RoomTYpe); _DbParameterCollection = cmdSel.Parameters; using (IDataReader dr = _DatabaseFactory.GetIDataReader(Helper.GetDataBaseType, cmdSel, DataBaseConnectionString, sqlCmd, _DbParameterCollection).CreateDataReader()) { if (dr.Read()) { iOut = clsGeneric.NullToInteger(dr["cnt"]); } if (iOut > 0) { throw new Exception("Record Already Exist"); } } } if (iOut == 0) { sqlCmd = "INSERT INTO SAS_HostelStruct(SAHS_Code,SAHB_Code,SAHB_Block,SAHB_RoomTYpe,SAHS_EffectFm,SAFS_Status,SAHS_UpdatedUser,SAHS_UpdatedDtTm) VALUES (@SAHS_Code,@SAHB_Code,@SAHB_Block,@SAHB_RoomTYpe,@SAHS_EffectFm,@SAFS_Status,@SAHS_UpdatedUser,@SAHS_UpdatedDtTm) "; if (!FormHelp.IsBlank(sqlCmd)) { if (GenerateAutoNumber) { argEn.HostelStructureCode = GetAutoNumber("HostelCode"); } DbCommand cmd = _DatabaseFactory.GetDbCommand(Helper.GetDataBaseType, sqlCmd, DataBaseConnectionString); _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_Code", DbType.String, clsGeneric.NullToString(argEn.HostelStructureCode)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_Code", DbType.String, clsGeneric.NullToString(argEn.Code)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_Block", DbType.String, clsGeneric.NullToString(argEn.Block)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHB_RoomTYpe", DbType.String, clsGeneric.NullToString(argEn.RoomTYpe)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_EffectFm", DbType.String, clsGeneric.NullToString(argEn.EffectFm)); _DatabaseFactory.AddInParameter(ref cmd, "@SAFS_Status", DbType.Boolean, clsGeneric.NullToBoolean(argEn.Status)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_UpdatedUser", DbType.String, clsGeneric.NullToString(argEn.UpdatedUser)); _DatabaseFactory.AddInParameter(ref cmd, "@SAHS_UpdatedDtTm", DbType.String, clsGeneric.NullToString(DateTime.Now.ToString("dd/MM/yyyy"))); _DbParameterCollection = cmd.Parameters; int liRowAffected = _DatabaseFactory.ExecuteNonQuery(Helper.GetDataBaseType, cmd, DataBaseConnectionString, sqlCmd, _DbParameterCollection); int i = 0; int j = 0; HostelStrDetailsDAL loHstrDal = new HostelStrDetailsDAL(); HostelStrAmountDAL loHstrAmDal = new HostelStrAmountDAL(); while (i < argEn.lstHFeeSD.Count) { //inserting new hostel feeDetails argEn.lstHFeeSD[i].HSCode = argEn.HostelStructureCode; loHstrDal.Insert(argEn.lstHFeeSD[i]); j = 0; while (j < argEn.lstHFeeSD[i].ListFeeAmount.Count) { //inserting new hostel feeAmounts argEn.lstHFeeSD[i].ListFeeAmount[j].HSCode = argEn.HostelStructureCode; loHstrAmDal.Insert(argEn.lstHFeeSD[i].ListFeeAmount[j]); j = j + 1; } i = i + 1; } if (liRowAffected > -1) { lbRes = true; } else { throw new Exception("Insertion Failed! No Row has been updated..."); } } } } catch (Exception ex) { throw ex; } return(lbRes); }