Proprietary Dependence

One of the goals of the DotNetNuke project is to architect a solution in such a way that it minimizes its dependency on proprietary technologies ( the .NET Framework is obviously an exception to this rule ). And the Database Access Layer Abstraction project will certainly help improve our decoupled goals - removing the dependency from MS SQL Server and allowing the application to run an a variety of databases.

An interesting item which came up early in the DAL project involved the GetPortalSettings stored procedure. This stored procedure is the most frequently called data access routine in the application and uses a batch query to return multiple result sets in a single call. This technique provides a performance benefit as it requires less round trips to the database than making each database call individually. However it causes problems when you try to deal with more limited database engines like Access where batch queries are not supported.

The options were to override the GetPortalSettings method in the Access version to perform four individual database queries and then package them into a single multi-resultset package. But because I am still using DataReaders to pass the data, how do you dynamically create a DataReader stream? Not finding an answer, I thought maybe I should use a DataSet as the return value since it can be disconnected and contains methods for dynamic manipulation. But DataSets are more demanding on server resources than DataReaders, so I would take a performance hit and it would not be consistent with the data access strategy in the rest of the application.

Ultimately I decided ( for better or for worse ) to break the GetPortalSettings method into four distinct queries. The reason? To avoid the dependency on the multi-resultset concept and allow for simpler implementation on other database platforms. The next step is implementing some intelligent caching to offset the performance hit associated to this design decision.