static void Main(string[] args)
{
int exitCode = 0;
try
{
if (args == null || args.Length == 0)
{
Console.Write(usage);
}
else
{
var a = new Arguments(args);
if (!string.IsNullOrWhiteSpace(a.OutputFile))
{
File.WriteAllText(a.OutputFile, "");
}
//ExecuteRequest request = LoadRequest(a);
PowerQueryCommand powerQueryCommand = LoadCommand(a);
PowerQueryResponse powerQueryResponse = powerQueryService.Execute(powerQueryCommand);
OutputResponse(a, powerQueryResponse);
}
exitCode = 0;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
exitCode = 1;
}
Environment.Exit(exitCode);
}
private void BtnHelloWorldString_Click(object sender, EventArgs e)
{
var q = new Query {
Formula = "let hw = \"Hello World\" in hw"
};
var pq = new PowerQueryCommand();
var result = pq.Execute(q);
DisplayResult(result);
}
private void LoadPowerQueryCommand()
{
if (!File.Exists(credentialsPath))
{
credentialsPath = null;
}
powerQueryCommand = new PowerQueryCommand
{
Credentials = Credentials.LoadFromFile(credentialsPath),
Queries = Queries.LoadFromFolder(queriesPath),
};
}
private void BtnList2_Click(object sender, EventArgs e)
{
var pq = new PowerQueryCommand
{
Queries = Queries.LoadFromFolder(myQueriesPath), //Load every .pq file found in MyQueries folder
};
//Add parameter to the query
pq.Queries.Add("List2Xlsx", string.Format("\"{0}\"", Path.Combine(myExcelPath, "List2.xlsx")));
//Add the required credentials
pq.Credentials.Add(new CredentialFile {
Path = Path.Combine(myExcelPath, "List2.xlsx")
});
var result = pq.Execute("List2");
DisplayResult(result);
}
private static PowerQueryCommand LoadCommand(Arguments a)
{
var c = new PowerQueryCommand()
{
QueryName = a.QueryName,
};
if (!string.IsNullOrWhiteSpace(a.CredentialsFile))
{
c.Credentials = Credentials.LoadFromFile(a.CredentialsFile);
}
if (!string.IsNullOrWhiteSpace(a.OutputFile))
{
switch (a.OutputFlags)
{
case ExecuteOutputFlags.Csv:
c.CsvFileName = a.OutputFile;
break;
//case ExecuteOutputFlags.DataTable:
// break;
case ExecuteOutputFlags.Html:
c.HtmlFileName = a.OutputFile;
break;
case ExecuteOutputFlags.Json:
c.JsonFileName = a.OutputFile;
break;
case ExecuteOutputFlags.Xml:
c.XmlFileName = a.OutputFile;
break;
default:
break;
}
}
if (!string.IsNullOrWhiteSpace(a.SqlConnectionString))
{
c.SqlConnectionString = a.SqlConnectionString;
c.SqlTableName = a.TableName;
c.SqlTableAction = a.SqlTableAction;
c.SqlDecimalPrecision = 18;
c.SqlDecimalScale = 2;
}
if (string.IsNullOrWhiteSpace(a.SourceFileExtension))
{
c.Queries = Queries.LoadFromFolder(a.Source);
}
if (a.SourceFileExtension == ".pq")
{
c.Queries.AddFromFile(a.Source);
}
if (a.SourceFileExtension == ".xlsx" ||
a.SourceFileExtension == ".xlsm" ||
a.SourceFileExtension == ".pbix" ||
a.SourceFileExtension == ".pbit"
)
{
c.Mashup = powerQueryService.MashupFromFile(a.Source);
//req.Queries = powerQueryService.MashupToQueries(source);
}
c.ExecuteOutputFlags = a.OutputFlags.Value;
if (a.OutputToWindow)
{
c.ExecuteOutputFlags |= ExecuteOutputFlags.DataTable;
}
return(c);
}
private static void OutputToSQL(PowerQueryCommand powerQueryCommand, DataTable dataTable)
{
string tableName;
using (SqlConnection connection = new SqlConnection(powerQueryCommand.SqlConnectionString))
{
connection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = connection;
var dt = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(sqlCommand))
{
sqlCommand.CommandText = $@"
DECLARE @ObjectName sysname
DECLARE @SchemaName sysname
IF PARSENAME(@TableName, 1) IS NULL
OR PARSENAME(@TableName, 3) IS NOT NULL
OR PARSENAME(@TableName, 4) IS NOT NULL
BEGIN
;THROW 50000, 'Table name cannot include server name or database name. Use the connection string instead.', 1
END
SET @ObjectName = PARSENAME(@TableName, 1)
SET @SchemaName = PARSENAME(@TableName, 2)
SELECT SchemaName = isnull(@SchemaName, ''), ObjectName = isnull(@ObjectName, '')
";
var sqlParameter = new SqlParameter("TableName", SqlDbType.NVarChar, 128);
sqlParameter.Value = dataTable.TableName;
sqlCommand.Parameters.Add(sqlParameter);
da.Fill(dt);
sqlCommand.Parameters.Clear();
string schemaName = dt.Rows[0][0].ToString();
string objectName = dt.Rows[0][1].ToString();
tableName = schemaName == "" ? $"[{objectName}]" : $"[{schemaName}].[{objectName}]";
}
if (powerQueryCommand.SqlTableAction == SqlTableAction.DropAndCreate)
{
sqlCommand.CommandText = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL DROP TABLE {tableName}";
sqlCommand.ExecuteNonQuery();
}
if (powerQueryCommand.SqlTableAction == SqlTableAction.DropAndCreate || powerQueryCommand.SqlTableAction == SqlTableAction.Create)
{
sqlCommand.CommandText = CreateTable(tableName, dataTable, powerQueryCommand.SqlDecimalPrecision, powerQueryCommand.SqlDecimalScale);
sqlCommand.ExecuteNonQuery();
}
if (powerQueryCommand.SqlTableAction == SqlTableAction.TruncateAndInsert)
{
sqlCommand.CommandText = $"TRUNCATE TABLE {tableName}";
sqlCommand.ExecuteNonQuery();
}
if (powerQueryCommand.SqlTableAction == SqlTableAction.DeleteAndInsert)
{
sqlCommand.CommandText = $"DELETE FROM {tableName}";
sqlCommand.ExecuteNonQuery();
}
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
foreach (DataColumn c in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dataTable);
}
}
}
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);
}
