asp.net图书销售管理系统设计 第3页
SELECT dbo.Book.bookID, dbo.Book.isbn, dbo.Book.bookname, dbo.Book.author, dbo.Book.edition, dbo.Book.bookconcernID, dbo.BookConcern.conName,
dbo.Book.format, dbo.Book.publishTime, dbo.Book.totalpages, dbo.Book.price, dbo.Book.summary, dbo.Book.stocks, dbo.Book.class1,
dbo.Book.class2, dbo.Book.class3, BookClass_1.classname AS classname1, BookClass_2.classname AS classname2,
BookClass_3.classname AS classname3, dbo.Book.coverpic, dbo.Book.cost, dbo.Book.[level], dbo.Book.userRate, dbo.Book.addtime,
dbo.BookStat.commetcount, dbo.BookStat.clickcount
FROM dbo.Book INNER JOIN
dbo.BookConcern ON dbo.Book.bookconcernID = dbo.BookConcern.concernID INNER JOIN
dbo.BookStat ON dbo.Book.bookID = dbo.BookStat.bookid LEFT OUTER JOIN
dbo.BookClass AS BookClass_3 ON BookClass_3.classID = dbo.Book.class3 LEFT OUTER JOIN
dbo.BookClass AS BookClass_2 ON dbo.Book.class2 = BookClass_2.classID LEFT OUTER JOIN
dbo.BookClass AS BookClass_1 ON dbo.Book.class1 = BookClass_1.classID
SELECT dbo.BookInfo.isbn, dbo.BookInfo.bookname, dbo.BookInfo.author, dbo.BookInfo.edition, dbo.BookInfo.bookconcernID, dbo.BookInfo.conName,
dbo.BookInfo.format, dbo.BookInfo.publishTime, dbo.BookInfo.totalpages, dbo.BookInfo.price, dbo.BookInfo.summary, dbo.BookInfo.stocks,
dbo.BookInfo.classname1, dbo.BookInfo.classname2, dbo.BookInfo.classname3, dbo.UserInfo.username, dbo.BookSell.bookid, dbo.BookSell.userid,
dbo.BookSell.description, dbo.BookSell.addtime, dbo.BookSell.returnkey, dbo.BookSell.num, dbo.UserInfo.pwd, dbo.UserInfo.email,
dbo.UserInfo.address, dbo.UseIII. 存储过程
1. 会员搜索的实现
由于会员有很多的属性,包括性别,编号,添加日期,会员组,会员状态等等,同时又要实现分页显示, 所以该存储过程必须能根据条件来进行不同的查询,同时要返回符合条件的记录总数和某一页(用户需求)的记录,以供前台调用显示.
首先声明了需要进行判断的参数,然后对用户传参进行判断,如果保持默认值表示不需要对该条件进行添加,否则必须加上该条件.以此类推,直到对所有条件进行判断. 然后因为要实现分页显示,必须计算从哪一条记录开始,选取多少页,这里就应该是选择pagesize条记录,应该从pagesize*(pageindex-1)这里开始选取,这里在返回符合条件的总记录数的时候碰到了问题,因为在存储过程中每次exec执行后,都会将结果返回到客户端,所以获取总条数不能调用这个方法,这里我采用了一个投机取巧的办法勉强实现了,因为对某些函数不熟悉,所以下次还是需要好好摸索下.
实现代码:
-----------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SearchUser]
(
@idcard nvarchar(50)='-1',
@username nvarchar(50)='-1',
@serialnumber nvarchar(50)='-1',
@usertype int=-1,
@userstatus int=-1,
@addtime1 nvarchar(50)='1900-01-01',
@addtime2 nvarchar(50)='2900-01-01',
@pageindex int=1,
@pagesize int=15,
@mycount int=0 OUT )
declare @sql nvarchar(2000)
declare @sql_count nvarchar(2000)
set @sql='select top '+CAST(@pagesize as varchar(32))+' * from userinfo where 1>0'
set @sql_count='update [user] set wanttoread=1 where 1>0'
if @idcard<>'-1'
begin
set @sql=@sql+' and idcard like ''%'+CAST( @idcard as nvarchar)+'%'''
set @sql_count=@sql_count+' and idcard like ''%'+CAST( @idcard as nvarchar)+'%'''
end
if @username<>'-1'
begin
set @sql=@sql+' and username like ''%'+CAST( @username as nvarchar)+'%'''
set @sql_count=@sql_count+' and username like ''%'+CAST( @username as nvarchar)+'%'''
end
if @serialnumber<>'-1'
begin
set @sql=@sql+' and serialnumber like ''%'+CAST( @serialnumber as nvarchar)+'%'''
set @sql_count=@sql_count+' and serialnumber like ''%'+CAST( @serialnumber as nvarchar)+'%'''
end
if @usertype<>-1
begin
set @sql=@sql+' and usertype_id='+CAST(@usertype as nvarchar)
set @sql_count=@sql_count+' and usertype='+CAST(@usertype as nvarchar)
end
if @userstatus<>-1
begin
set @sql=@sql+' and userstatusid='+CAST(@userstatus as nvarchar)
set @sql_count=@sql_count+' and status='+CAST(@userstatus as nvarchar)
end
if @addtime1<>'-1' and @addtime2<>'-1'
begin
set @sql=@sql+' and addtime>'''+CONVERT(varchar(50
),@addtime1,120)+''' and addtime<'''+CONVERT(varchar(50),@addtime2,120)+''''
set @sql_count=@sql_count+' and addtime>'''+CONVERT(varchar(50
),@addtime1,120)+''' and addtime<'''+CONVERT(varchar(50),@addtime2,120)+''''
end
exec(@sql_count)
set @mycount= @@rowcount
set @sql=@sql+' and addtime not in (select top '+CAST((@pagesize*(@pageindex-1)) as varchar(32))+ ' addtime from userinfo where 1>0'
if @idcard<>'-1'
begin
set @sql=@sql+' and idcard like ''%'+CAST( @idcard as nvarchar)+'%'''
end
if @username<>'-1'
begin
set @sql=@sql+' and username like ''%'+CAST( @username as nvarchar)+'%'''
end
if @serialnumber<>'-1' begin