books_install.sql

Books source code for Rainbow Portal

/* Install script, Books Module, Last Updated 9/25/03 */
/* Problems, comments.  Chris Farrell, [e-mail]*/
/* based upon Wrox press ASP.Net "Building an ASP.Net Intranet", ch3*/

USE [Rainbow]
GO

if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rb_Books]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    CREATE TABLE [rb_Books] (
	   [ItemID] [int] IDENTITY (1, 1) NOT NULL ,
	   [ModuleID] [int] NOT NULL ,
	   [CreatedByUser] [nvarchar] (100)  NULL ,
	   [CreatedDate] [datetime] NULL ,
	   [Title] [nvarchar] (150)  NULL ,
	   [ImageUrl] [nvarchar] (150) NULL ,
	   [Authors] [nvarchar] (150) NULL ,
	   [Price] [nvarchar] (10) NULL ,
	   [ISBN] [nvarchar] (10) NULL ,
	   [Buylink] [nvarchar] (150) NULL 
    ) ON [PRIMARY]

    ALTER TABLE [rb_Books] WITH NOCHECK ADD 
        CONSTRAINT [PK_rbBooks] PRIMARY KEY  NONCLUSTERED 
        (
            [ItemID]
        )  ON [PRIMARY] 

    ALTER TABLE [rb_Books] WITH NOCHECK ADD
        CONSTRAINT [FK_rbBooksModules] FOREIGN KEY
        (
            [ModuleID]
        ) REFERENCES [rb_Modules] (
            [ModuleID]
        ) ON DELETE CASCADE NOT FOR REPLICATION

END
GO

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

CREATE PROCEDURE rb_AddBook
(
    @ModuleID       int,
    @CreatedByUser  nvarchar(100),
    @Title          nvarchar(150),
    @ImageUrl       nvarchar(150),
    @Authors	    nvarchar(150),
    @Price          nvarchar(10),
    @ISBN           nvarchar(10),
    @BuyLink	    nvarchar(150),
    @ItemID         int OUTPUT
)
AS

INSERT INTO rb_Books
(
    ModuleID,
    CreatedByUser,
    CreatedDate,
    Title,
    ImageUrl,
    Authors,
    Price,
    ISBN,
    BuyLink
)

VALUES
(
    @ModuleID,
    @CreatedByUser,
    GetDate(),
    @Title,
    @ImageUrl,
    @Authors,
    @Price,
    @ISBN,
    @BuyLink
)

SELECT
    @ItemID = @@Identity
GO

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

CREATE PROCEDURE rb_DeleteBook
(
    @ItemID int
)
AS

DELETE FROM
    rb_Books

WHERE
    ItemID = @ItemID
GO


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

CREATE PROCEDURE rb_GetBooks
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedByUser,
    CreatedDate,
    Title,
    ImageUrl,
    Authors,
    Price,
    ISBN,
    BuyLink

FROM 
    rb_Books

WHERE
    ModuleID = @ModuleID
GO

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

CREATE PROCEDURE rb_GetSingleBook
(
    @ItemID int
)
AS

SELECT
    CreatedByUser,
    CreatedDate,
    Title,
    ImageUrl,
    Authors,
    Price,
    ISBN,
    BuyLink

FROM 
    rb_Books

WHERE
    ItemID = @ItemID
GO

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

CREATE PROCEDURE rb_UpdateBook
(
    @ItemID         int,
    @CreatedByUser  nvarchar(100),
    @Title          nvarchar(150),
    @ImageUrl       nvarchar(150),
    @Authors	    nvarchar(150),
    @Price          nvarchar(10),
    @ISBN           nvarchar(10),
    @BuyLink	    nvarchar(150)
)
AS

UPDATE
    rb_Books

SET
    CreatedByUser   = @CreatedByUser,
    Title           = @Title,
    ImageUrl        = @ImageUrl,
    Authors  	    = @Authors,
    Price	        = @Price,
    ISBN            = @ISBN,
    BuyLink         = @BuyLink

WHERE
    ItemID = @ItemID
GO