/// <summary> /// Description: Delete method /// </summary> /// <param name="oEntity"></param> /// <param name="ht"></param> /// <returns>Boolean</returns> public bool Delete(MasterEntity oEntity, Hashtable ht) { bool flag = false; try { DataSet DTSet = FillEntityWithValues(oEntity); flag = this.oDalc.CRUD(DTSet, ht); } catch (Exception ex) { throw ex; } return(flag); }
/// <summary> /// Description: Get data which need to be fetch on Auto Complete Dropdown's /// </summary> /// <param name="oEntity"></param> /// <param name="ht"></param> /// <returns>List<object></returns> public List <object> GetAutoData(MasterEntity oEntity, Hashtable ht) { List <object> ol = new List <object>(); try { DataSet DTSet = FillEntityWithValues(oEntity); DataSet ds = this.oDalc.GetAutoDataSet(DTSet, ht); if (ds != null && ds.Tables.Count >= 1) { ol.Add(MyCommonFunctions.DataSetToJSON(ds)); } } catch (Exception ex) { throw ex; } return(ol); }
/// <summary> /// This method is used to get Initial data in dataset /// </summary> /// <param name="DataSet"></param> /// <param name="Hashtable"></param> /// <returns></returns> public DataSet GetInitDataSet(DataSet DTSet, Hashtable ht) { DataSet ds = new DataSet(); try { //if DTSet is null then pass blank DBStructure to SP if (DTSet == null) { DTSet = new MasterEntity().GetTablesStructure(); } using (SqlConnection con = base.GetConnection()) { using (SqlCommand cmd = new SqlCommand(MasterEntity.StoredProcedures.CRUD, con)) { cmd.CommandType = CommandType.StoredProcedure; SqlParameter parameter = new SqlParameter(MasterEntity.Parameters.Param_TypeTable_UserProfile, DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile]); parameter.SqlDbType = SqlDbType.Structured; parameter.TypeName = MasterEntity.DBTableAndType.TYPE_TABLE_UserProfile; cmd.Parameters.Add(parameter); // SQL TypeTable Parameter for Auto Search or Global Search (Common Search) Table parameter = new SqlParameter(MasterEntity.Parameters.Param_TypeTable_UserProfile_CommonSearch, DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile_CommonSearch]); parameter.SqlDbType = SqlDbType.Structured; parameter.TypeName = MasterEntity.DBTableAndType.TYPE_TABLE_UserProfile_CommonSearch; cmd.Parameters.Add(parameter); if (ht != null) { // Operation mode if (ht[MyCommonEntity.Fields.OPERATIONMODE] != null) { cmd.Parameters.Add(MyCommonEntity.Parameters.OperationMode, SqlDbType.VarChar, 10).Value = ht[MyCommonEntity.Fields.OPERATIONMODE]; } #region Output parameter // this represent Output parameter if (ht[MasterEntityFields.USERID] != null) { cmd.Parameters.Add(MasterEntity.Parameters.Param_UserID, SqlDbType.Int).Value = ht[MasterEntityFields.USERID]; } else { cmd.Parameters.Add(MasterEntity.Parameters.Param_UserID, SqlDbType.Int).Value = 0; } cmd.Parameters[MasterEntity.Parameters.Param_UserID].Direction = ParameterDirection.InputOutput; #endregion } SqlDataAdapter AdapterObj = new SqlDataAdapter(cmd); AdapterObj.TableMappings.Add("Table", MasterEntity.DataTableName.DTData); AdapterObj.Fill(ds); } } } catch (SqlException sqlException) { throw sqlException; } catch (Exception ex) { throw ex; } return(ds); }
/// <summary> /// Description: Perform Add/Update/deleted operation /// </summary> /// <param name="DataSet"></param> /// <param name="Hashtable"></param> /// <returns></returns> public bool CRUD(DataSet DTSet, Hashtable ht) { bool flag = false; try { //if DTSet is null then pass blank DBStructure to SP if (DTSet == null) { DTSet = new MasterEntity().GetTablesStructure(); } using (SqlConnection con = base.GetConnection()) { using (SqlCommand cmd = new SqlCommand(MasterEntity.StoredProcedures.CRUD, con)) { cmd.CommandType = CommandType.StoredProcedure; SqlParameter parameter = new SqlParameter(MasterEntity.Parameters.Param_TypeTable_UserProfile, DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile]); parameter.SqlDbType = SqlDbType.Structured; parameter.TypeName = MasterEntity.DBTableAndType.TYPE_TABLE_UserProfile; cmd.Parameters.Add(parameter); // SQL TypeTable Parameter for Auto Search or Global Search (Common Search) Table parameter = new SqlParameter(MasterEntity.Parameters.Param_TypeTable_UserProfile_CommonSearch, DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile_CommonSearch]); parameter.SqlDbType = SqlDbType.Structured; parameter.TypeName = MasterEntity.DBTableAndType.TYPE_TABLE_UserProfile_CommonSearch; cmd.Parameters.Add(parameter); if (ht != null) { // Operation mode if (ht[MyCommonEntity.Fields.OPERATIONMODE] != null) { cmd.Parameters.Add(MyCommonEntity.Parameters.OperationMode, SqlDbType.VarChar, 10).Value = ht[MyCommonEntity.Fields.OPERATIONMODE]; } #region Output parameter // this represent Output parameter if (ht[MasterEntityFields.USERID] != null) { cmd.Parameters.Add(MasterEntity.Parameters.Param_UserID, SqlDbType.Int).Value = ht[MasterEntityFields.USERID]; } else { cmd.Parameters.Add(MasterEntity.Parameters.Param_UserID, SqlDbType.Int).Value = 0; } cmd.Parameters[MasterEntity.Parameters.Param_UserID].Direction = ParameterDirection.InputOutput; #endregion } cmd.Connection.Open(); flag = cmd.ExecuteNonQuery() > 0; if (flag) { ht[MasterEntityFields.USERID] = Convert.ToInt32(cmd.Parameters[MasterEntity.Parameters.Param_UserID].Value); } } } } catch (SqlException sqlException) { throw sqlException; } catch (Exception ex) { throw ex; } return(flag); }
/// <summary> /// Description: Fill DataSet with Actual Values which needs to be send on DB /// </summary> /// <param name="oEntity"></param> /// <returns>Convert oEntity to DataSet which contains data in DataTable collection format</returns> public DataSet FillEntityWithValues(MasterEntity oEntity) { DataSet DTSet = new MasterEntity().GetTablesStructure(); DataTable DTMasterTable = DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile]; DataTable DT_USERPROFILE_COMMONSEARCH = DTSet.Tables[MasterEntity.DBTableAndType.TABLE_UserProfile_CommonSearch]; DataRow dr = DTMasterTable.NewRow(); try { if (oEntity.UserID != null) { dr[MasterEntityFields.USERID] = oEntity.UserID; } if (oEntity.FirstName != null) { dr[MasterEntityFields.FIRSTNAME] = oEntity.FirstName; } if (oEntity.LastName != null) { dr[MasterEntityFields.LASTNAME] = oEntity.LastName; } if (oEntity.DOB != null) { dr[MasterEntityFields.DOB] = oEntity.DOB; } if (oEntity.Phone != null) { dr[MasterEntityFields.PHONE] = oEntity.Phone; } if (oEntity.Address != null) { dr[MasterEntityFields.ADDRESS] = oEntity.Address; } if (oEntity.Password != null) { dr[MasterEntityFields.PASSWORD] = oEntity.Password; } if (oEntity.UserName != null) { dr[MasterEntityFields.USERNAME] = oEntity.UserName; } if (oEntity.Remark != null) { dr[MasterEntityFields.REMARK] = oEntity.Remark; } if (oEntity.ValidFrom != null) { dr[MasterEntityFields.VALIDFROM] = oEntity.ValidFrom; } if (oEntity.ValidTo != null) { dr[MasterEntityFields.VALIDTO] = oEntity.ValidTo; } if (oEntity.CreatedBy != null) { dr[MasterEntityFields.CREATEDBY] = oEntity.CreatedBy; } if (oEntity.CreatedOn != null) { dr[MasterEntityFields.CREATEDON] = oEntity.CreatedOn; } if (oEntity.CreatedIP != null) { dr[MasterEntityFields.CREATEDIP] = oEntity.CreatedIP; } if (oEntity.ModifiedBy != null) { dr[MasterEntityFields.MODIFIEDBY] = oEntity.ModifiedBy; } if (oEntity.ModifiedOn != null) { dr[MasterEntityFields.MODIFIEDON] = oEntity.ModifiedOn; } if (oEntity.ModifiedIP != null) { dr[MasterEntityFields.MODIFIEDIP] = oEntity.ModifiedIP; } if (oEntity.IsActive != null) { dr[MasterEntityFields.ISACTIVE] = oEntity.IsActive; } if (oEntity.IsDeleted != null) { dr[MasterEntityFields.ISDELETED] = oEntity.IsDeleted; } DTMasterTable.Rows.Add(dr); DTMasterTable.AcceptChanges(); // Collect the information from oEntity UserProfile_CommonSearch and fill the data in DT_USERPROFILE_COMMONSEARCH DataTable if (oEntity.UserProfile_CommonSearchList != null) { foreach (ChildUserProfile_CommonSearchEntity oEntity_ in oEntity.UserProfile_CommonSearchList) { //Initialize New row with DataTable dr = DT_USERPROFILE_COMMONSEARCH.NewRow(); if (oEntity_.FirstName != null) { dr[ChildUserProfile_CommonSearchEntityFields.FIRSTNAME] = oEntity_.FirstName; } if (oEntity_.LastName != null) { dr[ChildUserProfile_CommonSearchEntityFields.LASTNAME] = oEntity_.LastName; } if (oEntity_.DOB != null) { dr[ChildUserProfile_CommonSearchEntityFields.DOB] = oEntity_.DOB; } if (oEntity_.UserName != null) { dr[ChildUserProfile_CommonSearchEntityFields.USERNAME] = oEntity_.UserName; } if (oEntity_.Password != null) { dr[ChildUserProfile_CommonSearchEntityFields.PASSWORD] = oEntity_.Password; } if (oEntity_.SiteId != null) { dr[ChildUserProfile_CommonSearchEntityFields.SITEID] = oEntity_.SiteId; } //Add Row into DataTable DT_USERPROFILE_COMMONSEARCH.Rows.Add(dr); } // Save State of DataTable with Currently filled Data DT_USERPROFILE_COMMONSEARCH.AcceptChanges(); } } catch (Exception exe) { throw exe; } return(DTSet); }