/// <summary>
        /// Generates T-SQL for data to be upserted using Merge.
        /// This needs to be regenerated for every batch to upsert.
        /// </summary>
        /// <param name="table">Information about the table we will be upserting into</param>
        /// <param name="rows">Rows to be upserted</param>
        /// <returns>T-SQL containing data for merge</returns>
        private static void GenerateDataQueryForMerge(TableInformation table, IEnumerable <T> rows, out string newDataQuery, out string rowData)
        {
            IList <T> rowsToUpsert = new List <T>();

            // Here, we assume that primary keys are case INsensitive, which is the SQL Server default.
            HashSet <string> uniqueUpdatedPrimaryKeys = new HashSet <string>(StringComparer.OrdinalIgnoreCase);

            // If there are duplicate primary keys, we'll need to pick the LAST (most recent) row per primary key.
            foreach (T row in rows.Reverse())
            {
                // SQL Server allows 900 bytes per primary key, so use that as a baseline
                StringBuilder combinedPrimaryKey = new StringBuilder(900 * table.PrimaryKeys.Count());

                // Look up primary key of T. Because we're going in the same order of fields every time,
                // we can assume that if two rows with the same primary key are in the list, they will collide
                foreach (PropertyInfo primaryKey in table.PrimaryKeys)
                {
                    combinedPrimaryKey.Append(primaryKey.GetValue(row).ToString());
                }

                // If we have already seen this unique primary key, skip this update
                if (uniqueUpdatedPrimaryKeys.Add(combinedPrimaryKey.ToString()))
                {
                    // This is the first time we've seen this particular PK. Add this row to the upsert query.
                    rowsToUpsert.Add(row);
                }
            }

            rowData      = JsonConvert.SerializeObject(rowsToUpsert, table.JsonSerializerSettings);
            newDataQuery = $"WITH {NewDataParameter} AS ( SELECT * FROM OPENJSON({RowDataParameter}) WITH ({string.Join(",", table.ColumnDefinitions)}) )";
        }
Exemplo n.º 2
0
        private IEtlServiceTable CreateTableFromResponse(TableInformation resp)
        {
            var tableId = resp.TableId;
            var columnNamesInResponse = resp.ColumnNames;

            return(new EtlServiceTable(_client, tableId, columnNamesInResponse));
        }
        public static ReverseEngineerOptions FromV1(ReverseEngineerOptionsV1 v1)
        {
            if (v1 == null)
            {
                throw new ArgumentNullException(nameof(v1));
            }

            return(new ReverseEngineerOptions
            {
                DatabaseType = v1.DatabaseType,
                ConnectionString = v1.ConnectionString,
                ProjectPath = v1.ProjectPath,
                OutputPath = v1.OutputPath,
                ProjectRootNamespace = v1.ProjectRootNamespace,
                UseFluentApiOnly = v1.UseFluentApiOnly,
                ContextClassName = v1.ContextClassName,
                Tables = v1.Tables
                         .Select(m =>
                {
                    // Try to parse the strings
                    TableInformation.TryParse(m, out var ti);
                    return ti;
                })
                         .Where(m => m != null)   // Only select the table information that could be parsed
                         .ToList(),
                UseDatabaseNames = v1.UseDatabaseNames,
                UseInflector = v1.UseInflector,
                IdReplace = v1.IdReplace,
                UseHandleBars = v1.UseHandleBars,
                IncludeConnectionString = v1.IncludeConnectionString,
                SelectedToBeGenerated = v1.SelectedToBeGenerated,
                Dacpac = v1.Dacpac,
                CustomReplacers = v1.CustomReplacers,
                DefaultDacpacSchema = v1.DefaultDacpacSchema
            });
Exemplo n.º 4
0
        private TableInformation GetTableBasics(string tableName, IRequestContext ctx)
        {
            var table = this.Database[tableName];

            TableInformation ti = new TableInformation();

            ti.Name             = tableName;
            ti.PartitionCount   = table.PartitionCount;
            ti.RowCount         = table.Count;
            ti.LastWriteTimeUtc = table.LastWriteTimeUtc;

            IList <string> restrictedColumns = this.Database.GetRestrictedColumns(tableName, (si) => this.IsInIdentity(ctx.Request.User, si));

            if (restrictedColumns == null)
            {
                ti.Columns = table.ColumnDetails;
            }
            else
            {
                List <ColumnDetails> allowedColumns = new List <ColumnDetails>();
                foreach (ColumnDetails column in table.ColumnDetails)
                {
                    if (!restrictedColumns.Contains(column.Name))
                    {
                        allowedColumns.Add(column);
                    }
                }
                ti.Columns = allowedColumns;
            }

            return(ti);
        }
Exemplo n.º 5
0
        public TableInformation[] GetTables()
        {
            if (this.metadata == null)
            {
                GetMetadata(this.odataMetadataURL);
            }

            if (this.tables == null || this.tables.Length == 0)
            {
                IEdmEntityContainer defaultContainer = GetDefaultEntityContainer(this.metadata);

                IEdmEntitySet[] entitySets = defaultContainer.EntitySets().ToArray();

                List <TableInformation> tables = new List <TableInformation>();

                foreach (IEdmEntitySet entitySet in entitySets)
                {
                    TableInformation table = new TableInformation();
                    table.entityTypeName = entitySet.ElementType.FullName();
                    table.tableName      = entitySet.Name;
                    table.headers        = GetHeaders(table.tableName);
                    table.key            = GetKey(table.tableName);
                    table.types          = GetTypes(table.tableName);

                    tables.Add(table);
                }

                this.tables = tables.ToArray();
            }

            return(this.tables);
        }
Exemplo n.º 6
0
        public void TableAndSchmeaNamesSpecified_SchemaIsSet()
        {
            var target = new TableInformation($"{TestSchemaName}.{TestTableName}");

            Assert.Equal(TestTableName, target.TableName);
            Assert.Equal(TestSchemaName, target.SchemaName);
            Assert.True(target.HasSchemaName);
        }
Exemplo n.º 7
0
        public void OnlyTableNameSpecified_SchemaIsNull()
        {
            var target = new TableInformation(TestTableName);

            Assert.Equal(TestTableName, target.TableName);
            Assert.Null(target.SchemaName);
            Assert.False(target.HasSchemaName);
        }
        public void Parse_ArgumentException_Empty()
        {
            // Arrange
            string table = null;

            // Act & Assert
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table));
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table, true));
        }
