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 string GetMarkAsProcessedCommand(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
if (ProcessedDateTime <= AnTypes.MinDateTimeValue)
{
ProcessedDateTime = DateTime.UtcNow;
}
result = AnDatabaseTypes.GenerateUpdateStatement(DbTableName, GeneratePrimaryKeyWhereClause(driverType),
new List <AnDatabaseCommandData>
{
new AnDatabaseCommandData(nameof(ProcessedDateTime), ProcessedDateTime, AnTypes.DataType.DateTime, AnSerialization.DateTimeSize),
new AnDatabaseCommandData(nameof(ErrorMessage), ErrorMessage, AnTypes.DataType.VarChar, ErrorMessageSize),
new AnDatabaseCommandData(nameof(Result), Result, AnTypes.DataType.Boolean, AnSerialization.BooleanSize),
new AnDatabaseCommandData(nameof(Processed), true, AnTypes.DataType.Boolean, AnSerialization.BooleanSize)
},
new List <AnDatabaseCommandData>(), driverType, false);
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public virtual string GenerateInsertStatement(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
result = AnDatabaseTypes.GenerateInsertStatement(GetDbTableName(), GenerateDatabaseCommandDataList(false), driverType, false);
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public string GenerateDeleteStatement(AnDatabaseTypes.DriverType driverType)
{
var result = "";
try
{
var whereClause = GeneratePrimaryKeyWhereClause(driverType);
if (!string.IsNullOrEmpty(whereClause))
{
result = $"DELETE FROM {GetDbTableName()} WHERE {whereClause}";
}
}
catch (Exception ex)
{
AnLog.Error(ex);
}
return(result);
}
public string GenerateUpdateStatement <T>(AnDatabaseTypes.DriverType driverType, T modelObject)
{
var result = "";
try
{
var whereClause = GeneratePrimaryKeyWhereClause(driverType);
if (!string.IsNullOrEmpty(whereClause))
{
result = AnDatabaseTypes.GenerateUpdateStatement(GetDbTableName(), whereClause, GenerateDatabaseCommandDataList(false), (modelObject as AnModelBase)?.GenerateDatabaseCommandDataList(false) ?? new AnList <AnDatabaseCommandData>(), driverType, false);
}
}
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 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 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 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 GenerateSelectQuery(AnDatabaseTypes.DriverType driverType)
{
return(AnDatabaseTypes.GenerateSelectQuery(driverType, GetDbTableName(), AnDatabaseTypes.FormatColumnNames(GetDbTableName(), GetColumnNames(true), driverType), GeneratePrimaryKeyWhereClause(driverType)));
}
