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