public async Task EnumerateObjectsInDatabases(
SqlConnectionViewModel connection,
IEnumerable <DatabaseViewModel> databases,
string nameSearchString,
string schemaSearchString,
string definitionSearchString,
IEventAggregator eventAggregator)
{
Contract.Requires(databases != null);
//int databaseCount = databases.Count();
var log = LogManager.GetLog(typeof(SchemaDBContext));
var tasks = new List <Task>();
eventAggregator.PublishOnUIThread(new ObjectEnumerationStartingMessage());
foreach (var db in databases)
{
var t = Task.Factory.StartNew(() =>
{
try
{
log.Info("Sch DB Ctx begin foreach DB {0}", db.db_name);
var connectionString = ConnectionViewModel.ConnectionString();
var cnStringBuilder = new SqlConnectionStringBuilder(connectionString);
cnStringBuilder.InitialCatalog = db.db_name;
using (var cn = new SqlConnection(cnStringBuilder.ConnectionString))
{
cn.Open();
var objectNameSearchParam = new SqlParameter("@objectNameSearchParam______", (nameSearchString ?? "").Trim());
var objectSchemaSearchParam = new SqlParameter("@objectSchemaSearchParam______", (schemaSearchString ?? "").Trim());
var objectDefinitionSearchParam = new SqlParameter("@objectDefinitionSearchParam______", (definitionSearchString ?? "").Trim());
var cmd = cn.CreateCommand();
cmd.CommandTimeout = 0;
cmd.CommandText = objectsQuery;
cmd.Parameters.Add(objectNameSearchParam);
cmd.Parameters.Add(objectSchemaSearchParam);
cmd.Parameters.Add(objectDefinitionSearchParam);
using (var result = cmd.ExecuteReader())
{
var objects = SysObject.MapFrom(result).ToArray();
var message = new EnumerateObjectsInDatabaseMessage(ConnectionViewModel, objects, db.db_name, connection.ServerAndInstance);
eventAggregator.PublishOnUIThread(message);
}
}
}
catch (Exception ex)
{
connection.ErrorMessage = ex.ToString();
eventAggregator.PublishOnUIThread(ex);
}
}, TaskCreationOptions.LongRunning);
tasks.Add(t);
}
await Task.Factory.StartNew(() => Task.WaitAll(tasks.ToArray()));
return;
}
public static IEnumerable <SysObject> MapFrom(IDataReader rdr)
{
while (rdr.Read())
{
var rv = new SysObject
{
db_name = rdr["db_name"].ToString(),
create_date = Convert.ToDateTime(rdr["create_date"]),
is_encrypted = Convert.ToBoolean(rdr["is_encrypted"]),
modify_date = Convert.ToDateTime(rdr["modify_date"]),
object_id = Convert.ToInt32(rdr["object_id"]),
object_name = Convert.ToString(rdr["object_name"]),
parent_object_name = Convert.ToString(rdr["parent_object_name"]),
parent_object_schema_name = Convert.ToString(rdr["parent_object_schema_name"]),
schema_name = Convert.ToString(rdr["schema_name"]),
server_name = Convert.ToString(rdr["server_name"]),
type_desc = Convert.ToString(rdr["type_desc"])
//,
//Urn = Convert.ToString(rdr["Urn"])
};
yield return(rv);
}
}
public async Task <string> GetObjectDefinition(SysObject so)
{
using (var cn = new SqlConnection(ConnectionViewModel.ConnectionString()))
{
var srvConnect = new ServerConnection(cn);
var srv = new smo.Server(srvConnect);
srv.Refresh();
SetDefaultInitFields(srv);
smo.Database database = null;
StringBuilder sb = null;
await Task.Factory.StartNew(() =>
{
database = srv.Databases[so.db_name];
// SMO keeps different top level collections for each type of object. You can't go to the DB and say,
// "Give me the object with this URI," even though each object DOES have a URI property. In some cases,
// you have to supply a parent object (e.g. Triggers). So the switch block below figures out how to
// grab each object from the database based on its type.
//
// Worse yet, even though each of them does contain a method called Script() that takes a single parameter
// of type ScriptOptions, there's no base object or interface that defines these objects as something
// that can script itself. To get make this easier, we cheat and just use a dynamic type then call the
// Script() method on that dynamic object. This is pretty hokey, but this only gets called once each
// time the user requests that an object get scripted out, i.e. it's not running in a loop thousands of times.
dynamic scriptableObject = null;
switch (so.type_desc)
{
case "CHECK_CONSTRAINT":
scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Checks[so.object_name];
break;
case "DEFAULT_CONSTRAINT":
// We basically ignore default constraints - they get scripted out as part of scripting a table... I think that's why I did this...
//ob = db.Tables[so.parent_object_name, so.parent_object_schema_name]
break;
case "SQL_STORED_PROCEDURE":
scriptableObject = database.StoredProcedures[so.object_name, so.schema_name];
break;
case "USER_TABLE":
scriptableObject = database.Tables[so.object_name, so.schema_name];
break;
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
case "SQL_SCALAR_FUNCTION":
case "SQL_TABLE_VALUED_FUNCTION":
scriptableObject = database.UserDefinedFunctions[so.object_name, so.schema_name];
break;
case "SQL_TRIGGER":
scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Triggers[so.object_name];
break;
case "VIEW":
scriptableObject = database.Views[so.object_name, so.schema_name];
break;
default:
throw new InvalidOperationException(String.Format("Unknown DB object type of «{0}» was encountered. " +
"I don't know what to do with objects of that type.", so.type_desc));
}
sb = new StringBuilder();
if (scriptableObject == null)
{
sb.Append("The specified object was not found on the server.");
}
else
{
sb.AppendFormat(@"-- {0} :: {1}.{2}.{3}{4}", so.server_name, so.db_name, so.schema_name, so.object_name, Environment.NewLine);
sb.AppendFormat(@"-- {0}{1}", so.type_desc, Environment.NewLine);
sb.AppendFormat(@"-- Script Created: {0:yyyy-MM-dd HH\:mm\:ss zz}{1}", DateTimeOffset.Now, Environment.NewLine);
sb.AppendFormat(@"-- Object Created: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.create_date, Environment.NewLine);
sb.AppendFormat(@"-- Object LastMod: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.modify_date, Environment.NewLine);
sb.AppendFormat("use {0}{1}go{1}", QuoteName(so.db_name), Environment.NewLine);
// Script the DROP surrounded by multiline comments
ProcessScriptResults(scriptableObject.Script(scriptDropOptions), sb, "/*", "*/");
// now script the actual body of the object
ProcessScriptResults(scriptableObject.Script(scriptCreateOptions), sb);
}
});
return(sb.ToString());
}
}