quick sql select for published pages
Hi all
I have done some quick checks on the SQL backend for Sitefinity 5.1.3450 (and this may apply to earlier versions too but I haven't tested those) to write a select statement that fetches all published page urls in a site. It turns out that the page urls are stored in a table called sf_page_node in a hierarchical structure. You can fetch your page urls from this table using the Common Table Element (CTE) structure in SQL 2005 onwards, and the following query seems to work nicely:
WITH Pages as
(
SELECT N.content_id, N.id,CONVERT(uniqueidentifier,NULL) as parent_id, CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
FROM [dbo].sf_page_node N
WHERE url_name_ = 'Pages'
UNION ALL
SELECT N.content_id,N.id, parent.id, CONVERT(nvarchar(max),parent.url) +CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
FROM [dbo].sf_page_node N
JOIN Pages parent
ON N.parent_id = parent.id
AND N.approval_workflow_state_ = 'Published'
)
SELECT SUBSTRING(url,7,LEN(url)-6) from Pages
ORDER BY url
I hope this helps some of you
Hello,
Thanks for submitting your resolution to our forums. The same functionality can be achieved via our API.
private NewsItem GetNewsItemNativeAPI(Guid masterNewsId)
NewsManager newsManager = NewsManager.GetManager();
NewsItem item = newsManager.GetNewsItems().Where(newsItem => newsItem.Id == masterNewsId).FirstOrDefault();
if (item != null)
item = newsManager.Lifecycle.GetLive(item) as NewsItem;
return item;
Thanks Patrick. I know it is always preferable to use the API if possible, however I required this as a quick solution for an external process which I am using to evaluate the effectiveness of the Sitefinity output cache settings and to monitor uncached page load times. The only other alternative would have been to write a web service that references the API. Indeed I am going to look at that solution in any case, because it will help with other areas which involve automated inserts and updates to Sitefinity, something that I would never attempt via direct sql updates. I only posted this query because it is purely a select statement. I hope I didn't cause any problems.