/// <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));
}