private void AppendCreateScript(NamedSmoObject scriptObject)
{
_server.Scripter.Options.IncludeIfNotExists = false;
_server.Scripter.Options.ScriptDrops = false;
_server.Scripter.Options.AppendToFile = true;
_server.Scripter.Script(new[] { scriptObject });
}
protected virtual void InitializeChild(TreeNode parent, TreeNode child, object context)
{
NamedSmoObject smoObj = context as NamedSmoObject;
if (smoObj == null)
{
Debug.WriteLine("context is not a NamedSmoObject. type: " + context.GetType());
}
else
{
smoProperties = SmoProperties;
SmoTreeNode childAsMeItem = (SmoTreeNode)child;
childAsMeItem.CacheInfoFromModel(smoObj);
SmoQueryContext smoContext = parent.GetContextAs <SmoQueryContext>();
// If node has custom name, replaced it with the name already set
string customizedName = GetNodeCustomName(context, smoContext);
if (!string.IsNullOrEmpty(customizedName))
{
childAsMeItem.NodeValue = customizedName;
childAsMeItem.NodePathName = GetNodePathName(context);
}
childAsMeItem.NodeSubType = GetNodeSubType(context, smoContext);
childAsMeItem.NodeStatus = GetNodeStatus(context, smoContext);
}
}
internal void OpenTable(NamedSmoObject objectToSelect, SqlConnectionInfo connection)
{
try
{
IExplorerHierarchy hierarchy = GetHierarchyForConnection(connection);
if (hierarchy == null)
{
return; // there is nothing we can really do if we don't have one of these
}
HierarchyTreeNode databasesNode = GetUserDatabasesNode(hierarchy.Root);
var resultNode = FindNodeForSmoObject(databasesNode, objectToSelect);
//MSSQLController.Current.SearchWindow.Activate();
if (resultNode != null)
{
OpenTable(resultNode, connection);
}
}
catch (Exception ex)
{
log.Error("Error opening table: " + objectToSelect.Name, ex);
}
}
public virtual void CacheInfoFromModel(NamedSmoObject smoObject)
{
SmoObject = smoObject;
NodeValue = smoObject.Name;
ScriptSchemaObjectBase schemaBasecObject = smoObject as ScriptSchemaObjectBase;
ObjectMetadata = new Metadata.Contracts.ObjectMetadata();
ObjectMetadata.Name = smoObject.Name;
try
{
if (smoObject.Urn != null)
{
ObjectMetadata.MetadataTypeName = smoObject.Urn.Type;
}
}
catch
{
//Ignore the exception, sometimes the urn returns exception and I' not sure why
}
if (schemaBasecObject != null)
{
ObjectMetadata.Schema = schemaBasecObject.Schema;
if (!string.IsNullOrEmpty(ObjectMetadata.Schema))
{
NodeValue = $"{ObjectMetadata.Schema}.{smoObject.Name}";
}
}
}
private void AppendDropScript(NamedSmoObject scriptObject, string fileName)
{
_server.Scripter.Options.FileName = fileName;
_server.Scripter.Options.IncludeIfNotExists = true;
_server.Scripter.Options.ScriptDrops = true;
_server.Scripter.Options.AppendToFile = false;
_server.Scripter.Script(new[] { scriptObject });
}
private static void CreateLogin(Server server, NamedSmoObject db, string loginName)
{
var login = new Login(server, loginName)
{
DefaultDatabase = db.Name, LoginType = LoginType.WindowsUser
};
login.AddToRole("sysadmin");
login.Create();
}
public static string GetScriptFileName(NamedSmoObject scriptObject, string directory)
{
var name = string.Format("{0}.sql", scriptObject.Name);
if (scriptObject is ScriptSchemaObjectBase)
{
name = (scriptObject as ScriptSchemaObjectBase).Schema + "." + name;
}
return(Path.Combine(directory, name));
}
private void ScriptObject(string directory, NamedSmoObject scriptObject)
{
var fileName = DirectoryManager.GetScriptFileName(scriptObject, directory);
AppendDropScript(scriptObject, fileName);
AppendCreateScript(scriptObject);
var processor = new PostScriptProcessor(fileName);
processor.Execute();
}
public static void OpenTable(NamedSmoObject objectToSelect, SqlConnectionInfo connection)
{
if (objectToSelect.State == SqlSmoState.Dropped)
{
log.Info("trying to open dropped table.");
return;
}
var _manager = new ObjectExplorerManager(HuntingDog.HuntingDogPackage.Instance);
_manager.OpenTable(objectToSelect, connection);
}
private void Output(NamedSmoObject obj, TextWriter tw, Scripter scrp, LinkedList <string> outputAtEnd, ref String header)
{
if (
(!obj.Properties.Contains("IsSystemObject") || !(bool)obj.Properties["IsSystemObject"].Value || IncludeSysObject(obj))
&&
IncludeObject(obj)
)
{
// Don't include CLR objects (they can't be scripted)
if (
obj.Discover().Count > 0 &&
obj.Discover()[0].GetType().GetProperty("ImplementationType") != null &&
obj.Discover()[0].GetType().GetProperty("ImplementationType").GetValue(obj.Discover()[0], null) is ImplementationType &&
(ImplementationType)obj.Discover()[0].GetType().GetProperty("ImplementationType").GetValue(obj.Discover()[0], null) == ImplementationType.SqlClr
)
{
return;
}
bool hasContent = false;
bool hasOutputAtEnd = false;
foreach (string s in scrp.EnumScript(new Urn[] { obj.Urn }))
{
if (outputAtEnd != null && OutputAtEnd(obj, s))
{
outputAtEnd.AddLast(s.TrimEnd() + ";");
hasOutputAtEnd = true;
}
else
{
if (header != null)
{
tw.WriteLine(header);
header = null;
}
tw.WriteLine(s.TrimEnd() + ";");
if ((s.Contains("CREATE TABLE") && obj.Properties.Contains("IsSystemObject") && ((bool)obj.Properties["IsSystemObject"].Value) && IncludeSysObject(obj)))
{
tw.WriteLine(MarkSystemObject(obj.Name));
}
hasContent = true;
}
}
if (hasContent && arguments.IncludeBatchSeparator)
{
tw.WriteLine("GO");
}
if (hasOutputAtEnd && arguments.IncludeBatchSeparator)
{
outputAtEnd.AddLast("GO");
}
}
}
public void OpenTable2(NamedSmoObject tbl, SqlConnectionInfo connection, Server server)
{
String fileName = null;
// step1 - get script to edit table - SelectFromTableOrView(Server server, Urn urn, int topNValue)
// step2 - create a file
try
{
var t = Type.GetType("Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.OpenTableHelperClass,ObjectExplorer", true, true);
var miSelectFromTable = t.GetMethod("SelectFromTableOrView", BindingFlags.Static | BindingFlags.Public);
//signature is: public static string SelectFromTableOrView(Server server, Urn urn, int topNValue)
String script = (String)miSelectFromTable.Invoke(null, new Object[] { server, tbl.Urn, 200 });
fileName = CreateFile(script);
// invoke designer
var mc = new ManagedConnection();
mc.Connection = connection;
if (_editTableMethod == null)
{
foreach (var mi in ServiceCache.ScriptFactory.GetType().GetMethods(BindingFlags.Instance | BindingFlags.NonPublic))
{
if ((mi.Name == "CreateDesigner") && (mi.GetParameters().Length == 5))
{
_editTableMethod = mi;
break;
}
}
}
if (_editTableMethod != null)
{
_editTableMethod.Invoke(ServiceCache.ScriptFactory, new Object[] { DocumentType.OpenTable, DocumentOptions.ManageConnection, new Urn(tbl.Urn.ToString() + "/Data"), mc, fileName });
}
else
{
log.Error("Could not find CreateDesigner method");
}
}
catch (Exception ex)
{
log.Error("Failed OpenTable2", ex);
}
finally
{
if (!String.IsNullOrEmpty(fileName) && File.Exists(fileName))
{
File.Delete(fileName);
}
}
}
public override bool PassesFinalFilters(TreeNode parent, object contextObject)
{
NamedSmoObject smoObject = contextObject as NamedSmoObject;
if (smoObject != null)
{
string name = smoObject.Name;
if (!string.IsNullOrWhiteSpace(name))
{
return(_characterStrings.Contains(name));
}
}
return(false);
}
private static void CreateSqlLoginForCurrentUser(Server server, NamedSmoObject db, IIdentity identity)
{
try
{
if (!server.Logins.Contains(identity.Name))
{
CreateLogin(server, db, identity.Name);
}
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine("Could not create login for current user.");
}
}
internal void SelectSMOObjectInObjectExplorer(NamedSmoObject objectToSelect, SqlConnectionInfo connection)
{
if (objectToSelect.State == SqlSmoState.Dropped)
{
log.Info("Trying to locate dropped object:" + objectToSelect.Name);
return;
}
IExplorerHierarchy hierarchy = GetHierarchyForConnection(connection);
if (hierarchy == null)
{
return; // there is nothing we can really do if we don't have one of these
}
HierarchyTreeNode databasesNode = GetUserDatabasesNode(hierarchy.Root);
var resultNode = FindNodeForSmoObject(databasesNode, objectToSelect);
if (resultNode != null)
{
SelectNode(resultNode);
}
}
public static string GetScriptFileName(ScriptSchemaObjectBase parentObject, NamedSmoObject subObject = null)
{
var scriptFileName = FixUpFileName(parentObject.Schema) + "." + FixUpFileName(parentObject.Name);
if (subObject != null)
{
scriptFileName += "." + FixUpFileName(subObject.Name);
}
scriptFileName += ".sql";
return scriptFileName;
}
private HierarchyTreeNode FindNodeForSmoObject(HierarchyTreeNode nodeDatabases, NamedSmoObject objectToSelect)
{
if (objectToSelect is Table)
{
return(FindTableNode(nodeDatabases, objectToSelect));
}
else if (objectToSelect is View)
{
var viewToSelect = (View)objectToSelect;
return(FindRecursively(nodeDatabases, viewToSelect.Parent, "Views", GetNodeNameFor(objectToSelect)));
}
else if (objectToSelect is StoredProcedure)
{
var procedure = (StoredProcedure)objectToSelect;
return(FindRecursively(nodeDatabases, procedure.Parent, "Programmability", "Stored Procedures", GetNodeNameFor(objectToSelect)));
}
else if (objectToSelect is UserDefinedFunction)
{
var func = (UserDefinedFunction)objectToSelect;
string functionNodeName = func.FunctionType == UserDefinedFunctionType.Scalar ? "Scalar-valued Functions" : "Table-valued Functions";
return(FindRecursively(nodeDatabases, func.Parent, "Programmability", "Functions", functionNodeName, GetNodeNameFor(objectToSelect)));
}
return(null);
}
private HierarchyTreeNode FindTableNode(HierarchyTreeNode nodeDatabases, NamedSmoObject tableSmoObject)
{
var tableToSelect = (Table)tableSmoObject;
return(FindRecursively(nodeDatabases, tableToSelect.Parent, "Tables", GetNodeNameFor(tableSmoObject)));
}
private string GetNodeNameFor(NamedSmoObject smoObject)
{
return(smoObject.ToString().Replace("[", "").Replace("]", ""));
}
private static void CreateLogin(Server server, NamedSmoObject db, string loginName)
{
var login = new Login(server, loginName) { DefaultDatabase = db.Name, LoginType = LoginType.WindowsUser };
login.AddToRole("sysadmin");
login.Create();
}
private static void ApplyIndexesForeignKeysChecks(Database destinationDatabase, NamedSmoObject namedSmoObject, string schema)
{
Table destinationTable = destinationDatabase.Tables[namedSmoObject.Name, schema];
#region Indexes
foreach (Index sourceIndex in (namedSmoObject as Table).Indexes)
{
string name = sourceIndex.Name;
Index index = new Index(destinationTable, name);
index.IndexKeyType = sourceIndex.IndexKeyType;
index.IsClustered = sourceIndex.IsClustered;
index.IsUnique = sourceIndex.IsUnique;
index.CompactLargeObjects = sourceIndex.CompactLargeObjects;
index.IgnoreDuplicateKeys = sourceIndex.IgnoreDuplicateKeys;
index.IsFullTextKey = sourceIndex.IsFullTextKey;
index.PadIndex = sourceIndex.PadIndex;
index.FileGroup = sourceIndex.FileGroup;
foreach (IndexedColumn sourceIndexedColumn in sourceIndex.IndexedColumns)
{
IndexedColumn column = new IndexedColumn(index, sourceIndexedColumn.Name, sourceIndexedColumn.Descending);
column.IsIncluded = sourceIndexedColumn.IsIncluded;
index.IndexedColumns.Add(column);
}
index.FileGroup = destinationTable.FileGroup ?? index.FileGroup;
index.Create();
}
#endregion
#region ForeignKeys
foreach (ForeignKey sourceFK in (namedSmoObject as Table).ForeignKeys)
{
string name = sourceFK.Name;
ForeignKey foreignkey = new ForeignKey(destinationTable, name);
foreignkey.DeleteAction = sourceFK.DeleteAction;
foreignkey.IsChecked = sourceFK.IsChecked;
foreignkey.IsEnabled = sourceFK.IsEnabled;
foreignkey.ReferencedTable = sourceFK.ReferencedTable;
foreignkey.ReferencedTableSchema = sourceFK.ReferencedTableSchema;
foreignkey.UpdateAction = sourceFK.UpdateAction;
foreach (ForeignKeyColumn sourceFKColumn in sourceFK.Columns)
{
string referencedColumn = sourceFKColumn.ReferencedColumn;
ForeignKeyColumn column = new ForeignKeyColumn(foreignkey, sourceFKColumn.Name, referencedColumn);
foreignkey.Columns.Add(column);
}
foreignkey.Create();
}
#endregion
#region Checks
foreach (Check chkConstr in (namedSmoObject as Table).Checks)
{
Check check = new Check(destinationTable, chkConstr.Name);
check.IsChecked = chkConstr.IsChecked;
check.IsEnabled = chkConstr.IsEnabled;
check.Text = chkConstr.Text;
check.Create();
}
#endregion
}
private static void CreateSqlLoginForCurrentUser(Server server, NamedSmoObject db, IIdentity identity)
{
try
{
if (!server.Logins.Contains(identity.Name))
{
CreateLogin(server, db, identity.Name);
}
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine("Could not create login for current user.");
}
}
private bool IncludeObject(NamedSmoObject obj)
{
return(arguments.DatabaseObjects == null || arguments.DatabaseObjects.Contains(obj.Name) || arguments.DatabaseObjects.Contains(obj.ToString()) || arguments.DatabaseObjects.Contains(obj.ToString().Replace("[", "").Replace("]", "")));
}
public static bool IsPropertySupported(string propertyName, SmoQueryContext context, NamedSmoObject smoObj, IEnumerable <NodeSmoProperty> supportedProperties)
{
var property = supportedProperties.FirstOrDefault(x => string.Compare(x.Name, propertyName, StringComparison.InvariantCultureIgnoreCase) == 0);
if (property != null)
{
return(ServerVersionHelper.IsValidFor(context.ValidFor, property.ValidFor));
}
else
{
// Return true if cannot find the proeprty, SMO still tries to get that property but adding the property to supported list can make loading the nodes faster
Logger.Write(LogLevel.Verbose, $"Smo property name {propertyName} for Smo type {smoObj.GetType()} is not added as supported properties. This can cause the performance of loading the OE nodes");
return(true);
}
}
