public void Build(SharedParent parent)
{
try
{
parent.SeedQueries = new List <Query>();
TableInfo info = _impersonation.Get(parent.TableType);
Create(info, parent);
if (info.UniqueColumns.Count > 0)
{
Index(info, parent);
}
} catch (Exception e)
{
_log.Accept(new Execution($"Failed to build query for {parent.TableType} " +
$"problem: {e.Message}"));
throw;
}
}
private void Index(TableInfo info, SharedParent parent)
{
StringBuilder indexQuery = new StringBuilder($"IF NOT EXISTS(SELECT * FROM sys.indexes " +
$"WHERE name = '{info.TableName}_clustered' AND object_id " +
$"= OBJECT_ID('[dbo].[{info.TableName}]'))\nBEGIN\n");
indexQuery.Append($"\tEXEC('CREATE UNIQUE CLUSTERED INDEX [{info.TableName}_clustered] " +
$"ON [dbo].[{info.TableName}] (\n");
foreach (var constraint in info.UniqueColumns)
{
indexQuery.Append($"\t[{constraint}] ASC,\n");
}
parent.SeedQueries.Add(new Query
{
QueryBody = ReplaceLastOccurrence(indexQuery.ToString(), ",", string.Empty) +
")WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')\nEND\n",
QueryType = QueryType.Index
});
}
private void FillShareds(ExtractionModel model, TableInfo info)
{
if (!File.Exists(Path.Combine(_configModel.DbfLookUpDir, model.DbfName)))
{
throw new ExtractionException($"Can't find dbf file to receive data on: {model.FullDescription}");
}
DataTable data = ReceiveData(model, info);
if (info.CustomColumns.Count > 0)
{
Custom(model, data, info);
}
if (!_parents.Any(t => t.TableType.Equals(model.TableType)))
{
_parents.Add(new SharedParent
{
TableType = model.TableType,
SharedChilds = new List <SharedChild>()
});
}
SharedParent parent = _parents
.Where(t => t.TableType.Equals(model.TableType))
.FirstOrDefault();
if (parent is null)
{
throw new ExtractionException($"Can't find parent table with type, {model.FullDescription}");
}
SharedChild child = new SharedChild
{
FileName = model.DbfName,
PackageName = model.Package,
Rows = data.Select()
};
parent.SharedChilds.Add(child);
}
private void Create(TableInfo info, SharedParent parent)
{
string[] schemas = { "stage", "dbo" };
foreach (string schema in schemas)
{
StringBuilder query =
new StringBuilder(
$"IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = '{schema}' " +
$"AND table_name = '{info.TableName}')\n");
query.Append($"\tCREATE TABLE [{schema}].[{info.TableName}] (\n");
foreach (var col in info.SqlColumnTypes)
{
query.Append($"\t[{col.Key}] {col.Value},\n");
}
query.Append(")\n");
parent.SeedQueries.Add(new Query
{
QueryBody = ReplaceLastOccurrence(query.ToString(), ",", string.Empty),
QueryType = QueryType.Create
});
}
}
private void BulkCopy(SharedParent parent)
{
string sql = "EXEC [dbo].[sp_InsertSyncInfo] @packName, @fileName, @bulked, @timeNow;";
