/// <inheritdoc/>
public ICollection <DataTable> ExecuteSqlScript(string sqlCommand, WindowsIdentity identity = null)
{
if (identity == null)
{
// even if identity hasn't been passed there is a possibility that settings will be available.
var settings = this.RetrieveSettingsAsDictionary();
if (settings.TryGetValue($"{this.Environment ?? "Default"}.Impersonation@{this.Name}.User", out var user) &&
settings.TryGetValue($"{this.Environment ?? "Default"}.Impersonation@{this.Name}.Password", out var password))
{
identity = settings.TryGetValue($"{this.Environment ?? "Default"}.Impersonation@{this.Name}.Domain", out var domain)
? SecurityTools.GetIdentityByName(userName: user.ToString(), password: password.ToString(), domainName: domain.ToString())
: SecurityTools.GetIdentityByName(userName: user.ToString(), password: password.ToString());
}
}
return(Core.Windows.Tools.Database.DbHelper.ExecuteSqlScript(
ConfigurationManager.ConnectionStrings[this.ConnectionStringName].ProviderName,
ConfigurationManager.ConnectionStrings[this.ConnectionStringName].ConnectionString,
sqlCommand,
impersonationIdentity: identity) as List <DataTable>);
}
private void ChangeProjectConnection(string chosenProject, string chosenEnvironment = "Default")
{
var dataEntities = GetEntityTypes(chosenProject);
var sqlScripts = GetSqlScripts(chosenProject);
if (dataEntities == null)
{
return;
}
var plugin =
(Application.Current as ISoluiNetUiWpfApp)?.SqlPlugins.FirstOrDefault(x => x.Name == chosenProject);
if (plugin == null)
{
return;
}
WindowsIdentity identity = null;
if (plugin is IContainsSettings)
{
var settings = (plugin as IContainsSettings).RetrieveSettingsAsDictionary();
if (settings.TryGetValue($"{chosenEnvironment ?? "Default"}.Impersonation@{plugin.Name}.User", out var user) &&
settings.TryGetValue($"{chosenEnvironment ?? "Default"}.Impersonation@{plugin.Name}.Password", out var password))
{
identity =
settings.TryGetValue($"{chosenEnvironment ?? "Default"}.Impersonation@{plugin.Name}.Domain", out var domain)
? SecurityTools.GetIdentityByName(userName: user.ToString(), password: password.ToString(), domainName: domain.ToString())
: SecurityTools.GetIdentityByName(userName: user.ToString(), password: password.ToString());
}
}
try
{
this.DatabaseSchema.Items.Clear();
var tablesNodeIndex = this.DatabaseSchema.Items.Add(new TreeViewItem()
{
Header = "Tables"
});
var tablesNode = (TreeViewItem)this.DatabaseSchema.Items[tablesNodeIndex];
foreach (var entity in dataEntities)
{
var entityNodeIndex = tablesNode.Items.Add(new TreeViewItem()
{
Header = entity.Name, Tag = entity
});
var fields = GetEntityFields(chosenProject, entity.Name);
foreach (var field in fields)
{
((TreeViewItem)tablesNode.Items[entityNodeIndex]).Items.Add(field);
}
}
var storedProceduresNodeIndex =
this.DatabaseSchema.Items.Add(new TreeViewItem()
{
Header = "Stored Procedures"
});
var storedProceduresNode = (TreeViewItem)this.DatabaseSchema.Items[storedProceduresNodeIndex];
if (System.Configuration.ConfigurationManager.ConnectionStrings[plugin.ConnectionStringName].ProviderName == "System.Data.SqlClient")
{
// this works only for Microsoft SQL Server
var sqlCommand = "SELECT OBJECT_NAME(OBJECT_ID) AS name, " +
"definition " +
"FROM sys.sql_modules " +
"WHERE objectproperty(OBJECT_ID, 'IsProcedure') = 1 " +
"ORDER BY OBJECT_NAME(OBJECT_ID)";
var data = identity != null?plugin.ExecuteSql(sqlCommand, identity) : plugin.ExecuteSql(sqlCommand);
if (data.TableName == "Error")
{
throw new SoluiNetPluginException($"Couldn't select Stored Procedures for plugin '{plugin.Name}': {data.DefaultView[0].Row[0]}");
}
foreach (DataRowView record in data.DefaultView)
{
storedProceduresNode.Items.Add(new TreeViewItem()
{
Header = record.Row["name"],
Tag = new StoredProcedure
{
Name = record.Row["name"].ToString(),
BodyDefinition = record.Row["definition"].ToString(),
},
});
}
}
var storedFunctionsNodeIndex =
this.DatabaseSchema.Items.Add(new TreeViewItem()
{
Header = "Stored Functions"
});
var storedFunctionsNode = (TreeViewItem)this.DatabaseSchema.Items[storedFunctionsNodeIndex];
if (System.Configuration.ConfigurationManager.ConnectionStrings[plugin.ConnectionStringName].ProviderName == "System.Data.SqlClient")
{
// this works only for Microsoft SQL Server
var sqlCommand = "SELECT DISTINCT " +
"s.name + '.' + o.name AS object_name, " +
"o.type_desc, " +
"m.definition " +
"FROM sys.sql_modules m " +
"INNER JOIN sys.objects o ON m.object_id = o.object_id " +
"INNER JOIN sys.schemas s ON o.schema_id = s.schema_id " +
"WHERE o.type_desc = 'SQL_SCALAR_FUNCTION'";
var data = identity != null?plugin.ExecuteSql(sqlCommand, identity) : plugin.ExecuteSql(sqlCommand);
if (data.TableName == "Error")
{
throw new SoluiNetPluginException($"Couldn't select Stored Functions for plugin '{plugin.Name}': {data.DefaultView[0].Row[0]}");
}
foreach (DataRowView record in data.DefaultView)
{
storedFunctionsNode.Items.Add(new TreeViewItem()
{
Header = record.Row["object_name"],
Tag = new StoredFunction
{
Name = record.Row["object_name"].ToString(),
BodyDefinition = record.Row["definition"].ToString(),
},
});
}
}
var viewsNodeIndex = this.DatabaseSchema.Items.Add(new TreeViewItem()
{
Header = "Views"
});
var viewsNode = (TreeViewItem)this.DatabaseSchema.Items[viewsNodeIndex];
if (System.Configuration.ConfigurationManager.ConnectionStrings[plugin.ConnectionStringName].ProviderName == "System.Data.SqlClient")
{
// this works only for Microsoft SQL Server
var sqlCommand = "SELECT name, definition " +
"FROM sys.objects obj " +
"JOIN sys.sql_modules mod ON mod.object_id = obj.object_id " +
"WHERE obj.type = 'V'";
var data = identity != null?plugin.ExecuteSql(sqlCommand, identity) : plugin.ExecuteSql(sqlCommand);
if (data.TableName == "Error")
{
throw new SoluiNetPluginException($"Couldn't select Views for plugin '{plugin.Name}': {data.DefaultView[0].Row[0]}");
}
foreach (DataRowView record in data.DefaultView)
{
viewsNode.Items.Add(new TreeViewItem()
{
Header = record.Row["name"],
Tag = new View
{
Name = record.Row["name"].ToString(),
BodyDefinition = record.Row["definition"].ToString(),
},
});
}
}
var scriptsNodeIndex = this.DatabaseSchema.Items.Add(new TreeViewItem()
{
Header = "Scripts"
});
var scriptsNode = (TreeViewItem)this.DatabaseSchema.Items[scriptsNodeIndex];
foreach (var sqlScript in sqlScripts)
{
scriptsNode.Items.Add(new TreeViewItem()
{
Header = sqlScript.Name, Tag = sqlScript
});
}
}
finally
{
identity?.Dispose();
}
}