/// <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 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> /// 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> /// 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> /// 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 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> /// 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) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetDeletedObservations"); commandBuilder.AddParameter(Artportalen.CHANGED_FROM, changedFrom); commandBuilder.AddParameter(Artportalen.CHANGED_TO, changedTo); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with all taxa that matches the search criteria. /// </summary> /// <param name="changedFrom">Get observations registered at or later than this date.</param> /// <param name="changedTo">Get observations registered before this date.</param> /// <returns>DataReader after reading has been finished.</returns> public DataReader GetSpeciesObservations(DateTime changedFrom, DateTime changedTo) { ArtDatabanken.Database.SqlCommandBuilder commandBuilder = new ArtDatabanken.Database.SqlCommandBuilder("GetSpeciesObservations"); commandBuilder.AddParameter(Observationsdatabasen.CHANGED_FROM, changedFrom); commandBuilder.AddParameter(Observationsdatabasen.CHANGED_TO, changedTo); commandBuilder.AddParameter(Observationsdatabasen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production)); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with all observations that matches the list of id's (used for sync purposes and not normal harvest), including any project parameters as a second dataset /// </summary> /// <param name="sightingIds">Get observations that has it's id in this list</param> /// <returns>An open DataReader for reading.</returns> public DataReader GetSpeciesObservationsByIds(List <Int64> sightingIds) { CommandTimeout = 600; var commandBuilder = new SqlCommandBuilder("GetSpeciesObservationsByIds", true); commandBuilder.AddParameter(Artportalen.SIGHTING_IDS, sightingIds); commandBuilder.AddParameter(Artportalen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production)); return(GetReader(commandBuilder, CommandBehavior.Default)); }
public string GetCountyFromCoordinates(double x, double y) { var command = new SqlCommandBuilder("GetCountyFromCoordinates", false); command.AddParameter("X", x); command.AddParameter("Y", y); return(ExecuteScalarString(command).Trim()); }
/// <summary> /// Get species observations by ids in a format that /// is adjusted to the needs of Elasticsearch. /// </summary> /// <param name="speciesObservationIds">Species observation ids.</param> /// <returns>Information about species observations.</returns> public DataReader GetSpeciesObservationsByIdElasticsearch(List <Int64> speciesObservationIds) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetSpeciesObservationsByIdElasticsearch", true); commandBuilder.AddParameter("SpeciesObservationIdTable", speciesObservationIds); commandBuilder.AddParameter(SpeciesObservationElasticsearchData.IS_DEBUG, Configuration.Debug); return(GetReader(commandBuilder)); }
/// <summary> /// Log when latest harvest was made for this data provider. /// </summary> /// <param name="dataProviderId">Data provider.</param> /// <param name="harvestDate">Latest date when harvest was made.</param> /// <returns>If database operation was successful.</returns> public Int32 SetDataProviderLatestHarvestDate(Int32 dataProviderId, DateTime harvestDate) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderLatestHarvestDate", false); commandBuilder.AddParameter("dataProviderId", dataProviderId); commandBuilder.AddParameter("harvestDate", harvestDate); return(ExecuteCommand(commandBuilder)); }
/// <summary> /// Get DataReader with all observations that matches the search criteria, including any project parameters as a second dataset. /// </summary> /// <param name="changedFrom">Get observations registered at or later than this date.</param> /// <param name="changedTo">Get observations registered before this date.</param> /// <returns>DataReader after reading has been finished.</returns> public DataReader GetSpeciesObservations(DateTime changedFrom, DateTime changedTo) { SqlCommandBuilder commandBuilder; CommandTimeout = 1200; commandBuilder = new SqlCommandBuilder("GetSpeciesObservations"); commandBuilder.AddParameter(Artportalen.CHANGED_FROM, changedFrom); commandBuilder.AddParameter(Artportalen.CHANGED_TO, changedTo); commandBuilder.AddParameter(Artportalen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production)); return(GetReader(commandBuilder, CommandBehavior.Default)); }
/// <summary> /// Get species observations with specified ids. /// </summary> /// <param name="speciesObservationIds">Ids for species observations to get.</param> /// <param name="maxProtectionLevel">Max protection level.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetDarwinCoreByIds(List <Int64> speciesObservationIds, Int32 maxProtectionLevel) { SqlCommandBuilder commandBuilder; // Use SqlCommandBuilder with SqlParameter, name the stored procedure CommandTimeout = 600; commandBuilder = new SqlCommandBuilder("GetDarwinCoreById", true); commandBuilder.AddParameter("SpeciesObservationIdTable", speciesObservationIds); commandBuilder.AddParameter(SpeciesObservationData.MAX_PROTECTION_LEVEL, maxProtectionLevel); return(GetReader(commandBuilder)); }
/// <summary> /// Get all factor tree nodes that match search criteria. /// </summary> /// <param name="factorIds">Factors to filter on.</param> /// <returns>Factor tree nodes.</returns> public DataReader GetFactorTreesBySearchCriteria(List <Int32> factorIds) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetFactorTreesByIdsAndSearchCriteria", true); if (factorIds.IsNotEmpty()) { commandBuilder.AddParameter(FactorTreeData.FACTOR_IDS, factorIds); } commandBuilder.AddParameter(FactorTreeData.IS_FACTOR_IDS_SPECIFIED, factorIds.IsNotEmpty()); return(GetReader(commandBuilder, CommandBehavior.Default)); }
/// <summary> /// Write to log of statistics. /// </summary> /// <param name="action">What type of action should be logged.</param> /// <param name="processtime">How long time did the process take.</param> /// <param name="result">How did the action went.</param> public void WriteToLogStatistics(string action, long processtime, string result) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("WriteToLogStatistics"); commandBuilder.AddParameter(HarvestLog.ACTION, action); commandBuilder.AddParameter(HarvestLog.RESULT, result); commandBuilder.AddParameter(HarvestLog.PROCESSTIME, processtime); Debug.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + ": " + action + ", " + result + ", " + processtime); ExecuteCommand(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> /// /// </summary> /// <param name="queryString"></param> /// <returns></returns> public DataReader GetDyntaxaTaxonIdByQueryString(String queryString) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetTaxonIdByQueryString"); commandBuilder.AddParameter("QueryString", queryString); return(GetReader(commandBuilder)); }
/// <summary> /// Creates a reference relation. /// </summary> /// <param name="relatedObjectGuid"> /// Object GUID representing an object /// (taxon , taxon name, revision etc.). /// </param> /// <param name="referenceId">Id of the reference.</param> /// <param name="typeId">Reference type.</param> /// <returns>Id for the created reference relation.</returns> public Int32 CreateReferenceRelation(String relatedObjectGuid, Int32 referenceId, Int32 typeId) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("CreateReferenceRelation"); commandBuilder.AddParameter(ReferenceRelationData.OBJECT_GUID, relatedObjectGuid); commandBuilder.AddParameter(ReferenceRelationData.REFERENCEID, referenceId); commandBuilder.AddParameter(ReferenceRelationData.TYPE, typeId); lock (this) { CheckTransaction(); return(ExecuteScalar(commandBuilder)); } }
/// <summary> /// Get information about species observations that has /// changed. /// /// Scope is restricted to those observations that the /// user has access rights to. There is no access right /// check on deleted species observations. This means /// that a client can obtain information about deleted /// species observations that the client has not /// received any create or update information about. /// </summary> /// <param name="changeId"> /// Start id for changes that should be returned. /// The species observation that is changed in the /// specified change id may be included in returned /// information. /// </param> /// <param name="maxReturnedChanges">Max number of changes to return.</param> /// <param name="nextIndexHarvestStart"> /// Start date of harvest. /// This value is used to avoid handling old delete of species observations. /// </param> /// <returns>Information about changed species observations.</returns> public DataReader GetSpeciesObservationChangesElasticsearch(long changeId, long maxReturnedChanges, DateTime?nextIndexHarvestStart) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetSpeciesObservationChangesElasticsearch"); commandBuilder.AddParameter(SpeciesObservationElasticsearchData.CHANGE_ID, changeId); commandBuilder.AddParameter(SpeciesObservationElasticsearchData.IS_DEBUG, Configuration.Debug); commandBuilder.AddParameter(SpeciesObservationElasticsearchData.MAX_RETURNED_CHANGES, maxReturnedChanges); if (nextIndexHarvestStart.HasValue) { commandBuilder.AddParameter(SpeciesObservation.Database.SpeciesObservationElasticsearchData.NEXT_INDEX_HARVEST_START, nextIndexHarvestStart.Value); } return(GetReader(commandBuilder, CommandBehavior.Default)); }
/// <summary> /// Get DataReader with all observations that has been deleted and also matches the list of id's (used for sync purposes and not normal harvest) /// </summary> /// <param name="sightingIds">Get observations that has it's id in this list</param> /// <returns>An open DataReader for reading.</returns> public DataReader GetDeletedObservationsByIds(List <string> sightingIds) { CommandTimeout = 600; var commandBuilder = new SqlCommandBuilder("GetDeletedObservationsByIds", true); commandBuilder.AddParameter(Artportalen.SIGHTING_IDS, sightingIds); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about reference relation types. /// </summary> /// <param name="localeId">Language id.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetReferenceRelationTypes(Int32 localeId) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetReferenceRelationTypes"); commandBuilder.AddParameter(LocaleData.LOCALE_ID, localeId); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about a reference relation. /// </summary> /// <param name="guid">Reference relation GUID.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetReferenceRelationsByGuid(String guid) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetReferenceRelationsByGuid"); commandBuilder.AddParameter(ReferenceRelationData.GUID, guid); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about references /// that matches search criteria. /// </summary> /// <param name="whereCondition">Where condition.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetReferencesBySearchCriteria(String whereCondition) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetReferencesBySearchCriteria"); commandBuilder.AddParameter(ReferenceData.WHERE_CONDITION, whereCondition); return(GetReader(commandBuilder)); }
/// <summary> /// Get DataReader with information about specified references. /// </summary> /// <param name="referenceIds">Reference ids.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetReferencesByIds(List <Int32> referenceIds) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetReferencesByIds", true); commandBuilder.AddParameter(ReferenceData.IDS, referenceIds); return(GetReader(commandBuilder)); }
/// <summary> /// Get entries from the LogUpdateError log. /// </summary> /// <param name="rowCount">Maximum number of log entries to get.</param> /// <returns> /// Returns an open DataReader. Remember to close the /// DataReader after reading has been finished. /// </returns> public DataReader GetLogUpdateError(Int32 rowCount) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetLogUpdateError"); commandBuilder.AddParameter(WebServiceLogData.ROW_COUNT, rowCount); return(GetReader(commandBuilder)); }
/// <summary> /// Write the data provider's change id to database. /// </summary> /// <param name="dataProviderId"> /// The data Provider Id. /// </param> /// <param name="changeId"> /// The data provider's Change Id. /// </param> /// <returns> /// The status of the query. /// </returns> public Int32 SetMaxChangeId(Int32 dataProviderId, Int64?changeId) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderMaxChangeId", false); commandBuilder.AddParameter("dataProviderId", dataProviderId); if (changeId.HasValue) { commandBuilder.AddParameter("maxChangeId", changeId.Value); } else { commandBuilder.AddParameter("maxChangeId", (Int64?)null); } return(ExecuteCommand(commandBuilder)); }
public DataReader GetWeekOfYear(DateTime date) { SqlCommandBuilder commandBuilder; commandBuilder = new SqlCommandBuilder("GetWeekOfYear"); commandBuilder.AddParameter("Date", date); return(GetReader(commandBuilder)); }
/// <summary> /// Log the latest date when information about retrieved observations have been changed at provider. /// </summary> /// <param name="dataProviderId">Data provider.</param> /// <returns>If database operation was successful.</returns> public Int32 SetDataProviderLatestChangedDate(Int32 dataProviderId) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderLatestChangedDate", false); commandBuilder.AddParameter("dataProviderId", dataProviderId); return(ExecuteCommand(commandBuilder)); }
/// <summary> /// Returns a list of id's with all differences between the tables DarwinCoreObservation and AllSourceSpeciesObservationIds that has a suggestedAction of INSERT /// </summary> /// <param name="dataProviderId"></param> /// <returns></returns> public List <string> GetAllSourceSpeciesObservationIdsForInsert(int dataProviderId) { var list = new List <string>(); var commandBuilder = new SqlCommandBuilder("GetAllSourceSpeciesObservationIds"); commandBuilder.AddParameter("dataProviderId", dataProviderId); commandBuilder.AddParameter("suggestedAction", "INSERT"); using (var reader = GetReader(commandBuilder)) { while (reader.Read()) { list.Add(reader.GetString(0)); } } return(list); }