我已经知道如何使用NHibernate调用存储过程并填充DTO(如此处所述)。
但是,我想知道是否可以使用NHibernate中的ResultTransformer概念以相同方式填充DataTable,或者是否有其他推荐的方法?
我更喜欢使用NHibernate中的ResultTransformer概念。 但是,我不知道是否可行以及如何实现。 有其他人尝试过这个吗?
我已经知道如何使用NHibernate调用存储过程并填充DTO(如此处所述)。
但是,我想知道是否可以使用NHibernate中的ResultTransformer概念以相同方式填充DataTable,或者是否有其他推荐的方法?
我更喜欢使用NHibernate中的ResultTransformer概念。 但是,我不知道是否可行以及如何实现。 有其他人尝试过这个吗?
public class DataTableResultTransformer : IResultTransformer
{
private DataTable dataTable;
public IList TransformList(IList collection)
{
var rows = collection.Cast<DataRow>().ToList();
rows.ForEach(dataRow => dataTable.Rows.Add(dataRow));
return new List<DataTable> { dataTable };
}
public object TransformTuple(object[] tuple, string[] aliases)
{
//Create the table schema based on aliases if its not already done
CreateDataTable(aliases);
//Create and Fill DataRow
return FillDataRow(tuple, aliases);
}
private DataRow FillDataRow(object[] tuple, string[] aliases)
{
DataRow dataRow = dataTable.NewRow();
aliases.ToList().ForEach(alias =>
{
dataRow[alias] = tuple[Array.FindIndex(aliases, colName => colName == alias)];
});
return dataRow;
}
private void CreateDataTable(IEnumerable<string> aliases)
{
if (dataTable == null)
{
dataTable = new DataTable();
aliases.ToList().ForEach(alias => dataTable.Columns.Add(alias));
}
}
}
并按以下方式使用:
using (ISession session = sessionFactory.OpenSession())
{
var sqlQuery = session.CreateSQLQuery("SELECT ID, NAME, ADDRESS FROM CUSTOMER");
var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
return transformedQuery.List().Single();
}
更新的示例,考虑了列数据类型
[SuppressMessage("Design", "CA1001")]
public class DataTableResultTransformer : IResultTransformer
{
readonly Type?[] m_DataTypeOverrides = Array.Empty<Type?>();
readonly DataTable m_DataTable = new DataTable();
/// <summary>
/// Initializes a new instance of the <see cref="DataTableResultTransformer"/> class.
///
/// Only use this constructor if none of the columns are nullable.
/// </summary>
/// <remarks>Warning: If a field is NULL in the first row, that entire column will be cast as a String.</remarks>
public DataTableResultTransformer()
{
}
/// <summary>
/// Initializes a new instance of the <see cref="DataTableResultTransformer"/> class.
///
/// 1. If dataTypeOverrides for a given column is not null, it is used.
/// 2. If the field is not null for the first row, then that field's data type if used.
/// 3. If both the dataTypeOverride and the field in the first row are null, the column's data type is String.
/// </summary>
/// <param name="dataTypeOverrides">The expected data types.</param>
public DataTableResultTransformer(params Type?[] dataTypeOverrides)
{
m_DataTypeOverrides = dataTypeOverrides;
}
public IList TransformList(IList collection)
{
return new List<DataTable> { m_DataTable };
}
public object TransformTuple(object[] tuple, string[] aliases)
{
if (tuple == null || tuple.Length == 0)
throw new ArgumentException($"{nameof(tuple)} is null or empty.", nameof(tuple));
if (aliases == null || aliases.Length == 0)
throw new ArgumentException($"{nameof(aliases)} is null or empty.", nameof(aliases));
if (m_DataTable.Columns.Count == 0)
{
//Create the DataTable if this is the first row
for (var i = 0; i < aliases.Length; i++)
{
var col = m_DataTable.Columns.Add(aliases[i]);
if (i < m_DataTypeOverrides.Length && m_DataTypeOverrides[i] != null)
col.DataType = m_DataTypeOverrides[i];
else if (tuple[i] != null && tuple[i] != DBNull.Value)
col.DataType = tuple[i].GetType();
}
}
return m_DataTable.Rows.Add(tuple);
}
}