Posted by: Philip Beadle
6/30/2008 

I am rewriting the Help module and am doing it TDD style.  As I was writing a test to check the adding of a tutorial to the db I realised that what I really wanted to do was not only add the tutorial but also the list of associated category IDs.  So I did a bit of research and came up with this.  There is now a new parameter on the Help_AddTutorial sproc that takes an xml field.  The XML is a list of the categories.  The XML is then parsed and added to a temporary table which is then used to add the multiple items to the Help_TutorialCategory table.

Here's the new sproc

CREATE PROCEDURE {databaseOwner}{objectQualifier}Help_AddTutorial
 @Title nvarchar(250),
 @ModuleID int,
 @ProductImage nvarchar(150),
 @Featured bit,
 @KeyWords nvarchar(200),
 @DownloadName nvarchar(200),
 @ShortDesc nvarchar(500),
 @Archive bit,
 @Description ntext,
 @CategoryIDs xml
AS

INSERT INTO {databaseOwner}{objectQualifier}Help_Tutorials (
 [Title],
 [ModuleID],
 [ProductImage],
 [Featured],
 [KeyWords],
 [DownloadName],
 [ShortDesc],
 [Archive],
 [Description],
 [LastUpdated],
 [TutorialGUID]
) VALUES (
 @Title,
 @ModuleID,
 @ProductImage,
 @Featured,
 @KeyWords,
 @DownloadName,
 @ShortDesc,
 @Archive,
 @Description,
 GetDate(),
 NewID()
)
DECLARE @TutorialID int
SET @TutorialID = SCOPE_IDENTITY()

DECLARE @Categorys TABLE (ID int)
INSERT INTO @Categorys (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @CategoryIDs.nodes('/Categorys/id') as ParamValues(ID)

INSERT INTO {databaseOwner}{objectQualifier}Help_TutorialCategory([TutorialId],[CategoryId])
SELECT @TutorialID, CategoryId FROM {objectQualifier}Help_Category INNER JOIN @Categorys c ON {objectQualifier}Help_Category.CategoryID =  c.ID

SELECT TutorialID FROM {objectQualifier}Help_Tutorials WHERE TutorialID = @TutorialID

GO

and of course how to call it.

dnn_Help_AddTutorial
@Title ='test',
@ModuleID = 321,
@ProductImage ='test',
@Featured = false,
@KeyWords  ='test',
@DownloadName  ='test',
@ShortDesc  ='test',
@Archive = true,
@Description  ='test',
@CategoryIDs = '12'

Now my test passes and the Help module is much better off for it and TDD again saves the day. 

Do you TDD??

Trackback Print

Tags:

Categories:

Location: Blogs Philip Beadle Module :: Help