c#中调用 存储过程 output获取不到值,在salserver中执行有值,求大牛解答
下面是代码:
DAL层:
```
public List GetBooks(int categoryId, int pageIndex, int pageSize, ref int pageCount, string sortField)
{
SqlParameter[]parameters={new SqlParameter("@SortField",sortField),
new SqlParameter("@CategoryId",categoryId),
new SqlParameter("@PageSize",pageSize),
new SqlParameter("@PageIndex",pageIndex),
new SqlParameter("@PageCount",SqlDbType.Int)};
List bookList = new List();
parameters[parameters.Length - 1].Direction = ParameterDirection.Output;
SqlDataReader reader=SqlHelper.ExecuteReader("sp_QueryPagedBook", CommandType.StoredProcedure, parameters);
pageCount = Convert.ToInt32(parameters[parameters.Length - 1].Value);
if(reader.HasRows){
while(reader.Read()){
Book book = new Book();
book.Id = Convert.ToInt32(reader["bId"]);
book.Title = Convert.ToString(reader["Title"]);
book.Author = Convert.ToString(reader["Author"]);
book.Publisher = new Publisher();
book.Publisher.Id = Convert.ToInt32(reader["pubId"]);
book.Publisher.Name = Convert.ToString(reader["pName"]);
book.PublishDate = Convert.ToDateTime(reader["PublishDate"]);
book.ISBN = Convert.ToString(reader["ISBN"]);
book.UnitPrice = Convert.ToDouble(reader["UnitPrice"]);
book.ContentDescription = Convert.ToString(reader["ContentDescription"]);
book.TOC = Convert.ToString(reader["TOC"]);
book.categorie = new Categorie();
book.categorie.Id = Convert.ToInt32(reader["cId"]);
book.categorie.Name = Convert.ToString(reader["cName"]);
book.categorie.PId = reader["PId"] != DBNull.Value ? (int?)reader["PId"] : null;
book.categorie.SortNum = reader["SortNum"] != DBNull.Value ? (int?)reader["SortNum"] : null;
book.Clicks = Convert.ToInt32(reader["Clicks"]);
bookList.Add(book);
}
}
return bookList;
}
```
sqlserver中存储过程:
```
USE [BookShopPlus]
GO
/****** Object: StoredProcedure [dbo].[sp_QueryPagedBook] Script Date: 2018/4/2 15:32:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_QueryPagedBook]
@SortField Varchar(20),--排序字段
@CategoryId int,--图书类型编号
@PageSize int,--页大小
@PageIndex int,--当前页
@PageCount int output--页总数
AS
--计算总页数
Declare @TotalCount int
SELECT @TotalCount=COUNT(1) FROM Books where CategoryId=@CategoryId
if(@TotalCount%@PageSize<>0)
SET @PageCount=@TotalCount/@PageSize+1
ELSE
SET @PageCount=@TotalCount/@PageSize
--按出版日期排序
if(@SortField='PublishDate')
BEGIN
SELECT top(@PageSize) ,c.Id as cId,p.Id as pubId,b.Id as bId,p.Name as pName,c.Name as cName FROM Books b
LEFT JOIN Publishers p ON b.PublisherId=p.Id LEFT JOIN Categories c on b.CategoryId=c.Id
WHERE CategoryId=@CategoryId and b.Id not in(SELECT TOP ((@PageIndex-1)@PageSize) id from Books WHERE CategoryId=@CategoryId)
ORDER BY PublishDate
END
--按价格排序
ELSE
BEGIN
SELECT top(@PageSize) ,c.Id as cId,p.Id as pubId,b.Id as bId,p.Name as pName,c.Name as cName FROM Books b
LEFT JOIN Publishers p ON b.PublisherId=p.Id LEFT JOIN Categories c on b.CategoryId=c.Id
WHERE CategoryId=@CategoryId and b.Id not in(SELECT TOP ((@PageIndex-1)@PageSize) id from Books WHERE CategoryId=@CategoryId)
ORDER BY UnitPrice
END
```
在serlserver中执行后有值:
![图片说明](https://img-ask.csdn.net/upload/201804/02/1522655513_762961.png)
在c#中调用后没有值: