NHibernate is my first experience with creating ADO.net provider independent applications. Unfortunately the reporting tool that I'm using (ActiveReports) doesn't support binding to object collections and so I must use one of those nasty DataSets.

My problem is that ISession.Connection returns an IDbConnection, but I have no way of getting an appropriate IDbDataAdapter instance from it. Unfortunately there is no IDbConnection.CreateDataAdapter() method (something similar to IDbConnection.CreateCommand()).

My current solution is to use reflection to find an adapter type based on the name of the connection, found in the same assembly/namespace. Does anybody know of a better way to generically fill a DataSet?

Otherwise you can ask for the Dialect from the _SessionFactory by calling _SessionFactory.Dialect and inspect the type to determine the DB and keep a mapping between DB and DataAdapter to instantiate somewhere and instantiate accordingly

But my dilemma is that I want a way to map from an IDbConnection (or Dialect, either way) to an IDbDataAdapter without maintaining a mapping lookup table.

Anyway, my solution above worked well until I moved my application to Jet (using NHibernate.JetDriver from the NH contrib project). Instead of using the plain OleDb stuff, they decided to implement the ADO.net interfaces and decided to not include a DataAdapter!

This makes it impossible to fill a DataSet from an existing NH connection object using the JetDriver. You would have to create a new OleDbConnection based on the same connection astring and use an OleDbDataAdapter from there.

There goes my generic approach...

There are other ways to populate a dataset than IDataAdapter.Fill(DataSet). If you aren't using complex relationships in you data binding, you can just build a DataTable from your returned object array and add that DataTable to a DataSet.

Sorry if you already knew that. :) I would just post the DB agnostic IDataAdapter / DbDataAdapter code I wrote last year, if only I could find it!

Yes, but the whole reason I need the DataAdapter in the first place is because I need to pass the results of a POSQ (plain old sql query) to an ActiveReports document. The report is not based on HQL (and I'm assuming HQL is not compatible with SQL either). I could probably write something that makes a DataTable from a DataReader, but then I'm just reinventing the DataAdapter (but a kind of generic one).

NHibernate is much more than just provider independent. It manages the connection and ado.net provider, writes your SQL for you, caches your queries and data, and lets you use plain objects in a simple manner. In your situation the only different thing you need (it seems) is a DataTable or DataSet rather than a collection of objects.

I understand ActiveReports can use DataReaders, DataTables, DataViews, and DataSets. If you want to use NHibernate but need to give ActiveReports a DataSet, what I suggested works just fine.



Is the SQL coming from some other tool or are you writing it yourself? If you are writing it yourself yet want to use NHibernate, use HQL instead.



I assume the report is based on a need to retrieve data from a database table. HQL fills this need by querying on your mapped objects. Behind the scenes, it creates SQL from your HQL based on the mappings of the objects used in the HQL. So it's not a matter of being compatible, it's a matter of doing the same thing two ways. Rather than: you can query against the Employee object like this: or even an example using parameters, which creates ado.net parameters behind the scenes: So if your app is creating SQL in code, NHibernate allows much more object-oriented query building with HQL, IQuery, and ICriteria.