Пример #1
0
        public async Task ProcessAsync(CancellationToken cancellationToken)
        {
            bool tableExists = DatabaseCommander.ExecuteScalar <int>($"SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{Settings.SchemaName}' AND TABLE_NAME = '{Settings.TableName}'") > 0;

            if (tableExists)
            {
                throw new Exception($"Table {Settings.TableNameQualified} already exists");
            }

            DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(Settings.FilePath);

            PrependPrimaryKeyIfNeeded(dataTable);

            AppendAuditFieldsIfNeeded(dataTable);

            SetColumnsToCreate(dataTable);

            CreateTable();

            await DatabaseCommander.BuildCommand()
            .ForBulkCopy()
            .Into(Settings.TableNameQualified)
            .From(dataTable)
            .Mapping(mapping => mapping.UseAutoMap())
            .ExecuteAsync(cancellationToken);
        }
Пример #2
0
        public override void Run()
        {
            base.Run();
            var connection = new NpgsqlConnection(_ConnectionString);

            _Database = new DatabaseCommander(connection);
        }
Пример #3
0
 void Disconnect()
 {
     if (_Database != null)
     {
         _Database.Dispose();
         _Database = null;
     }
 }
Пример #4
0
 public override void Dispose()
 {
     if (_Database != null)
     {
         _Database.Dispose();
         _Database = null;
     }
     base.Dispose();
 }
Пример #5
0
 public void Run()
 {
     while (true)
     {
         try
         {
             var connection = new NpgsqlConnection(_ConnectionString);
             _Database = new DatabaseCommander(connection);
             var files = Directory.GetFiles(_LogPath);
             foreach (var file in files)
             {
                 ProcessLog(file);
             }
         }
         catch (Exception exception)
         {
             Console.WriteLine("Failed to update database: {0} ({1})", exception.Message, exception.GetType());
         }
         Disconnect();
         Thread.Sleep(UpdateInterval);
     }
 }
        public async Task ProcessAsync(CancellationToken cancellationToken)
        {
            //await DatabaseCommander.ExecuteNonQueryAsync($"DELETE FROM {Settings.TableNameQualified}", cancellationToken);

            DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(Settings.FilePath, '~');

            await DatabaseCommander.BuildCommand()
            .ForBulkCopy()
            .Into(Settings.TableNameQualified)
            .From(dataTable)
            .Mapping(mapping => mapping.UseAutoMap())
            .ExecuteAsync(cancellationToken);

            StoredProcedureResult result = await DatabaseCommander.BuildCommand()
                                           .ForStoredProcedure("[dbo].[LoadData]")
                                           .AddInputParameter("DataFile", Settings.FilePath)
                                           .ExecuteAsync(cancellationToken);

            if (result.GetReturnParameter <string>() == "-1")
            {
                throw new Exception("[dbo].[LoadData] encountered error");
            }
        }
        public ColumnMapping MapDataTableToTable(string tableName, DataTable dataTable)
        {
            DataTable emptyDataTable = DatabaseCommander.ExecuteSql($"SELECT * FROM {tableName} WHERE 1 = 0");

            List <string> databaseColumnNames = emptyDataTable.Columns.Cast <DataColumn>().Select(dc => dc.ColumnName).ToList();
            List <string> fileColumnNames     = dataTable.Columns.Cast <DataColumn>().Select(dc => dc.ColumnName).ToList();

            var columnMapping = new ColumnMapping();

            // Ensure proper casing of source columns
            foreach (string fileColumnName in fileColumnNames)
            {
                ColumnMap columnMap = columnMapping.ColumnMaps.FirstOrDefault(cm => cm.Source.Equals(fileColumnName, StringComparison.InvariantCultureIgnoreCase));

                if (columnMap != null && columnMap.Source != fileColumnName)
                {
                    columnMap.Source = fileColumnName;
                }
            }

            // Ensure proper casing of destination columns
            foreach (string databaseColumnName in databaseColumnNames)
            {
                ColumnMap columnMap = columnMapping.ColumnMaps.FirstOrDefault(cm => cm.Destination.Equals(databaseColumnName, StringComparison.InvariantCultureIgnoreCase));

                if (columnMap != null && columnMap.Destination != databaseColumnName)
                {
                    columnMap.Destination = databaseColumnName;
                }
            }

            // Ensure existence of source columns
            foreach (string sourceColumnName in columnMapping.ColumnMaps.Select(cm => cm.Source))
            {
                if (!fileColumnNames.Contains(sourceColumnName))
                {
                    throw new InvalidOperationException($"The source column '{sourceColumnName}' does not exist");
                }
            }

            // Ensure existence of destination columns
            foreach (string destinationColumnName in columnMapping.ColumnMaps.Select(cm => cm.Destination))
            {
                if (!databaseColumnNames.Contains(destinationColumnName))
                {
                    throw new InvalidOperationException($"The destination column '{destinationColumnName}' does not exist");
                }
            }

            // AutoMap any columns that are not already mapped from the source to the destination
            foreach (string fileColumnName in fileColumnNames)
            {
                string databaseColumnName = databaseColumnNames.FirstOrDefault(s => s.Equals(fileColumnName, StringComparison.InvariantCultureIgnoreCase));

                if (databaseColumnName != null && columnMapping.ColumnMaps.All(cm => cm.Source != fileColumnName) && columnMapping.ColumnMaps.All(cm => cm.Destination != databaseColumnName))
                {
                    columnMapping.ColumnMaps.Add(new ColumnMap(fileColumnName, databaseColumnName));
                }
            }

            return(columnMapping);
        }