Open the database in Server explorer or Database Explorer and create tables using the following script. Use New Query to open query window and type the following commands in SQL Pane. create table categories
( catcode varchar(10) primary key,
catname varchar(50),
catdesc varchar(200),
noquestions int
)
create table questions
( qid int identity primary key,
title varchar(100),
question varchar(2000),
addedon datetime,
catcode varchar(10) references categories(catcode),
userid uniqueidentifier references aspnet_users(userid)
)
create table answers
( aid int identity primary key,
qid int references questions(qid),
answer varchar(2000),
addedon datetime,
userid uniqueidentifier references aspnet_users(userid)
)
insert into categories values('music','Music','Songs , Albums etc.')
insert into categories values('sports','Sports and Games','Sports and Games')
insert into categories values('health','Health and diet','Health, diet and excercise')
Create the following stored procedure in the database.
CREATE PROCEDURE dbo.AddQuestion(@userid UniqueIdentifier, @title varchar(100),@question varchar(2000), @catcode varchar(10))
AS
begin tran
insert into questions (title,question, catcode,addedon, userid)
values(@title,@question, @catcode, getdate(), @userid);
update categories set noquestions = noquestions + 1
where catcode = @catcode;
commit tran
CREATE PROCEDURE dbo.GetAllCategories
AS
select * from categories
order by catname
CREATE PROCEDURE dbo.GetAnswers(@qid int)
AS
select aid,answer, addedon, username, a.userid
from answers a inner join aspnet_users u on ( a.userid = u.userid)
where qid = @qid
order by aid desc
CREATE PROCEDURE dbo.GetQuestionDetails
(@qid int)
AS
select qid,title,question, c.catcode, catname, addedon, username, q.userid
from questions q inner join categories c on ( c.catcode = q.catcode)
inner join aspnet_users u on ( q.userid = u.userid)
where qid = @qid
CREATE PROCEDURE dbo.GetRecentQuestions
AS
select top 10 qid, c.catcode,catname,title,addedon
from questions q inner join categories c
on (q.catcode = c.catcode)
order by qid desc
CREATE PROCEDURE dbo.SearchQuestions
(@pattern varchar(100) )
AS
select qid, c.catcode,catname,title,addedon
from questions q inner join categories c
on (q.catcode = c.catcode)
where title like '%' + @pattern + '%' or question like '%' + @pattern + '%'
order by qid desc
CREATE PROCEDURE dbo.AddAnswer
( @qid int, @userid uniqueidentifier, @answer varchar(2000))
AS
insert into answers (qid,userid,answer,addedon)
values( @qid,@userid,@answer, getdate())
Penulis 
EmoticonEmoticon