/// <summary>
/// builds the result table. NULLs in the output mean no match was found
/// </summary>
public async Task ExecuteAsync(SqlConnection connection)
{
await RebuildResultTableAsync(connection);
// create blank rows in the result table for each source row.
// this is what we'll update with key values next
var sourceObj = ObjectName.FromName(SourceTable);
var resultObj = ObjectName.FromName(ResultTable);
var columnNames = string.Join(", ", Lookups.Select(kp => $"[{kp.Value.ResultColumn}]"));
var defaultValues = string.Join(", ", Lookups.Select(kp => "NULL"));
await connection.ExecuteAsync(
$@"INSERT INTO [{resultObj.Schema}].[{resultObj.Name}] ([{IdentityColumn}], {columnNames})
SELECT [{IdentityColumn}], {defaultValues}
FROM [{sourceObj.Schema}].[{sourceObj.Name}]");
// attempt to match text values in the source table with named values in the corresponding lookup table.
// if no match, we simply get nulls in the corresponding output col
foreach (var col in Lookups)
{
var lookupObj = ObjectName.FromName(col.Value.LookupTable);
var sqlUpdate =
$@"UPDATE [result] SET
[{col.Value.ResultColumn}]=[lookup].[{col.Value.LookupIdentityColumn}]
FROM
[{sourceObj.Schema}].[{sourceObj.Name}] [src]
INNER JOIN [{resultObj.Schema}].[{resultObj.Name}] [result] ON [src].[{IdentityColumn}]=[result].[{IdentityColumn}]
INNER JOIN [{lookupObj.Schema}].[{lookupObj.Name}] [lookup] ON [src].[{col.Key}]=[lookup].[{col.Value.LookupNameColumn}]";
await connection.ExecuteAsync(sqlUpdate);
}
}
/// <summary>
/// finds source values that don't have a mapping, grouped by column.
/// Helps users know what they need to fix in their spreadsheet if they enter a value that doesn't exist
/// </summary>
public async Task <ILookup <string, string> > GetErrorsAsync(SqlConnection cn)
{
List <KeyValuePair <string, string> > results = new List <KeyValuePair <string, string> >();
var sourceObj = ObjectName.FromName(SourceTable);
foreach (var col in Lookups)
{
var lookupObj = ObjectName.FromName(col.Value.LookupTable);
var errors = await cn.QueryAsync <KeyValuePair <string, string> >(
$@"WITH [source] AS (
SELECT [{col.Key}] FROM [{sourceObj.Schema}].[{sourceObj.Name}] GROUP BY [{col.Key}]
) SELECT
'{col.Key}' AS [Key], [{col.Key}] AS [Value]
FROM
[source] [src]
WHERE
NOT EXISTS(SELECT 1 FROM [{lookupObj.Schema}].[{lookupObj.Name}] WHERE [{col.Value.LookupNameColumn}]=[src].[{col.Key}]);" );
results.AddRange(errors);
}
return(results.ToLookup(row => row.Key, row => row.Value));
}
private void DropCacheTable(DbCache cache)
{
using (var cn = LocalDb.GetConnection(dbName))
{
var obj = ObjectName.FromName(cache.TableName);
if (cn.TableExistsAsync(obj.Schema, obj.Name).Result)
{
cn.Execute($"DROP TABLE {cache.TableName}");
}
}
}
private async Task RebuildResultTableAsync(SqlConnection connection)
{
var objName = ObjectName.FromName(ResultTable);
if (connection.TableExists(objName.Schema, objName.Name))
{
await connection.ExecuteAsync($"DROP TABLE [{objName.Schema}].[{objName.Name}]");
}
string sql = $"CREATE TABLE [{objName.Schema}].[{objName.Name}] (";
List <string> columns = new List <string>();
// this is what joins to the source table (spreadsheet uploaded by user with text key values we're going to lookup)
// it's hardcoded int because ExcelLoader.cs:93
columns.Add($"[{IdentityColumn}] int NOT NULL PRIMARY KEY");
// now add the lookup columns -- this is where the key lookup results are stored
columns.AddRange(Lookups.Select(kp => $"[{kp.Value.ResultColumn}] {ColumnSqlTypeSyntax} NULL"));
sql += string.Join("\r\n\t, ", columns) + "\r\n)";
await connection.ExecuteAsync(sql);
}
public DbDictionary(Func <IDbConnection> getConnection, string tableName)
{
_getConnection = getConnection;
_tableName = ObjectName.FromName(tableName);
TableName = tableName;
}
public void SampleNamesFromString()
{
Assert.IsTrue(ObjectName.FromName("dbo.Account").Equals(new ObjectName("dbo", "Account")));
Assert.IsTrue(ObjectName.FromName("Account").Equals(new ObjectName("dbo", "Account")));
}
