Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }
Example #4
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
        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);
        }
Example #7
0
        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);
        }
Example #8
0
        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);
        }
Example #9
0
 public string GenerateSelectQuery(AnDatabaseTypes.DriverType driverType)
 {
     return(AnDatabaseTypes.GenerateSelectQuery(driverType, GetDbTableName(), AnDatabaseTypes.FormatColumnNames(GetDbTableName(), GetColumnNames(true), driverType), GeneratePrimaryKeyWhereClause(driverType)));
 }