private bool ExportSecurity(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "Security";
string templatePath = LibraryHelper.AddDirectorySeparator(templateDirectory) + template + ".xlsx";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
DbCommand command;
DbDataReader reader;
string connectionName;
System.IO.File.Copy(templatePath, filePath);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(filePath);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet;
int row, columns;
// Identity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Identity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Users
worksheet = workbook.Worksheets["Users"];
command.CommandText = @"
SELECT
UserName UserName,
Email EMail,
LockoutEnabled Lockout,
LockoutEndDateUtc LockoutEndDate
FROM
AspNetUsers
ORDER BY
UserName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("EMail");
worksheet.Range[row, column++].Value2 = reader.ToBoolean("Lockout") ? EasyLOB.Resources.PresentationResources.Yes : EasyLOB.Resources.PresentationResources.No;
worksheet.Range[row, column++].ClearDateTime(reader.ToDateTimeNullable("LockoutEndDate"));
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles
worksheet = workbook.Worksheets["Roles"];
command.CommandText = @"
SELECT
Name RoleName
FROM
AspNetRoles
ORDER BY
RoleName
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by User
worksheet = workbook.Worksheets["Roles by User"];
command.CommandText = @"
SELECT
AspNetUsers.UserName UserName,
AspNetRoles.Name RoleName
FROM
AspNetUserRoles
INNER JOIN AspNetUsers ON
AspNetUsers.Id = AspNetUserRoles.UserId
INNER JOIN AspNetRoles ON
AspNetRoles.Id = AspNetUserRoles.RoleId
ORDER BY
1,2
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("UserName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
connection.Close();
// Activity ////////////////////////////////////////////////////////////////////////
connectionName = MultiTenantHelper.GetConnectionName("Activity");
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Activities
worksheet = workbook.Worksheets["Activities"];
command.CommandText = @"
SELECT
Name ActivityName
FROM
EasyLOBActivity
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
// Roles by Activity
worksheet = workbook.Worksheets["Roles by Activity"];
command.CommandText = @"
SELECT
EasyLOBActivity.Name ActivityName,
RoleName,
Operations
FROM
EasyLOBActivityRole
INNER JOIN EasyLOBActivity ON
EasyLOBActivity.Id = EasyLOBActivityRole.ActivityId
ORDER BY
Name
";
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
columns = reader.FieldCount;
row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToString("ActivityName");
worksheet.Range[row, column++].Value2 = reader.ToString("RoleName");
worksheet.Range[row, column++].Value2 = reader.ToString("Operations");
row++;
}
reader.Close();
worksheet.AutoAlign(1, columns);
workbook.Save();
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
public static bool ExportRDLC(ZOperationResult operationResult, ref string exportPath, string exportFormat,
string rdlcDirectory, string reportDirectory, string reportName, IDictionary <string, string> reportParameters)
{
try
{
string reportPath = Path.Combine(rdlcDirectory,
MultiTenantHelper.Tenant.Name,
reportDirectory,
reportName + ".rdl");
ReportWriter reportWriter = new ReportWriter(reportPath);
reportWriter.ReportProcessingMode = ProcessingMode.Local;
// Parameter(s)
IList <ReportParameter> reportWriterParameters = new List <ReportParameter>(); // Syncfusion.Reports.EJ.ReportParameter
foreach (KeyValuePair <string, string> pair in reportParameters)
{
ReportParameter reportParameter = new ReportParameter();
reportParameter.Name = pair.Key;
reportParameter.Values.Add(pair.Value);
reportWriterParameters.Add(reportParameter);
}
//reportWriter.SetParameters(reportWriterParameters); // ???
// Data Source(s) & Data Set(s)
global::Syncfusion.RDL.DOM.ReportDefinition reportDefinition = DeSerializeReport(reportPath);
IList <string> dataSetNames = reportWriter.GetDataSetNames();
reportWriter.DataSources.Clear();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
//foreach (var reportParameter in reportWriter.GetParameters()) // ???
foreach (var reportParameter in reportWriterParameters)
{
var defaultValue = reportParameter.Values.First() == null ? null : reportParameter.Values.First();
DbParameter parameter = command.CreateParameter();
//parameter.DbType = GetDbType(reportParameter.DataType); // ???
parameter.DbType = DbType.String;
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportWriter.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
if (operationResult.Ok)
{
// Export
string fileExtension = ".pdf";
WriterFormat writerFormat = WriterFormat.PDF;
switch (exportFormat.ToLower())
{
case "doc":
fileExtension = ".doc";
writerFormat = WriterFormat.Word;
break;
case "xls":
fileExtension = ".xls";
writerFormat = WriterFormat.Excel;
break;
}
//PageSettings pageSettings = new PageSettings();
//pageSettings.LeftMargin = 0.39; // 10mm
//pageSettings.RightMargin = 0.39; // 10mm
//pageSettings.TopMargin = 0.39; // 10mm
//pageSettings.BottomMargin = 0.39; // 10mm
//pageSettings.PageWidth = 8.27; // 210mm
//pageSettings.PageHeight = 11.69; // 297mm
//reportWriter.PageSettings = pageSettings;
exportPath = exportPath.Trim() + fileExtension;
FileStream fileStream = new FileStream(exportPath, FileMode.Create);
reportWriter.Save(fileStream, writerFormat);
fileStream.Close();
operationResult.StatusMessage = exportPath;
}
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
return(operationResult.Ok);
}
/// <summary>
/// Report loaded method that is triggered when report and sub report begins to be loaded.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnReportLoaded(ReportViewerOptions reportOptions)
{
ReportDefinition reportDefinition = DeSerializeReport(reportOptions.ReportModel.ReportPath);
ReportParameters reportParameters = reportDefinition.ReportParameters;
IList <string> dataSetNames = ReportHelper.GetDataSetNames();
reportOptions.ReportModel.DataSources.Clear();
// User Parameters
if (HttpContext.Current.Items.Contains("userParameters"))
{
reportOptions.ReportModel.Parameters =
new JavaScriptSerializer().Deserialize <List <global::Syncfusion.Reports.EJ.ReportParameter> >(HttpContext.Current.Items["userParameters"].ToString());
HttpContext.Current.Items.Remove("userParameters");
}
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = dataSet.First().Query.DataSourceName;
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
if (reportOptions.ReportModel.Parameters != null && reportOptions.ReportModel.Parameters.Count() > 0)
{
int index = 0;
foreach (var userParameter in reportOptions.ReportModel.Parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameters[index].DataType);
parameter.ParameterName = "@" + userParameter.Name;
parameter.Value = userParameter.Values.First();
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + userParameter.Name, userParameter.Values.First()); // DbType.String
index++;
}
}
else
{
foreach (var reportParameter in reportParameters)
{
var defaultValue = reportParameter.DefaultValue == null ? null : reportParameter.DefaultValue.Values.First();
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameter.DataType);
parameter.ParameterName = "@" + reportParameter.Name;
parameter.Value = defaultValue;
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
}
reader = command.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportOptions.ReportModel.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch // (Exception exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/*
* XmlDocument xml = new XmlDocument();
* string rdlcFileName = reportOptions.ReportModel.ReportPath;
* xml.Load(rdlcFileName);
* xml.InnerXml = xml.InnerXml.Replace("xmlns=", "xmlns:ns=");
*
* string connectionName =
* xml.DocumentElement.SelectSingleNode("/Report/DataSets/DataSet[@Name='Query']/Query/DataSourceName").InnerText;
* if (!String.IsNullOrEmpty(connectionName))
* {
* string sql =
* xml.DocumentElement.SelectSingleNode("/Report/DataSets/DataSet[@Name='Query']/Query/CommandText").InnerText;
* if (!String.IsNullOrEmpty(sql))
* {
* DbConnection connection = null;
*
* try
* {
* DbProviderFactory provider;
*
* provider = AdoNetHelper.GetProvider(connectionName);
* connection = provider.CreateConnection();
* connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
* connection.Open();
*
* DbCommand command;
* DbDataReader reader;
*
* command = provider.CreateCommand();
* command.Connection = connection;
* command.CommandTimeout = 600;
* command.CommandType = System.Data.CommandType.Text;
* command.CommandText = sql;
*
* reader = command.ExecuteReader();
*
* DataTable table = new DataTable();
* table.Load(reader);
*
* // System.Data.EnumerableRowCollection<System.Data.DataRow>
* // https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
* // System.Data.DataRow
* // https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
*
* reportOptions.ReportModel.DataSources.Clear();
* reportOptions.ReportModel.DataSources.Add(new ReportDataSource { Name = "Query", Value = table.AsEnumerable() });
* }
* catch // (Exception exception)
* {
* }
* finally
* {
* if (connection != null)
* {
* connection.Close();
* }
* }
* }
* }
*/
}
public bool ExportAlbumByArtistXLSX(ZOperationResult operationResult, string templateDirectory, string fileDirectory,
out string filePath)
{
string template = "AlbumByArtist";
string templatePath = LibraryHelper.AddDirectorySeparator(templateDirectory) + template + ".xlsx";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"
SELECT
Artist.ArtistId
,Artist.Name ArtistName
,Album.AlbumId
,Album.Title AlbumTitle
FROM
Artist
LEFT JOIN Album ON
Album.ArtistId = Artist.ArtistId
ORDER BY
Artist.Name
,Album.Title
";
//parameter = command.CreateParameter();
//parameter.DbType = DbType.DateTime;
//parameter.ParameterName = "@Data";
//parameter.Value = viewModel.XDateTime;
//command.Parameters.Add(parameter);
File.Copy(templatePath, filePath);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open(filePath);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet;
// Sintético
worksheet = workbook.Worksheets[0];
worksheet.Range[1, 4].Value2 = String.Format("{0:dd/MM/yyyy}", DateTime.Today);
// Analítico
worksheet = workbook.Worksheets[2];
reader = command.ExecuteReader();
int row = 2, column;
while (reader.Read())
{
column = 1;
worksheet.Range[row, column++].Value2 = reader.ToInt32("ArtistId");
worksheet.Range[row, column++].Value2 = reader.ToString("ArtistName");
worksheet.Range[row, column++].Value2 = reader.ToInt32("AlbumId");
worksheet.Range[row, column++].Value2 = reader.ToString("AlbumTitle");
row++;
}
reader.Close();
worksheet.AutoAlign(1, 4);
workbook.Save();
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
public bool ExportGenreXLSX(ZOperationResult operationResult, string fileDirectory,
out string filePath)
{
string template = "Genre";
string file = template + "." + String.Format("{0:yyyyMMdd.HHmmssfff}", DateTime.Now) + ".xlsx";
filePath = Path.Combine(fileDirectory, file);
DbConnection connection = null;
ExcelEngine excelEngine = null;
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
//DbParameter parameter;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"
SELECT
GenreId
,Name
FROM
Genre
ORDER BY
Genre.Name
";
//parameter = command.CreateParameter();
//parameter.DbType = DbType.DateTime;
//parameter.ParameterName = "@Data";
//parameter.Value = viewModel.XDateTime;
//command.Parameters.Add(parameter);
excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
//workbook.Version = ExcelVersion.Excel2013;
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range[1, 1].Text = "GenreId";
worksheet.Range[1, 2].Text = "Name";
reader = command.ExecuteReader();
int row = 2;
while (reader.Read())
{
int column = 1;
worksheet.Range[row, column++].Value2 = reader.ToInt32("GenreId");
worksheet.Range[row, column++].Value2 = reader.ToString("Name");
row++;
}
reader.Close();
worksheet.AutoAlign(1, 2);
workbook.SaveAs(filePath);
workbook.Close();
}
catch (Exception exception)
{
operationResult.ParseException(exception);
}
finally
{
if (excelEngine != null)
{
excelEngine.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(operationResult.Ok);
}
private static void SQLServer2MongoDB()
{
Console.WriteLine("\nChinook SQL Server => MongoDB");
Console.Write("\nPress <Y> to execute... ");
ConsoleKeyInfo key = Console.ReadKey();
if (key.KeyChar != 'y' && key.KeyChar != 'Y')
{
return;
}
Console.WriteLine();
DbConnection connection = null;
ChinookMongoDB chinook = new ChinookMongoDB();
try
{
DbProviderFactory provider;
string connectionName = "Chinook";
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
// Chinook
DbCommand2MongoDB <Album>(command, chinook);
DbCommand2MongoDB <Artist>(command, chinook);
DbCommand2MongoDB <Customer>(command, chinook);
DbCommand2MongoDB <Employee>(command, chinook);
DbCommand2MongoDB <Genre>(command, chinook);
DbCommand2MongoDB <Invoice>(command, chinook);
DbCommand2MongoDB <InvoiceLine>(command, chinook);
DbCommand2MongoDB <MediaType>(command, chinook);
DbCommand2MongoDB <Playlist>(command, chinook);
DbCommand2MongoDB <PlaylistTrack>(command, chinook);
DbCommand2MongoDB <Track>(command, chinook);
//DbCommand2MongoDB<CustomerDocument>(command, chinook);
// Sequence
IUnitOfWork unitOfWork = new ChinookUnitOfWorkMongoDB();
command.CommandText = "SELECT MAX(AlbumId) FROM Album";
unitOfWork.GetRepository <Album>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(ArtistId) FROM Artist";
unitOfWork.GetRepository <Artist>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(CustomerId) FROM Customer";
unitOfWork.GetRepository <Customer>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(EmployeeId) FROM Employee";
unitOfWork.GetRepository <Employee>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(GenreId) FROM Genre";
unitOfWork.GetRepository <Genre>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(InvoiceId) FROM Invoice";
unitOfWork.GetRepository <Invoice>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(InvoiceLineId) FROM InvoiceLine";
unitOfWork.GetRepository <InvoiceLine>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(MediaTypeId) FROM MediaType";
unitOfWork.GetRepository <MediaType>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(PlaylistId) FROM Playlist";
unitOfWork.GetRepository <Playlist>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(PlaylistId * 1000000 + TrackId) FROM PlaylistTrack"; // !!!
unitOfWork.GetRepository <PlaylistTrack>().SetSequence((int)command.ExecuteScalar());
command.CommandText = "SELECT MAX(TrackId) FROM Track";
unitOfWork.GetRepository <Track>().SetSequence((int)command.ExecuteScalar());
//command.CommandText = "SELECT MAX(CustomerDocumentId) FROM CustomerDocument";
//unitOfWork.GetRepository<CustomerDocument>().SetSequence((int)command.ExecuteScalar());
unitOfWork.GetRepository <CustomerDocument>().SetSequence(1);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Report loaded method that is triggered when report and sub report begins to be loaded.
/// </summary>
/// <param name="reportOptions">The ReportViewer options.</param>
public void OnReportLoaded(ReportViewerOptions reportOptions)
{
// User Parameters
if (HttpContext.Current.Items.Contains("userParameters"))
{
reportOptions.ReportModel.Parameters =
new JavaScriptSerializer().Deserialize <List <global::Syncfusion.Reports.EJ.ReportParameter> >(HttpContext.Current.Items["userParameters"].ToString());
HttpContext.Current.Items.Remove("userParameters");
}
// DataSet(s)
reportOptions.ReportModel.DataSources.Clear();
ReportDefinition reportDefinition = DeSerializeReport(reportOptions.ReportModel.ReportPath);
ReportParameters reportParameters = reportDefinition.ReportParameters;
IList <string> dataSetNames = ReportHelper.GetDataSetNames();
foreach (var dataSetName in dataSetNames)
{
var dataSet = reportDefinition.DataSets.Where(x => x.Name == dataSetName);
var connectionName = MultiTenantHelper.GetConnectionName(dataSet.First().Query.DataSourceName);
var sql = dataSet.First().Query.CommandText;
DbConnection connection = null;
try
{
DbProviderFactory provider;
provider = AdoNetHelper.GetProvider(connectionName);
connection = provider.CreateConnection();
connection.ConnectionString = AdoNetHelper.GetConnectionString(connectionName);
connection.Open();
DbCommand command;
DbDataReader reader;
command = provider.CreateCommand();
command.Connection = connection;
command.CommandTimeout = 600;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
command.Parameters.Clear();
if (reportOptions.ReportModel.Parameters != null && reportOptions.ReportModel.Parameters.Count() > 0)
{
int index = 0;
foreach (var userParameter in reportOptions.ReportModel.Parameters)
{
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameters[index].DataType);
parameter.ParameterName = "@" + userParameter.Name;
parameter.Value = userParameter.Values.First();
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + userParameter.Name, userParameter.Values.First()); // DbType.String
index++;
}
}
else
{
foreach (var reportParameter in reportParameters)
{
var defaultValue = reportParameter.DefaultValue == null ? null : reportParameter.DefaultValue.Values.First();
DbParameter parameter = command.CreateParameter();
parameter.DbType = GetDbType(reportParameter.DataType);
parameter.ParameterName = "@" + reportParameter.Name;
if (defaultValue == null)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = defaultValue;
}
command.Parameters.Add(parameter);
//command.AddParameterWithValue("@" + reportParameter.Name, defaultValue);
}
}
reader = command.ExecuteReader(IsolationLevel.ReadUncommitted);
DataTable table = new DataTable();
table.Load(reader);
// System.Data.EnumerableRowCollection<System.Data.DataRow>
// https://msdn.microsoft.com/en-us/library/system.data.enumerablerowcollection%28v=vs.110%29.aspx
// System.Data.DataRow
// https://msdn.microsoft.com/en-us/library/system.data.datarow%28v=vs.110%29.aspx
reportOptions.ReportModel.DataSources.Add(new ReportDataSource {
Name = dataSetName, Value = table.AsEnumerable()
});
}
catch // (Exception exception)
{
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
}