When creating custom Dotnetnuke modules using Open Web Studio, we often need to create a table in Microsoft SQL Server to hold the custom modules data.
Below is the script that we use internally to quickly build a table to hold our custom modules data. After we have the basic table in place, we just edit it to suit our requirements using Microsoft's SQL Server Management Studio.
CREATE TABLE [dbo].[YourBusiness_ModuleName](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_YourBusiness_ModuleName] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[YourBusiness_ModuleName] ADD CONSTRAINT [DF_YourBusiness_ModuleName_Created] DEFAULT (getdate()) FOR [Created]
NOTE: The script above was created & tested in Microsoft SQL Server 2008.
HOW TO USE THIS SQL SCRIPT?
STEP 1:
Paste the folowing script into DNN's built in SQL module (HOST > SQL).
CREATE TABLE [dbo].[YourBusiness_ModuleName](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_YourBusiness_ModuleName] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[YourBusiness_ModuleName] ADD CONSTRAINT [DF_YourBusiness_ModuleName_Created] DEFAULT (getdate()) FOR [Created]
STEP 2:
Modify the table name to suit your business & module name.
EXAMPLE:
If we were building a "jobs module" then we would change the 4 occurrences of [YourBusiness_ModuleName] on lines 1, 12 & 13:
CREATE TABLE [dbo].[YourBusiness_ModuleName](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_YourBusiness_ModuleName] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[YourBusiness_ModuleName] ADD CONSTRAINT [DF_YourBusiness_ModuleName_Created] DEFAULT (getdate()) FOR [Created]
to something like [MWD_Jobs]:
CREATE TABLE [dbo].[MWD_Jobs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_MWD_Jobs] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[MWD_Jobs] ADD CONSTRAINT [DF_MWD_Jobs_Created] DEFAULT (getdate()) FOR [Created]
STEP 3 (OPTIONAL):
Modify the database object qualifier to suit your custom requirements.
EXAMPLE:
If you are using a custom database object qualifier then you will need to modify the 2 occurrences of [dbo] on lines 1 & 13:
CREATE TABLE [dbo].[MWD_Jobs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_MWD_Jobs] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[MWD_Jobs] ADD CONSTRAINT [DF_MWD_Jobs_Created] DEFAULT (getdate()) FOR [Created]
to something like [mydbo]:
CREATE TABLE [mydbo].[MWD_Jobs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](100) NULL,
[Summary] [varchar](200) NULL,
[Detail] [varchar](max) NULL,
[ModuleID] [int] NULL,
[PortalID] [int] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL,
CONSTRAINT [PK_MWD_Jobs] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [mydbo].[MWD_Jobs] ADD CONSTRAINT [DF_MWD_Jobs_Created] DEFAULT (getdate()) FOR [Created]
STEP 5:
Make sure that the "Run As Script" checkbox is ticked (you can find this checkbox at the bottom of DNN's built in SQL module), then click on "Execute" link to create your modules table.
STEP 6:
Now you have the basic module table setup you can easily add or remove columns using Microsoft's SQL Server Management Studio.
Please leave any comments or ideas below ;)