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