03.01.01.sqldataprovider

SharpNuke源代码

	SELECT COUNT(*) as TotalRecords
	FROM #PageIndex


	SELECT {objectQualifier}Vendors.*,
       		'Banners' = ( select count(*) from {objectQualifier}Banners where {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId )
	FROM {objectQualifier}Vendors
	INNER JOIN #PageIndex PageIndex
		ON {objectQualifier}Vendors.VendorId = PageIndex.VendorId
	WHERE ( (PageIndex.IndexID > @PageLowerBound) OR @PageLowerBound is null )	
		AND ( (PageIndex.IndexID < @PageUpperBound) OR @PageUpperBound is null )	
	ORDER BY
		PageIndex.IndexID	
GO


if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[FK_{objectQualifier}EventLog_{objectQualifier}EventLogConfig]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}EventLog] DROP CONSTRAINT [FK_{objectQualifier}EventLog_{objectQualifier}EventLogConfig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[PK_EventLogConfig]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] DROP CONSTRAINT [PK_EventLogConfig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[PK_{objectQualifier}EventLogConfig]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] DROP CONSTRAINT [PK_{objectQualifier}EventLogConfig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[FK_EventLogConfig_EventLogTypes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] DROP CONSTRAINT [FK_EventLogConfig_EventLogTypes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[FK_{objectQualifier}EventLogConfig_{objectQualifier}EventLogTypes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] DROP CONSTRAINT [FK_{objectQualifier}EventLogConfig_{objectQualifier}EventLogTypes]
GO

DROP INDEX {databaseOwner}[{objectQualifier}EventLogConfig].[LogTypeKey_LogTypePortalID]
GO


ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] ADD CONSTRAINT [PK_{objectQualifier}EventLogConfig] PRIMARY KEY CLUSTERED  ([ID])
GO

ALTER TABLE {databaseOwner}[{objectQualifier}EventLogConfig] ADD CONSTRAINT [FK_{objectQualifier}EventLogConfig_{objectQualifier}EventLogTypes] FOREIGN KEY ([LogTypeKey]) REFERENCES {databaseOwner}[{objectQualifier}EventLogTypes] ([LogTypeKey])
GO

ALTER TABLE {databaseOwner}[{objectQualifier}EventLog] ADD CONSTRAINT [FK_{objectQualifier}EventLog_{objectQualifier}EventLogConfig] FOREIGN KEY ([LogConfigID]) REFERENCES {databaseOwner}[{objectQualifier}EventLogConfig] ([ID])
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_{objectQualifier}LogTypeKey_{objectQualifier}LogTypePortalID] ON {databaseOwner}[{objectQualifier}EventLogConfig] ([LogTypeKey], [LogTypePortalID])
GO

ALTER TABLE {databaseOwner}[{objectQualifier}Tabs] ADD
	RefreshInterval int NULL,
	PageHeadText nvarchar(500) NULL
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetTabsByParentId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetTabsByParentId]
GO


CREATE procedure {databaseOwner}{objectQualifier}GetTabsByParentId

@ParentId int

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when Files_1.FileName is null then {objectQualifier}Tabs.IconFile else Files_1.Folder + Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       RefreshInterval,
       PageHeadText
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,Files_1.FileID)
where  {objectQualifier}Tabs.ParentId = @ParentId
order by TabOrder



GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetTabs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetTabs]
GO

CREATE procedure {databaseOwner}{objectQualifier}GetTabs

@PortalId int

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end,
       RefreshInterval,
       PageHeadText
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
where  {objectQualifier}Tabs.PortalId = @PortalId
order by TabOrder, TabName




GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetTabByName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetTabByName]
GO

CREATE procedure {databaseOwner}{objectQualifier}GetTabByName

@TabName  nvarchar(50),
@PortalId int

as

select TabID, 
TabOrder, 
{objectQualifier}Tabs.PortalID, 
TabName, 
IsVisible, 
ParentId, 
[Level], 
DisableLink, 
Title, 
Description, 
KeyWords, 
IsDeleted, 
Url, 
SkinSrc, 
ContainerSrc, 
TabPath, 
StartDate, 
EndDate,
 'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end,
RefreshInterval,
PageHeadText
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
LEFT OUTER JOIN {objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
where  TabName = @TabName
and    (({objectQualifier}Tabs.PortalId = @PortalId) or (@PortalId is null AND {objectQualifier}Tabs.PortalId is null))
order by TabID

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}UpdateTab]
GO


CREATE procedure {databaseOwner}{objectQualifier}UpdateTab

@TabId              int,
@TabName            nvarchar(50),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@Title              nvarchar(200),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@IsDeleted          bit,
@Url                nvarchar(255),
@SkinSrc            nvarchar(200),
@ContainerSrc       nvarchar(200),
@TabPath            nvarchar(255),
@StartDate          DateTime,
@EndDate            DateTime,
@RefreshInterval	int,
@PageHeadText		nvarchar(500)

as

update {objectQualifier}Tabs
set    TabName            = @TabName,
       IsVisible          = @IsVisible,
       DisableLink        = @DisableLink,
       ParentId           = @ParentId,
       IconFile           = @IconFile,
       Title              = @Title,
       Description        = @Description,
       KeyWords           = @KeyWords,
       IsDeleted          = @IsDeleted,
       Url                = @Url,
       SkinSrc            = @SkinSrc,
       ContainerSrc       = @ContainerSrc,
       TabPath            = @TabPath,
       StartDate          = @StartDate,
       EndDate            = @EndDate,
	   RefreshInterval	  = @RefreshInterval,
	   PageHeadText		  = @PageHeadText
where  TabId = @TabId


GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetAllTabs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetAllTabs]
GO



CREATE procedure {databaseOwner}{objectQualifier}GetAllTabs

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end,
       RefreshInterval,
       PageHeadText
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
order by TabOrder, TabName

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}GetTab]
GO


CREATE  procedure {databaseOwner}{objectQualifier}GetTab

@TabId    int

as

select TabID, 
TabOrder, 
{objectQualifier}Tabs.PortalID, 
TabName, 
IsVisible, 
ParentId, 
[Level], 
DisableLink, 
Title, 
Description, 
KeyWords, 
IsDeleted, 
'Url'= case when F2.FileName is null then {objectQualifier}Tabs.Url else F2.Folder + F2.FileName end, 
SkinSrc, 
ContainerSrc, 
TabPath, 
StartDate, 
EndDate,
'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end,
RefreshInterval,
PageHeadText
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files F2 on {objectQualifier}Tabs.Url = 'fileid=' + convert(varchar,F2.FileID)
where  TabId = @TabId


GO


if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}[{objectQualifier}AddTab]
GO


CREATE procedure {databaseOwner}{objectQualifier}AddTab

@PortalId           int,
@TabName            nvarchar(50),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@Title              nvarchar(200),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@Url                nvarchar(255),
@SkinSrc            nvarchar(200),
@ContainerSrc       nvarchar(200),
@TabPath            nvarchar(255),
@StartDate          DateTime,
@EndDate            DateTime,
@RefreshInterval	int,
@PageHeadText		nvarchar(500)

as

insert into {objectQualifier}Tabs (
    PortalId,
    TabName,
    IsVisible,
    DisableLink,
    ParentId,
    IconFile,
    Title,
    Description,
    KeyWords,
    IsDeleted,
    Url,