public string GenerateExistsQuery(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var keyColumnName = GetKeyColumnName();
if (!string.IsNullOrEmpty(keyColumnName))
{
var whereClause = GeneratePrimaryKeyWhereClause(driverType);
if (!string.IsNullOrEmpty(whereClause))
{
result = AnDatabaseTypes.GenerateSelectQuery(driverType, GetDbTableName(), AnDatabaseTypes.FormatDatabaseColumnName(keyColumnName, GetDbTableName(), AnDatabaseTypes.ExecutionType.Query, driverType), whereClause);
}
}
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public static string GenerateSelectQuery(string customerId, string universalNodeId, string dataSourceType, DateTime beginDateTime, DateTime endDateTime, bool processed, AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var whereClause = new StringBuilder();
whereClause.Append($"{nameof(RawDataRecord.Processed)} = {AnDatabaseTypes.FormatBooleanValue(processed, driverType)}");
if (!string.IsNullOrEmpty(customerId))
{
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.CustomerId)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(customerId, RawDataRecord.CustomerIdSize))}'");
}
if (!string.IsNullOrEmpty(universalNodeId))
{
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.UniversalNodeId)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(universalNodeId, RawDataRecord.UniversalNodeIdSize))}'");
}
if (!string.IsNullOrEmpty(dataSourceType))
{
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.DataSourceType)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(dataSourceType, RawDataRecord.DataSourceTypeSize))}'");
}
if ((DateTime.Compare(beginDateTime, AnTypes.MinDateTimeValue) > 0) &&
(DateTime.Compare(endDateTime, beginDateTime) > 0))
{
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.EnteredDateTime)} > '{AnTypes.SqlDateTimeString(beginDateTime)}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.EnteredDateTime)} <= '{AnTypes.SqlDateTimeString(endDateTime)}'");
}
whereClause.Append(" ORDER BY ");
whereClause.Append(AnDatabaseTypes.FormatDatabaseColumnName(nameof(RawDataRecord.Id), RawDataRecord.DbTableName, AnDatabaseTypes.ExecutionType.Query, driverType));
result = AnDatabaseTypes.GenerateSelectQuery(driverType, RawDataRecord.DbTableName, AnDatabaseTypes.FormatColumnNames(RawDataRecord.DbTableName, new RawDataRecord().GetColumnNames(true), driverType), whereClause.ToString());
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public static string GenerateSelectQuery(string customerId, string sourceSystemCompanyId, string universalNodeId, string dataSourceType, string organization, string tableName, string action, AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var whereClause = new StringBuilder();
if (!string.IsNullOrEmpty(customerId) &&
!string.IsNullOrEmpty(sourceSystemCompanyId) &&
!string.IsNullOrEmpty(universalNodeId) &&
!string.IsNullOrEmpty(dataSourceType) &&
!string.IsNullOrEmpty(tableName) &&
!string.IsNullOrEmpty(action))
{
whereClause.Append($"{nameof(RawDataRecord.CustomerId)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(customerId, RawDataRecord.CustomerIdSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.SourceSystemCompanyId)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(sourceSystemCompanyId, RawDataRecord.SourceSystemCompanyIdSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.UniversalNodeId)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(universalNodeId, RawDataRecord.UniversalNodeIdSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.DataSourceType)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(dataSourceType, RawDataRecord.DataSourceTypeSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.Organization)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(organization ?? "", RawDataRecord.OrganizationSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.TableName)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(tableName, RawDataRecord.TableNameSize))}'");
whereClause.Append(" AND ");
whereClause.Append($"{nameof(RawDataRecord.Action)} = '{AnDatabaseTypes.FormatSingleQuote(AnDatabaseTypes.ValidStringLength(action, RawDataRecord.ActionSize))}'");
result = AnDatabaseTypes.GenerateSelectQuery(driverType, RawDataRecord.DbTableName, AnDatabaseTypes.FormatColumnNames(RawDataRecord.DbTableName, new RawDataRecord().GetColumnNames(true), driverType), whereClause.ToString());
}
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public static string GenerateSelectQueryForNotProcessedRecords(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var whereClause = new StringBuilder();
whereClause.Append($"{nameof(RawDataRecord.Processed)} = {AnDatabaseTypes.FormatBooleanValue(false, driverType)} ");
whereClause.Append("ORDER BY ");
whereClause.Append(AnDatabaseTypes.FormatDatabaseColumnName(nameof(RawDataRecord.Id), RawDataRecord.DbTableName, AnDatabaseTypes.ExecutionType.Query, driverType));
result = AnDatabaseTypes.GenerateSelectQuery(driverType, RawDataRecord.DbTableName, AnDatabaseTypes.FormatColumnNames(RawDataRecord.DbTableName, new RawDataRecord().GetColumnNames(true), driverType), whereClause.ToString());
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public string GenerateSelectQuery(AnDatabaseTypes.DriverType driverType)
{
return(AnDatabaseTypes.GenerateSelectQuery(driverType, GetDbTableName(), AnDatabaseTypes.FormatColumnNames(GetDbTableName(), GetColumnNames(true), driverType), GeneratePrimaryKeyWhereClause(driverType)));
}