Thx.
This is the code ...
private readonly string licenseKey = "6vJh...";
private readonly IConnectionStringBuilder _connectionStringBuilder;
public List<TableColumns> TableColumns { get; set; }
public List<TableContraint> TableContraints { get; set; }
public HandleAndExportService(IConnectionStringBuilder connectionStringBuilder)
{
this._connectionStringBuilder = connectionStringBuilder;
this.TableColumns = GetAllColumns(this._connectionStringBuilder.GetApplicationConnectionString());
this.TableContraints = GetAllConstraints(this._connectionStringBuilder.GetApplicationConnectionString());
}
public StiReport GetReport(StiReportDocumentDefinitionDto definitionDto)
{
// initialize report
StiReport stiReport = new StiReport();
if(definitionDto.FileIsJson)
{
stiReport.LoadFromJson(definitionDto.File);
}
else
{
stiReport.Load(definitionDto.File);
}
stiReport.RetrieveOnlyUsedData = true;
stiReport.IsRendered = false;
// initialize connection
var databaseName = $"dag_app_{definitionDto.Application.Id}";
var connectionString = this._connectionStringBuilder.GetApplicationConnectionString();
stiReport.Dictionary.Databases.Clear();
stiReport.Dictionary.Databases.Add(new StiPostgreSQLDatabase(databaseName, connectionString));
stiReport.Dictionary.Synchronize();
// initialize variables
if (Enumerable.Any(stiReport.Dictionary.Variables.ToList()))
{
foreach (StiVariable stiVariable in stiReport.Dictionary.Variables)
{
stiVariable.Value = definitionDto.ReportVariables.First(r => r.Name == stiVariable.Name)?.Value;
}
}
// fill all entity objects
stiReport.Dictionary.DataSources.ToList().ForEach(datasource =>
{
var stiDatasource = ((StiSqlSource)datasource);
stiDatasource.SqlCommand = $"SELECT * FROM public.\"{stiDatasource.Name.ToLower()}\"";
stiDatasource.Columns = new StiDataColumnsCollection(GetStiDataColumns(datasource));
stiDatasource.CommandTimeout = 60;
});
// fill all relation objects
stiReport.Dictionary.Relations.ToList().ForEach(relation =>
{
var stiParentDatasource = ((StiSqlSource)relation.ParentSource);
stiParentDatasource.SqlCommand = $"SELECT * FROM public.\"{stiParentDatasource.Name.ToLower()}\"";
stiParentDatasource.Columns = new StiDataColumnsCollection(GetStiDataColumns(relation.ParentSource));
stiParentDatasource.CommandTimeout = 60;
var stiChildDatasource = ((StiSqlSource)relation.ChildSource);
stiChildDatasource.SqlCommand = $"SELECT * FROM public.\"{stiChildDatasource.Name.ToLower()}\"";
stiParentDatasource.Columns = new StiDataColumnsCollection(GetStiDataColumns(relation.ChildSource));
stiChildDatasource.CommandTimeout = 60;
SetJointureColumnCollection(relation, stiParentDatasource, stiChildDatasource);
});
...
...
Stimulsoft.Base.StiLicense.LoadFromString(this.licenseKey);
stiReport.Render();
MemoryStream stream = new MemoryStream();
if (generateReportSettings != null && exportType == ExportType.Csv)
{
StiExportSettings settings = new StiDataExportSettings()
{
SkipColumnHeaders = generateReportSettings.SkipColumnHeaders
};
stiReport.ExportDocument(GetStiExportFormat(exportType), stream, settings);
}
else
{
stiReport.ExportDocument(GetStiExportFormat(exportType), stream);
}
return new GeneratedReportDto()
{
ExportType = ExportType.Pdf,
File = stream.ToArray(),
MimeType = GetMimeTypeByExportType(ExportType.Pdf)
};
}
private List<StiDataColumn> GetStiDataColumns(StiDataSource datasource)
{
return this.TableColumns
.Where(s => s.TableName == datasource.Name.ToLower())
.Select(s => new StiDataColumn() { Alias = s.ColumnName, Name = s.ColumnName, Type = s.DataType })
.ToList();
}
private void SetJointureColumnCollection(StiDataRelation relation, StiDataSource parentDatasource, StiDataSource childDatasource)
{
var results = this.TableContraints
.Where(s => s.ForeignKeyTableName == childDatasource.Name.ToLower() && s.ForeignKeyTableName == parentDatasource.Name.ToLower())
.Select(s => s)
.ToList();
relation.ParentColumns = results.Select(s => s.PrimaryKeyColumnName).ToArray();
relation.ChildColumns = results.Select(s => s.ForeignKeyColumnName).ToArray();
}
private static List<TableColumns> GetAllColumns(string connectionString)
{
var query = "SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS";
var cmd = new NpgsqlCommand(query, new NpgsqlConnection(connectionString));
var rdr = cmd.ExecuteReader();
var tableColumns = new List<TableColumns>();
while (rdr.Read())
{
tableColumns.Add(new TableColumns()
{
TableName = rdr.GetString(0),
ColumnName = rdr.GetString(1),
DataType = typeof(int),
});
}
return tableColumns;
}
private static List<TableContraint> GetAllConstraints(string connectionString)
{
var query = "" +
"SELECT " +
"tc.table_name AS primary_table_name, " +
"kcu.column_name AS primary_column_name, " +
"ccu.table_name AS foreign_table_name, " +
"ccu.column_name AS foreign_column_name " +
"FROM " +
"information_schema.table_constraints AS tc " +
"JOIN information_schema.key_column_usage " +
"AS kcu ON tc.constraint_name = kcu.constraint_name " +
"JOIN information_schema.constraint_column_usage " +
"AS ccu ON ccu.constraint_name = tc.constraint_name " +
"WHERE constraint_type = 'FOREIGN KEY'";
var cmd = new NpgsqlCommand(query, new NpgsqlConnection(connectionString));
var rdr = cmd.ExecuteReader();
var tableContraints = new List<TableContraint>();
while (rdr.Read())
{
tableContraints.Add(new TableContraint()
{
PrimaryKeyTableName = rdr.GetString(0),
PrimaryKeyColumnName = rdr.GetString(1),
ForeignKeyTableName = rdr.GetString(2),
ForeignKeyColumnName = rdr.GetString(3),
});
}
return tableContraints;
}
private static StiExportFormat GetStiExportFormat(ExportType exportType)
{
switch (exportType)
{
case ExportType.Pdf:
return StiExportFormat.Pdf;
case ExportType.Excel:
return StiExportFormat.Excel;
case ExportType.Doc:
return StiExportFormat.Word2007;
case ExportType.Csv:
return StiExportFormat.Csv;
default:
return StiExportFormat.Pdf;
}
}
private static string GetMimeTypeByExportType(ExportType exportType)
{
switch (exportType)
{
case ExportType.Pdf:
return "application/pdf";
case ExportType.Excel:
return "application/vnd.ms-excel";
case ExportType.Doc:
return "application/msword";
case ExportType.Csv:
return "text/csv";
default:
return null;
}
}
....
public class TableColumns
{
public string TableName { get; set; }
public string ColumnName { get; set; }
public Type DataType { get; set; }
}
public class TableContraint
{
public string PrimaryKeyTableName { get; set; }
public string PrimaryKeyColumnName { get; set; }
public string ForeignKeyTableName { get; set; }
public string ForeignKeyColumnName { get; set; }
}
}
