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 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 virtual string GeneratePrimaryKeyWhereClause(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var keyColumnName = GetKeyColumnName();
if (!string.IsNullOrEmpty(keyColumnName))
{
var columnData = GenerateDatabaseCommandDataList(true);
if (columnData?.Count > 0)
{
foreach (var column in columnData.Where(column => !string.IsNullOrEmpty(column?.Name) && string.Equals(column.Name, keyColumnName, StringComparison.CurrentCultureIgnoreCase)))
{
string columnValue = null;
switch (column.DataType)
{
case AnTypes.DataType.Character:
columnValue = AnSafeConvert.ToChar(column.Value).ToString();
break;
case AnTypes.DataType.Byte:
columnValue = AnSafeConvert.ToByte(column.Value).ToString();
break;
case AnTypes.DataType.Short:
columnValue = AnSafeConvert.ToInt16(column.Value).ToString();
break;
case AnTypes.DataType.UShort:
columnValue = AnSafeConvert.ToUInt16(column.Value).ToString();
break;
case AnTypes.DataType.Int:
columnValue = AnSafeConvert.ToInt32(column.Value).ToString();
break;
case AnTypes.DataType.UInt:
columnValue = AnSafeConvert.ToUInt32(column.Value).ToString();
break;
case AnTypes.DataType.Long:
columnValue = AnSafeConvert.ToInt64(column.Value).ToString();
break;
case AnTypes.DataType.ULong:
columnValue = AnSafeConvert.ToUInt64(column.Value).ToString();
break;
case AnTypes.DataType.Single:
columnValue = AnSafeConvert.ToSingle(column.Value).ToString(CultureInfo.InvariantCulture);
break;
case AnTypes.DataType.Double:
columnValue = AnSafeConvert.ToDouble(column.Value).ToString(CultureInfo.InvariantCulture);
break;
case AnTypes.DataType.Decimal:
case AnTypes.DataType.Currency:
columnValue = AnSafeConvert.ToDecimal(column.Value).ToString(CultureInfo.InvariantCulture);
break;
case AnTypes.DataType.Boolean:
columnValue = AnSafeConvert.ToBoolean(column.Value).ToString(CultureInfo.InvariantCulture);
break;
case AnTypes.DataType.DateTime:
case AnTypes.DataType.FileTime:
columnValue = AnSafeConvert.ToDateTime(column.Value).ToString(CultureInfo.InvariantCulture);
break;
case AnTypes.DataType.Guid:
columnValue = AnSafeConvert.ToGuid(column.Value).ToString();
break;
case AnTypes.DataType.Char:
case AnTypes.DataType.VarChar:
case AnTypes.DataType.Text:
case AnTypes.DataType.NChar:
case AnTypes.DataType.NVarChar:
case AnTypes.DataType.NText:
columnValue = $"'{AnSafeConvert.ToString(column.Value)}'";
break;
// These types cannot be primary keys.
//case AnTypes.DataType.Image:
//case AnTypes.DataType.Binary:
//case AnTypes.DataType.VarBinary:
//case AnTypes.DataType.Null:
//case AnTypes.DataType.Table:
//default:
//break;
}
if (columnValue != null)
{
result = $"{AnDatabaseTypes.FormatDatabaseColumnName(keyColumnName, GetDbTableName(), AnDatabaseTypes.ExecutionType.Query, driverType)} = {columnValue}";
}
break;
}
}
}
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
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);
}
