DNN is a great portal application, but some of the provided modules are lacking. The weblog module is one of them (even though it has made some improvements in this last version).
Due to the lack of certain features, we found Ventrian News Articles module that provides everything we needed. Problem we had was we were using the DNN blog module for so long, we had a considerable amount of content that we didn't want to lose and be able to export to the new module.
The following SQL stored procedure was developed to export all the blog content, including the user comments into the new module.
I managed to accomplish the export/import in SQL. Use at your own risk. Many values were hard coded, since the blog didn't have an equivalent.
You have to first place a target instance of the articles module in a page, and then pass the module id of the module to this sproc.
This imports the previous version of the DNN blog module. The most recent has additional fields, you can modify this code to grab those fields and import into the News articles module.
GO
/****** Object: StoredProcedure [dbo].[ExportBlogToNews] Script Date: 05/22/2009 18:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Patrick Santry
-- Create date: 5/22/2009
-- Description: Export DNN blog entries to News articles.
-- =============================================
ALTER PROCEDURE [dbo].[ExportBlogToNews]
@BlogModuleId As int,
@NewsModuleId As int,
@AuthorId As int
AS
DECLARE @ApproverID int, @CreatedDate datetime, @LastUpdate datetime, @Title nvarchar(255)
DECLARE @IsApproved bit, @NumberOfViews int, @IsDraft bit, @StartDate datetime, @EndDate datetime
DECLARE @ModuleID int, @ImageUrl nvarchar(255), @IsFeatured bit, @LastUpdateID int, @URL nvarchar(255)
DECLARE @IsSecure bit, @IsNewWindow bit, @Summary nvarchar(4000), @CommentCount int
DECLARE @PageCount int, @Rating decimal(3,2), @RatingCount int
DECLARE @PageText nvarchar(4000), @EntryID int
DECLARE @NewArticleID int
DECLARE @NewPageCount int
DECLARE @CommentUserID int, @Comment nvarchar(4000), @CommentAdded datetime, @CommentTitle nvarchar(255), @CommentAuthor nvarchar(50)
DECLARE @SecondCursorQuery CURSOR
DECLARE CursorQuery CURSOR FOR
SELECT EntryID, Title, Entry, AddedDate, Description
FROM dbo.Blog_Entries Where BlogId = @BlogModuleId AND Published = 1
OPEN CursorQuery
-- Perform the first fetch
FETCH NEXT FROM CursorQuery
INTO @EntryID, @Title, @PageText, @CreatedDate, @Summary
PRINT 'Record Status' + CAST(@@FETCH_STATUS as varchar)
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- now insert the id from the main table into the related table.
-- hard code values for now for some options.
-- This is executed as long as the previous fetch succeeds.
INSERT INTO dbo.DnnForge_NewsArticles_Article (
[AuthorID],
[ApproverID],
[CreatedDate],
[LastUpdate],
[Title],
[Summary],
[IsApproved],
[NumberOfViews],
[IsDraft],
[StartDate],
[EndDate],
[ModuleID],
[ImageUrl],
[IsFeatured],
[LastUpdateID],
[Url],
[IsSecure],
[IsNewWindow],
[PageCount],
[CommentCount]
) VALUES (
@AuthorID,
@AuthorId,
@CreatedDate,
@CreatedDate,
@Title,
@Summary,
1,
2456,
0,
@CreatedDate,
Null,
@NewsModuleId,
Null,
0,
@AuthorId,
Null,
0,
0,
1,
0
)
select @NewArticleID = SCOPE_IDENTITY()
-- Now insert page
INSERT INTO dbo.DnnForge_NewsArticles_Page (
[ArticleID],
[Title],
[PageText],
[SortOrder]
) VALUES (
@NewArticleID,
@Title,
@PageText,
0
)
-- Now insert comments into the articles comments
SET @SecondCursorQuery = CURSOR FOR
SELECT UserID, Comment, AddedDate, Title, Author
FROM dbo.Blog_Comments Where EntryId = @EntryID AND Approved = 1
OPEN @SecondCursorQuery
FETCH NEXT FROM @SecondCursorQuery
INTO @CommentUserID, @Comment, @CommentAdded, @CommentTitle, @CommentAuthor
WHILE @@FETCH_STATUS = 0
BEGIN
-- In case we have a null user id in the blog comments
IF @CommentUserID Is NULL
BEGIN
SET @CommentUserID = 0
END
INSERT INTO dbo.DnnForge_NewsArticles_Comment (
[ArticleID],
[UserID],
[Comment],
[RemoteAddress],
[Type],
[TrackbackUrl],
[TrackbackTitle],
[TrackbackBlogName],
[TrackbackExcerpt],
[AnonymousName],
[AnonymousEmail],
[AnonymousURL],
[NotifyMe],
[IsApproved],
[ApprovedBy]
) VALUES (
@NewArticleID,
@CommentUserID,
@Comment,
Null,
0,
Null,
Null,
Null,
Null,
@CommentAuthor,
Null,
Null,
0,
1,
@AuthorId
)
UPDATE dbo.DnnForge_NewsArticles_Article
SET [CommentCount] = (select count(*) from dbo.DnnForge_NewsArticles_Comment Comment where Comment.ArticleID = dbo.DnnForge_NewsArticles_Article.ArticleID and Comment.IsApproved = 1)
WHERE ArticleID = @NewArticleID
FETCH NEXT FROM @SecondCursorQuery
INTO @CommentUserID, @Comment, @CommentAdded, @CommentTitle, @CommentAuthor
END
FETCH NEXT FROM CursorQuery
INTO @EntryID, @Title, @PageText, @CreatedDate, @Summary
END
CLOSE CursorQuery
DEALLOCATE CursorQuery
-- Insert statements for procedure here DnnForge_NewsArticles_Article