Exemplo n.º 9
0
        public SqlCommandHelper(ProviderConfiguration p_configuration, TableInformation p_tableInformation)
        {
            if (p_configuration == null || (p_tableInformation.Fields == null || p_tableInformation.Fields.Length == 0))
            {
                throw new ArgumentNullException("ProviderConfiguration o TableInformation nulli.");
            }

            this.m_configuration    = p_configuration;
            this.m_tableInformation = p_tableInformation;
        }
Exemplo n.º 10
0
        private Task SaveAddresses(DataTable table)
        {
            TableInformation info = new TableInformation
            {
                ColumnMappings = DefaultMappings,
                Name           = "Addresses"
            };

            return(BulkCopyTable(info, table));
        }
Exemplo n.º 11
0
        public CreateTask(Configuration configuration, IEnumerable <string> settings)
            : base(configuration, settings)
        {
            if (string.IsNullOrWhiteSpace(Configuration.TableName))
            {
                throw new RequestValidationException("{table-name} not specified in request");
            }

            _tableInformation = new TableInformation(Configuration.TableName);
            Overwrite         = SettingExists("Overwrite");
        }
Exemplo n.º 12
0
        private Task SaveOrganisations(Mainfile data)
        {
            TableInformation info = new TableInformation
            {
                ColumnMappings   = DefaultMappings,
                ColumnsToConvert = new[] { 1, 2 },
                Name             = "Organisations"
            };

            return(this.BulkCopy(data.CreateReader(MainfileType.Organisations), info));
        }
Exemplo n.º 13
0
        private Task SaveThoroughfares(Mainfile data)
        {
            TableInformation info = new TableInformation
            {
                ColumnMappings   = DefaultMappings,
                ColumnsToConvert = new[] { 1 },
                Name             = "Thoroughfares"
            };

            return(this.BulkCopy(data.CreateReader(MainfileType.Thoroughfares), info));
        }
Exemplo n.º 14
0
        private static async Task BulkCopyTable(TableInformation info, DataTable table)
        {
            using (var connection = new SqlConnection(ConnectionString))
                using (SqlBulkCopy bulk = CreateBulkCopy(connection))
                {
                    SetupBulkCopy(info, bulk);

                    await connection.OpenAsync();

                    await bulk.WriteToServerAsync(table);
                }
        }
