/// <summary>
/// Creates a ScriptingObject instance based on the passed SMO Urn instance.
/// </summary>
/// <param name="urn">The urn instance.</param>
/// <returns>The scripting object instance.</returns>
public static ScriptingObject ToScriptingObject(this Urn urn)
{
Validate.IsNotNull("urn", urn);
return(new ScriptingObject
{
Type = urn.Type,
Schema = urn.GetAttribute("Schema"),
Name = urn.GetAttribute("Name"),
});
}
/// <summary>
/// Creates new DB Group folders and move DB under them
/// </summary>
/// <param name="node">Server/DatabaseFolder node</param>
/// <param name="imageName">folder icon</param>
/// <param name="subItemImage">database icon</param>
public void ReorganizeDbNodes(TreeNode node, string imageName, string subItemImage, Dictionary <string, Dictionary <string, string> > dbFolderLinks)
{
if (dbFolderLinks == null || dbFolderLinks.Count == 0)
{
return;
}
Dictionary <string, string> dbGroups = null;
var nodesToMove = new List <KeyValuePair <string, List <TreeNode> > >();
var createNodes = new List <KeyValuePair <string, TreeNode> >();
string serverName = string.Empty;
for (int i = node.Nodes.Count - 1; i > -1; i--)
{
TreeNode tn = node.Nodes[i];
Urn rn = GetNodeUrn(tn);
if (rn == null)
{
continue;
}
if (string.IsNullOrEmpty(serverName))
{
serverName = rn.GetAttribute("Name", "Server");
}
if (dbGroups == null && !string.IsNullOrEmpty(serverName) && dbFolderLinks.ContainsKey(serverName))
{
dbGroups = dbFolderLinks[serverName];
}
string dbName = rn.GetAttribute("Name", "Database");
if (!string.IsNullOrEmpty(dbName) && dbGroups != null && dbGroups.ContainsKey(dbName))
{
var kvpCreateNd = new KeyValuePair <string, TreeNode>(dbGroups[dbName], null);
var kvpNodesToMove = new KeyValuePair <string, List <TreeNode> >(dbGroups[dbName], new List <TreeNode>());
kvpNodesToMove.Value.Add(tn);
if (!createNodes.Contains(kvpCreateNd))
{
createNodes.Add(kvpCreateNd);
}
if (!nodesToMove.Contains(kvpNodesToMove))
{
nodesToMove.Add(kvpNodesToMove);
}
}
}
DoReorganization(node, nodesToMove, createNodes, imageName, subItemImage);
}
/// <summary>
/// Create schema nodes and move tables under its schema node, functions and stored procedures
/// </summary>
/// <param name="node">Table node to reorganize</param>
/// <param name="imageName">Image of new node</param>
/// <param name="subItemImage">Image for subnodes, if empty - current image will be used</param>
public void ReorganizeNodes(TreeNode node, string imageName, string subItemImage)
{
List <KeyValuePair <string, List <TreeNode> > > nodesToMove = new List <KeyValuePair <string, List <TreeNode> > >();
List <KeyValuePair <string, TreeNode> > createNodes = new List <KeyValuePair <string, TreeNode> >();
for (int i = node.Nodes.Count - 1; i > -1; i--)
{
TreeNode tn = node.Nodes[i];
Urn rn = GetNodeUrn(tn);
if (rn == null)
{
continue;
}
string schema = rn.GetAttribute("Schema", "Table");
if (string.IsNullOrEmpty(schema))
{
schema = rn.GetAttribute("Schema", "StoredProcedure");
}
if (string.IsNullOrEmpty(schema))
{
schema = rn.GetAttribute("Schema", "UserDefinedFunction");
}
if (string.IsNullOrEmpty(schema))
{
continue;
}
KeyValuePair <string, TreeNode> kvpCreateNd = new KeyValuePair <string, TreeNode>(schema, null);
KeyValuePair <string, List <TreeNode> > kvpNodesToMove = new KeyValuePair <string, List <TreeNode> >(schema, new List <TreeNode>());
kvpNodesToMove.Value.Add(tn);
if (!createNodes.Contains(kvpCreateNd))
{
createNodes.Add(kvpCreateNd);
}
if (!nodesToMove.Contains(kvpNodesToMove))
{
nodesToMove.Add(kvpNodesToMove);
}
}
DoReorganization(node, nodesToMove, createNodes, imageName, subItemImage);
}
private string QualifiedObjectName(Urn urn)
{
if (urn == null)
{
return(String.Empty);
}
string name = urn.GetNameForType(urn.Type);
string schema = urn.GetAttribute("Schema", urn.Type);
return("[" + schema + "]" + ".[" + name + "]");
}
/// <summary>
/// Get the name and schema (if applicable) for the object we are referring to
/// </summary>
private void InitializeObjectNameAndSchema()
{
string documentUrn = this.GetDocumentPropertyString("urn");
if (documentUrn.Length != 0)
{
Urn urn = new Urn(documentUrn);
string name = urn.GetAttribute("Name");
string schema = urn.GetAttribute("Schema");
if ((name != null) && (name.Length != 0))
{
this.ObjectName = name;
}
if ((schema != null) && (schema.Length != 0))
{
this.ObjectSchema = schema;
}
}
}
/// <summary>
/// Gets connection string from tree node view item
/// </summary>
/// <param name="node">TreeNode view item</param>
/// <returns>Connection string of the tree node item</returns>
public string GetConnectionString(TreeNode node)
{
INodeInformation service = GetNodeInformation(node);
Urn urn = GetServiceUrn(service);
//System.Diagnostics.Debug.Print(service.Connection.ConnectionString);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(service.Connection.ConnectionString);
builder.InitialCatalog = urn.GetAttribute("Name", "Database");
return(builder.ToString());
}
internal static string SelectAllValues(Urn urn)
{
string script = string.Empty;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT TOP (1000) ");
selectQuery.Append("*, casted_message_body = \r\nCASE message_type_name WHEN 'X' \r\n THEN CAST(message_body AS NVARCHAR(MAX)) \r\n ELSE message_body \r\nEND \r\n");
// from clause
selectQuery.Append("FROM ");
Urn dbUrn = urn;
// database
while (dbUrn.Parent != null && dbUrn.Type != "Database")
{
dbUrn = dbUrn.Parent;
}
selectQuery.AppendFormat("{0}{1}{2}",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
// schema
selectQuery.AppendFormat(".{0}{1}{2}",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(urn.GetAttribute("Schema"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
// object
selectQuery.AppendFormat(".{0}{1}{2}",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(urn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
//Adding no lock in the end.
selectQuery.AppendFormat(" WITH(NOLOCK)");
script = selectQuery.ToString();
return(script);
}
/// <summary>
/// Gets the connection string from a node
/// </summary>
/// <param name="node">The node.</param>
/// <returns></returns>
private string GetConnectionString(TreeNode node)
{
INodeInformation service = null;
IServiceProvider provider = node as IServiceProvider;
if (provider != null)
{
service = provider.GetService(typeof(INodeInformation)) as INodeInformation;
}
Urn urn = new Urn(service.Context);
System.Diagnostics.Debug.Print(service.Connection.ConnectionString);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(service.Connection.ConnectionString);
builder.InitialCatalog = urn.GetAttribute("Name", "Database");
return(builder.ToString());
}
internal static string SelectAllValuesFromTransmissionQueue(Urn urn)
{
string script = string.Empty;
StringBuilder selectQuery = new StringBuilder();
/*
* SELECT TOP *, casted_message_body =
* CASE MESSAGE_TYPE_NAME WHEN 'X'
* THEN CAST(MESSAGE_BODY AS NVARCHAR(MAX))
* ELSE MESSAGE_BODY
* END
* FROM [new].[sys].[transmission_queue]
*/
selectQuery.Append("SELECT TOP (1000) ");
selectQuery.Append("*, casted_message_body = \r\nCASE message_type_name WHEN 'X' \r\n THEN CAST(message_body AS NVARCHAR(MAX)) \r\n ELSE message_body \r\nEND \r\n");
// from clause
selectQuery.Append("FROM ");
Urn dbUrn = urn;
// database
while (dbUrn.Parent != null && dbUrn.Type != "Database")
{
dbUrn = dbUrn.Parent;
}
selectQuery.AppendFormat("{0}{1}{2}",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
//SYS
selectQuery.AppendFormat(".{0}sys{1}",
ScriptingGlobals.LeftDelimiter,
ScriptingGlobals.RightDelimiter);
//TRANSMISSION QUEUE
selectQuery.AppendFormat(".{0}transmission_queue{1}",
ScriptingGlobals.LeftDelimiter,
ScriptingGlobals.RightDelimiter);
script = selectQuery.ToString();
return(script);
}
internal string SelectFromTableOrView(Server server, Urn urn, bool isDw)
{
DataTable dt = GetColumnNames(server, urn, isDw);
StringBuilder selectQuery = new StringBuilder();
// build the first line
if (dt != null && dt.Rows.Count > 0)
{
selectQuery.Append("SELECT TOP (1000) ");
// first column
selectQuery.AppendFormat("{0}{1}{2}\r\n",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(dt.Rows[0][0] as string, ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
// add all other columns on separate lines. Make the names align.
for (int i = 1; i < dt.Rows.Count; i++)
{
selectQuery.AppendFormat(" ,{0}{1}{2}\r\n",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(dt.Rows[i][0] as string, ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
}
}
else
{
selectQuery.Append("SELECT TOP (1000) * ");
}
// from clause
selectQuery.Append(" FROM ");
if (server.ServerType != DatabaseEngineType.SqlAzureDatabase)
{
// Azure doesn't allow qualifying object names with the DB, so only add it on if we're not in Azure database URN
Urn dbUrn = urn.Parent;
selectQuery.AppendFormat("{0}{1}{2}.",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
}
// schema
selectQuery.AppendFormat("{0}{1}{2}.",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(urn.GetAttribute("Schema"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
// object
selectQuery.AppendFormat("{0}{1}{2}",
ScriptingGlobals.LeftDelimiter,
ScriptingUtils.QuoteObjectName(urn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter),
ScriptingGlobals.RightDelimiter);
// In Hekaton M5, if it's a memory optimized table, we need to provide SNAPSHOT hint for SELECT.
if (urn.Type.Equals("Table") && ScriptingUtils.IsXTPSupportedOnServer(server))
{
try
{
Table table = (Table)server.GetSmoObject(urn);
table.Refresh();
if (table.IsMemoryOptimized)
{
selectQuery.Append(" WITH (SNAPSHOT)");
}
}
catch (Exception ex)
{
// log any exceptions determining if InMemory, but don't treat as fatal exception
Logger.Write(TraceEventType.Error, "Could not determine if is InMemory table " + ex.ToString());
}
}
return(selectQuery.ToString());
}
private bool CompareObjectContents(Urn urn1, Urn urn2, string schema1, string schema2)
{
ArrayList sysSchemas
= new ArrayList(new string[] { "sys", "INFORMATION_SCHEMA" });
bool result = true;
string objectName1 = null;
string objectName2 = null;
string databaseName1 = null;
string databaseName2 = null;
string command1 = null;
string command2 = null;
SqlDataReader myReader1 = null;
SqlDataReader myReader2 = null;
Type type1 = null;
Type type2 = null;
object value1 = null;
object value2 = null;
bool notEnd1 = false;
bool notEnd2 = false;
bool bAux = true;
int nRows = 0;
if (sysSchemas.Contains(schema1) || sysSchemas.Contains(schema2))
return true;
WriteLine(string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Comparing,
urn1, urn2));
try
{
try
{
// This has to be a database
databaseName1 = urn1.Parent.GetAttribute("Name");
objectName1 = urn1.GetAttribute("Name");
command1 = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
"SELECT * FROM [{0}].[{1}].[{2}]",
databaseName1, schema1, objectName1);
myReader1
= server1.ConnectionContext.ExecuteReader(command1);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorReadingFirst,
objectName1, ex), MessageType.Error);
return false;
}
try
{
// This has to be a database
databaseName2 = urn2.Parent.GetAttribute("Name");
objectName2 = urn2.GetAttribute("Name");
command2 = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
"SELECT * FROM [{0}].[{1}].[{2}]",
databaseName2, schema2, objectName2);
myReader2
= server2.ConnectionContext.ExecuteReader(command2);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorReadingSecond,
objectName2, ex), MessageType.Error);
return false;
}
if (myReader1.FieldCount != myReader2.FieldCount)
{
WriteLine(Properties.Resources.FieldCountDiffers,
MessageType.Error);
result = false;
}
else
{
notEnd1 = myReader1.Read();
notEnd2 = myReader2.Read();
while (notEnd1 && notEnd2)
{
bAux = true;
nRows++;
for (int k = 0; k < myReader1.FieldCount; k++)
{
type1 = myReader1.GetFieldType(k);
type2 = myReader2.GetFieldType(k);
value1 = myReader1.GetValue(k);
value2 = myReader2.GetValue(k);
bAux = value1.Equals(value2);
result &= bAux;
if (!bAux)
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Values,
value1.ToString(), value2.ToString()));
bAux = type1.Equals(type2);
result &= bAux;
if (!bAux)
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Types, type1.FullName,
type2.FullName));
}
notEnd1 = myReader1.Read();
notEnd2 = myReader2.Read();
}
if (notEnd1 != notEnd2)
{
result = false;
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.MoreRows,
nRows), MessageType.Error);
}
}
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorCreatingFirstObject, ex));
throw new ApplicationException(
Properties.Resources.ErrorCreatingFirstObjectException,
ex);
}
finally
{
myReader1.Close();
myReader2.Close();
}
return result;
}
private void Download(IEnumerable <IGrouping <int, string> > smoObjectsGroup)
{
Parallel.ForEach(smoObjectsGroup, g =>
{
Server serv = new Server(new ServerConnection()
{
ConnectionString = ServerOption.ToString(),
});
Scripter scripter = new Scripter
{
Server = serv
};
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.AllowSystemObjects = false;
try
{
foreach (var urn in g)
{
var urnn = new Urn(urn);
var type = urnn.Type;
var name = urnn.GetNameForType(type);
if (!urnn.GetAttribute("Schema").ToUpper().Equals("dbo".ToUpper()))
{
continue;
}
var smoObject = serv.GetSmoObject(urnn);
var scriptLines = scripter.Script(new SqlSmoObject[] { smoObject });
Logger.Log($"Forming script: '{name}'");
if (scriptLines.Count == 0)
{
Logger.Log($"Неизвестная ошибка формирования скрипта для: '{name}'");
continue;
}
scriptLines.RemoveAt(0);
if (ServerOption.ReplaceFirstCreate)
{
var replacer = ReplacerFactories.GetReplacer(type);
scriptLines = replacer.Replace(scriptLines);
}
var outputString = OutputSchemaObject(ServerOption.DbName, scriptLines);
var extension = "sql";
var fileName = $"{name}.{type}.{extension}";
var fullFilePath = Path.Combine(WriteToFolderPath, ServerOption.ServerName, DateTime.Now.ToString("yyyyMMdd"), fileName);
Logger.Log($"Write File: '{fullFilePath}'");
WriteFile(fullFilePath, outputString);
}
}
catch (Exception e)
{
Logger.Log(e);
}
});
}