public List<ChargeEntity> Search(QueryCondition<ChargeEntity> entity) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ChargeSearch"); using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc")) { ChargeEntity query = entity.Condition; sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1"); if (query != null) { if (!string.IsNullOrEmpty(query.Title)) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (Title Like'%'+@Title+'%')"); command.AddInputParameter("@Title", DbType.String, query.Title); } if (!string.IsNullOrEmpty(query.InDateCondition) && query.InDateCondition.IndexOf('-') > 0) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " InDate BETWEEN @BeginInDate AND @EndInDate"); command.AddInputParameter("@BeginInDate", DbType.String, query.InDateCondition.Split('-')[0]); command.AddInputParameter("@EndInDate", DbType.String, query.InDateCondition.Split('-')[1]); } } command.CommandText = sqlBuilder.BuildQuerySql(); return command.ExecuteEntityList<ChargeEntity>(); } }
/// <summary> /// Returns the WHERE portion of an SQL statement contains the fields describing the criteria passed across. /// </summary> /// <param name="aircraft"></param> /// <param name="criteria"></param> /// <returns></returns> public static CriteriaAndProperties GetFlightsCriteria(BaseStationAircraft aircraft, SearchBaseStationCriteria criteria) { var result = new CriteriaAndProperties(); StringBuilder command = new StringBuilder(); if (aircraft != null) { DynamicSqlBuilder.AddWhereClause(command, "[Flights].[AircraftID]", " = @aircraftID"); result.Parameters.Add("aircraftID", aircraft.AircraftID); } if (criteria.UseAlternateCallsigns && criteria.Callsign != null && criteria.Callsign.Condition == FilterCondition.Equals && !String.IsNullOrEmpty(criteria.Callsign.Value)) { GetAlternateCallsignCriteria(command, result.Parameters, criteria.Callsign, "[Flights].[Callsign]"); } else { DynamicSqlBuilder.AddCriteria(command, criteria.Callsign, result.Parameters, "[Flights].[Callsign]", "callsign"); } DynamicSqlBuilder.AddCriteria(command, criteria.Date, result.Parameters, "[Flights].[StartTime]", "fromStartTime", "toStartTime"); DynamicSqlBuilder.AddCriteria(command, criteria.Operator, result.Parameters, "[Aircraft].[RegisteredOwners]", "registeredOwners"); DynamicSqlBuilder.AddCriteria(command, criteria.Registration, result.Parameters, "[Aircraft].[Registration]", "registration"); DynamicSqlBuilder.AddCriteria(command, criteria.Icao, result.Parameters, "[Aircraft].[ModeS]", "icao"); DynamicSqlBuilder.AddCriteria(command, criteria.Country, result.Parameters, "[Aircraft].[ModeSCountry]", "modeSCountry"); DynamicSqlBuilder.AddCriteria(command, criteria.IsEmergency, "[Flights].[HadEmergency]"); DynamicSqlBuilder.AddCriteria(command, criteria.Type, result.Parameters, "[Aircraft].[ICAOTypeCode]", "modelIcao"); DynamicSqlBuilder.AddCriteria(command, criteria.FirstAltitude, result.Parameters, "[Flights].[FirstAltitude]", "fromFirstAltitude", "toFirstAltitude"); DynamicSqlBuilder.AddCriteria(command, criteria.LastAltitude, result.Parameters, "[Flights].[LastAltitude]", "fromLastAltitude", "toLastAltitude"); result.SqlChunk = command.ToString(); return(result); }
public List<ErrorLogEntity> Seach(QueryCondition<ErrorLogEntity> entity) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ErrorLogSearch"); using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc")) { ErrorLogEntity query = entity.Condition; if (query != null) { if (!string.IsNullOrEmpty(query.GlobalName)) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (GlobalName Like'%'+@GlobalName+'%')"); command.AddInputParameter("@GlobalName", DbType.String, query.GlobalName); //sqlBuilder.Conditions.AddCondition("UserName", DbType.String, "@UserName", query.UserName); } } command.CommandText = sqlBuilder.BuildQuerySql(); return command.ExecuteEntityList<ErrorLogEntity>(); } }
public List<BrowsingHistoryEntity> Search(QueryCondition<BrowsingHistoryEntity> entity) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("BrowsingHistorySearch"); using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc")) { BrowsingHistoryEntity query = entity.Condition; sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1"); if (query != null) { if (!string.IsNullOrEmpty(query.PageName)) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (PageName Like'%'+@PageName+'%')"); command.AddInputParameter("@PageName", DbType.String, query.PageName); } } command.CommandText = sqlBuilder.BuildQuerySql(); return command.ExecuteEntityList<BrowsingHistoryEntity>(); } }
/// <summary> /// Builds up the criteria and properties for all alternate callsigns. /// </summary> /// <param name="command"></param> /// <param name="parameters"></param> /// <param name="criteria"></param> /// <param name="callsignField"></param> private static void GetAlternateCallsignCriteria(StringBuilder command, DynamicParameters parameters, FilterString criteria, string callsignField) { if (criteria != null && !String.IsNullOrEmpty(criteria.Value)) { var callsignParser = Factory.Resolve <ICallsignParser>(); var alternates = callsignParser.GetAllAlternateCallsigns(criteria.Value); for (var i = 0; i < alternates.Count; ++i) { var isFirst = i == 0; var isLast = i + 1 == alternates.Count; var callsign = alternates[i]; var parameterName = String.Format("callsign{0}", i + 1); DynamicSqlBuilder.AddWhereClause(command, callsignField, String.Format(" {0} @{1}", !criteria.ReverseCondition ? "=" : "<>", parameterName), useOR: !isFirst && !criteria.ReverseCondition, openParenthesis: isFirst, closeParenthesis: isLast); parameters.Add(parameterName, callsign); } } }
public List<SystemUserEntity> Search(QueryCondition<SystemUserEntity> entity) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SystemUserSearch"); using (DynamicSqlBuilder sqlBuilder = new DynamicSqlBuilder(command, entity.PagingInfo, "SysNo Desc")) { SystemUserEntity query = entity.Condition; sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " Status <> -1"); if (query != null) { if (!string.IsNullOrEmpty(query.UserName)) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " (UserName Like'%'+@UserName+'%')"); command.AddInputParameter("@UserName", DbType.String, query.UserName); //sqlBuilder.Conditions.AddCondition("UserName", DbType.String, "@UserName", query.UserName); } if (query.DropRole > 0) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " SysNo IN (SELECT SystemUserSysNo FROM SystemUser_RoleMapping WHERE RoleSysNo = RoleSysNo)"); command.AddInputParameter("@RoleSysNo", DbType.Int32, query.DropRole); } if (!string.IsNullOrEmpty(query.InDateCondition) && query.InDateCondition.IndexOf('-') > 0) { sqlBuilder.Conditions.AddCustomCondition(RelationType.AND, " InDate BETWEEN @BeginInDate AND @EndInDate"); command.AddInputParameter("@BeginInDate", DbType.String, query.InDateCondition.Split('-')[0]); command.AddInputParameter("@EndInDate", DbType.String, query.InDateCondition.Split('-')[1]); } } command.CommandText = sqlBuilder.BuildQuerySql(); return command.ExecuteEntityList<SystemUserEntity>(); } }