GeneralSQLReporter 1.0.0
dotnet add package GeneralSQLReporter --version 1.0.0
NuGet\Install-Package GeneralSQLReporter -Version 1.0.0
<PackageReference Include="GeneralSQLReporter" Version="1.0.0" />
paket add GeneralSQLReporter --version 1.0.0
#r "nuget: GeneralSQLReporter, 1.0.0"
// Install GeneralSQLReporter as a Cake Addin #addin nuget:?package=GeneralSQLReporter&version=1.0.0 // Install GeneralSQLReporter as a Cake Tool #tool nuget:?package=GeneralSQLReporter&version=1.0.0
GeneralSQLReporter
.dll for General SQL Reporting, provides utilities to run any SELECT query against SQL and generate various outputs
Acknowledgements
I acknowledge and thank the creators of FreeSpire.XLSX
who I used to create the Excel & PDF Reports, their NuGet Package can be found at https://www.nuget.org/packages/FreeSpire.XLS
Setup
Once the NuGet Package / .dll has been added to your Project, you must Setup the SqlRepository
by calling SqlRepository.SetupSqlConnection()
like below:
var connected = SqlRepository.SetupSqlConnection(connectionString);
This uses the default ConnectionString
defined at https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-7.0
Creating a SqlReport
To create a SqlReport
that we can run, just create a new instance of the SqlReport
class like so:
var sqlReport = new SqlReport("SELECT * FROM [AvailableCars]", ReportFormat.Csv);
With the first parameter in this example being just a standard SELECT * FROM X
query and the second parameter being the desired Report Output format.
Creating a StoredProcedureReport
Similar to the SqlReport
you just need to create the instance of the StoredProcedureReport
class like so:
var storedProcedureReport = new StoredProcedureReport("sp_GetCarsByName", ReportFormat.Csv);
With the first parameter being the name of the Stored Procedure you wish to run.
Adding Parameters to a Report (SqlReport
or StoredProcedureReport
)
Both the SqlReport
and StoredProcedureReport
support using SqlParameters
(https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter?view=dotnet-plat-ext-7.0).
In the example I'll be following from the section Creating a StoredProcedureReport
.
There is a helpful AddParameter
method on both the SqlReport
and StoredProcedureReport
base class (GenericReport
) which handles defining and assigning the parameters with the values you specify like below:
storedProcedureReport.AddParameter("@name", SqlDbType.NVarChar, "Audi");
storedProcedureReport.AddParameter("@make", SqlDbType.NVarChar, "A8");
Where the first parameter is the name
of the SqlParameter
, the second being the DbType
/ Data Type for the parameter and the last parameter being the value you want to assign.
Running a Report (SqlReport
or StoredProcedureReport
)
As both the SqlReport
and StoredProcedureReport
share the same base class (GenericReport)
the SqlRepository
can dynamically handle either of these classes.
To run the Report all you need to add is:
var storedProcedureResult = SqlRepository.RunSingleReport(storedProcedureReport);
With the parameter being passed in (in this example the storedProcedureReport
created in Creating a StoredProcedureReport
and the resulting ReportResultSet
being stored into the storedProcedureResult
variable for exporting.
Exporting a Report
Exporting a report can be done for various output formats, listed below:
- HTML
- XLSX (Excel)
- CSV
All of which can be called from the SqlReportExporter
class.
For example, to export to an HTML file you would first gather the ReportResultSet
and then call:
var filePath = SqlReportExporter.ExportHtml(result, fileName: "testReport.html");
In the example above, it will create the HTML export of the results from the SQL query using the in-built HTML template and save the output file to a file named: testReport.html
, there are a various overload parameters to all of the export methods which allow more customisation, as well as providing a template to the exporter.
For example, the Excel (Xlsx) Exporter allows you to provide a template XLSX document to use for the Report Export, this will also allow you to specify the headerRow
, firstRow
and firstColumn
for the report and the package will automatically create and populate the XLSX document with the data from the SQL Report.
With headerRow
being the Cell Address for the header cell of the stylized table, e.g A1
being the first Header Cell, this would corellate to headerRow: 1
, firstCol: 1
and with the first cell where data would be presented being A2
this would correlate to: firstRow: 2
, firstCol: 1
.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET Framework | net472 is compatible. net48 was computed. net481 was computed. |
-
.NETFramework 4.7.2
- FreeSpire.XLS (>= 12.7.0)
- Newtonsoft.Json (>= 13.0.3)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.0.0 | 155 | 5/20/2023 |
First Release.