Database Providers

Database Design

This section reviews the tables and stored procedures that make up the backend database for the Events module.

The DotNetNuke (DNN) development team wanted a module that could track events, provide a time for the event, and expire the event so it would no longer be displayed after the expiration date was met. To accomplish this, the team needed to create a database structure to store event information, associate the information with DNN, and then create stored procedures to add, update, and delete event information. In the next sections, you learn the structure of the database tables for the module and the stored procedures for performing actions on the data.

For your database manipulation, you can use SQL Enterprise Manager, Query Analyzer, or Visual Studio .NET. The examples in this chapter use Visual Studio .NET 2003 for module development and database design. The idea behind this chapter and the next three chapters on module development is to give you a comprehensive review of the module structure contained within the DNN distribution.

As discussed in Chapter 12, keep in mind that you are not bound by the underlying physical database. You can use any other database as your backend for module development, and you can develop modules for whatever database your DotNetNuke install is using. SQL Server 2000 is used throughout the book because DotNetNuke natively supports SQL Server 2000 and SQL Server 2005 out of the box. The point of this exercise is to help you understand the physical database structure and how it applies to the database provider and abstraction layer you will create for your modules.

The next section covers the tables used to store the information. After a table is created, you can begin writing stored procedures to manipulate the data. One table is used for storing the event information: the Events table.

Events Table

The Events table stores your event information for the module. It is defined as shown in Figure 13-1.

Figure 13-1

The most important field contained within the structure as far as DNN integration goes is ModuleID. It contains an integer value that is assigned by DNN when you create an instance of your module. This value identifies the specific instance of the module with which you are working. It's important because multiple instances of a module can exist on a single page and a button click on one instance of the module must not operate on data from another instance of the module. Any values you store specific to this module instance will key off the value contained in this field. You will see this as you go create the stored procedures. The other fields within the database are specific to the module itself, and all depend on how you structure your application.

The ItemID field is for identifying the primary key of a specific item within your table. The main concept here, however, is to fully integrate and create a unique instance of your module with unique instance items. You need to specify a ModuleID value that relates to the key provided by DotNetNuke for every module instance.

The following list describes each item in the Events table:

• ItemID: The primary key of the event information within the table.

• ModuleID: Because DNN can contain many instances of a module all with different information, this key consolidates all event information into the one module instance.

• Description: A text description of your event for display.

• DateTime: A date and time for when the event begins.

• Title: The title of the event presented to the user.

• ExpireDate: When the event will no longer be displayed in your portal.

• CreatedByUser: Tracks the ID of the portal user that created the event.

• CreatedDate: When the event was created.

• Every: If this is a recurring event, the period of time the event occurs. This is related to the Period field, which defines the amount of time between events.

• Period: Related to the Every field, this specifies the period between events, that is, days, weeks, months, years.

• IconFile: Displays an icon next to the event listing within the module.

• AltText: Alternate text to be displayed when you hover your mouse cursor over the icon. This also aids your portal in complying with the Americans with Disabilities Act (ADA) Section 508.

This covers how you're going to store the data that is entered into the module. Next, you create the stored procedures necessary for working with the data.

AddEvent Stored Procedure

The first stored procedure you're going to create for this module is AddEvent (see Listing 13-1). This procedure is used to add an event to the Events table.

Listing 13-1: The AddEvent Stored Procedure for the Events Module

 create procedure dbo.AddEvent    @ModuleID    int,    @Description nvarchar(2000),    @DateTime    datetime,    @Title       nvarchar(100),    @ExpireDate  datetime = null,    @UserName    nvarchar(200),    @Every       int,    @Period      char(1),    @IconFile    nvarchar(256),    @AltText     nvarchar(50) as insert into Events (    ModuleID,    Description,    DateTime,    Title,    ExpireDate,    CreatedByUser,    CreatedDate,    Every,    Period,    IconFile,    AltText ) values (    @ModuleID,    @Description,    @DateTime,    @Title,    @ExpireDate,    @UserName,    getdate(),    @Every,    @Period,    @IconFile,    @AltText ) select SCOPE_IDENTITY() GO 

There is nothing special about this stored procedure — it's a basic insert statement, and it accepts parameters from your database provider for populating the event information. One thing to keep in mind throughout all the stored procedures is that the ModuleID parameter is always passed when creating a new record that is associated with your module. In Chapter 14, you learn how to obtain the module ID from DotNetNuke and pass it to your stored procedure.

DeleteEvent Stored Procedure

The DeleteEvent stored procedure (see Listing 13-2) is for deleting an event previously added to DotNetNuke.

