SQL Scripts for Modules

Return to previous page

  • 4/7/2015
  • 4996 Views

Comments

4996 Views

SQL Scripts for Modules

Last updated 5 years ago

Comments

Common

(Enter the content of this article below)

Advanced

 


DotnetNuke SQL Scripts

Generating SQL Scripts for installing, upgrading and uninstalling your modules in DotNetNuke is an important thing to understand, below we cover some of the basics that everyone generating SQL Scripts should keep in mind.

Repeatable SQL Scripts

When writing SQL Scripts for DNN modules, you should write them in a manner that you can execute them over and over without them throwing errors. This is typically done by always checking for something before doing something. For example, if you are adding a table, you should always check to make sure that table doesn't exist before adding it. How you handle it if it does exist is up to you, you could drop it, or you could simply choose to not create it again. Here's some sample script that does just that from the http://dnnsimplearticle.codeplex.com project.


/** CREATE DNNSimpleArticle_Article Table **/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]') AND type in (N'U'))
DROP TABLE {databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}DNNSimpleArticle_Article]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE {databaseOwner}[{objectQualifier}DNNSimpleArticle_Article](
[ID] [int] NOT NULL IDENTITY (1, 1),
[Title] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[Body] [nvarchar](max) NULL,
[CreatedOnDate] [datetime] NULL,
[LastModifiedOnDate] [datetime] NULL,
[CreatedByUserID] [int] NULL,
[LastModifiedByUserID] [int] NULL,
[ModuleId] [int] NULL,
[ContentItemId] [int] NULL

CONSTRAINT [PK_{objectQualifier}DNNSimpleArticle_Article] PRIMARY KEY CLUSTERED
(

[ID] ASC{br}
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO



Tokens

There are two common tokens in DotNetNuke SQL Scripts that you should utilize when you create SQL scripts. databaseOwner and objectQualifier, these tokens will cause SQL scripts to not execute in tools such as Query Analyzer or SQL Management Studio's query window, but will run on the Host/SQL page of a DotNetNuke website. DNN replaces these tokens at execution time with the values defined in the web.config file of the website. Please note: token names are case sensitive.


Versions

Creating SQL Script versions is an important feature to understand when you are developing modules that are upgradeable, meaning someone who is using Version 1 of a module can easily upgrade to Version 2 of a module. More info coming soon

Sample Project

The SQL script used in the example above is from the DNNSimpleArticle project on Codeplex http://dnnsimplearticle.codeplex.com
Contents
No sections defined

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out