public override async Task Run()
{
var findObjectHelper = new FindObjectHelper();
var columnTypes = await findObjectHelper.ExtractColumnTypes(HubCommunicator, ActivityContext);
if (columnTypes == null)
{
RaiseError("No column types crate found.");
return;
}
var queryPayloadValue = ExtractSqlQuery();
if (queryPayloadValue == null)
{
RaiseError("No Sql Query payload crate found.");
return;
}
var connectionString = await ExtractConnectionString();
var query = BuildQuery(connectionString, queryPayloadValue, columnTypes);
var dbProvider = DbProvider.GetDbProvider(DefaultDbProvider);
var data = dbProvider.ExecuteQuery(query);
var payloadCM = BuildStandardPayloadData(data, columnTypes);
var payloadCMCrate = Crate.FromContent("Sql Query Result", payloadCM);
Payload.Add(payloadCMCrate);
Success();
}
private async Task <List <KeyValueDTO> > MatchColumnsForSelectedObject(string selectedObject)
{
var findObjectHelper = new FindObjectHelper();
var columnDefinitions = await ExtractColumnDefinitions();
var columnTypeMap = await findObjectHelper.ExtractColumnTypes(HubCommunicator, ActivityContext);
var matchedColumns = findObjectHelper.MatchColumnsForSelectedObject(
columnDefinitions, selectedObject, columnTypeMap);
return(matchedColumns);
}
private StandardPayloadDataCM BuildStandardPayloadData(Table data, Dictionary <string, DbType> columnTypes)
{
var findObjectHelper = new FindObjectHelper();
var payloadCM = new StandardPayloadDataCM();
var tableData = new List <PayloadObjectDTO>();
if (data.Rows != null)
{
foreach (var row in data.Rows)
{
if (row.Values == null)
{
continue;
}
var payloadObject = new PayloadObjectDTO();
foreach (var fieldValue in row.Values)
{
var columnName = data.TableInfo.ToString() + "." + fieldValue.Field;
DbType dbType;
if (!columnTypes.TryGetValue(columnName, out dbType))
{
throw new ApplicationException("No column data type found.");
}
payloadObject.PayloadObject.Add(
new KeyValueDTO()
{
Key = fieldValue.Field,
Value = findObjectHelper.ConvertValueToString(fieldValue.Value, dbType)
}
);
}
tableData.Add(payloadObject);
}
}
payloadCM.PayloadObjects.AddRange(tableData);
return(payloadCM);
}
public Connect_To_Sql_v1(ICrateManager crateManager)
: base(crateManager)
{
FindObjectHelper = new FindObjectHelper();
}
