/// <summary> /// Get DataReader with information about all factor update modes. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetFactorUpdateModes() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetFactorUpdateModes"); return(GetReader(commandBuilder)); }
/// <summary> /// Create log entry in the log of updated observations. /// </summary> /// <param name="dataProvider">Log for which data provider.</param> /// <param name="type">What type of log entry.</param> /// <param name="description">Description of the log entry.</param> /// <param name="observationId">What observation id is related to the log entry.</param> public void UpdateLog(String dataProvider, LogType type, String description, String observationId) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("UpdateUpdateLog"); if (dataProvider.IsNotEmpty()) { commandBuilder.AddParameter(HarvestLog.DATAPROVIDER, dataProvider); } commandBuilder.AddParameter(HarvestLog.TYPE, type.ToString()); if (description.IsNotEmpty()) { commandBuilder.AddParameter(HarvestLog.DESCRIPTION, description); } if (description.IsNotEmpty()) { commandBuilder.AddParameter(HarvestLog.OBSERVATIONID, observationId); } ExecuteCommand(commandBuilder); }
/// <summary> /// Get provenance data provider(s) that matches search criteria. /// </summary> /// <param name="polygons">Selected polygons.</param> /// <param name="regionIds">Region ids.</param> /// <param name="taxonIds"> /// Taxon ids. Observations related to specified taxa and /// all underlying taxa are included. /// </param> /// <param name="joinCondition">SQL join condition.</param> /// <param name="whereCondition">SQL where condition.</param> /// <param name="localeId">Output should be returned in this language.</param> /// <param name="speciesObservationIds">Ids for species observations to get.</param> public DataReader GetProvenanceDataProvidersBySearchCriteria(List <SqlGeometry> polygons, List <Int32> regionIds, List <Int32> taxonIds, String whereCondition, String joinCondition, Int32 localeId, List <Int64> speciesObservationIds) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter, name the stored procedure commandBuilder = new SqlCommandBuilder("GetProvenanceDataProvidersBySearchCriteria", true); commandBuilder.AddParameter(SpeciesObservationData.POLYGONS, polygons); commandBuilder.AddParameter(SpeciesObservationData.REGION_IDS, regionIds); commandBuilder.AddParameter(SpeciesObservationData.TAXON_IDS, taxonIds); if (joinCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.JOIN_CONDITION, joinCondition, 1); } commandBuilder.AddParameter(SpeciesObservationData.WHERE_CONDITION, whereCondition, 1); commandBuilder.AddParameter(SpeciesObservationData.LOCALE_ID, localeId); commandBuilder.AddParameter(SpeciesObservationData.SPECIES_OBSERVATION_IDS, speciesObservationIds); return(GetReader(commandBuilder)); }
/// <summary> /// Remove all rows from temporary Elasticsearch tables. /// </summary> public void EmptyTempElasticsearchTables() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("EmptyTempElasticsearchTables"); ExecuteCommand(commandBuilder); }
/// <summary> /// Used by Get Temp Taxon Tree List in HarvestManager. /// </summary> /// <returns> A temp Taxon tree data reader. /// </returns> public DataReader GetTempTaxonTree() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetTempTaxonTree"); return(GetReader(commandBuilder)); }
/// <summary> /// Get all region categories. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetRegionCategories() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetRegionCategories"); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about all references. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetReferences() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetReferences"); return(GetReader(commandBuilder)); }
/// <summary> /// Get species observations /// that matches search criteria. /// </summary> /// <param name="polygons">Selected polygons.</param> /// <param name="regionIds">Region ids.</param> /// <param name="taxonIds"> /// Taxon ids. Observations related to specified taxa and /// all underlying taxa are returned. /// </param> /// <param name="joinCondition">SQL join condition.</param> /// <param name="whereCondition">SQL where condition.</param> /// <param name="geometryWhereCondition">SQL geometry where condition.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetSpeciesObservationsAccessRights(List <SqlGeometry> polygons, List <Int32> regionIds, List <Int32> taxonIds, String joinCondition, String whereCondition, String geometryWhereCondition) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter, name the stored procedure commandBuilder = new SqlCommandBuilder("GetSpeciesObservationsAccessRights", true); commandBuilder.AddParameter(SpeciesObservationData.POLYGONS, polygons); commandBuilder.AddParameter(SpeciesObservationData.REGION_IDS, regionIds); commandBuilder.AddParameter(SpeciesObservationData.TAXON_IDS, taxonIds); if (joinCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.JOIN_CONDITION, joinCondition); } commandBuilder.AddParameter(SpeciesObservationData.WHERE_CONDITION, whereCondition); if (geometryWhereCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.GEOMETRY_WHERE_CONDITION, geometryWhereCondition); } return(GetReader(commandBuilder)); }
/// <summary> /// /// </summary> /// <param name="queryString"></param> /// <returns></returns> public DataReader GetDyntaxaTaxonIdByQueryString(String queryString) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetTaxonIdByQueryString"); commandBuilder.AddParameter("QueryString", queryString); return(GetReader(commandBuilder)); }
public Int64 GetSpeciesObservationCountBySearchCriteria(List <SqlGeometry> polygons, List <Int32> regionIds, List <Int32> taxonIds, String joinCondition, String whereCondition, String geometryWhereCondition) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter, name the stored procedure CommandTimeout = 600; commandBuilder = new SqlCommandBuilder("GetSpeciesObservationCountBySearchCriteria", true); commandBuilder.AddParameter(SpeciesObservationData.POLYGONS, polygons); commandBuilder.AddParameter(SpeciesObservationData.REGION_IDS, regionIds); commandBuilder.AddParameter(SpeciesObservationData.TAXON_IDS, taxonIds); if (joinCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.JOIN_CONDITION, joinCondition); } commandBuilder.AddParameter(SpeciesObservationData.WHERE_CONDITION, whereCondition); if (geometryWhereCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.GEOMETRY_WHERE_CONDITION, geometryWhereCondition); } return(ExecuteScalar(commandBuilder)); }
/// <summary> /// Adds or updates the SpeciesObservationFieldMapping tabel /// </summary> /// <param name="table">The added or updated rows</param> public void UpdateSpeciesObservationFieldMapping(DataTable table) { var commandBuilder = new SqlCommandBuilder("UpdateSpeciesObservationFieldMapping", true); commandBuilder.AddParameter("Values", table); ExecuteCommand(commandBuilder); }
/// <summary> /// Get information about all species fact qualities. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetSpeciesFactQualities() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetSpeciesFactQualities"); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about all period types. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetPeriodTypes() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetPeriodTypes"); return(GetReader(commandBuilder)); }
/// <summary> /// Get max species fact id. /// </summary> /// <returns>Max species fact id.</returns> public Int32 GetMaxSpeciesFactId() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetMaxSpeciesFactId"); return(ExecuteScalar(commandBuilder)); }
/// <summary> /// Get information about all taxa. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetTaxonInformation() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetTaxon"); return(GetReader(commandBuilder)); }
/// <summary> /// Check if the database is up and running. /// </summary> /// <returns> /// True = OK. /// False = Some kind of problem. /// </returns> public Boolean Ping() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("Ping"); return(ExecuteScalar(commandBuilder) == 1); }
/// <summary> /// Test if species observation information are being updated. /// </summary> /// <returns>True, if species observation information are being updated.</returns> public Boolean IsDatabaseUpdated() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("IsDatabaseUpdated"); return(ExecuteScalar(commandBuilder) > 0); }
/// <summary> /// Add an entry to the web service log /// </summary> /// <param name="text">Log text.</param> /// <param name="type">Type of log entry.</param> /// <param name="information">Extended information about the log entry.</param> /// <param name="userName">Name of user that creates the log entry.</param> /// <param name="clientIpAddress">IP address of user that creates the log entry.</param> /// <param name="applicationIdentifier">Application identifier.</param> public void UpdateLog(String text, String type, String information, String userName, String clientIpAddress, String applicationIdentifier) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("UpdateWebServiceLog"); if (text.IsNotEmpty()) { commandBuilder.AddParameter(WebServiceLogData.TEXT, text.CheckSqlInjection()); } commandBuilder.AddParameter(WebServiceLogData.TYPE, type.CheckSqlInjection()); if (userName.IsNotEmpty()) { commandBuilder.AddParameter(WebServiceLogData.WEB_SERVICE_USER, userName.CheckSqlInjection()); } if (clientIpAddress.IsNotEmpty()) { commandBuilder.AddParameter(WebServiceLogData.TCP_IP, clientIpAddress.CheckSqlInjection()); } if (information.IsNotEmpty()) { commandBuilder.AddParameter(WebServiceLogData.INFORMATION, information.CheckSqlInjection()); } if (applicationIdentifier.IsNotEmpty()) { commandBuilder.AddParameter(WebServiceLogData.APPLICATION_IDENTIFIER, applicationIdentifier.CheckSqlInjection()); } ExecuteCommand(commandBuilder); }
/// <summary> /// Get regions that matches the search criteria. /// </summary> /// <param name="nameSearchString">Name search string.</param> /// <param name="typeId">Id of type to search for.</param> /// <param name="categoryIds">Category ids.</param> /// <param name="countryIsoCodes">The country iso codes.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetRegionsBySearchCriteria(String nameSearchString, Int32?typeId, List <Int32> categoryIds, List <Int32> countryIsoCodes) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter commandBuilder = new SqlCommandBuilder("GetRegionsBySearchCriteria", true); if (nameSearchString.IsNotEmpty()) { commandBuilder.AddParameter(RegionData.NAME, nameSearchString, 1); } if (typeId.HasValue) { commandBuilder.AddParameter(RegionData.TYPE_ID, typeId.Value); } if (categoryIds.IsNotEmpty()) { commandBuilder.AddParameter(RegionData.CATEGORY_IDS, categoryIds); } if (countryIsoCodes.IsNotEmpty()) { commandBuilder.AddParameter(RegionData.COUNTRY_ISO_CODES, countryIsoCodes); } return(GetReader(commandBuilder)); }
/// <summary> /// Delete trace information from the web service log. /// </summary> public void DeleteTrace() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("DeleteWebServiceTrace"); ExecuteCommand(commandBuilder); }
/// <summary> /// Delete unnecessary species observation change information. /// </summary> public void DeleteUnnecessaryChanges() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("DeleteUnnecessaryChanges"); ExecuteCommand(commandBuilder); }
/// <summary> /// Get length of a column in a database. /// This method is mainly used to retrive information about /// text columns but is works on any data type. /// </summary> /// <param name="tableName">Name of table to get information from.</param> /// <param name="columnName">Name of column to get length of.</param> /// <returns> The column length.</returns> public Int32 GetColumnLength(String tableName, String columnName) { Int32 columnLength; SqlCommandBuilder commandBuilder; String hashKey; // Get length of specified column. hashKey = "Table:" + tableName + "Column:" + columnName; if (_columnLenghtTable.Contains(hashKey)) { // Get cached value. columnLength = (Int32)(_columnLenghtTable[hashKey]); } else { // Get value from database. commandBuilder = new SqlCommandBuilder("GetColumnLength"); commandBuilder.AddParameter(ColumnLenghtData.TABLE_NAME, tableName); commandBuilder.AddParameter(ColumnLenghtData.COLUMN_NAME, columnName); columnLength = ExecuteScalar(commandBuilder); // Add value to cache. lock (_columnLenghtTable) { if (!(_columnLenghtTable.Contains(hashKey))) { _columnLenghtTable.Add(hashKey, columnLength); } } } return(columnLength); }
/// <summary> /// Get difference between Elasticsearch and SQL Server. /// </summary> /// <returns>Information about changed species observations.</returns> public DataReader GetSpeciesObservationDifference() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetSpeciesObservationDifference"); return(GetReader(commandBuilder, CommandBehavior.Default)); }
/// <summary> /// Get DataReader with all observation ids that has been deleted during the period changedFrom - changedTo. /// </summary> /// <param name="changedFrom">Get observations ids that was deleted at or later than this date.</param> /// <param name="changedTo">Get observations ids that was deleted at or before date.</param> /// <returns>An open DataReader for reading.</returns> public DataReader GetDeletedObservations(DateTime changedFrom, DateTime changedTo) { ArtDatabanken.Database.SqlCommandBuilder commandBuilder = new ArtDatabanken.Database.SqlCommandBuilder("GetDeletedObservations"); commandBuilder.AddParameter(Observationsdatabasen.CHANGED_FROM, changedFrom); commandBuilder.AddParameter(Observationsdatabasen.CHANGED_TO, changedTo); return(GetReader(commandBuilder)); }
/// <summary> /// Update columns disturbanceRadius and /// maxAccuracyOrDisturbanceRadius in table /// TempUpdateDarwinCoreObservation. /// </summary> public void UpdateAccuracyAndDisturbanceRadius() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("UpdateAccuracyAndDisturbanceRadius"); ExecuteCommand(commandBuilder); }
/// <summary> /// Get next higher species observation change id that is larger than current change id. /// If no higher change id exists then currentChangeId is returned. /// </summary> /// <param name="currentChangeId">Current change id.</param> /// <param name="nextIndexHarvestStart"> /// Start date of harvest. /// This value is used to avoid handling old delete of species observations. /// </param> /// <returns>Next higher species observation change id that is larger than current change id.</returns> public Int64 GetNextChangeId(Int64 currentChangeId, DateTime?nextIndexHarvestStart) { Int64 nextChangeId; SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetNextChangeId"); commandBuilder.AddParameter(SpeciesObservationChangeData.CURRENT_CHANGE_ID, currentChangeId); if (nextIndexHarvestStart.HasValue) { commandBuilder.AddParameter(SpeciesObservationElasticsearchData.NEXT_INDEX_HARVEST_START, nextIndexHarvestStart.Value); } using (DataReader dataReader = GetReader(commandBuilder)) { if (dataReader.Read()) { nextChangeId = dataReader.GetInt64(SpeciesObservationChangeData.NEXT_CHANGE_ID); } else { throw new ApplicationException("Next species observation change id was not found."); } } return(nextChangeId); }
/// <summary> /// Set harvest job's data provider metadata. /// Used by HarvestManager (UpdateSpeciesObservations). /// </summary> /// <param name="harvestJob"> /// The harvest Job. /// </param> /// <returns> /// The result of the query. /// </returns> public int SetHarvestJobDataProvider(HarvestJob harvestJob) { if (harvestJob.IsNotNull()) { int cumulativeResult = 1; if (harvestJob.DataProviders.Any()) { Debug.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + ": harvestJob(dataproviders), "); foreach (var harvestJobDataProvider in harvestJob.DataProviders) { var commandBuilder = new SqlCommandBuilder("HarvestJobSetDataProvider"); commandBuilder.AddParameter(HarvestJobTableData.DATAPROVIDERID, harvestJobDataProvider.DataProviderId); commandBuilder.AddParameter(HarvestJobTableData.CHANGEID, harvestJobDataProvider.ChangeId); int result = ExecuteCommand(commandBuilder); if (result < 1) { cumulativeResult = result; } } } return(cumulativeResult); } return(-1); }
/// <summary> /// Get information related to Elasticsearch and species observations. /// </summary> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetSpeciesObservationElasticsearch() { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetSpeciesObservationElasticsearch"); return(GetReader(commandBuilder)); }
public DataReader GetTimeSpeciesObservationCountsBySearchCriteria(List <SqlGeometry> polygons, List <Int32> regionIds, List <Int32> taxonIds, String joinCondition, String whereCondition, String periodicity, List <Int64> speciesObservationIds) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter, name the stored procedure commandBuilder = new SqlCommandBuilder("GetTimeSpeciesObservationCountsBySearchCriteria", true); commandBuilder.AddParameter(SpeciesObservationData.POLYGONS, polygons); commandBuilder.AddParameter(SpeciesObservationData.REGION_IDS, regionIds); commandBuilder.AddParameter(SpeciesObservationData.TAXON_IDS, taxonIds); if (joinCondition.IsNotEmpty()) { commandBuilder.AddParameter(SpeciesObservationData.JOIN_CONDITION, joinCondition, 1); } commandBuilder.AddParameter(SpeciesObservationData.WHERE_CONDITION, whereCondition, 1); commandBuilder.AddParameter(TimeSpeciesObservationCountData.PERIODICITY, periodicity); commandBuilder.AddParameter(SpeciesObservationData.SPECIES_OBSERVATION_IDS, speciesObservationIds); return(GetReader(commandBuilder)); }
/// <summary> /// Get all factors that match search criteria. /// </summary> /// <param name="factorIds">Factors to filter on.</param> /// <param name="factorNameSearchString">String to match factor names with.</param> /// <param name="nameSearchMethod">How search should be performed.</param> /// <param name="restrictSearchToScope">Indicates scope of factor search.</param> /// <param name="restrictReturnToScope">Indicates scope for returned factors.</param> /// <returns>Filtered factors.</returns> public DataReader GetFactorsBySearchCriteria(List <Int32> factorIds, String factorNameSearchString, String nameSearchMethod, String restrictSearchToScope, String restrictReturnToScope) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetFactorsByIdsAndSearchCriteria", true); if (factorIds.IsNotEmpty()) { commandBuilder.AddParameter(FactorData.FACTOR_IDS, factorIds); } if (factorNameSearchString.IsNotEmpty()) { commandBuilder.AddParameter(FactorData.FACTOR_NAME_SEARCH_STRING, factorNameSearchString); } commandBuilder.AddParameter(FactorData.FACTOR_NAME_SEARCH_METHOD, nameSearchMethod); commandBuilder.AddParameter(FactorData.IS_FACTOR_IDS_SPECIFIED, factorIds.IsNotEmpty()); commandBuilder.AddParameter(FactorData.RESTRICT_SEARCH_TO_SCOPE, restrictSearchToScope); commandBuilder.AddParameter(FactorData.RESTRICT_RETURN_TO_SCOPE, restrictReturnToScope); return(GetReader(commandBuilder)); }