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