是否可以使用NHibernate调用存储过程并填充DataTable? [英] Is it possible to call the stored procedure using NHibernate and fill a DataTable?

问题描述

我已经知道我们如何使用NHibernate调用存储过程并填充DTO(如

I already know how we call a stored procedure using NHibernate and fill a DTO (as answered here).

但是，我想知道是否有可能使用NHibernate中相同的ResultTransformer概念填充DataTable，还是有其他推荐的方法?

But, I was wondering if its possible somehow to fill a DataTable using the same concept of ResultTransformer in NHibernate or is there any other recommended approach?

我更喜欢在NHibernate中使用ResultTransformer的概念.但是，我不知道它是否可能以及如何.还有其他人尝试过吗?

I'd prefer using the concept of ResultTransformer in NHibernate. But, I don't know whether its possible and how. Has anyone else tried this?

推荐答案

我使用ResultTransformer的概念提出了以下解决方案，该概念描述了

I came up with the following solution using the concept of ResultTransformer described here:

  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();
    }

我刚刚创建了一个自定义ResultTransformer，并根据我在DataTableResultTransformer中的逻辑在SQL查询中使用它来转换查询结果.

I have just created a custom ResultTransformer and use it in my sql query to transform the result of the query based on my logic in the DataTableResultTransformer.

为结果集中的每个项目调用TransformTupple方法.元组包含数据，其中别名包含数据名称.因此，我们几乎拥有构建和填充DataTable的所有内容.一旦结果集的所有项目都已通过TransformTupple方法进行了转换，则最后将调用TransformList方法.集合参数包含我们在TransformTupple方法中转换为DataRow的所有项目.因此，在这里我们可以轻松地用DataRows填充我们的DataTable并返回.

The TransformTupple method is called for each item in the result set. The tuple contains the data where as the aliases contain the names for the data. So, we have almost everything to build and fill our DataTable. Once all the items of the result set have been transformed by the TransformTupple method then the TransformList method is called at the end. The collection parameter contains all items that we transformed into DataRow in TransformTupple method. So, here we can easily fill our DataTable with the DataRows and return.

希望它对处理相同情况的其他人有所帮助.

Hope its helpful for others dealing with same kind of scenario.

这篇关于是否可以使用NHibernate调用存储过程并填充DataTable?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！