public PowerQueryResponse Execute(PowerQueryCommand powerQueryCommand)
{
Command command = null;
PowerQueryResponse powerQueryResponse = new PowerQueryResponse();
CommandCredentials commandCredentials = new CommandCredentials();
string mashup;
try
{
if (powerQueryCommand.Credentials != null && powerQueryCommand.Credentials.Count > 0)
{
foreach (Credential credential in powerQueryCommand.Credentials)
{
if (credential is CredentialFile credentialFile)
{
commandCredentials.SetCredentialFile(credentialFile.Path);
}
else if (credential is CredentialFolder credentialFolder)
{
commandCredentials.SetCredentialFolder(credentialFolder.Path);
}
else if (credential is CredentialWeb credentialWeb)
{
commandCredentials.SetCredentialWeb(credentialWeb.Url);
}
else if (credential is CredentialSQL credentialSQL)
{
commandCredentials.SetCredentialSQL(credentialSQL.SQL, credentialSQL.Username, credentialSQL.Password);
}
else if (credential is CredentialOData credentialOData)
{
commandCredentials.SetCredentialOData(((CredentialOData)credential).Url, credentialOData.Username, credentialOData.Password);
}
else
{
throw new NotImplementedException("This Credential kind is not supported for now.");
}
}
}
command = new Command(commandCredentials);
DataTable dataTable = null;
if (powerQueryCommand.Queries != null && powerQueryCommand.Queries.Count > 0)
{
if (powerQueryCommand.Queries.Count == 1)
{
powerQueryCommand.QueryName = powerQueryCommand.Queries[0].Name;
}
mashup = "section Section1;\n\r";
foreach (Query q in powerQueryCommand.Queries)
{
string name;
if (q.Name.Contains(" "))
{
name = string.Format("#\"{0}\"", q.Name);
}
else
{
name = q.Name;
}
mashup += string.Format("\n\rshared {0} = {1};", name, q.Formula);
}
}
else
{
mashup = powerQueryCommand.Mashup;
}
dataTable = command.Execute(powerQueryCommand.QueryName, mashup);
if (powerQueryCommand.ExecuteOutputFlags == 0 && powerQueryCommand.SqlConnectionString != null)
{
powerQueryCommand.ExecuteOutputFlags = ExecuteOutputFlags.Sql;
}
else if (powerQueryCommand.ExecuteOutputFlags == 0)
{
powerQueryCommand.ExecuteOutputFlags = ExecuteOutputFlags.DataTable | ExecuteOutputFlags.Xml;
}
if (isRemote)
{
powerQueryResponse.DataTableFile = $"{powerQueryCommand.TempPath}{Guid.NewGuid()}.xml";
File.WriteAllText(powerQueryResponse.DataTableFile, dataTable.ToXML());
}
else
{
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.DataTable))
{
powerQueryResponse.DataTable = dataTable;
}
}
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Csv) || !string.IsNullOrWhiteSpace(powerQueryCommand.CsvFileName))
{
powerQueryResponse.Csv = dataTable.ToDelimitedFile(',', true);
if (!string.IsNullOrWhiteSpace(powerQueryCommand.CsvFileName))
{
File.WriteAllText(powerQueryCommand.CsvFileName, powerQueryResponse.Csv);
}
if (isRemote)
{
powerQueryResponse.Csv = null;
}
}
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Html) || !string.IsNullOrWhiteSpace(powerQueryCommand.HtmlFileName))
{
powerQueryResponse.Html = dataTable.ToHTML();
if (!string.IsNullOrWhiteSpace(powerQueryCommand.HtmlFileName))
{
File.WriteAllText(powerQueryCommand.HtmlFileName, powerQueryResponse.Html);
}
if (isRemote)
{
powerQueryResponse.Html = null;
}
}
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Json) || !string.IsNullOrWhiteSpace(powerQueryCommand.JsonFileName))
{
powerQueryResponse.Json = dataTable.ToContentJSON();
if (!string.IsNullOrWhiteSpace(powerQueryCommand.JsonFileName))
{
File.WriteAllText(powerQueryCommand.JsonFileName, powerQueryResponse.Json);
}
if (isRemote)
{
powerQueryResponse.Json = null;
}
}
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Sql))
{
if (powerQueryCommand.SqlConnectionString == null)
{
throw new InvalidOperationException("Cannot output to SQL. SqlConnectionString must be defined.");
}
dataTable.TableName = powerQueryCommand.SqlTableName;
OutputToSQL(powerQueryCommand, dataTable);
}
if (powerQueryCommand.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Xml) || !string.IsNullOrWhiteSpace(powerQueryCommand.XmlFileName))
{
powerQueryResponse.Xml = dataTable.ToContentXML();
if (!string.IsNullOrWhiteSpace(powerQueryCommand.XmlFileName))
{
File.WriteAllText(powerQueryCommand.XmlFileName, powerQueryResponse.Xml);
}
if (isRemote)
{
powerQueryResponse.Xml = null;
}
}
}
catch (Exception ex)
{
Program.Log.WriteEntry(ex.ToString(), EventLogEntryType.Error);
if (powerQueryCommand.Queries != null && powerQueryCommand.Queries.Count > 0)
{
Program.Log.WriteEntry(powerQueryCommand.Queries.ToXML(), EventLogEntryType.Error);
}
if (powerQueryCommand.Mashup != null)
{
Program.Log.WriteEntry(powerQueryCommand.Mashup, EventLogEntryType.Error);
}
if (powerQueryCommand.Credentials != null && powerQueryCommand.Credentials.Count > 0)
{
Program.Log.WriteEntry(powerQueryCommand.Credentials.ToXML(), EventLogEntryType.Error);
}
powerQueryResponse.ExceptionMessage = ex.Message;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
return(powerQueryResponse);
}
//public ExecuteResponse Execute(string queryName, Queries queries, Credentials credentials, ExecuteOutputFlags executeOutputFlags = ExecuteOutputFlags.DataTable | ExecuteOutputFlags.Xml)
public ExecuteResponse Execute(ExecuteRequest executeRequest)
{
Command command = null;
ExecuteResponse executeResponse = new ExecuteResponse();
CommandCredentials commandCredentials = new CommandCredentials();
string mashup = "section Section1;\n\r";
try
{
if (executeRequest.Credentials != null)
{
foreach (Credential credential in executeRequest.Credentials)
{
if (credential is CredentialFile)
{
commandCredentials.SetCredentialFile(((CredentialFile)credential).Path);
}
else if (credential is CredentialWeb)
{
commandCredentials.SetCredentialWeb(((CredentialWeb)credential).Url);
}
else if (credential is CredentialSQL)
{
commandCredentials.SetCredentialSQL(((CredentialSQL)credential).SQL);
}
else
{
throw new NotImplementedException("This Credential kind is not supported for now.");
}
}
}
command = new Command(commandCredentials);
DataTable dataTable = null;
if (executeRequest.Queries != null)
{
foreach (Query q in executeRequest.Queries)
{
string name;
if (q.Name.Contains(" "))
{
name = string.Format("#\"{0}\"", q.Name);
}
else
{
name = q.Name;
}
mashup += string.Format("\n\rshared {0} = {1};", name, q.Formula);
}
}
dataTable = command.Execute(executeRequest.QueryName, mashup);
if (executeRequest.ExecuteOutputFlags == 0)
{
executeRequest.ExecuteOutputFlags = ExecuteOutputFlags.DataTable | ExecuteOutputFlags.Xml;
}
if (executeRequest.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.DataTable))
{
executeResponse.DataTableXML = dataTable.ToXML();
}
if (executeRequest.ExecuteOutputFlags.HasFlag(ExecuteOutputFlags.Xml))
{
executeResponse.Xml = dataTable.ToContentXML();
}
}
catch (Exception ex)
{
Program.Log.WriteEntry(ex.ToString(), EventLogEntryType.Error);
Program.Log.WriteEntry(mashup, EventLogEntryType.Error);
if (executeRequest.Queries != null)
{
Program.Log.WriteEntry(executeRequest.Queries.ToXML(), EventLogEntryType.Error);
}
if (executeRequest.Credentials != null)
{
Program.Log.WriteEntry(executeRequest.Credentials.ToXML(), EventLogEntryType.Error);
}
executeResponse.ExceptionMessage = ex.Message;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
return(executeResponse);
}