Listing 13-2: The DeleteEvent Stored Procedure for the Events Module

 create procedure dbo.DeleteEvent    @ItemId int as delete from   Events where  ItemId = @ItemId GO 

There is no need to pass a parameter value for ModuleID in this procedure. Because you're only concerned about performing a delete operation on the data, you only need to determine the primary key of the specific event record.

GetEvent Stored Procedure

You use the GetEvent stored procedure (see Listing 13-3) to get a single event's information.

Listing 13-3: The GetEvent Stored Procedure for the Events Module

 CREATE procedure dbo.GetEvent    @ItemId   int,    @ModuleId int as select    Events.ItemId,    Events.ModuleId,    Events.Description,    Events.DateTime,    Events.Title,    Events.ExpireDate,    ‘CreatedByUser' = Users.FirstName + ' ' + Users.LastName,    Events.CreatedDate,    Events.Every,    Events.Period,    ‘IconFile' = case when Files.FileName is null then Events.IconFile else Files.Folder + Files.FileName end,    Events.AltText from   Events left outer join Users on Events.CreatedByUser = Users.UserId left outer join Files on Events.IconFile = ‘fileid=' + convert(varchar,Files.FileID) where  ItemId = @ItemId and    ModuleId = @ModuleId GO 

Here you are passing the ModuleID parameter along with the specific ItemID for the event. Use this stored procedure for obtaining a single event for modification or display.

GetEvents Stored Procedure

You use the GetEvents stored procedure (see Listing 13-4) for obtaining several events for a listing of a specific module instance.

Listing 13-4: The GetEvents Stored Procedure for the Events Module

 CREATE procedure dbo.GetEvents    @ModuleId int as select    Events.ItemId,    Events.ModuleId,    Events.Description,    Events.DateTime,    Events.Title,    Events.ExpireDate,    Events.CreatedByUser,    Events.CreatedDate,    Events.Every,    Events.Period,    ‘IconFile' = case when Files.FileName is null then Events.IconFile else  Files.Folder + Files.FileName end,    Events.AltText,    ‘MaxWIdth' = (select max(WIdth) from Events left outer join Files on  Events.IconFile = ‘fileid=' + convert(varchar,Files.FileID) where  ModuleId =   @ModuleId and (ExpireDate > getdate() or ExpireDate is null)) from   Events left outer join Files on Events.IconFile = ‘fileid=' +  convert(varchar,Files.FileID) where  ModuleId = @ModuleId and    (ExpireDate > getdate() or ExpireDate is null) order by DateTime GO 

The only parameter passed to this stored procedure is the ModuleID value. This pulls all events for one module instance.

GetEventsByDate Stored Procedure

The GetEventsByDate stored procedure (see Listing 13-5) pulls all events within a specified date range for a specific module instance.

Listing 13-5: The GetEventsByDate Stored Procedure for the Events Module

 CREATE procedure dbo.GetEventsByDate    @ModuleId int,    @StartDate datetime,    @EndDate datetime as select    Events.ItemId,    Events.ModuleId,    Events.Description,    Events.DateTime,    Events.Title,    Events.ExpireDate,    Events.CreatedByUser,    Events.CreatedDate,    Events.Every,    Events.Period,    ‘IconFile' = case when Files.FileName is null then Events.IconFile else  Files.Folder + Files.FileName end,    Events.AltText from   Events left outer join Files on Events.IconFile = ‘fileid=' +  convert(varchar,Files.FileID) where  ModuleId = @ModuleId and    ( (Period is null and (DateTime >= @StartDate and DateTime  <= @EndDate)) or Period is not null ) order by DateTime GO 

UpdateEvent Stored Procedure

The final stored procedure for the Events module is the UpdateEvent module (see Listing 13-6). This enables you to update an existing event's information.

Listing 13-6: The UpdateEvent Stored Procedure for the Events Module

 create procedure dbo.UpdateEvent    @ItemId      int,    @Description nvarchar(2000),    @DateTime    datetime,    @Title       nvarchar(100),    @ExpireDate  datetime = null,    @UserName    nvarchar(200),    @Every       int,    @Period      char(1),    @IconFile    nvarchar(256),    @AltText     nvarchar(50) as update Events set    Description = @Description,        DateTime = @DateTime,        Title = @Title,        ExpireDate = @ExpireDate,        CreatedByUser = @UserName,        CreatedDate = getdate(),        Every = @Every,        Period = @Period,        IconFile = @IconFile,        AltText = @AltText where  ItemId = @ItemId GO 

That's it for your database procedures. The next section covers how to wrap this up and create your own physical database provider for DotNetNuke.