/// <summary> /// This performns a lookup directly against the underlying data source, returns the result, and adds the result to cache. /// </summary> /// <param name="table"></param> /// <param name="filters"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public async Task <ICollection <object[]> > LookupRow(Table table, List <Filter> filters, CancellationToken cancellationToken) { try { var restFunction = (WebService)table; var baseRow = new object[table.Columns.Count]; var response = await GetWebServiceResponse(restFunction, filters, cancellationToken); var responseStatusOrdinal = restFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.ResponseStatus); var responseSuccessOrdinal = restFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.ResponseSuccess); var responseDataOrdinal = restFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.ResponseData); var urlOrdinal = restFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.Url); var errorOrdinal = restFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.Error); var lookupResult = new List <object[]>(); if (responseStatusOrdinal >= 0) { baseRow[responseStatusOrdinal] = response.statusCode; } if (responseSuccessOrdinal >= 0) { baseRow[responseSuccessOrdinal] = response.isSuccess; } if (urlOrdinal >= 0) { baseRow[urlOrdinal] = response.url; } foreach (var column in restFunction.Columns.Where(c => c.IsInput)) { if (filters != null) { var filter = filters.Where(c => c.Column1.Name == column.Name).ToArray(); if (!filter.Any()) { baseRow[restFunction.GetOrdinal(column)] = column.DefaultValue; } else { baseRow[restFunction.GetOrdinal(column)] = filter.First().Value2; } } else { baseRow[restFunction.GetOrdinal(column)] = column.DefaultValue; } } if (response.isSuccess) { FileHandlerBase fileHanlder = null; if (restFunction.FormatType == ETypeCode.Json) { fileHanlder = new FileHandlerJson(restFunction, restFunction.RowPath); } if (restFunction.FormatType == ETypeCode.Xml) { fileHanlder = new FileHandlerXml(restFunction, restFunction.RowPath); } if (fileHanlder != null) { await fileHanlder.SetStream(response.response, null); return(await fileHanlder.GetAllRows(baseRow)); } } else { if (errorOrdinal >= 0) { var reader = new StreamReader(response.response); var errorString = await reader.ReadToEndAsync(); baseRow[errorOrdinal] = errorString; } return(new[] { baseRow }); } throw new ConnectionException($"The lookup failed as the web service format type {restFunction.FormatType} is not currently supported."); } catch (Exception ex) { throw new ConnectionException($"Lookup on the web service {table.Name} failed. {ex.Message}", ex); } }
/// <summary> /// Retrieves web services information. The RestfulUri must be passed through the properties. This should be in the format http://sitename/{value1}/{value2} where the names between {} are the names for input parameters. The properties can also contain default values for the parameters. /// </summary> /// <param name="table"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public override async Task <Table> GetSourceTableInfo(Table table, CancellationToken cancellationToken) { try { var restFunction = (WebService)table; if (string.IsNullOrEmpty(restFunction.RestfulUri)) { throw new ConnectionException($"The restful uri has not been specified."); } var restfulUri = restFunction.RestfulUri; var rowPath = restFunction.RowPath; var newRestFunction = new WebService { Name = restFunction.Name, Description = "", RestfulUri = restfulUri, LogicalName = restFunction.Name, FormatType = restFunction.FormatType }; //The new datatable that will contain the table schema newRestFunction.Columns.Clear(); TableColumn col; //use the regex to extract items in uri between { }. These will be input columns var match = Regex.Match(restfulUri, @"\{([^}]+)\}"); while (match.Success) { var name = match.Groups[1].Value; col = new TableColumn { //add the basic properties Name = name, IsInput = true, LogicalName = name, DataType = ETypeCode.String, DeltaType = TableColumn.EDeltaType.NaturalKey, MaxLength = 1024, Description = "Url Parameter " + name, AllowDbNull = true, IsUnique = false }; //Copy the inputvalue from the table input. This allows the preview table function below to get sample data. var originalColumn = table.Columns.SingleOrDefault(c => c.Name == col.Name); if (originalColumn != null) { var inputValue = originalColumn.DefaultValue; col.DefaultValue = inputValue; } newRestFunction.Columns.Add(col); match = match.NextMatch(); } //This column is use to capture the entire response from the web services call. col = new TableColumn() { Name = "Response", IsInput = false, LogicalName = "Response", DataType = newRestFunction.FormatType, DeltaType = TableColumn.EDeltaType.ResponseData, MaxLength = null, Description = "Response content from the service", AllowDbNull = true, IsUnique = false }; newRestFunction.Columns.Add(col); col = new TableColumn() { Name = "ResponseStatusCode", IsInput = false, LogicalName = "ResponseStatusCode", DataType = ETypeCode.String, DeltaType = TableColumn.EDeltaType.ResponseStatus, MaxLength = null, Description = "The status code returned by the service", AllowDbNull = true, IsUnique = false }; newRestFunction.Columns.Add(col); col = new TableColumn() { Name = "ResponseSuccess", IsInput = false, LogicalName = "ResponseSuccess", DataType = ETypeCode.Boolean, DeltaType = TableColumn.EDeltaType.ResponseSuccess, MaxLength = null, Description = "Is the web service call successful.", AllowDbNull = true, IsUnique = false }; newRestFunction.Columns.Add(col); col = new TableColumn() { Name = "ResponseError", IsInput = false, LogicalName = "ResponseError", DataType = ETypeCode.Boolean, DeltaType = TableColumn.EDeltaType.Error, MaxLength = null, Description = "Error message calling the web service.", AllowDbNull = true, IsUnique = false }; newRestFunction.Columns.Add(col); col = new TableColumn() { Name = "Url", IsInput = false, LogicalName = "Url", DataType = ETypeCode.Boolean, DeltaType = TableColumn.EDeltaType.Url, MaxLength = null, Description = "Url used to call the web service.", AllowDbNull = true, IsUnique = false }; newRestFunction.Columns.Add(col); var query = new SelectQuery(); query.Columns.Add(new SelectColumn(new TableColumn("Response"))); query.Columns.Add(new SelectColumn(new TableColumn("ResponseSuccess"))); query.Table = newRestFunction.Name; query.Rows = 1; if (newRestFunction.Columns.Count > 0) { var data = await GetPreview(newRestFunction, query, cancellationToken); var reader = data.Data; var row = reader[0]; var success = (bool)row[newRestFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.ResponseSuccess)]; if (!success) { var url = (string)row[newRestFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.Url)]; var error = (string)row[newRestFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.Error)]; throw new ConnectionException($"The web service called failed. Url used: {url}. Response Error: {error}."); } var dataString = (string)row[newRestFunction.GetDeltaColumnOrdinal(TableColumn.EDeltaType.ResponseData)]; var byteArray = Encoding.ASCII.GetBytes(dataString); var dataStream = new MemoryStream(byteArray); ICollection <TableColumn> fileColumns = null; if (newRestFunction.FormatType == ETypeCode.Json) { var jsonHandler = new FileHandlerJson(restFunction, rowPath); fileColumns = await jsonHandler.GetSourceColumns(dataStream); } if (newRestFunction.FormatType == ETypeCode.Xml) { var xmlHandler = new FileHandlerXml(restFunction, rowPath); fileColumns = await xmlHandler.GetSourceColumns(dataStream); } if (fileColumns != null) { foreach (var column in fileColumns) { newRestFunction.Columns.Add(column); } } } return(newRestFunction); } catch (Exception ex) { throw new ConnectionException($"Get web service information for {table.Name} failed. {ex.Message}", ex); } }
public override async Task <Table> GetSourceTableInfo(Table originalTable, CancellationToken cancellationToken) { try { var flatFile = (FlatFile)originalTable; if (flatFile.FileConfiguration == null || flatFile.FileSample == null) { throw new ConnectionException($"The properties have not been set to import the flat files structure. Required properties are (FileFormat)FileFormat and (Stream)FileSample."); } var stream = new MemoryStream(); var writer = new StreamWriter(stream); writer.Write(flatFile.FileSample); writer.Flush(); stream.Position = 0; FileHandlerBase fileHandler = null; switch (flatFile.FormatType) { case ETypeCode.Json: fileHandler = new FileHandlerJson(flatFile, flatFile.RowPath); break; case ETypeCode.Text: fileHandler = new FileHandlerText(flatFile, flatFile.FileConfiguration); break; case ETypeCode.Xml: fileHandler = new FileHandlerXml(flatFile, flatFile.RowPath); break; default: throw new ConnectionException($"The source type {flatFile.FormatType} is not currently supported."); } var columns = await fileHandler.GetSourceColumns(stream); //The new datatable that will contain the table schema var newFlatFile = new FlatFile(); flatFile.Name = originalTable.Name; newFlatFile.Columns.Clear(); newFlatFile.LogicalName = newFlatFile.Name; newFlatFile.Description = ""; newFlatFile.FileConfiguration = flatFile.FileConfiguration; foreach (var column in columns) { newFlatFile.Columns.Add(column); } var col = new TableColumn() { //add the basic properties Name = "FileName", LogicalName = "FileName", IsInput = false, DataType = ETypeCode.String, DeltaType = TableColumn.EDeltaType.FileName, Description = "The name of the file the record was loaded from.", AllowDbNull = false, IsUnique = false }; newFlatFile.Columns.Add(col); return(newFlatFile); } catch (Exception ex) { throw new ConnectionException($"Failed to import the file structure. {ex.Message}", ex); } }