How to add database script in DNN custom module development package?

Question

Hi,i have made a custom module on DNN ,created its package and integrated with another DNN application.It works fine.But after uploading the module in another DNN application i am creating module database manually.thats what my problem..

I want to create a module package with its database script.so that when end user upload my module the module database should create automatically.

In simple words i need one click solution just like commercial modules avilable in market. please suggest any solution.

Answer 1

Welcome to developing for DotNetNuke, where the official website provides zero documentation and everything is learned from experimentation, blogs, forums and sites attempting to sell you something.

I suggest going to your DNN root folder and opening up the /Install/Module/UsersOnline_05.01.00_Install.resources file. It's just a zip archive renamed to .resources. Inside that archive is the packaged "Users Online" module and that's the example I'm going to walk through.

If you already have your .DNN xml package created for your module, you need to add a new <compontent> entry to let DNN know to execute your SQL scripts during installation:

...snip...
<components>
    <component type="Script">
      <scripts>
        <basePath>DesktopModules\UsersOnline</basePath>
        <script type="Install">
          <path>Providers\DataProviders\SqlDataProvider</path>
          <name>04.09.04.SqlDataProvider</name>
          <version>04.09.04</version>
        </script>
        <script type="Install">
          <path>Providers\DataProviders\SqlDataProvider</path>
          <name>05.01.00.SqlDataProvider</name>
          <version>05.01.00</version>
        </script>
        <script type="UnInstall">
          <path>Providers\DataProviders\SqlDataProvider</path>
          <name>Uninstall.SqlDataProvider</name>
          <version>05.01.00</version>
        </script>
      </scripts>
</component>
...snip...

During module installation, DNN will execute the scripts entered here in the order of their version number. If the current module being installed has never been installed then it would go in this order:

1. 04.09.04
2. 05.01.00

If the module was already installed and is being upgraded (from 04.09.04), it would skip the previous versions' scripts (assuming they have already been executed) and just run the newer 05.01.00 script which should bring everything up to date. It's your responsibility to create your SQL scripts to support the built-in upgrade mechanism.

There is also an "UnInstall" script which is executed when the user un-installs the module. This lets you clean up after your module.

Each SQL script contains the T-SQL commands needed to create your module's schema, default data, stored procedures, etc. Here's a snippet of the OnlineUsers module's script:

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DNNUOL_GetOnlineUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE {databaseOwner}{objectQualifier}DNNUOL_GetOnlineUsers
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DNNUOL_GetOnlineUsers
@PortalID int,
@IncludeHosts bit
AS
IF @IncludeHosts = 0
BEGIN
SELECT 
    UO.UserID,
    U.UserName,
    U.DisplayName,
    U.FirstName, 
    U.LastName, 
    U.FirstName + ' ' + U.LastName AS FullName
FROM
    {databaseOwner}{objectQualifier}UsersOnline UO INNER JOIN {databaseOwner}{objectQualifier}Users U ON UO.UserID = U.UserID INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP ON U.UserID = UP.UserID
WHERE
    UO.PortalID = @PortalID AND UO.UserID = U.UserID AND UP.Authorised = 1 AND U.IsSuperUser = 0 -- Inner Join takes care of SU = 0, but for sanity.
END
ELSE
BEGIN
SELECT DISTINCT
    UO.UserID,
    U.UserName,
    U.DisplayName,
    U.FirstName, 
    U.LastName, 
    U.FirstName + ' ' + U.LastName AS FullName
FROM
    {databaseOwner}{objectQualifier}UsersOnline UO INNER JOIN {databaseOwner}{objectQualifier}Users U ON UO.UserID = U.UserID, {databaseOwner}{objectQualifier}UserPortals UP
WHERE
    UO.PortalID = @PortalID AND UO.UserID = U.UserID AND UP.Authorised = 1
END
GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/

Note the use of {databaseOwner} and {objectQualifier} in front of each table or procedure being created in the database. These are tokens and are replaced at run-time with the settings from the installation's web.config file. You can generally assume these are going to be replaced with "dbo." but if you are selling your module or providing it to third-parties for installation you will need to support custom owners and qualifiers.

Here are some additional resources:

• Creating DotNetNuke SqlDataProvider Files Using SSMS
• Best Practices: Development Team Collaboration and DotNetNuke (Part 2: The SqlDataProvider Files)
• Creating the DotNetNuke® Survey Module (for DNN 4.0 but still helpful)