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