private static object DataQueryFunction(string functionName, object[] functionArguments)
{
var providerConnectionString = functionArguments[0] as string;
var query = functionArguments[1] as string;
var limit = functionArguments.Length > 2 ? Convert.ToInt32(functionArguments[2]) : 100;
var outputVariable = functionArguments.Length > 3 ? functionArguments[3] as string : "$data_";
if (string.IsNullOrWhiteSpace(providerConnectionString))
{
throw new Exception("Argument 0: provider connection string is required");
}
if (string.IsNullOrWhiteSpace(query))
{
throw new Exception("Argument 1: query is required");
}
if (limit <= 0)
{
throw new Exception("Optional argument 2: limit is invalid");
}
if (!IsVariableNameValid(outputVariable, out var errorText))
{
throw new Exception("Optional argument 3: output variable " + errorText);
}
var tokens = providerConnectionString.Split(new[] { "|" }, StringSplitOptions.RemoveEmptyEntries);
var providerName = tokens[0];
var connectionString = tokens[1];
var dbProviderFactory = DbProviderFactories.GetFactory(providerName);
var row = 0;
using (var dbConnection = dbProviderFactory.CreateConnection())
{
dbConnection.ConnectionString = connectionString;
var dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query;
for (var ax = 4; ax < functionArguments.Length; ax++)
{
var dbParameter = dbCommand.CreateParameter();
dbParameter.ParameterName = "@" + (ax - 4);
dbParameter.Value = functionArguments[ax];
dbCommand.Parameters.Add(dbParameter);
}
dbConnection.Open();
using (var dbDataReader = dbCommand.ExecuteReader())
{
//Intentional: create empty arrays to return data
var numberOfColumns = dbDataReader.FieldCount;
for (var col = 0; col < numberOfColumns; col++)
{
VariableManager.CreateVariableArray(outputVariable + col, string.Empty, 0);
}
for (row = 0; dbDataReader.Read() && row < limit; row++)
{
for (var col = 0; col < numberOfColumns; col++)
{
var value = dbDataReader.IsDBNull(col) ? string.Empty : dbDataReader.GetValue(col);
VariableManager.AppendToVariableArray(outputVariable + col, value);
}
}
}
}
return(row);
}