DotNetNuke (DNN) 2.x Module Architecture, Part III

In this final article of our module architecture series, we will look at the architecture of the database provider class, and finally creating our distribution and installing our module in DNN.

If you're not familiar with DotNetNuke, be sure you visit http://www.dotnetnuke.com to learn more about this fast growing open source portal that you can use and develop against absolutely free.

The Database Provider Class

The database provider class project is the assembly that provides direct interaction with a specific vendor's database. Up until now, we were entirely within an our module assembly and all database interaction was abstracted from the database using the Data Abstraction Layer provided by our module's dataprovider.vb class. Now we are going to look at the specific class that interacts with the database, in this example, the SQLDataProvider.vb class of the survery module's SQLDataProvider project.

Let's review how we configure our project for the SQLDataProvider:

• Create a new class project.
• Change the properties of your project the following way:
  • Rename it to Company.ModuleName.SQLDataProvider
  • Remove the namespace.
  • Add the following references:
    • DotNetNuke (project)
    • Company.ModuleName (project)
    • Microsoft.ApplicationBlocks.Data
  • Change the compilation path to your DotNetNuke bin directory.

  You can see a picture of the solution in part I of this series.

The Data Provider Class

Lets go over some important points about the SQL data provider:

• Database specific, for each database you want to use you will need a data provider for your module.

• Makes a call using the IDataReader for obtaining information from your database.

• Overrides methods contained in the data provider abstraction layer.

• May change drastically based on the specific vendor database.

Now that we covered what actually does the database interaction, lets look at the code from the Survey module example:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports DotNetNuke
Namespace YourCompanyName.Survey
    Public Class SqlDataProvider
        Inherits DataProvider
        Private Const ProviderType As String = "data"
        Private _providerConfiguration As ProviderConfiguration = _
             ProviderConfiguration.GetProviderConfiguration(ProviderType)
        Private _connectionString As String
        Private _providerPath As String
        Private _objectQualifier As String
        Private _databaseOwner As String
        Public Sub New()
            ' Read the configuration specific information for this provider
            Dim objProvider As Provider = _
              CType(_providerConfiguration.Providers(_providerConfiguration.DefaultProvider), _
                 Provider)
            ' Read the attributes for this provider
            _connectionString = objProvider.Attributes("connectionString")
            _providerPath = objProvider.Attributes("providerPath")
            _objectQualifier = objProvider.Attributes("objectQualifier")
            If _objectQualifier <> "" And _objectQualifier.EndsWith("_") = False Then
                _objectQualifier += "_"
            End If
            _databaseOwner = objProvider.Attributes("databaseOwner")
            If _databaseOwner <> "" And _databaseOwner.EndsWith(".") = False Then
                _databaseOwner += "."
            End If
        End Sub

        Public ReadOnly Property ConnectionString() As String
            Get
                Return _connectionString
            End Get
        End Property

        Public ReadOnly Property ProviderPath() As String
            Get
                Return _providerPath
            End Get
        End Property

        Public ReadOnly Property ObjectQualifier() As String
            Get
                Return _objectQualifier
            End Get
        End Property

        Public ReadOnly Property DatabaseOwner() As String
            Get
                Return _databaseOwner
            End Get
        End Property

        ' general
        Private Function GetNull(ByVal Field As Object) As Object
            Return Null.GetNull(Field, DBNull.Value)
        End Function

        Public Overrides Function GetSurveys(ByVal ModuleId As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, _
              DatabaseOwner & ObjectQualifier & "GetSurveys", ModuleId), IDataReader)
        End Function
        Public Overrides Function GetSurvey(ByVal SurveyID As Integer, _
            ByVal ModuleId As Integer) As IDataReader
            Return CType(SqlHelper.ExecuteReader(ConnectionString, _
              DatabaseOwner & ObjectQualifier & "GetSurvey", SurveyID, ModuleId), IDataReader)
        End Function
        ::
        ::
        ::
End Class

You can see from the code above we have some properties for the connection string for the database as defined in the web.config, the provider path, which is also defined in the web.config; remember the last article where we specified which data provider to use? This is the provider path (_providerPath) value here in our class. Finally we have two more strings, the database owner (_databaseOwner), and the object qualifier (_objectQualifier).

The database owner allows us to specify this value and have it passed to our database methods, this solves some problems we had in the past with hard coding dbo or some other value at the database owner account. This way an ISP can specify individual owner accounts for the various databases they host and not have issues with ownership or account security.

The object qualifier is the prefix name for your table structure, currently this value is empty.

Below the properties you have methods which match up with the stored procedures located in the DNN database. You could also have SQL here or parameterized queries, basically anything that needs to connect to the database directly. As long as your function returns an IDataReader type to be handled by the abstraction layer which will later be populated into a collection of objects by our Custom Business Objects (CBO.vb) helper class provided by the DNN framework (remember that from the previous article?).

Once all of your layers are completed, compile them as release and get ready for distribution.

Distributing Your Module

We covered every layer of the DotNetNuke module architecture, now let's get the module ready for distribution. In the following steps we're going to create a private assembly package out of our survey module so it can distributed easily to other DNN portals.

Creating Your Database For Distribution

Now that we have our provider project created, we need to create a SQL script for generating the database structure of our database. Remember the database owner and object qualifier strings specified in the data provider class? Well we will need to specify these variables in our SQL scripts as well for deployment. DotNetNuke will then look check the values located. Creating this script takes a little bit of extra effort than simply going to SQL Query Analyzer and exporting creation scripts from your database. Yes, that's how we start off, by first generating creation scripts via Query Analyzer or Enterprise Manager, but we need to add some additional information to these scripts in order for DNN to properly create your database structure.

After you generate SQL creation scripts, you will need to add the following prefixes in front of your table, and stored procedure references within the newly created scripts.

{databaseOwner}{objectQualifier}

These prefixes will then be replaced by the values defined within the web.config at database generation time during module installation. So in the following example of a SQL script to generate a table:

if not exists (select * from dbo.sysobjects where 
 id = object_id(N'{databaseOwner}{objectQualifier}[SurveyOptions]')
 and OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE {databaseOwner}{objectQualifier}SurveyOptions (
	[SurveyOptionID] [int] IDENTITY (1, 1) NOT NULL ,
	[SurveyID] [int] NOT NULL ,
	[ViewOrder] [int] NOT NULL ,
	[OptionName] [nvarchar] (500) NOT NULL ,
	[Votes] [int] NOT NULL 
) ON [PRIMARY]

GO

You can see where we highlighted the prefixes, then in your stored procedure generation script you will see these are added in any references to the tables:

create procedure {databaseOwner}{objectQualifier}UpdateSurveyOption

@SurveyOptionID int,
@OptionName     nvarchar(500),
@ViewOrder      int

as

update {objectQualifier}SurveyOptions
set    OptionName = @OptionName,
       ViewOrder = @ViewOrder
where  SurveyOptionID = @SurveyOptionID

GO

Once your SQL script is complete, save it using the following naming convention:

versionnum.dataprovidertype, for example: 01.00.00.SQLDataProvider

This will then get included in your distribution package. Depending on which data provider is configured in DNN, it will look for the appropriate database generation script based on the extension value.

One other file you should create as part of your distribution is an uninstall script. This script basically contains drop routines for deleting your tables and stored procedure in the event a portal admin wants to remove your module from their DNN install. The naming convention of this uninstall script is as follows:

uninstall.dataprovidertype, for example: uninstall.SqlDataProvider