Exemplo n.º 15
0
        public void TryParse_Empty()
        {
            // Arrange
            string table = null;

            // Act
            var parsed = TableInformation.TryParse(table, out var tableInformation);

            // Assert
            Assert.IsFalse(parsed);
            Assert.IsNull(tableInformation);
        }
Exemplo n.º 16
0
        public void Parse_ArgumentException_NotTwoPeriods()
        {
            // Arrange
            var table1 = "Album";
            var table2 = "[config.legacy].Album";

            // Act & Assert
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table1));
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table2));
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table1, true));
            Assert.Throws <ArgumentException>(() => TableInformation.Parse(table2, true));
        }
Exemplo n.º 17
0
        private IResponse GetTableInformation(IRequestContext ctx, Route route)
        {
            var tableName = GetAndValidateTableName(route);

            if (!this.Database.TableExists(tableName))
            {
                return(ArribaResponse.NotFound());
            }

            TableInformation ti = GetTableBasics(tableName, ctx);

            return(ArribaResponse.Ok(ti));
        }
Exemplo n.º 18
0
        private static void SetupBulkCopy(TableInformation info, SqlBulkCopy bulk)
        {
            bulk.BatchSize            = 16384;
            bulk.DestinationTableName = info.Name;

            int length = info.ColumnMappings.GetLength(0);

            for (int i = 0; i < length; i++)
            {
                bulk.ColumnMappings.Add(
                    info.ColumnMappings[i, 0],
                    info.ColumnMappings[i, 1]);
            }
        }
Exemplo n.º 19
0
        public void Parse_OnlyTable_CorrectCreation()
        {
            // Arrange
            var table = "dbo.Album";

            // Act
            var ti = TableInformation.Parse(table);

            // Assert
            Assert.AreEqual("dbo", ti.Schema);
            Assert.AreEqual("Album", ti.Name);
            Assert.IsTrue(ti.HasPrimaryKey);
            Assert.AreEqual("dbo.Album", ti.UnsafeFullName);
            Assert.AreEqual("[dbo].[Album]", ti.SafeFullName);
        }
Exemplo n.º 20
0
        public void TryParse_NotTwoPeriods()
        {
            // Arrange
            var table1 = "Album";
            var table2 = "[config.legacy].Album";

            // Act
            var parsed1 = TableInformation.TryParse(table1, out var tableInformation1);
            var parsed2 = TableInformation.TryParse(table2, out var tableInformation2);

            // Assert
            Assert.IsFalse(parsed1);
            Assert.IsFalse(parsed2);
            Assert.IsNull(tableInformation1);
            Assert.IsNull(tableInformation2);
        }
Exemplo n.º 21
0
        public void Constructor_CorrectCreation()
        {
            // Arrange
            var schema        = "dbo";
            var table         = "Album";
            var hasPrimaryKey = true;

            // Act
            var ti = new TableInformation(schema, table, hasPrimaryKey);

            // Assert
            Assert.AreEqual("dbo", ti.Schema);
            Assert.AreEqual("Album", ti.Name);
            Assert.IsTrue(ti.HasPrimaryKey);
            Assert.AreEqual("dbo.Album", ti.UnsafeFullName);
            Assert.AreEqual("[dbo].[Album]", ti.SafeFullName);
        }
Exemplo n.º 22
0
        public (string sql, object parameters) CreateInsertSqlStatement(ConnectionType connectionType)
        {
            var insertBegin = $"INSERT INTO {TableInformation.TableName(connectionType)}";
            var columnNamesStringBuilder           = new StringBuilder();
            var columnValueParametersStringBuilder = new StringBuilder();
            var columnValues = new ExpandoObject() as IDictionary <string, Object>;

            foreach (var column in Columns.Where(column => !column.IsKey))
            {
                columnNamesStringBuilder.Append(column.Name).Append(", ");
                columnValueParametersStringBuilder.Append("@").Append(column.Name).Append(", ");
                columnValues.Add(column.Name, _type.GetProperty(column.Name).GetValue(this, null));
            }

            return(
                sql : $"{insertBegin} ({columnNamesStringBuilder.ToString(0, columnNamesStringBuilder.Length - 2)}) VALUES ({columnValueParametersStringBuilder.ToString(0, columnValueParametersStringBuilder.Length - 2)})",
                parameters : columnValues
                );
        }
Exemplo n.º 23
0
        public static void Generate(TableInformation tableInformation, string filePath)
        {
            var builder = new StringBuilder();

            foreach (var column in tableInformation.Columns)
            {
                builder.Append(column.Name.ToLower());
                builder.Append(",");
            }

            if (builder[builder.Length - 1] == ',')
            {
                builder.Length--;
            }

            builder.Append(Environment.NewLine);

            foreach (DataRow row in tableInformation.Table.Rows)
            {
                IFormatter formatter = new CsvFormatter();

                for (var i = 0; i < row.ItemArray.Length; i++)
                {
                    var column = tableInformation.Columns[i];
                    if (i + 1 != column.OdinalPosition)
                    {
                        throw new ArgumentException(nameof(i));
                    }

                    builder.Append($"{formatter.GetString(row[i], column)},");
                }

                if (builder[builder.Length - 1] == ',')
                {
                    builder.Length--;
                }

                builder.Append(Environment.NewLine);
            }

            File.WriteAllText(filePath, builder.ToString());
        }
Exemplo n.º 24
0
        /// <summary>
        /// Update all columns (except identity, computed, ignore)
        /// </summary>
        public UpdateSet <TableType> Set(TableType entity)
        {
            var tableInformation = new TableInformation <TableType>();
            var columns          = tableInformation.GetColumnNames(excludeIdentityColumns: true, excludeComputedFields: true, excludeIgnoredFields: true);
            var values           = tableInformation.GetColumnValues(entity, excludeIdentityColumns: true, excludeComputedFields: true, excludeIgnoredFields: true);

            for (var i = 0; i < columns.Count; i++)
            {
                var column = columns[i];
                var value  = values[i];

                manager.AddSetExpression(new SetExpression <TableType>
                {
                    ColumnName = column,
                    Value      = value
                });
            }

            return(new UpdateSet <TableType>(manager));
        }
Exemplo n.º 25
0
        private async Task BulkCopy(IDataReader reader, TableInformation info)
        {
            try
            {
                DataTable table = await Task.Run(() => this.CreateTable(reader, info.ColumnsToConvert));

                Console.Write('.');

                await BulkCopyTable(info, table);

                Console.Write('.');
            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                }
            }
        }
        /// <summary>
        /// Upserts the rows specified in "rows" to the table specified in "attribute"
        /// If a primary key in "rows" already exists in the table, the row is interpreted as an update rather than an insert.
        /// The column values associated with that primary key in the table are updated to have the values specified in "rows".
        /// If a new primary key is encountered in "rows", the row is simply inserted into the table.
        /// </summary>
        /// <param name="rows"> The rows to be upserted </param>
        /// <param name="attribute"> Contains the name of the table to be modified and SQL connection information </param>
        /// <param name="configuration"> Used to build up the connection </param>
        private async Task UpsertRowsAsync(IEnumerable <T> rows, SqlAttribute attribute, IConfiguration configuration)
        {
            using (SqlConnection connection = SqlBindingUtilities.BuildConnection(attribute.ConnectionStringSetting, configuration))
            {
                string fullDatabaseAndTableName = attribute.CommandText;

                // Include the connection string hash as part of the key in case this customer has the same table in two different Sql Servers
                string cacheKey = $"{connection.ConnectionString.GetHashCode()}-{fullDatabaseAndTableName}";

                ObjectCache      cachedTables = MemoryCache.Default;
                TableInformation tableInfo    = cachedTables[cacheKey] as TableInformation;

                if (tableInfo == null)
                {
                    tableInfo = await TableInformation.RetrieveTableInformationAsync(connection, fullDatabaseAndTableName);

                    CacheItemPolicy policy = new CacheItemPolicy
                    {
                        // Re-look up the primary key(s) after 10 minutes (they should not change very often!)
                        AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(10)
                    };

                    _logger.LogInformation($"DB and Table: {fullDatabaseAndTableName}. Primary keys: [{string.Join(",", tableInfo.PrimaryKeys.Select(pk => pk.Name))}]. SQL Column and Definitions:  [{string.Join(",", tableInfo.ColumnDefinitions)}]");
                    cachedTables.Set(cacheKey, tableInfo, policy);
                }

                int batchSize = 1000;
                await connection.OpenAsync();

                foreach (IEnumerable <T> batch in rows.Batch(batchSize))
                {
                    GenerateDataQueryForMerge(tableInfo, batch, out string newDataQuery, out string rowData);
                    var cmd = new SqlCommand($"{newDataQuery} {tableInfo.MergeQuery};", connection);
                    var par = cmd.Parameters.Add(RowDataParameter, SqlDbType.NVarChar, -1);
                    par.Value = rowData;

                    await cmd.ExecuteNonQueryAsync();
                }
                await connection.CloseAsync();
            }
        }
