DotNetNuke and Data Access
When you create a DotNetNuke module that needs to connect to the database, you have the option of coding the data access methods manually or leveraging the DotNetNuke framework.
The DotNetNuke framework provides a full featured Data Access Layer (DAL). The DAL also includes an special subset of methods commonly refereed to as the DAL+
The DAL and the DAL+
The following diagram shows an overview of the DAL and the DAL+ :
 |
 |
| The DAL |
The DAL+ |
The Purpose of the DAL (and the DAL+)
The DAL has this purpose:
To allow DotNetNuke (and it's modules) to communicate with any data source.
A Close-up Look at the DAL+
The DAL+ does not require an Abstract Data Provider and it does not require that you code a custom Concrete Provider. The DAL+ is a subset of the DAL. It is comprised of 4 methods that exist in the DotNetNuke framework's currently configured Concrete Provider. The methods are:
- ExecuteNonQuery - Used to execute a stored procedure that will not return a value.
- ExecuteReader - Used to execute a stored procedure that will return multiple records.
- ExecuteScalar - Used to execute a stored procedure that will return a single value.
- ExecuteSQL - Used to execute a sql statement.
Below is an explanation of the format used to implement the DAL+ using the ExecuteReader method:
The DAL+ allows you to use code such as this (in the Controller Class) to connect to the currently configured database:
Public Shared Function ThingsForSale_SelectAll(ByVal ModuleId As Integer) As List(Of ThingsForSaleInfo)
Return CBO.FillCollection(Of ThingsForSaleInfo)(CType(DataProvider.Instance().ExecuteReader("ThingsForSale_SelectAll", ModuleId), IDataReader))
End Function
(note: CBO.FillCollection is a special method provided by the DotNetNuke framework to hydrate a custom collection. See further reading at the end of this article for more information)
However, unlike the DAL, the DAL+ is not 100% portable to other data sources. For example, if a module is developed using the DAL+ that retrieves data using one stored procedure, an alternate database must be able to perform the exact same functionality using only one stored procedure. In some cases this is not possible due to differences in databases.
However, in those instances where you will not need to run your module on alternate databases (for example for internal development) it is recommended that you use the DAL+. The code savings is significant.
A Close-up Look at the DAL
The DAL does require that you code a Abstract Provider and one or more custom Concrete Providers. This allows you to create modules that are 100% portable to other databases.
The Abstract Provider reads the settings in the web.config file to determine what the currently configured database is and exposes data access methods. In the example below the method is GetSurveys which is declared MustOverride.
' return the provider
Public Shared Shadows Function Instance() As DataProvider
Return objProvider
End Function
' dynamically create provider
Private Shared Sub CreateProvider()
objProvider = CType(Framework.Reflection.CreateObject("data", "DotNetNuke.Modules.Survey", ""), DataProvider)
End Sub
' methods to be overridden by the concrete provider
Public MustOverride Function GetSurveys(ByVal ModuleId As Integer) As IDataReader
The Concrete Provider overrides the methods in the Abstract Provider and performs the data access task.
Public
Class SqlDataProvider
Inherits DataProvider
Public Overrides Function GetSurveys(ByVal ModuleId As Integer) As IDataReader
Return CType(SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner & ObjectQualifier & "GetSurveys", ModuleId), IDataReader)
End Function
(note: SqlHelper is a special method provided by the DotNetNuke framework (using the Microsoft Application Blocks) to reduce the code needed for data access. See further reading at the end of this article for more information)
The Controller Class uses DataProvider.Instance() to call methods in the Abstract Provider which calls the corresponding overridden methods in the Concrete Provider.
Public Shared Function GetSurveys(ByVal ModuleId As Integer) As List(Of SurveyInfo)
Dim SurveyInfolist As List(Of SurveyInfo) = New List(Of SurveyInfo)
Using dr As IDataReader = DataProvider.Instance().GetSurveys(ModuleId)
While dr.Read
Dim SurveyInfo As SurveyInfo = New SurveyInfo
SurveyInfo.SurveyId = Convert.ToInt32(dr("SurveyId"))
SurveyInfo.Question = Convert.ToString(dr("Question"))
SurveyInfo.OptionType = Convert.ToString(dr("OptionType"))
SurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr("ViewOrder")))
SurveyInfo.CreatedByUser = Convert.ToInt32(dr("CreatedByUser"))
SurveyInfo.CreatedDate = Convert.ToDateTime(dr("CreatedDate"))
SurveyInfolist.Add(SurveyInfo)
End While
End Using
Return SurveyInfolist
End Function
Leverage the DotNetNuke Framework
Using the DAL+ allows a module developer to write less code to access the database than they would if they did not use the DotNetNuke framework. The DAL+ has it's limitations and the DAL is provided in those cases where 100% portability is required.
Sample Code
DAL+ - Super-Fast Super-Easy Module (DAL+)
DAL - DNN4 Survey Module (Beta)
Further Reading
Data Access Guidelines
http://www.dotnetnuke.com/About/Documentation/ProjectDocuments/tabid/478/Default.aspx
Developing Your Own DotNetNuke Module
To develop your own DotNetNuke modules, it is recommended that you follow these tutorials in this order:
DotNetNuke® 4 Module Tutorial Series: