private int GenerateDbObjects(int index, string dbConnectionString, GenerateDbObjectsType generateType, List <KeyValuePair <string, string> > dbObjectNames, DirectoryInfo targetDir, string locationPart, bool includeIndexes, List <string> ignoreSchemas) { var start = DateTime.Now; using (var sqlConnection = new SqlConnection(dbConnectionString)) { var connection = new ServerConnection(sqlConnection); var server = new Server(connection); var database = server.Databases[sqlConnection.Database]; var scripter = new Scripter(server) { Options = { ScriptDrops = false, IncludeHeaders = false, Indexes = includeIndexes } }; var dbObjects = GetDbObjects(generateType, database, ignoreSchemas); if (string.IsNullOrWhiteSpace(locationPart)) { locationPart = generateType.ToString(); } locationPart = locationPart.Replace("_", "\\"); var count = dbObjects .Where(e => dbObjectNames == null || dbObjectNames.Any(o => o.Key == e.Schema && o.Value == e.Name)) .Count(e => GenerateDbObject(scripter, e, targetDir, locationPart)); var end = DateTime.Now; Console.WriteLine( $"{locationPart} Done (Batch : {index}, Objects : {count}, Time : {end.Subtract(start).TotalSeconds}s)"); return(count); } }
private void GenerateDbObjectsInThreadsAndWait( GenerateDbObjectsType generateDbObjectsType, IReadOnlyCollection <KeyValuePair <string, string> > dbObjects, string dbConnectionString, DirectoryInfo targetDir, List <string> ignoreSchemas) { var threadCount = 5; int.TryParse(System.Configuration.ConfigurationManager.AppSettings.Get("GenerateDbObjectsThreadCount"), out threadCount); if (threadCount <= 0) { threadCount = 1; } var batchCount = dbObjects.Count / threadCount; var count = 0; var tasks = new List <Task>(); Console.WriteLine($"Starting {threadCount} threads to generate {dbObjects.Count} {generateDbObjectsType}"); var includeIndexes = generateDbObjectsType == GenerateDbObjectsType.Tables; for (var i = 0; i <= threadCount; i++) { var names = dbObjects.Skip(count).Take(batchCount).ToList(); count += batchCount; var index = i; tasks.Add(Task.Run(() => GenerateDbObjects(index, dbConnectionString, generateDbObjectsType, names, targetDir, null, includeIndexes, ignoreSchemas))); } Task.WaitAll(tasks.ToArray()); }
private static IEnumerable <ScriptSchemaObjectBase> GetDbObjects(GenerateDbObjectsType generateType, Database database, List <string> ignoreSchemas) { var items = new List <ScriptSchemaObjectBase>(); switch (generateType) { case GenerateDbObjectsType.Tables: foreach (ScriptSchemaObjectBase e in database.Tables) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Views: foreach (ScriptSchemaObjectBase e in database.Views) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Programmability_UserDefinedTableTypes: foreach (ScriptSchemaObjectBase e in database.UserDefinedTableTypes) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Programmability_UserDefinedTypes: foreach (ScriptSchemaObjectBase e in database.UserDefinedTypes) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Programmability_UserDefinedDataTypes: foreach (ScriptSchemaObjectBase e in database.UserDefinedDataTypes) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Programmability_UserDefinedFunctions: foreach (ScriptSchemaObjectBase e in database.UserDefinedFunctions) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; case GenerateDbObjectsType.Programmability_StoredProcedures: foreach (StoredProcedure e in database.StoredProcedures) { if (!ignoreSchemas.Contains(e.Schema)) { items.Add(e); } } break; default: throw new ArgumentOutOfRangeException(nameof(generateType), generateType, null); } return(items); }