After reading Nick’s great post on Consuming PPM web services - Apache Axis (Java) https://communities.ca.com/docs/DOC-231175690-consuming-ppm-web-services-apache-axis it got me thinking. I currently build little console apps for exporting NSQL query data into MSExcel. I mostly use C# (the process Nick outline is basically the same for C#).
With each new NSQL there is the need to generate stub classes to use the NSQL query in a program. So, I set off today to figure out a way to dynamically create stub classes on the fly. If one really looks at the steps to call a query via web services, we see that the pattern is the same for each NSQL query. The only thing that changes is the name of the methods and classes. But on closer inspection even those contain a pattern.
I elected not in implement the Sort, Slice or Filter object (commented out in the codebase). I did implement the FilterExpression (one of these days, I will figure out how this works as it seems hit or miss depending on the field type in the query).
I have built a NsqlToExcel utility and uploaded the VS2015 project along with a set of complied binaries. I didn't spend anytime documenting the code, it is only 200 lines. Basically, it imports the NSQL query wsdl to generate C# code, complies the generated code from the import and then calls the query method via reflection.
The NsqlToExcel has four required arguments:
- The PPM hostname.
- A PPM User
- The PPM User’s password
- The NSQL query code to run.
There are two optional arguments:
- The name of the XLSX file to dump the results of the query. The filename defaults to the code name if not supplied.
- A FilterExpression to apply against the NSQL query results set.
To run you just supply the options to a command shell (I think a PowerShell would also work).
NsqlToExcel -h cppm.ondemand.ca.com -u admin -p password -c rightsbylicensetype -f "group_name like 'A%'"
Which in this case, generated a MSExcel file with all the records from the rightsbylicensetype query in which the group_name starts with A. The MSExcel file is saved to rightsbylicensetype.xlsx which is defaulted from the code argument.
** NOTE ***: In the zip, I left the complied binary for anyone that just wants to run it without recompiling it. Just look in the NsqlToExcelBinaries folder. I noted this because some IT departments do not want foreign EXEs run on corporate computers. The source code is there for anyone to look at and if one wants to recompile the code here is a command line that should work (haven't tried it but point one in the right direction).
C:\Program Files (x86)\MSBuild\14.0\bin\csc.exe /noconfig /nowarn:1701,1702,2008 /nostdlib+ /platform:AnyCPU /errorreport:prompt /warn:4 /define:TRACE /errorendlocation /preferreduilang:en-US /highentropyva- /reference:"ClosedXML.dll" /reference:"CommandLine.dll" /reference:"DocumentFormat.OpenXml.dll" /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorlib.dll /reference:"NLog.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll" /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.DataSetExtensions.dll" /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.Services.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /reference:"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll" /debug:pdbonly /filealign:512 /optimize+ /out:obj\Release\NsqlToExcel.exe /target:exe /utf8output ExtensionMethods.cs Options.cs Program.cs Properties\AssemblyInfo.cs
V/r,
Gene