03.01.00.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/** Create Announcements Table **/

if not exists (select * from {databaseOwner}sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}Announcements]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}Announcements]
		(
			[ItemID] [int] NOT NULL IDENTITY(0, 1),
			[ModuleID] [int] NOT NULL,
			[CreatedByUser] [nvarchar] (100) NULL,
			[CreatedDate] [datetime] NULL,
			[Title] [nvarchar] (150) NULL,
			[URL] [nvarchar] (150) NULL,
			[ExpireDate] [datetime] NULL,
			[Description] [nvarchar] (2000) NULL,
			[ViewOrder] [int] NULL
		)

		ALTER TABLE {databaseOwner}[{objectQualifier}Announcements] ADD CONSTRAINT [PK_{objectQualifier}Announcements] PRIMARY KEY NONCLUSTERED  ([ItemID])
		CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Announcements] ON {databaseOwner}[{objectQualifier}Announcements] ([ModuleID])

		ALTER TABLE {databaseOwner}[{objectQualifier}Announcements] WITH NOCHECK ADD
		CONSTRAINT [FK_{objectQualifier}Announcements_{objectQualifier}Modules] FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
	END
GO


/** Drop Existing Stored Procedures **/
if exists (select * from {databaseOwner}sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddAnnouncement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}AddAnnouncement
GO

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

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

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


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


/** Create Stored Procedures **/

create procedure {databaseOwner}{objectQualifier}AddAnnouncement

@ModuleId       int,
@UserName       nvarchar(100),
@Title          nvarchar(150),
@URL            nvarchar(150),
@ExpireDate     DateTime,
@Description    nvarchar(2000),
@ViewOrder	int

as

insert into {objectQualifier}Announcements (
	ModuleId,
	CreatedByUser,
	CreatedDate,
	Title,
	URL,
	ExpireDate,
	Description,
	ViewOrder
)
values (
	@ModuleId,
	@UserName,
	getdate(),
	@Title,
	@URL,
	@ExpireDate,
	@Description,
	@ViewOrder
)

select SCOPE_IDENTITY()

GO

create procedure {databaseOwner}{objectQualifier}DeleteAnnouncement

	@ItemId int

as

delete
from {objectQualifier}Announcements
where  ItemId = @ItemId

GO

CREATE procedure {databaseOwner}{objectQualifier}GetAnnouncement

	@ItemId   int,
	@ModuleId int

as

select 
	{objectQualifier}Announcements.ItemID,
	{objectQualifier}Announcements.ModuleID,
	{objectQualifier}Announcements.Title,
	'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Announcements.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
	{objectQualifier}Announcements.ExpireDate,
	{objectQualifier}Announcements.Description,
	'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
	{objectQualifier}Announcements.CreatedDate,
	{objectQualifier}Announcements.ViewOrder,
	{objectQualifier}UrlTracking.TrackClicks,
	{objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Announcements
left outer join {objectQualifier}Users on {objectQualifier}Announcements.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Announcements.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID left outer join {objectQualifier}Files on {objectQualifier}Announcements.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Announcements.ItemId = @ItemId
and    {objectQualifier}Announcements.ModuleId = @ModuleId

GO

CREATE procedure {databaseOwner}{objectQualifier}GetAnnouncements

	@ModuleId int

as

select 
	{objectQualifier}Announcements.ItemId,
	{objectQualifier}Announcements.ModuleID,
    {objectQualifier}Announcements.CreatedByUser,
    {objectQualifier}Announcements.CreatedDate,
    {objectQualifier}Announcements.Title,
    'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Announcements.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
    {objectQualifier}Announcements.ExpireDate,
    {objectQualifier}Announcements.Description,
    {objectQualifier}Announcements.ViewOrder,
    {objectQualifier}UrlTracking.TrackClicks,
    {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Announcements
left outer join {objectQualifier}UrlTracking on {objectQualifier}Announcements.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID left outer join {objectQualifier}Files on {objectQualifier}Announcements.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Announcements.ModuleId = @ModuleId
and    ({objectQualifier}Announcements.ExpireDate > GetDate() or {objectQualifier}Announcements.ExpireDate is null)
order by {objectQualifier}Announcements.ViewOrder asc, {objectQualifier}Announcements.CreatedDate desc

GO

create procedure {databaseOwner}{objectQualifier}UpdateAnnouncement

	@ItemId         int,
	@UserName       nvarchar(100),
	@Title          nvarchar(150),
	@URL            nvarchar(150),
	@ExpireDate     datetime,
	@Description    nvarchar(2000),
	@ViewOrder	int

as

update {objectQualifier}Announcements
set    CreatedByUser = @UserName,
       CreatedDate   = GetDate(),
       Title         = @Title,
       URL           = @URL,
       ExpireDate    = @ExpireDate,
       Description   = @Description,
       ViewOrder     = @ViewOrder
where  ItemId = @ItemId

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/