C# Photo Album Viewer

USE [master]
GO

CREATE DATABASE [AlbumViewer]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AlbumViewer', FILENAME = N'd:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AlbumViewer.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'AlbumViewer_log', FILENAME = N'd:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AlbumViewer_log.ldf' , SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [AlbumViewer] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AlbumViewer].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [AlbumViewer] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [AlbumViewer] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [AlbumViewer] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [AlbumViewer] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [AlbumViewer] SET ARITHABORT OFF 
GO
ALTER DATABASE [AlbumViewer] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [AlbumViewer] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [AlbumViewer] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [AlbumViewer] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [AlbumViewer] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [AlbumViewer] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [AlbumViewer] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [AlbumViewer] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [AlbumViewer] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [AlbumViewer] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [AlbumViewer] SET  DISABLE_BROKER 
GO
ALTER DATABASE [AlbumViewer] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [AlbumViewer] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [AlbumViewer] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [AlbumViewer] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [AlbumViewer] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [AlbumViewer] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [AlbumViewer] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [AlbumViewer] SET RECOVERY FULL 
GO
ALTER DATABASE [AlbumViewer] SET  MULTI_USER 
GO
ALTER DATABASE [AlbumViewer] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [AlbumViewer] SET DB_CHAINING OFF 
GO
ALTER DATABASE [AlbumViewer] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [AlbumViewer] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'AlbumViewer', N'ON'
GO
USE [AlbumViewer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DeleteAlbum]
 @id INT
AS

DELETE FROM Albums
WHERE id = @id


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[DeletePhoto]
 @id INT
AS

DELETE FROM Photos
WHERE id = @id
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[GetAlbums] AS
SELECT id, [name], [description]
FROM Albums
ORDER BY [name]


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPhoto] 
	@id INT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT [name], ISNULL([description], '') AS description, photo 
	FROM Photos 
	WHERE id = @id

END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[GetPhotosByAlbum]
	@albumId INT
AS

SELECT id, [name], ISNULL([description], '') AS [description], [photo]
FROM Photos
WHERE [album_id] = @albumId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertAlbum]
	@name AS NVARCHAR(20),
	@desc NVARCHAR(200)
 AS

INSERT INTO Albums ([name], [description]) 
VALUES (@name, @desc) 

RETURN SCOPE_IDENTITY()


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertPhoto]
	@name AS NVARCHAR(50),
	@desc AS NVARCHAR(200) = null,
	@photo AS VARBINARY(MAX),
	@albumId AS INT
 AS

INSERT INTO Photos ([name], [description], photo, album_id) 
VALUES (@name, @desc, @photo, @albumId) 

RETURN SCOPE_IDENTITY()



GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[UpdateAlbum]
	@id INT,
	@name AS NVARCHAR(20),
	@desc NVARCHAR(200)

AS

UPDATE Albums
SET name = @name, [description] = @desc
WHERE id = @id

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[UpdatePhoto]
	@id INT,
	@name AS NVARCHAR(20),
	@desc NVARCHAR(200)

AS

UPDATE Photos
SET name = @name, [description] = @desc
WHERE id = @id

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Albums](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[description] [nvarchar](200) NULL,
 CONSTRAINT [PK_Albums] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photos](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[description] [nvarchar](200) NULL,
	[album_id] [int] NOT NULL,
	[photo] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Photos]  WITH CHECK ADD  CONSTRAINT [FK_Albums_Photos] FOREIGN KEY([album_id])
REFERENCES [dbo].[Albums] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photos] CHECK CONSTRAINT [FK_Albums_Photos]
GO
USE [master]
GO
ALTER DATABASE [AlbumViewer] SET  READ_WRITE 
GO