static DbTableList TryExecute(DbSource dbSource) { Dev2Logger.Info("Get Database Tables. " + dbSource.DatabaseName, GlobalConstants.WarewolfInfo); var tables = new DbTableList(); Common.Utilities.PerformActionInsideImpersonatedContext(Common.Utilities.OrginalExecutingUser, () => { DataTable columnInfo = null; switch (dbSource.ServerType) { case enSourceType.SqlDatabase: { using (var connection = new SqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } default: { using (var connection = new MySqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } } if (columnInfo != null) { foreach (DataRow row in columnInfo.Rows) { var tableName = row["TABLE_NAME"] as string; var schema = row["TABLE_SCHEMA"] as string; tableName = '[' + tableName + ']'; var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema); if (dbTable == null) { dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List <IDbColumn>() }; tables.Items.Add(dbTable); } } } if (tables.Items.Count == 0) { tables.HasErrors = true; const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: " + "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}"; if (dbSource.AuthenticationType == AuthenticationType.User) { tables.Errors = string.Format(ErrorFormat, "SQL Authentication (User: '******')", "EXECUTE AS USER = '******';\r\n", "\r\nREVERT;"); } else { tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", ""); } } }); return(tables); }
/// <summary> /// Executes the service /// </summary> /// <param name="values">The values.</param> /// <param name="theWorkspace">The workspace.</param> /// <returns></returns> public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace) { Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(values == null) { throw new InvalidDataContractException("No parameter values provided."); } string serializedSource = null; StringBuilder tmp; values.TryGetValue("SharepointServer", out tmp); if(tmp != null) { serializedSource = tmp.ToString(); } if(string.IsNullOrEmpty(serializedSource)) { ExecuteMessage message = new ExecuteMessage(); message.HasError = true; message.SetMessage("No sharepoint server set."); Dev2Logger.Log.Debug("No sharepoint server set."); return serializer.SerializeToBuilder(message); } SharepointSource source; SharepointSource runtimeSource = null; try { source = serializer.Deserialize<SharepointSource>(serializedSource); if(source.ResourceID != Guid.Empty) { runtimeSource = ResourceCatalog.Instance.GetResource<SharepointSource>(theWorkspace.ID, source.ResourceID); if(runtimeSource == null) { var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID); runtimeSource = new SharepointSource(contents.ToXElement()); } } } catch(Exception e) { Dev2Logger.Log.Error(e); var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message); return serializer.SerializeToBuilder(res); } if(runtimeSource == null) { var res = new DbTableList("Invalid sharepoint server source"); Dev2Logger.Log.Debug("Invalid sharepoint server source"); return serializer.SerializeToBuilder(res); } if(string.IsNullOrEmpty(runtimeSource.Server)) { var res = new DbTableList("Invalid sharepoint server sent {0}.", serializedSource); Dev2Logger.Log.Debug(String.Format("Invalid sharepoint server sent {0}.", serializedSource)); return serializer.SerializeToBuilder(res); } try { Dev2Logger.Log.Info("Get Sharepoint Server Lists. " + source.Server); List<SharepointListTo> lists = runtimeSource.LoadLists(); return serializer.SerializeToBuilder(lists); } catch(Exception ex) { var tables = new DbTableList(ex); return serializer.SerializeToBuilder(tables); } }
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { var serializer = new Dev2JsonSerializer(); if (values == null) { throw new InvalidDataContractException(ErrorResource.NoParameter); } string database = null; values.TryGetValue("Database", out StringBuilder tmp); if (tmp != null) { database = tmp.ToString(); } if (string.IsNullOrEmpty(database)) { var res = new DbTableList("No database set."); Dev2Logger.Debug("No database set.", GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } DbSource dbSource; DbSource runtimeDbSource = null; try { dbSource = serializer.Deserialize <DbSource>(database); if (dbSource.ResourceID != Guid.Empty) { runtimeDbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID); } } catch (Exception e) { Dev2Logger.Error(e, GlobalConstants.WarewolfError); var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message); return(serializer.SerializeToBuilder(res)); } if (runtimeDbSource == null) { var res = new DbTableList("Invalid Database source"); Dev2Logger.Debug("Invalid Database source", GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server)) { var res = new DbTableList("Invalid database sent {0}.", database); Dev2Logger.Debug($"Invalid database sent {database}.", GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } try { return(serializer.SerializeToBuilder(TryExecute(dbSource))); } catch (Exception ex) { var tables = new DbTableList(ex); return(serializer.SerializeToBuilder(tables)); } }
static Dictionary<DbSource, DbTableList> CreateDatabases(int count, bool varcharNullable = false, bool intAsIdentity = false) { var result = new Dictionary<DbSource, DbTableList>(); for(var i = 0; i < count; i++) { var dbName = "Db" + i; var tables = new List<DbTable>(); for(var j = 0; j < 10; j++) { var columns = new List<IDbColumn>(); var colCount = ((j % 4) + 1) * (i + 1); for(var k = 0; k < colCount; k++) { var t = k % 4; switch(t) { case 0: columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.VarChar, MaxLength = 50, IsNullable = varcharNullable }); break; case 1: columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Int, IsAutoIncrement = intAsIdentity }); break; case 2: columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Money }); break; case 3: columns.Add(new DbColumn { ColumnName = dbName + "_Column_" + j + "_" + k, SqlDataType = SqlDbType.Float }); break; } } tables.Add(new DbTable { Schema = "MySchema", TableName = dbName + "_Table_" + j, Columns = columns }); } var tableList = new DbTableList(); tableList.Items.AddRange(tables); result.Add(new DbSource { ResourceID = Guid.NewGuid(), ResourceName = dbName, }, tableList); } return result; }
/// <summary> /// Executes the service /// </summary> /// <param name="values">The values.</param> /// <param name="theWorkspace">The workspace.</param> /// <returns></returns> public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace) { Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(values == null) { throw new InvalidDataContractException("No parameter values provided."); } string database = null; StringBuilder tmp; values.TryGetValue("Database", out tmp); if(tmp != null) { database = tmp.ToString(); } if(string.IsNullOrEmpty(database)) { var res = new DbTableList("No database set."); Dev2Logger.Log.Debug("No database set."); return serializer.SerializeToBuilder(res); } DbSource dbSource; DbSource runtimeDbSource = null; try { dbSource = serializer.Deserialize<DbSource>(database); if(dbSource.ResourceID != Guid.Empty) { runtimeDbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID); } } catch(Exception e) { Dev2Logger.Log.Error(e); var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message); return serializer.SerializeToBuilder(res); } if(runtimeDbSource == null) { var res = new DbTableList("Invalid Database source"); Dev2Logger.Log.Debug("Invalid Database source"); return serializer.SerializeToBuilder(res); } if(string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server)) { var res = new DbTableList("Invalid database sent {0}.", database); Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database)); return serializer.SerializeToBuilder(res); } try { Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName); var tables = new DbTableList(); DataTable columnInfo; switch(dbSource.ServerType) { case enSourceType.SqlDatabase: { using (var connection = new SqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } default: { using (var connection = new MySqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } } if(columnInfo != null) { foreach(DataRow row in columnInfo.Rows) { var tableName = row["TABLE_NAME"] as string; var schema = row["TABLE_SCHEMA"] as string; tableName = '[' + tableName + ']'; var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema); if(dbTable == null) { dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List<IDbColumn>() }; tables.Items.Add(dbTable); } } } if(tables.Items.Count == 0) { tables.HasErrors = true; const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: " + "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}"; if(dbSource.AuthenticationType == AuthenticationType.User) { tables.Errors = string.Format(ErrorFormat, "SQL Authentication (User: '******')", "EXECUTE AS USER = '******';\r\n", "\r\nREVERT;"); } else { tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", ""); } } return serializer.SerializeToBuilder(tables); } catch(Exception ex) { var tables = new DbTableList(ex); return serializer.SerializeToBuilder(tables); } }
static TestSqlBulkInsertDesignerViewModel CreateViewModel(ModelItem modelItem, Dictionary<DbSource, DbTableList> sources, IEventAggregator eventAggregator, IResourceModel resourceModel, bool configureFindSingle = false, string columnListErrors = "") { var sourceDefs = sources == null ? null : sources.Select(s => s.Key.ToXml().ToString()); var envModel = new Mock<IEnvironmentModel>(); envModel.Setup(e => e.Connection.WorkspaceID).Returns(Guid.NewGuid()); var resourceRepo = new Mock<IResourceRepository>(); envModel.Setup(e => e.Connection.ExecuteCommand(It.Is<StringBuilder>(s => s.Contains("FindSourcesByType")), It.IsAny<Guid>(), It.IsAny<Guid>())) .Returns(new StringBuilder(string.Format("<XmlData>{0}</XmlData>", sourceDefs == null ? "" : string.Join("\n", sourceDefs)))); // return the resource repository now ;) envModel.Setup(e => e.ResourceRepository).Returns(resourceRepo.Object); // setup the FindSourcesByType command if(sources != null) { var dbs = sources.Keys.ToList(); resourceRepo.Setup(r => r.FindSourcesByType<DbSource>(It.IsAny<IEnvironmentModel>(), enSourceType.SqlDatabase)).Returns(dbs); } var tableJson = new DbTableList(); // ReSharper disable ImplicitlyCapturedClosure resourceRepo.Setup(r => r.GetDatabaseTables(It.IsAny<DbSource>())).Callback((DbSource src) => // ReSharper restore ImplicitlyCapturedClosure { if(sources != null) { var tableList = sources[src]; tableJson = tableList; } // ReSharper disable ImplicitlyCapturedClosure }).Returns(() => tableJson); // ReSharper restore ImplicitlyCapturedClosure var columnsJson = new DbColumnList(); // ReSharper disable ImplicitlyCapturedClosure resourceRepo.Setup(r => r.GetDatabaseTableColumns(It.IsAny<DbSource>(), It.IsAny<DbTable>())).Callback((DbSource src, DbTable tbl) => // ReSharper restore ImplicitlyCapturedClosure { var tableName = tbl.TableName; if(sources != null) { var tables = sources[src]; var table = tables.Items.First(t => t.TableName == tableName.Trim(new[] { '"' })); var columnList = new DbColumnList(); columnList.Items.AddRange(table.Columns); if(!string.IsNullOrEmpty(columnListErrors)) { columnList.HasErrors = true; columnList.Errors = columnListErrors; } columnsJson = columnList; } // ReSharper disable ImplicitlyCapturedClosure }).Returns(() => columnsJson); // ReSharper restore ImplicitlyCapturedClosure if(configureFindSingle) { envModel.Setup(e => e.ResourceRepository.FindSingle(It.IsAny<Expression<Func<IResourceModel, bool>>>(), false)).Returns(resourceModel); } return new TestSqlBulkInsertDesignerViewModel(modelItem, envModel.Object, eventAggregator); }
static void VerifyTables(DbTableList expectedTables, List<DbTable> actualTables) { for(var i = 0; i < expectedTables.Items.Count; i++) { var expected = expectedTables.Items[i]; var actual = actualTables[i]; Assert.AreEqual(expected.TableName, actual.TableName); Assert.AreEqual(expected.Columns.Count, actual.Columns.Count); } }
/// <summary> /// Executes the service /// </summary> /// <param name="values">The values.</param> /// <param name="theWorkspace">The workspace.</param> /// <returns></returns> public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if (values == null) { throw new InvalidDataContractException("No parameter values provided."); } string database = null; StringBuilder tmp; values.TryGetValue("Database", out tmp); if (tmp != null) { database = tmp.ToString(); } if (string.IsNullOrEmpty(database)) { var res = new DbTableList("No database set."); Dev2Logger.Log.Debug("No database set."); return(serializer.SerializeToBuilder(res)); } DbSource dbSource; DbSource runtimeDbSource = null; try { dbSource = serializer.Deserialize <DbSource>(database); if (dbSource.ResourceID != Guid.Empty) { runtimeDbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID); } } catch (Exception e) { Dev2Logger.Log.Error(e); var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message); return(serializer.SerializeToBuilder(res)); } if (runtimeDbSource == null) { var res = new DbTableList("Invalid Database source"); Dev2Logger.Log.Debug("Invalid Database source"); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server)) { var res = new DbTableList("Invalid database sent {0}.", database); Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database)); return(serializer.SerializeToBuilder(res)); } try { Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName); var tables = new DbTableList(); DataTable columnInfo; using (var connection = new SqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } if (columnInfo != null) { foreach (DataRow row in columnInfo.Rows) { var tableName = row["TABLE_NAME"] as string; var schema = row["TABLE_SCHEMA"] as string; tableName = '[' + tableName + ']'; var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema); if (dbTable == null) { dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List <IDbColumn>() }; tables.Items.Add(dbTable); } } } if (tables.Items.Count == 0) { tables.HasErrors = true; const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: " + "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}"; if (dbSource.AuthenticationType == AuthenticationType.User) { tables.Errors = string.Format(ErrorFormat, "SQL Authentication (User: '******')", "EXECUTE AS USER = '******';\r\n", "\r\nREVERT;"); } else { tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", ""); } } return(serializer.SerializeToBuilder(tables)); } catch (Exception ex) { var tables = new DbTableList(ex); return(serializer.SerializeToBuilder(tables)); } }
/// <summary> /// Executes the service /// </summary> /// <param name="values">The values.</param> /// <param name="theWorkspace">The workspace.</param> /// <returns></returns> public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if (values == null) { throw new InvalidDataContractException("No parameter values provided."); } string serializedSource = null; StringBuilder tmp; values.TryGetValue("SharepointServer", out tmp); if (tmp != null) { serializedSource = tmp.ToString(); } if (string.IsNullOrEmpty(serializedSource)) { ExecuteMessage message = new ExecuteMessage(); message.HasError = true; message.SetMessage("No sharepoint server set."); Dev2Logger.Log.Debug("No sharepoint server set."); return(serializer.SerializeToBuilder(message)); } SharepointSource source; SharepointSource runtimeSource = null; try { source = serializer.Deserialize <SharepointSource>(serializedSource); if (source.ResourceID != Guid.Empty) { runtimeSource = ResourceCatalog.Instance.GetResource <SharepointSource>(theWorkspace.ID, source.ResourceID); if (runtimeSource == null) { var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID); runtimeSource = new SharepointSource(contents.ToXElement()); } } } catch (Exception e) { Dev2Logger.Log.Error(e); var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message); return(serializer.SerializeToBuilder(res)); } if (runtimeSource == null) { var res = new DbTableList("Invalid sharepoint server source"); Dev2Logger.Log.Debug("Invalid sharepoint server source"); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(runtimeSource.Server)) { var res = new DbTableList("Invalid sharepoint server sent {0}.", serializedSource); Dev2Logger.Log.Debug(String.Format("Invalid sharepoint server sent {0}.", serializedSource)); return(serializer.SerializeToBuilder(res)); } try { Dev2Logger.Log.Info("Get Sharepoint Server Lists. " + source.Server); List <SharepointListTo> lists = runtimeSource.LoadLists(); return(serializer.SerializeToBuilder(lists)); } catch (Exception ex) { var tables = new DbTableList(ex); return(serializer.SerializeToBuilder(tables)); } }
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { var serializer = new Dev2JsonSerializer(); if (values == null) { throw new InvalidDataContractException(ErrorResource.NoParameter); } string serializedSource = null; values.TryGetValue("SharepointServer", out StringBuilder tmp); if (tmp != null) { serializedSource = tmp.ToString(); } if (string.IsNullOrEmpty(serializedSource)) { var message = new ExecuteMessage(); message.HasError = true; message.SetMessage(ErrorResource.NoSharepointServerSet); Dev2Logger.Debug(ErrorResource.NoSharepointServerSet, GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(message)); } SharepointSource source; SharepointSource runtimeSource = null; try { source = serializer.Deserialize <SharepointSource>(serializedSource); if (source.ResourceID != Guid.Empty) { runtimeSource = ResourceCatalog.Instance.GetResource <SharepointSource>(theWorkspace.ID, source.ResourceID); if (runtimeSource == null) { var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, source.ResourceID); runtimeSource = new SharepointSource(contents.ToXElement()); } } } catch (Exception e) { Dev2Logger.Error(e, GlobalConstants.WarewolfError); var res = new DbTableList("Invalid JSON data for sharepoint server parameter. Exception: {0}", e.Message); return(serializer.SerializeToBuilder(res)); } if (runtimeSource == null) { var res = new DbTableList(ErrorResource.InvalidSharepointServerSource); Dev2Logger.Debug(ErrorResource.InvalidSharepointServerSource, GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(runtimeSource.Server)) { var res = new DbTableList(ErrorResource.InvalidSharepointServerSent, serializedSource); Dev2Logger.Debug(string.Format(ErrorResource.InvalidSharepointServerSent, serializedSource), GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } try { Dev2Logger.Info("Get Sharepoint Server Lists. " + source.Server, GlobalConstants.WarewolfDebug); var lists = runtimeSource.LoadLists(); return(serializer.SerializeToBuilder(lists)); } catch (Exception ex) { var tables = new DbTableList(ex); return(serializer.SerializeToBuilder(tables)); } }