Even if you work with NHibernate, sometimes you need to use Stored Procedure to create reports, and sometimes you still have to work with dataset if you work with legacy code. I have this need: call a stored procedure and populate a Strongly Typed Database in a project based on NHibernate because the report designer require a dataset to work.
The project is simple, so I do not want to establish a structure to access database and I prefer to use nhibernate, so I create the stored procedure, create a dataset, Drag&Drop the stored into the DatasetDesigner and obtain a StronglyTyped Dataset.
Figure 1 :The strongly typed dataset into the designer.
Since the Dataset Designer created some classes behind the scene, I want NHibernate to be able to work directly with dataset rows, so I add this mapping to the project.
1
2
3
4
5
6
7
8
| <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<import class="MyProject.Report.WeeklyReportRow, MyProject" />
<sql-query name="QueryStoredWeeklyReport">
exec dbo.WeeklyReport :reportDate
</sql-query>
</hibernate-mapping>
|
Snippet 1: Mapping added to the project
As you can see, I simply add the the Report.WeeklyReportRow as <import> into the mapping, and then added a <sql-query> to call the stored procedure. Now you can write this code.:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| var datasetRows = Session.GetNamedQuery("QueryStoredWeeklyReport")
.SetParameter("reportDate", reportDate)
.List<Object[]>();
Report.Report returnDataset = new Report();
foreach (var datasetRow in datasetRows)
{
//Ricreiamo il dataset sapendo che l'ordine delle colonne è lo stesso del dataset
//dato che è stato creato dal designer.
returnDataset.WeeklyReport.AddWeeklyReportRow(
(Int32) datasetRow[0],
(String) datasetRow[1],
(Int32)datasetRow[2],
(String)datasetRow[3],
(DateTime) datasetRow[4],
(Int32)datasetRow[5],
(Int32)datasetRow[6],
(Int32)datasetRow[7],
(Decimal) datasetRow[8],
(Decimal)datasetRow[9],
(Decimal)datasetRow[10]);
}
return returnDataset;
|
Snippet 2: Code to query the database and add rows into the dataset.
This code is really ugly, it queries the database, returns the data as Object[] and then, knowing the order of the field in the database, recreates the rows one by one doing a lot of ugly casting.
You can try to use an AliasToBeanResultTransformer, but you will encounter an error, because the AliasToBeanResultTransformer needs a default constructor on the WeeklyReportRow, but code generated by dataset designer does not provide a default constructor. Since I really do not like the above code, I decided to create this Transformer, based on the original code of AliasToBeanResultTransformer
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
| public class AliasToBeanResultTransformerWithFactoryMethod<T> : IResultTransformer
{
private Func<T> _factory;
private readonly IPropertyAccessor _propertyAccessor;
private ISetter[] _setters;
public AliasToBeanResultTransformerWithFactoryMethod(Func<T> factory)
{
_factory = factory;
this._propertyAccessor = new ChainedPropertyAccessor(
new IPropertyAccessor[]
{
PropertyAccessorFactory.GetPropertyAccessor(null),
PropertyAccessorFactory.GetPropertyAccessor("field")
});
}
public IList TransformList(IList collection)
{
return collection;
}
public object TransformTuple(object[] tuple, string[] aliases)
{
object obj2;
try
{
if (this._setters == null)
{
this._setters = new ISetter[aliases.Length];
for (int j = 0; j < aliases.Length; j++)
{
string propertyName = aliases[j];
if (propertyName != null)
{
this._setters[j] = this._propertyAccessor.GetSetter(typeof(T), propertyName);
}
}
}
obj2 = _factory();
for (int i = 0; i < aliases.Length; i++)
{
if (this._setters[i] != null)
{
this._setters[i].Set(obj2, tuple[i]);
}
}
}
catch (InstantiationException exception)
{
throw new HibernateException("Could not instantiate result class: " + typeof(T).FullName, exception);
}
catch (MethodAccessException exception2)
{
throw new HibernateException("Could not instantiate result class: " + typeof(T).FullName, exception2);
}
return obj2;
}
}
|
Snippet 3: A new result transformer capable to use a factory method to create the entity
The code is the very same of AliasToBeanResultTransformer, except that my class accepts a factory method in the constructor, and used it instead of a default constructor to create an instance of the class during the transformation phase.
You can now rewrite the previous query (Snippet2) in this way.
1
2
3
4
5
6
7
8
9
10
11
| Report.Report returnDataset = new Report.Report();
AliasToBeanResultTransformerWithFactoryMethod<Report.WeeklyReportRow>
transformer = new AliasToBeanResultTransformerWithFactoryMethod<Report.Report.WeeklyReportRow>(
() => returnDataset.WeeklyReport.NewWeeklyReportRow());
var result = Session.GetNamedQuery("QueryStoredWeeklyReport")
.SetParameter("reportDate", reportDate)
.SetResultTransformer(transformer)
.List<Report.WeeklyReportRow>();
result.ForEach(returnDataset.WeeklyReport.AddWeeklyReportRow);
return returnDataset;
|
First I create the dataset, then I create an instance of my new transformer, passing a simple lambda that uses the NewWeeklyReportRow() function of the Table Object created by the dataset designer. Then I simply call the query with List<Report.WeeklyReportRow>() and let nhibernate recreate the Rows for me using the transformer. The final line simply adds all the rows to the dataset before returning it to the caller.
Alk.
