Create the following stored procedure in the database. CREATE PROCEDURE dbo.CreateProject(@title varchar(50),
@description varchar(1000),
@creatorid uniqueidentifier,
@estduration int,
@managerid uniqueidentifier)
AS
insert into projects values(@title,
@description,getdate(), @creatorid,@estduration,
@managerid)
CREATE PROCEDURE dbo.GetAllProjects
AS
select * from projects order by id;
CREATE PROCEDURE dbo.GetAllUsers
AS
select u.userid, u.username, m.email, m.createdate
from aspnet_users u join aspnet_membership m
on ( u.userid = m.userid)
order by u.username
CREATE PROCEDURE dbo.GetNonMembersOfProject(@projectid int)
AS
select userid, username from aspnet_users
where userid not in (
select userid from project_members
where projectid = @projectid)
CREATE PROCEDURE dbo.GetProjectDetails
(@projectid int)
AS
select p.id, p.title, p.description, p.createdon,
p.estduration, u.username
from projects p inner join aspnet_users u
on (p.managerid = u.userid)
where p.id = @projectid
CREATE PROCEDURE dbo.GetProjectMembers(@projectid int)
AS
select userid, username
from aspnet_users
where userid in
(select userid from project_members
where projectid = @projectid);
CREATE PROCEDURE dbo.GetTimeEntriesByProject
(@projectid int)
AS
select t.entryid, t.entrydate, t.duration, t.description, t.createdon, u.username
from timeentry t inner join aspnet_users u
on ( t.userid = u.userid)
where t.projectid = @projectid
CREATE PROCEDURE dbo.AddMemberToProject(@projectid int, @userid uniqueidentifier)
AS
insert into project_members values(@projectid, @userid);
CREATE PROCEDURE dbo.AddTimeEntry(@projectid int,@userid uniqueidentifier,@entrydate datetime,@duration int,@description varchar(1000))
AS
insert into timeentry values(@projectid,@userid,
@entrydate, getdate(), @duration,@description)
