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

Creating Your DNN Distribution Definition

The DNN assembly install definition file is an XML file that describes the structure of your module. It contains several pieces of information in order for DNN to install your module within the application. Let's look at a sample that comes with the survey module.

<?xml version="1.0" encoding="utf-8" ?>
   <dotnetnuke version="2.0" type="Module">
   <folders>
     <folder>
       <name>CompanyName - Survey</name>
       <description>Survey allows you to create custom surveys
          to obtain public feedback</description>
       <version>01.00.00</version>
       <modules>
         <module>
           <friendlyname>CompanyName - Survey</friendlyname>
           <controls>
             <control>
               <src>Survey.ascx</src>
               <type>View</type>
             </control>
             <control>
               <key>Edit</key>
               <title>Create Survey</title>
               <src>EditSurvey.ascx</src>
               <iconfile>icon_survey_32px.gif</iconfile>
               <type>Edit</type>
             </control>
             ::
           </controls>
        </module>
      </modules>

Let's look at the XML above. You can see we define our module at the top most level. Then we need to let DNN know what our module consists of, this will be the primary user interface or ascx files. In the survey module we have three defined, we provide two here in this example, a view control defined first, and the edit control. You can see within the edit control we define the name of the file used, a key name, title value for the module container, icon, and the type of the control., in this case it is an edit control. Since it is an edit control, we can use the Boolean value provided by DNN to check permissions to see if the user has access to edit functions on the module (remember "IsEditable" from part I?).

So now that we defined the basic structure of our module, we need to define the actual files that make up our module. These files include the DLL, the ascx controls, data providers, images, and any other files we use to support our specific application.

      ::
     <files>
       <file>
         <name>Survey.ascx</name>
       </file>
       <file>
         <name>EditSurvey.ascx</name>
       </file>
       <file>
         <name>EditSurveyOptions.ascx</name>
       </file>
       <file>
         <name>YourCompanyName.Survey.dll</name>
       </file>
       <file>
         <name>YourCompanyName.Survey.SqlDataProvider.dll</name>
       </file>
       <file>
         <name>01.00.00.SqlDataProvider</name>
       </file>
       <file>
         <name>Uninstall.SqlDataProvider</name>
       </file>
       <file>
         <name>YourCompanyName.Survey.AccessDataProvider.dll</name>
       </file>
       <file>
         <name>01.00.00.AccessDataProvider</name>
       </file>
       <file>
         <name>Uninstall.AccessDataProvider</name>
       </file>
       <file>
      ::
     </files>
   </folder>
 </folders>
</dotnetnuke>

Now that this file is created take all your files for your module and zip them up into a compressed zip file. This is the private assembly that you can distribute. Remember, you don't need to include any source files in this distribution, just the compiled assembly, ascx files, and other supporting files---no filename.vb class files are needed.

Installing The Assembly

Finally, now that we have our module packaged up into a distribution, let's cover how to install it into a DNN portal. DNN provides a very simple method for installing modules into the portal via the File Manager. That's the reason for all the work you did creating an assembly, a SQL creation script, and a .dnn file, now it all comes together so an admin can easily install it into their DNN installation. Use the following procedure to install your module:

1. Logon as Host access.
2. Go to the File Manager under the Host menu.
3. Under the Options Menu, select Add New File(s).
4. In the Upload File screen, select Custom Module from the radio button selection.
5. Click on the browse button to find your assembly package.
6. Click Add to display the package in the list.
7. Click Upload to upload the assembly to the server.

Once the file is uploaded DNN will look for the .dnn file to find the information it needs in order to configure your module into the portal. The File Manager will display the status of the module installation on the screen. If any errors are encountered during the install process, DNN will let you know by displaying the specific problem on the screen in bold red text.

If everything installs correctly, you should now be able to add your module to a page by selecting it from the drop down menu.

By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.