购物车-ShoppingCart-数据层

CREATE TABLE [dbo].[ShoppingCart](
	[CartID] [char](36) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[ProductID] [int] NOT NULL,
	[Quantity] [int] NULL,
	[DateAdded] [smalldatetime] NULL,
 CONSTRAINT [PK_ShoppingCart] PRIMARY KEY CLUSTERED 
(
	[CartID] ASC,
	[ProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [BalloonShop]
GO
ALTER TABLE [dbo].[ShoppingCart]  WITH CHECK ADD  CONSTRAINT [FK_ShoppingCart_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])

 

CREATE PROCEDURE [dbo].[ShoppingCartAddItem]
(@CartID char(36),
 @ProductID int)
AS
IF EXISTS
		(SELECT CartID
		 FROM ShoppingCart
		 WHERE ProductID = @ProductID AND CartID = @CartID)
	UPDATE ShoppingCart
	SET Quantity = Quantity + 1
	WHERE ProductID = @ProductID AND CartID = @CartID
ELSE
	IF EXISTS (SELECT Name FROM Product WHERE ProductID = @ProductID)
		INSERT INTO ShoppingCart (CartID, ProductID, Quantity, DateAdded)
		VALUES (@CartID, @ProductID, 1, GETDATE())

 

CREATE PROCEDURE ShoppingCartRemoveItem
(@CartID char(36),
 @ProductID int)
AS
DELETE FROM ShoppingCart
WHERE ProductID = @ProductID AND CartID = @CartID

 

CREATE PROCEDURE ShoppingCartUpdateItem
(@CartID char(36),
 @ProductID int,
 @Quantity int)
AS
IF @Quantity <= 0
	EXEC ShoppingCartRemoveItem @CartID,@ProductID
ELSE
	UPDATE ShoppingCart
	SET Quantity = @Quantity,DateAdded = GETDATE()
	WHERE ProductID = @ProductID AND CartID = @CartID

 

CREATE PROCEDURE ShoppingCartGetItems
(@CartID char(36))
AS
SELECT Product.ProductID,Product.Name,Product.Price,ShoppingCart.Quantity,
		Product.Price * ShoppingCart.Quantity AS Subtotal
FROM ShoppingCart INNER JOIN Product
ON ShoppingCart.ProductID = Product.ProductID
WHERE ShoppingCart.CartID = @CartID

 

CREATE PROCEDURE ShoppingCartTotalAmount
(@CartID char(36))
AS
SELECT ISNULL(SUM(Product.Price * ShoppingCart.Quantity),0)
FROM ShoppingCart INNER JOIN Product
ON ShoppingCart.ProductID = Product.ProductID
WHERE ShoppingCart.CartID = @CartID