/// <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();
}