/// <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)}) )"; }
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 });
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); }
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); }
public void TableAndSchmeaNamesSpecified_SchemaIsSet() { var target = new TableInformation($"{TestSchemaName}.{TestTableName}"); Assert.Equal(TestTableName, target.TableName); Assert.Equal(TestSchemaName, target.SchemaName); Assert.True(target.HasSchemaName); }
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)); }
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; }
private Task SaveAddresses(DataTable table) { TableInformation info = new TableInformation { ColumnMappings = DefaultMappings, Name = "Addresses" }; return(BulkCopyTable(info, table)); }
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"); }
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)); }
private Task SaveThoroughfares(Mainfile data) { TableInformation info = new TableInformation { ColumnMappings = DefaultMappings, ColumnsToConvert = new[] { 1 }, Name = "Thoroughfares" }; return(this.BulkCopy(data.CreateReader(MainfileType.Thoroughfares), info)); }
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); } }
public void TryParse_Empty() { // Arrange string table = null; // Act var parsed = TableInformation.TryParse(table, out var tableInformation); // Assert Assert.IsFalse(parsed); Assert.IsNull(tableInformation); }
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)); }
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)); }
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]); } }
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); }
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); }
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); }
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 ); }
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()); }
/// <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)); }
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(); } }
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); }
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)}) )"; }
public MainWindow() { TableInformation = new TableInformation(); InitializeComponent(); }