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;";