Exemplo n.º 27
0
        public void Parse_TableWithHasPrimaryKey_CorrectCreation()
        {
            // Arrange
            var table = "dbo.Album";

            // Act
            var ti1 = TableInformation.Parse(table, false);
            var ti2 = TableInformation.Parse(table, true);

            // Assert
            Assert.AreEqual("dbo", ti1.Schema);
            Assert.AreEqual("Album", ti1.Name);
            Assert.IsFalse(ti1.HasPrimaryKey);
            Assert.AreEqual("dbo.Album", ti1.UnsafeFullName);
            Assert.AreEqual("[dbo].[Album]", ti1.SafeFullName);
            Assert.AreEqual("dbo", ti2.Schema);
            Assert.AreEqual("Album", ti2.Name);
            Assert.IsTrue(ti2.HasPrimaryKey);
            Assert.AreEqual("dbo.Album", ti2.UnsafeFullName);
            Assert.AreEqual("[dbo].[Album]", ti2.SafeFullName);
        }
Exemplo n.º 28
0
        private List <TableInformation> GetTablesFromRepository(DatabaseInfo dbInfo)
        {
            if (dbInfo.DatabaseType == DatabaseType.Npgsql)
            {
                return(EnvDteHelper.GetNpgsqlTableNames(dbInfo.ConnectionString));
            }

            using (var repository = RepositoryHelper.CreateRepository(dbInfo))
            {
                var allPks    = repository.GetAllPrimaryKeys();
                var tableList = repository.GetAllTableNamesForExclusion();
                var tables    = new List <TableInformation>();

                foreach (var table in tableList)
                {
                    var hasPrimaryKey = allPks.Any(m => m.TableName == table);
                    tables.Add(TableInformation.Parse(table, hasPrimaryKey));
                }
                return(tables);
            }
        }
        /// <summary>
        /// Generates T-SQL for data to be upserted using Merge.
        /// This needs to be regenerated for every batch to upsert.
        /// </summary>
        /// <param name="table">Information about the table we will be upserting into</param>
        /// <param name="rows">Rows to be upserted</param>
        /// <returns>T-SQL containing data for merge</returns>
        private static void GenerateDataQueryForMerge(TableInformation table, IEnumerable <T> rows, out string newDataQuery, out string rowData)
        {
            IList <T> rowsToUpsert = new List <T>();

            var uniqueUpdatedPrimaryKeys = new HashSet <string>(table.Comparer);

            // If there are duplicate primary keys, we'll need to pick the LAST (most recent) row per primary key.
            foreach (T row in rows.Reverse())
            {
                // SQL Server allows 900 bytes per primary key, so use that as a baseline
                var combinedPrimaryKey = new StringBuilder(900 * table.PrimaryKeys.Count());

                // Look up primary key of T. Because we're going in the same order of fields every time,
                // we can assume that if two rows with the same primary key are in the list, they will collide
                foreach (PropertyInfo primaryKey in table.PrimaryKeys)
                {
                    object value = primaryKey.GetValue(row);
                    // Identity columns are allowed to be optional, so just skip the key if it doesn't exist
                    if (value == null)
                    {
                        continue;
                    }
                    combinedPrimaryKey.Append(value.ToString());
                }

                // If we have already seen this unique primary key, skip this update
                if (uniqueUpdatedPrimaryKeys.Add(combinedPrimaryKey.ToString()))
                {
                    // This is the first time we've seen this particular PK. Add this row to the upsert query.
                    rowsToUpsert.Add(row);
                }
            }

            rowData = JsonConvert.SerializeObject(rowsToUpsert, table.JsonSerializerSettings);
            IEnumerable <string> columnNamesFromPOCO = typeof(T).GetProperties().Select(prop => prop.Name);
            IEnumerable <string> bracketColumnDefinitionsFromPOCO = table.Columns.Where(c => columnNamesFromPOCO.Contains(c.Key, table.Comparer))
                                                                    .Select(c => $"{c.Key.AsBracketQuotedString()} {c.Value}");

            newDataQuery = $"WITH {CteName} AS ( SELECT * FROM OPENJSON({RowDataParameter}) WITH ({string.Join(",", bracketColumnDefinitionsFromPOCO)}) )";
        }
Exemplo n.º 30
0
 public MainWindow()
 {
     TableInformation = new TableInformation();
     InitializeComponent();
 }