Create the following stored procedure in the database.
CREATE PROCEDURE dbo.CreateAlbum
(
@username nvarchar(256),
@title varchar(256), @description varchar(1000)
)
AS
insert into albums (username,title,description,createdon)
values(@username,@title,@description, getdate())
CREATE PROCEDURE dbo.Get_Album_Details
(
@aid int
)
AS
select a.aid,a.title,a.description,createdon,
count(photoid) nophotos,
isnull(min(photoid),0) firstphoto
from albums a left outer join photos p
on a.aid = p.aid
where @aid = a.aid
group by a.aid,a.title,a.description, a.createdon
CREATE PROCEDURE dbo.GetAlbums
(
@username nvarchar(256)
)
AS
select a.aid,a.title,a.description,
count(photoid) nophotos,
isnull(min(photoid),0) firstphoto
from albums a left outer join photos p
on a.aid = p.aid
where @username = a.username
group by a.aid,a.title,a.description
CREATE Procedure AddPhoto
(
@aid int,
@filename varchar(100),
@title varchar(200),
@tags varchar(100),
@photoid int output
)
AS
insert into photos values(@filename,@title,@tags,getdate(),@aid);
select @photoid = @@identity;
CREATE PROCEDURE dbo.DeleteAlbum
(
@aid int
)
AS
begin tran
delete from photos where aid = @aid;
if @@error <> 0
begin
rollback transaction
raiserror('Could not delete photos from album',15,1);
return;
end;
delete from albums where aid = @aid;
if @@error <> 0
begin
rollback transaction
raiserror('Could not delete album',15,1);
return;
end;
commit transaction;
CREATE PROCEDURE dbo.Get_Photo_Details
(
@photoid int
)
AS
select title,tags,addedon from photos
where photoid = @photoid;
CREATE PROCEDURE dbo.GetPhotoIds
(
@aid int
)
AS
select photoid from photos where aid = @aid;
CREATE PROCEDURE dbo.GetPhotosFromAlbum
(
@aid int
)
AS
select photoid, title, tags, addedon
from photos
where aid = @aid
order by photoid
CREATE PROCEDURE dbo.SearchForPhotos
(
@pattern varchar(100)
)
AS
select * from photos
where title like '%' + @pattern + '%' or tags like '%' + @pattern + '%'
order by aid desc, photoid desc
