Hi,
thanks a lot for this useful module, I needed a pager and this module adds one nicely. My provider still sticks with sql server 2000 and just in case somebody else needs this modification, I appended it here (save as 02.00.01.SqlDataPovider and replace the one inside Radiant.BlogDisplay_02.00.01.zip for sql 2000 compatibility).
Keep up the good work, regards
Tim
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Radiant_ListEntriesByPortal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}Radiant_ListEntriesByPortal]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Radiant_GetEntryCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}Radiant_GetEntryCount]
GO
/****** Object: StoredProcedure Radiant_ListEntriesByPortal Script Date: 08/03/2007 19:46:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}Radiant_ListEntriesByPortal]
@PortalID int,
@MaxEntries int = 10,
@CurrentPage int = 1
AS
DECLARE @CurrentEntries int
DECLARE @EntryList TABLE (RowNo int identity, EntryID int)
SET @CurrentEntries = @MaxEntries * @CurrentPage
SET ROWCOUNT @CurrentEntries
INSERT INTO @EntryList (EntryID)
SELECT E.[EntryID]
FROM {databaseOwner}[{objectQualifier}Blog_Blogs] B
INNER JOIN {databaseOwner}[{objectQualifier}Blog_Entries] E ON B.[BlogID] = E.[BlogID]
INNER JOIN {databaseOwner}[{objectQualifier}Users] U ON B.[UserID] = U.[UserID]
WHERE B.PortalID = @PortalID
AND (E.[Published] = 1)
AND (B.[Public] = 1)
ORDER BY E.AddedDate DESC
SELECT TOP 100 PERCENT
U.[UserID],
U.[Username],
U.[FirstName] + ' ' + U.[LastName] AS UserFullName,
E.[EntryID] as ItemID,
E.[BlogID],
E.[Title],
Description = E.[Description],
E.[Entry],
E.[AddedDate],
E.[Published],
E.[Copyright],
E.[PermaLink],
IsNull(E.[AllowComments],B.[AllowComments]) As AllowComments,
B.[ParentBlogID],
B.[AllowAnonymous],
B.[Syndicated] AS BlogSyndicated,
B.[Public] AS BlogPublic,
(Select Count(*) FROM dbo.[Blog_Comments] WHERE EntryID = E.EntryID AND (Approved = 1)) As CommentCount
FROM {databaseOwner}[{objectQualifier}Blog_Blogs] B INNER JOIN
{databaseOwner}[{objectQualifier}Blog_Entries] E ON B.[BlogID] = E.[BlogID] INNER JOIN
@EntryList EL ON EL.EntryID = E.[EntryID] INNER JOIN
{databaseOwner}[{objectQualifier}Users] U ON B.[UserID] = U.[UserID]
WHERE El.RowNo >= 1 + (@CurrentPage - 1) * @MaxEntries
ORDER BY EL.RowNo ASC
GO
/****** Object: StoredProcedure [dbo].[Radiant_GetEntryCount] Script Date: 05/08/2008 08:55:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}Radiant_GetEntryCount]
@PortalID int = 0
AS
SET NOCOUNT ON
SELECT count(0) as rows
FROM {databaseOwner}[{objectQualifier}Blog_Blogs] B INNER JOIN
{databaseOwner}[{objectQualifier}Blog_Entries] E ON B.[BlogID] = E.[BlogID]
WHERE B.PortalID = @PortalID
AND E.Published = 1
AND B.[Public] = 1
GO
-- code to resolve caching problem
UPDATE {databaseOwner}[{objectQualifier}tabmodules] set cachetime = 0
FROM {databaseOwner}[{objectQualifier}TabModules] INNER JOIN
{databaseOwner}[{objectQualifier}Modules] ON {databaseOwner}[{objectQualifier}TabModules].ModuleID = {databaseOwner}[{objectQualifier}Modules].ModuleID INNER JOIN
{databaseOwner}[{objectQualifier}ModuleDefinitions] ON {databaseOwner}[{objectQualifier}Modules].ModuleDefID = {databaseOwner}[{objectQualifier}ModuleDefinitions].ModuleDefID
WHERE ({databaseOwner}[{objectQualifier}ModuleDefinitions].FriendlyName = 'blogdisplay')
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/