facebook  linkedin  Twitter  skype  Rss googlePlus

Exporting DNN Blogs to Ventrian News Articles

May 24 2009
8358

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

About the Author, Patrick Santry

Patrick Santry, has two decades of experience in enabling businesses to take advantage of the digital landscape. A well rounded experience in technology, and business is what sets me apart from the rest of the pack. When it comes to an overall digital strategy my experience is impressive.

BS in Computer Information Systems. Four time recipient of the Microsoft MVP Award, and author of several books and magazine articles on digital technologies.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.

Popular Articles