/// <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) { 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(); } Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if (string.IsNullOrEmpty(serializedSource)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage("No sharepoint server set"); Dev2Logger.Log.Debug("No sharepoint server set."); return(serializer.SerializeToBuilder(res)); } try { var sharepointSource = serializer.Deserialize <SharepointSource>(serializedSource); var result = sharepointSource.TestConnection(); var sharepointSourceTo = new SharepointSourceTo { TestMessage = result, IsSharepointOnline = sharepointSource.IsSharepointOnline }; return(serializer.SerializeToBuilder(sharepointSourceTo)); } catch (Exception ex) { Dev2Logger.Log.Error(ex); var res = new DbColumnList(ex); return(serializer.SerializeToBuilder(res)); } }
public DbHelper(BaseConfig config) { this.Config = config; DbColumnList = GetDbColumns(Config.ConnectionString, Config.DbDataBase, Config.TableName, null); FilterDbColumnList = Filter(DbColumnList, Config.NoExistFields); var tmp = DbColumnList.Where(p => p.IsPrimaryKey || p.ToString().ToLower().IndexOf("pkid") > -1); if (tmp == null || tmp.Count() <= 0) { PrimaryKey = DbColumnList.First().ColumnName; } else { PrimaryKey = DbColumnList.Where(p => p.IsPrimaryKey || p.ToString().ToLower().IndexOf("pkid") > -1).First().ColumnName; } ClassName = Config.TableName.Replace("_TBL", "").ToUpper(); TableCommit = GetDbTables(Config.ConnectionString, Config.DbDataBase, Config.TableName)[0].Commit; }
/// <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) { 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(); } Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(string.IsNullOrEmpty(serializedSource)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage("No sharepoint server set"); Dev2Logger.Log.Debug("No sharepoint server set."); return serializer.SerializeToBuilder(res); } try { var sharepointSource = serializer.Deserialize<SharepointSource>(serializedSource); var result = sharepointSource.TestConnection(); var sharepointSourceTo = new SharepointSourceTo { TestMessage = result, IsSharepointOnline = sharepointSource.IsSharepointOnline }; return serializer.SerializeToBuilder(sharepointSourceTo); } catch(Exception ex) { Dev2Logger.Log.Error(ex); var res = new DbColumnList(ex); return serializer.SerializeToBuilder(res); } }
static void AddDbColumn(DbSource dbSource, DbColumnList dbColumns, DataRow row) { var columnName = row["ColumnName"] as string; var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"]; var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"]; var dbColumn = new DbColumn { ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity }; var typeValue = dbSource.ServerType == enSourceType.SqlDatabase ? row["DataTypeName"] as string : ((Type)row["DataType"]).Name; if (Enum.TryParse(typeValue, true, out SqlDbType sqlDataType)) { dbColumn.SqlDataType = sqlDataType; } var columnLength = row["ColumnSize"] as int? ?? -1; dbColumn.MaxLength = columnLength; dbColumns.Items.Add(dbColumn); }
/// <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) { if(values == null) { throw new InvalidDataContractException("No parameter values provided."); } string database = null; string tableName = null; string schema = null; StringBuilder tmp; values.TryGetValue("Database", out tmp); if(tmp != null) { database = tmp.ToString(); } values.TryGetValue("TableName", out tmp); if(tmp != null) { tableName = tmp.ToString(); } values.TryGetValue("Schema", out tmp); if(tmp != null) { schema = tmp.ToString(); } Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(string.IsNullOrEmpty(database)) { var res = new DbColumnList("No database set."); Dev2Logger.Log.Debug("No database set."); return serializer.SerializeToBuilder(res); } if(string.IsNullOrEmpty(tableName)) { var res = new DbColumnList("No table name set."); Dev2Logger.Log.Debug("No table name set."); return serializer.SerializeToBuilder(res); } Dev2Logger.Log.Info(String.Format("Get Database Columns For Table. Database:{0} Schema:{1} Table{2}" ,database,schema,tableName)); try { var dbSource = serializer.Deserialize<DbSource>(database); var runtTimedbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID); DataTable columnInfo; switch (dbSource.ServerType) { case enSourceType.MySqlDatabase: { using (var connection = new MySqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[","").Replace("]","") + " Limit 1 "; using (var sqlcmd = new MySqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } default: { using (var connection = new SqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); // GUTTED TO RETURN ALL REQUIRED DATA ;) if (schema == null) { schema = string.Empty; } var sql = @"select top 1 * from " + schema.Trim('"') + "." + tableName.Trim('"'); using (var sqlcmd = new SqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } } var dbColumns = new DbColumnList(); if(columnInfo != null) { foreach(DataRow row in columnInfo.Rows) { var columnName = row["ColumnName"] as string; var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"]; var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"]; var dbColumn = new DbColumn { ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity }; SqlDbType sqlDataType; var typeValue = dbSource.ServerType == enSourceType.SqlDatabase? row["DataTypeName"] as string:((Type)row["DataType"]).Name; if(Enum.TryParse(typeValue, true, out sqlDataType)) { dbColumn.SqlDataType = sqlDataType; } var columnLength = row["ColumnSize"] is int ? (int)row["ColumnSize"] : -1; dbColumn.MaxLength = columnLength; dbColumns.Items.Add(dbColumn); } } return serializer.SerializeToBuilder(dbColumns); } catch(Exception ex) { Dev2Logger.Log.Error(ex); var res = new DbColumnList(ex); return serializer.SerializeToBuilder(res); } }
/// <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) { if (values == null) { throw new InvalidDataContractException("No parameter values provided."); } string database = null; string tableName = null; string schema = null; StringBuilder tmp; values.TryGetValue("Database", out tmp); if (tmp != null) { database = tmp.ToString(); } values.TryGetValue("TableName", out tmp); if (tmp != null) { tableName = tmp.ToString(); } values.TryGetValue("Schema", out tmp); if (tmp != null) { schema = tmp.ToString(); } Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if (string.IsNullOrEmpty(database)) { var res = new DbColumnList("No database set."); Dev2Logger.Log.Debug("No database set."); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(tableName)) { var res = new DbColumnList("No table name set."); Dev2Logger.Log.Debug("No table name set."); return(serializer.SerializeToBuilder(res)); } Dev2Logger.Log.Info(String.Format("Get Database Columns For Table. Database:{0} Schema:{1} Table{2}", database, schema, tableName)); try { var dbSource = serializer.Deserialize <DbSource>(database); var runtTimedbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID); DataTable columnInfo; switch (dbSource.ServerType) { case enSourceType.MySqlDatabase: { using (var connection = new MySqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 "; using (var sqlcmd = new MySqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } default: { using (var connection = new SqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); // GUTTED TO RETURN ALL REQUIRED DATA ;) if (schema == null) { schema = string.Empty; } var sql = @"select top 1 * from " + schema.Trim('"') + "." + tableName.Trim('"'); using (var sqlcmd = new SqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } } var dbColumns = new DbColumnList(); if (columnInfo != null) { foreach (DataRow row in columnInfo.Rows) { var columnName = row["ColumnName"] as string; var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"]; var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"]; var dbColumn = new DbColumn { ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity }; SqlDbType sqlDataType; var typeValue = dbSource.ServerType == enSourceType.SqlDatabase? row["DataTypeName"] as string:((Type)row["DataType"]).Name; if (Enum.TryParse(typeValue, true, out sqlDataType)) { dbColumn.SqlDataType = sqlDataType; } var columnLength = row["ColumnSize"] is int?(int)row["ColumnSize"] : -1; dbColumn.MaxLength = columnLength; dbColumns.Items.Add(dbColumn); } } return(serializer.SerializeToBuilder(dbColumns)); } catch (Exception ex) { Dev2Logger.Log.Error(ex); var res = new DbColumnList(ex); return(serializer.SerializeToBuilder(res)); } }
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { if (values == null) { throw new InvalidDataContractException(ErrorResource.NoParameter); } string serializedSource = null; string listName = null; string editableOnly = null; values.TryGetValue("SharepointServer", out StringBuilder tmp); if (tmp != null) { serializedSource = tmp.ToString(); } values.TryGetValue("ListName", out tmp); if (tmp != null) { listName = tmp.ToString(); } values.TryGetValue("OnlyEditable", out tmp); if (tmp != null) { editableOnly = tmp.ToString(); } var serializer = new Dev2JsonSerializer(); if (string.IsNullOrEmpty(serializedSource)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage(ErrorResource.NoSharepointServerSet); Dev2Logger.Debug(ErrorResource.NoSharepointServerSet, GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(listName)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage(ErrorResource.NoSharepointListNameSet); Dev2Logger.Debug(ErrorResource.NoSharepointListNameSet, GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } var editableFieldsOnly = false; if (!string.IsNullOrEmpty(editableOnly)) { editableFieldsOnly = serializer.Deserialize <bool>(editableOnly); } try { listName = serializer.Deserialize <string>(listName); var sharepointSource = serializer.Deserialize <SharepointSource>(serializedSource); var source = ResourceCatalog.Instance.GetResource <SharepointSource>(theWorkspace.ID, sharepointSource.ResourceID); if (source == null) { var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, sharepointSource.ResourceID); source = new SharepointSource(contents.ToXElement()); } var fields = source.LoadFieldsForList(listName, editableFieldsOnly); return(serializer.SerializeToBuilder(fields)); } catch (Exception ex) { Dev2Logger.Error(ex, GlobalConstants.WarewolfError); var res = new DbColumnList(ex); return(serializer.SerializeToBuilder(res)); } }
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); }
public DbColumn GetCol(string columnname) { return(DbColumnList.Where(p => p.ColumnName == columnname).First()); }
/// <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) { if(values == null) { throw new InvalidDataContractException("No parameter values provided."); } string serializedSource = null; string listName = null; string editableOnly = null; StringBuilder tmp; values.TryGetValue("SharepointServer", out tmp); if(tmp != null) { serializedSource = tmp.ToString(); } values.TryGetValue("ListName", out tmp); if(tmp != null) { listName = tmp.ToString(); } values.TryGetValue("OnlyEditable", out tmp); if (tmp != null) { editableOnly = tmp.ToString(); } Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(string.IsNullOrEmpty(serializedSource)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage("No sharepoint server set"); Dev2Logger.Log.Debug("No sharepoint server set."); return serializer.SerializeToBuilder(res); } if(string.IsNullOrEmpty(listName)) { var res = new ExecuteMessage(); res.HasError = true; res.SetMessage("No sharepoint list name set"); Dev2Logger.Log.Debug("No sharepoint list name set."); return serializer.SerializeToBuilder(res); } var editableFieldsOnly = false; if(!string.IsNullOrEmpty(editableOnly)) { editableFieldsOnly = serializer.Deserialize<bool>(editableOnly); } try { listName = serializer.Deserialize<string>(listName); var sharepointSource = serializer.Deserialize<SharepointSource>(serializedSource); var source = ResourceCatalog.Instance.GetResource<SharepointSource>(theWorkspace.ID, sharepointSource.ResourceID); if (source == null) { var contents = ResourceCatalog.Instance.GetResourceContents(theWorkspace.ID, sharepointSource.ResourceID); source = new SharepointSource(contents.ToXElement()); } List<ISharepointFieldTo> fields = source.LoadFieldsForList(listName, editableFieldsOnly); return serializer.SerializeToBuilder(fields); } catch(Exception ex) { Dev2Logger.Log.Error(ex); var res = new DbColumnList(ex); return serializer.SerializeToBuilder(res); } }
public override StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace) { if (values == null) { throw new InvalidDataContractException(ErrorResource.NoParameter); } string database = null; string tableName = null; string schema = null; values.TryGetValue("Database", out StringBuilder tmp); if (tmp != null) { database = tmp.ToString(); } values.TryGetValue("TableName", out tmp); if (tmp != null) { tableName = tmp.ToString(); } values.TryGetValue("Schema", out tmp); if (tmp != null) { schema = tmp.ToString(); } var serializer = new Dev2JsonSerializer(); if (string.IsNullOrEmpty(database)) { var res = new DbColumnList("No database set."); Dev2Logger.Debug("No database set.", GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } if (string.IsNullOrEmpty(tableName)) { var res = new DbColumnList("No table name set."); Dev2Logger.Debug("No table name set.", GlobalConstants.WarewolfDebug); return(serializer.SerializeToBuilder(res)); } Dev2Logger.Info($"Get Database Columns For Table. Database:{database} Schema:{schema} Table{tableName}", GlobalConstants.WarewolfInfo); try { var dbSource = serializer.Deserialize <DbSource>(database); var runtTimedbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID); DataTable columnInfo; switch (dbSource.ServerType) { case enSourceType.MySqlDatabase: { using (var connection = new MySqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 "; using (var sqlcmd = new MySqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } case enSourceType.SQLiteDatabase: { using (var connection = new SQLiteConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 "; using (var sqlcmd = new SQLiteCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } case enSourceType.Oracle: { using (var connection = new OracleConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 "; using (var sqlcmd = new OracleCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } case enSourceType.ODBC: { using (var connection = new OdbcConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 "; using (var sqlcmd = new OdbcCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } default: { using (var connection = new SqlConnection(runtTimedbSource.ConnectionString)) { // Connect to the database then retrieve the schema information. connection.Open(); // GUTTED TO RETURN ALL REQUIRED DATA ;) if (schema == null) { schema = string.Empty; } var sql = @"select top 1 * from " + schema.Trim('"') + "." + tableName.Trim('"'); using (var sqlcmd = new SqlCommand(sql, connection)) { // force it closed so we just get the proper schema ;) using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection)) { columnInfo = sdr.GetSchemaTable(); } } } break; } } var dbColumns = new DbColumnList(); if (columnInfo != null) { foreach (DataRow row in columnInfo.Rows) { AddDbColumn(dbSource, dbColumns, row); } } return(serializer.SerializeToBuilder(dbColumns)); } catch (Exception ex) { Dev2Logger.Error(ex, GlobalConstants.WarewolfError); var res = new DbColumnList(ex); return(serializer.SerializeToBuilder(res)); } }