public static DataTable LoadReportDataTable(string reportId, string dataSetName, IDictionary <string, object> parameterValues, DataTableCallback callback)
{
string reportPath = ConfigurationManager.AppSettings["VB.Reports.App.ReportDefinitionLibrary.Xml.Serialization.ReportMetadata.Path"];
IReport report = ReportFactory.NewReportFactory(reportPath).FindReport(reportId);
ILocalReportProcessingLocation location = (ILocalReportProcessingLocation)report.ReportProcessingLocation;
DataTable table;
using (IDataConnection connection = SimpleQuery.ConfigurationManagerConnection(location.DataSourceName))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
IReportDataCommandTemplate reportDataCommandTemplate = report.DataSource(dataSetName);
using (IDataCommand command = connection.CreateCommand(reportDataCommandTemplate, new DictionaryDataParameterValue(parameterValues).DataParameterValue))
{
command.CommandTimeout = 90;
using (IDataReader reader = command.ExecuteReader())
{
table = DataTableMapper.ToDataTable(reader, reportDataCommandTemplate.DataMap, callback);
}
}
}
return(table);
}
public void DataTableMapper_Map_WhenValidDataTable_ExpectValidPaths()
{
//------------Setup for test--------------------------
var dataTableMapper = new DataTableMapper();
DataTable obj = new DataTable("Foo");
obj.Columns.Add("Col1");
obj.Columns.Add("Col2");
obj.Rows.Add(new object[] { "a", "b" });
obj.Rows.Add(new object[] { "c", "d" });
obj.Rows.Add(new object[] { "e", "f" });
//------------Execute Test---------------------------
var result = dataTableMapper.Map(obj).ToList();
//------------Assert Results-------------------------
Assert.AreEqual(2, result.Count);
Assert.AreEqual("Foo().Col1", result[0].ActualPath);
Assert.AreEqual("Foo().Col1", result[0].DisplayPath);
Assert.AreEqual("a__COMMA__c__COMMA__e", result[0].SampleData);
Assert.AreEqual("Foo().Col2", result[1].ActualPath);
Assert.AreEqual("Foo().Col2", result[1].DisplayPath);
Assert.AreEqual("b__COMMA__d__COMMA__f", result[1].SampleData);
}
public void DataTableMapper_Map_WhenValidDataTableWithHTMLData_ExpectValidPaths()
{
//------------Setup for test--------------------------
var htmlFragment = @"<html xmlns=""http://www.w3.org/1999/xhtml"">
<head><title>
All Build Definitions - Microsoft Team Foundation Server
</title>
</head>
</html>";
var dataTableMapper = new DataTableMapper();
DataTable obj = new DataTable("Foo");
obj.Columns.Add("Col1");
obj.Columns.Add("Col2");
obj.Rows.Add(new object[] { "a", "b" });
obj.Rows.Add(new object[] { "c", htmlFragment });
//------------Execute Test---------------------------
var result = dataTableMapper.Map(obj).ToList();
//------------Assert Results-------------------------
Assert.AreEqual(2, result.Count);
Assert.AreEqual("Foo().Col1", result[0].ActualPath);
Assert.AreEqual("Foo().Col1", result[0].DisplayPath);
Assert.AreEqual("a__COMMA__c", result[0].SampleData);
Assert.AreEqual("Foo().Col2", result[1].ActualPath);
Assert.AreEqual("Foo().Col2", result[1].DisplayPath);
Assert.AreEqual("b__COMMA__" + htmlFragment, result[1].SampleData);
}
public void DataTableMapper_Map_WhenNull_ExpectNull()
{
//------------Setup for test--------------------------
var dataTableMapper = new DataTableMapper();
//------------Execute Test---------------------------
dataTableMapper.Map(null).ToList();
}
public void ValidateMappingProperties_GivenUnMatchingColumns_ShouldReturnFalse()
{
var mappings = new List <IPropertyMapping <TestC> >();
mappings.Add(new ColumnToPropertyMapping <TestC>("Namex", "testName"));
var mapper = new DataTableMapper <TestC>();
Assert.IsFalse(mapper.ValidateMappingProperties(mappings));
}
public void Map_GivenUnMatchingColumns_ShouldThrowMappingException()
{
var mappings = new List <IPropertyMapping <TestC> >();
mappings.Add(new ColumnToPropertyMapping <TestC>("Name", "testNameX"));
var mapper = new DataTableMapper <TestC>();
Assert.Throws <MappingException>(() => mapper.Map(mappings, dataTable));
}
public void Map_GivenMatchingColumns_ShouldReturnExpectedList()
{
var mappings = new List <IPropertyMapping <TestC> >();
mappings.Add(new ColumnToPropertyMapping <TestC>("Name", "testName"));
mappings.Add(new ColumnToPropertyMapping <TestC>("Id", "testId"));
var mapper = new DataTableMapper <TestC>();
var result = mapper.Map(mappings, dataTable).ToList();
Assert.AreEqual(2, result.Count);
Assert.AreEqual("X001", result[0].Name);
Assert.AreEqual(1, result[0].Id);
Assert.AreEqual("X002", result[1].Name);
Assert.AreEqual(2, result[1].Id);
}
public void ToDataTableNoRows()
{
var customerRecords = new List <CustomerRecord>();
var propertyMapper = new PropertyMapper <CustomerRecord>();
var dataColumnMapper = new DataColumnMapper <CustomerRecord>(propertyMapper);
var dataRowMapper = new DataRowMapper <CustomerRecord>(propertyMapper);
var dataTableMapper = new DataTableMapper <CustomerRecord>(dataColumnMapper, dataRowMapper);
var dataTable = dataTableMapper.ToDataTable(customerRecords);
Assert.Equal(customerRecords.Count, dataTable.Rows.Count);
Assert.Equal(6, dataTable.Columns.Count);
Assert.Equal(0, dataTable.Rows.Count);
}
/// <summary>
/// DataTable转化为实体列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <returns></returns>
public virtual List <T> DataTableToEntityList <T>(DataTable dt)
{
List <T> entityList = null;
if (dt == null)
{
return(entityList);
}
entityList = new List <T>();
foreach (DataRow dataRow in dt.Rows)
{
DataTableMapper <T> dataRowMapper = DataTableMapper <T> .GetInstance(dataRow);
var entity = dataRowMapper.Map(dataRow);
if (entity != null)
{
entityList.Add(dataRowMapper.Map(dataRow));
}
}
return(entityList);
}
public void ToDataTableWithMultipleRows()
{
var customerRecords = new List <CustomerRecord>
{
new CustomerRecord
{
Id = 1,
FirstName = "John",
LastName = "Smith",
AccountBalance = 25.60m,
DateJoined = new DateTime(2018, 9, 15),
IsActive = true,
},
new CustomerRecord
{
Id = 2,
FirstName = "Mary",
LastName = "McDonanld",
AccountBalance = 30.950m,
DateJoined = new DateTime(2017, 4, 10),
IsActive = false,
}
};
var propertyMapper = new PropertyMapper <CustomerRecord>();
var dataColumnMapper = new DataColumnMapper <CustomerRecord>(propertyMapper);
var dataRowMapper = new DataRowMapper <CustomerRecord>(propertyMapper);
var dataTableMapper = new DataTableMapper <CustomerRecord>(dataColumnMapper, dataRowMapper);
var dataTable = dataTableMapper.ToDataTable(customerRecords);
Assert.Equal(customerRecords.Count, dataTable.Rows.Count);
Assert.Equal(6, dataTable.Columns.Count);
// TODO: VC: Assert columns equality
CustomerRecordAssert.Equal(customerRecords, dataTable.Rows);
}
protected virtual EntityType Get(IdType id, string GET_STORED_PROCEDURE_NAME)
{
IEnumerable <SqlParameter> storedProcedureParameters = GetIdParameters(id);
IDataBaseCommandExecutor storedProcedure = TryGetStoredProcedure(GET_STORED_PROCEDURE_NAME, storedProcedureParameters);
DataSet resultDataSet = null;
try
{
resultDataSet = storedProcedure.Execute();
}
catch (Exception)
{
string EXCEPTION_MESSAGE = $"Ошибка выполнения хранимой процедуры получения экземпляра класса {typeof(EntityType).ToString()} из базы данных!";
ExceptionLogger.LogError(EXCEPTION_MESSAGE);
throw;
}
if (resultDataSet != null)
{
DataTableMapper mapper = TryGetDataTableMapper(resultDataSet.Tables[0]);
EntityType result = mapper.CreateObjectFromTable <EntityType>();
return(result);
}
else
{
string EXCEPTION_MESSAGE = $"Ошибка получения экземпляра класса {typeof(EntityType).ToString()} из базы данных!";
ExceptionLogger.LogError(EXCEPTION_MESSAGE);
throw new Exception();
}
}
protected virtual IEnumerable <EntityType> GetAll(string GET_ALL_STORED_PROCEDURE_NAME)
{
IDataBaseCommandExecutor storedProcedure = TryGetStoredProcedure(GET_ALL_STORED_PROCEDURE_NAME, new List <SqlParameter> {
});
DataSet resultDataSet = null;
try
{
resultDataSet = storedProcedure.Execute();
}
catch (Exception)
{
string EXCEPTION_MESSAGE = $"Ошибка выполнения хранимой процедуры для получения всех записей класса {typeof(EntityType).ToString()} из базы данных!";
ExceptionLogger.LogError(EXCEPTION_MESSAGE);
throw;
}
if (resultDataSet != null)
{
DataTableMapper mapper = TryGetDataTableMapper(resultDataSet.Tables[0]);
IEnumerable <EntityType> resultCollection = mapper.CreateListFromTable <EntityType>();
return(resultCollection);
}
else
{
string EXCEPTION_MESSAGE = $"Результат выполнения хранимой процедуры для получения всех записей класса {typeof(EntityType).ToString()} из базы данных вернул Null!";
ExceptionLogger.LogError(EXCEPTION_MESSAGE);
throw new Exception();
}
}
protected void BindLocalReport(MsLocalReport localReport, IReport report)
{
ILocalReportProcessingLocation location = (ILocalReportProcessingLocation)report.ReportProcessingLocation;
localReport.LoadReportDefinition(ReportDefinitionCache.GetReportDefinition(location.File).GetReportStream());
localReport.EnableHyperlinks = true;
localReport.EnableExternalImages = true;
localReport.SetBasePermissionsForSandboxAppDomain(new System.Security.PermissionSet(System.Security.Permissions.PermissionState.Unrestricted));
//localReport.ExecuteReportInCurrentAppDomain(AppDomain.CurrentDomain.Evidence);
//localReport.AddFullTrustModuleInSandboxAppDomain(new System.Security.Policy.StrongName(new System.Security.Permissions.StrongNamePublicKeyBlob(Nullable),"VB.Reports.App.ReportStyleLibrary.StyleSheet","1.0.0"));
//localReport.AddTrustedCodeModuleInCurrentAppDomain(System.Reflection.Assembly.GetAssembly(typeof(VB.Reports.App.ReportStyleLibrary.StyleSheet)).ToString());
if (localReport.IsDrillthroughReport)
{
localReport.SetParameters(localReport.OriginalParametersToDrillthrough);
}
else
{
IList <MsReportParameter> parameters = new List <MsReportParameter>();
foreach (IReportParameter parameter in report.ReportParameters)
{
parameters.Add(new MsReportParameter(parameter.Name, GetParameterValue(parameter)));
}
localReport.SetParameters(parameters);
}
localReport.DataSources.Clear();
foreach (IReportDataCommandTemplate reportDataCommandTemplate in report.ReportDataCommandTemplates)
{
DictionaryDataParameterValue parameterValues = new DictionaryDataParameterValue();
foreach (IReportParameter parameter in reportDataCommandTemplate.Parameters)
{
if (localReport.IsDrillthroughReport)
{
parameterValues[parameter.Name] =
GetParameterValue(parameter, localReport.OriginalParametersToDrillthrough);
}
else
{
parameterValues[parameter.Name] = GetParameterValue(parameter);
}
}
using (IDataConnection connection = SimpleQuery.ConfigurationManagerConnection(location.DataSourceName))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (IDataCommand command = connection.CreateCommand(reportDataCommandTemplate, parameterValues.DataParameterValue))
{
command.CommandTimeout = 200;
using (IDataReader reader = command.ExecuteReader())
{
DataTable table = DataTableMapper.ToDataTable(reader);
localReport.DataSources.Add(new MsReportDataSource(reportDataCommandTemplate.Name, table));
}
}
}
}
localReport.Refresh();
}
protected void BindLocalReport(MsLocalReport localReport, IReport report)
{
ILocalReportProcessingLocation location = (ILocalReportProcessingLocation)report.ReportProcessingLocation;
localReport.LoadReportDefinition(ReportDefinitionCache.GetReportDefinition(location.File).GetReportStream());
localReport.EnableHyperlinks = true;
localReport.EnableExternalImages = true;
localReport.SetBasePermissionsForSandboxAppDomain(new System.Security.PermissionSet(System.Security.Permissions.PermissionState.Unrestricted));
if (localReport.IsDrillthroughReport)
{
localReport.SetParameters(localReport.OriginalParametersToDrillthrough);
}
else
{
IList <MsReportParameter> parameters = new List <MsReportParameter>();
foreach (IReportParameter parameter in report.ReportParameters)
{
parameters.Add(new MsReportParameter(parameter.Name, GetParameterValue(parameter)));
}
localReport.SetParameters(parameters);
}
localReport.DataSources.Clear();
foreach (IReportDataCommandTemplate reportDataCommandTemplate in report.ReportDataCommandTemplates)
{
DictionaryDataParameterValue parameterValues = new DictionaryDataParameterValue();
foreach (var dataParameterTemplate in reportDataCommandTemplate.Parameters)
{
var parameter = (IReportParameter)dataParameterTemplate;
if (localReport.IsDrillthroughReport)
{
parameterValues[parameter.Name] =
GetParameterValue(parameter, localReport.OriginalParametersToDrillthrough);
}
else
{
parameterValues[parameter.Name] = GetParameterValue(parameter);
}
}
var dataCommandTemplate = reportDataCommandTemplate as ReportDataCommandTemplate;
if (dataCommandTemplate != null)
{
using (IDataConnection connection = SimpleQuery.ConfigurationManagerConnection(dataCommandTemplate.DataSourceName))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
DataTable table = null;
Boolean isOlap = ChangeOlapParameter(dataCommandTemplate.DataSourceName,
parameterValues);
if (reportDataCommandTemplate.Name.Equals("DealerGroupID"))
{
continue;
}
if (isOlap)
{
table = ReportAnalyticsClient.GetReportData <DataTable>(GetParameterList(parameterValues));
}
if (table == null)
{
using (IDataCommand command = connection.CreateCommand(reportDataCommandTemplate, parameterValues.DataParameterValue))
{
command.CommandTimeout = 200;
using (IDataReader reader = command.ExecuteReader())
{
table = (isOlap.Equals(true) ? DataTableMapper.ToOlapDataTable(reader, reportDataCommandTemplate.DataMap, new DataTableCallback()) :
DataTableMapper.ToDataTable(reader, reportDataCommandTemplate.DataMap, new DataTableCallback()));
}
}
}
localReport.DataSources.Add(new MsReportDataSource(reportDataCommandTemplate.Name, table));
}
}
}
localReport.Refresh();
}
