<< devLink Recap | Ohio Web Leaders Meeting Recap >>

Retrieve a list of pages from Sitefinity

Rick Kierner posted on August 26, 2008 17:43

Just ran into a situation where a marketing person needed a list of every page in my project and its URL. I got this query from a coworker and he graciously let me publish it to my blog.

     
DECLARE @PageID uniqueidentifier

CREATE TABLE #temp 

(name VARCHAR(1024),

url VARCHAR(1024)

)

  

DECLARE curs CURSOR FOR SELECT ID FROM sf_pagebase

OPEN curs

FETCH NEXT FROM curs INTO @PageID

WHILE @@FETCH_STATUS = 0

BEGIN

declare @url varchar(1024)

declare @ParentID uniqueidentifier

declare @MenuName varchar(1024)

  

SELECT @MenuName = NULL, @url = NULL, @ParentID = null

select @url = '/' + Name, @ParentID = ParentID, @MenuName = MenuName from sf_pagebase pb 

inner join sf_cmspagecontent cpc on pb.id = cpc.id 

where pb.id = @PageID

  

WHILE @ParentID IS NOT NULL

BEGIN

  

SELECT @ParentID = ParentID, @url = '/' + Name + @url, @MenuName = MenuName + ' > ' + @MenuName from sf_pagebase pb

inner join sf_cmspagecontent cpc on pb.id = cpc.id WHERE pb.id = @ParentID

END

select @url = '~' + @url + '.html'

if (@MenuName IS NOT NULL)

INSERT INTO #temp VALUES(@MenuName, @url)

  

FETCH NEXT FROM curs INTO @PageID

END

  

CLOSE curs

DEALLOCATE curs

  

SELECT * FROM #temp

order by url

  

DROP TABLE #temp

Technorati Tags: Sitefinity,SQL

Posted in: Tags: sql, sitefinity

Actions: E-mail | Permalink | Comments (2) | Comment RSS RSS comment feed

| E-mail | Kick it! | DZone it! | del.icio.us

Lookup Fields in a ListTemplate My goal is to have a single click deployment of my intranet site and data. I have several li...List of WSS 3.0 List Template Names If you are creating a list with code, you'd do it like this: 1: SPWeb web = proper...Creating Workflow for a List ProgrammaticallyIf you are familiar with Visual Studio Extensions for Windows SharePoint Services (VSeWSS), you know...

Comments

David

August 26. 2008 17:57

There is a little issue with the query and that happens when multiple language versions are available. This becomes an issue in the nested 'While' statement as it will repeat inner items because of multiple sf_cmspagecontent returns.

There is two solutions to this. The first one is to simply put a distinct after the select ('SELECT DISTINCT @ParentID = ParentID, @url = '/' + Name + @url, @MenuName = MenuName + ' > ' + @MenuName from sf_pagebase...')
The second solution is to just search on a specific langID ('SELECT @ParentID = ParentID, @url = '/' + Name + @url, @MenuName = MenuName + ' > ' + @MenuName from sf_pagebase inner join sf_cmspagecontent cpc on pb.id = cpc.id WHERE pb.id = @ParentID AND LangID = 127')

The LangID might change based on your project, but I believe that it is the default for english.

no site

Rick

August 27. 2008 18:21

Happy Birthday Dave!

http://www.rickdoes.net/http://www.rickdoes.net/

Comments are closed

Retrieve a list of pages from Sitefinity

Search Blog

Tag Cloud

Month List

Recent Posts