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