SqlDataAdapter 得不到output返回值
代码如下
/// <summary>
/// 分页获取配置信息列表
/// </summary>
/// <param name="pageIndex">索引</param>
/// <param name="pageSize">数据条数</param>
/// <param name="DictType">类型</param>
/// <param name="TotalCount">总计数</param>
/// <returns>DataTable</returns>
public DataTable GetDictInfoList(int pageIndex, int pageSize, string dictType, out int totalCount)
{
string strSql = "DictInfoList";
List<SqlParameter> parmeters = new List<SqlParameter>();
parmeters.Add(new SqlParameter("@pageIndex", pageIndex));
parmeters.Add(new SqlParameter("@pageSize", pageSize));
parmeters.Add(new SqlParameter("@dictType", dictType));
parmeters.Add(new SqlParameter("@totalCount", ParameterDirection.Output));
return this.GetListStoredProcedure(strSql, parmeters.ToArray(), out totalCount);
}
protected DataTable GetListStoredProcedure(string sqlContent, SqlParameter[] paramters, out int totalCount)
{
//查询结果
DataTable dtData = new DataTable();
// 建立数据库连接
using (SqlConnection conn = new SqlConnection(_dBConnString))
{
//SqlCommand sc = new SqlCommand()
//{
// Connection = conn,
// CommandText = sqlContent,
// CommandType = CommandType.StoredProcedure
//};
// sc.Parameters.AddRange(paramters);
// 创建一个适配器
SqlDataAdapter adapter = new SqlDataAdapter()
{
SelectCommand = new SqlCommand()
{
Connection = conn,
CommandText = sqlContent,
CommandType = CommandType.StoredProcedure
}
};
// SqlDataAdapter adapter = new SqlDataAdapter(sc);
adapter.SelectCommand.Parameters.AddRange(paramters);
try
{
// 执行查询,并将数据导入DataSet
adapter.Fill(dtData);
// object res = sc.Parameters["@totalCount"].Value;
totalCount = (int)adapter.SelectCommand.Parameters["@totalCount"].Value; }
finally
{
// 关闭数据库连接
conn.Close();
}
}
return dtData;
}
存储过程
USE [ErpManage]
GO
/****** Object: StoredProcedure [dbo].[DictInfoList] Script Date: 01/28/2014 10:42:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DictInfoList]
(
@pageIndex int=0,
@pageSize int =0,
@Id int=0,
@dictType varchar(4)='',
@totalCount int output
)
AS
BEGIN
Declare @totalRecord int;
Declare @strWhere nvarchar(500)='';
DECLARE @sql nvarchar(1000);
if(@Id>0)
begin
set
@strWhere=' AND Id='+ cast(@Id as CHAR)
end
if(ISNULL(@dictType,'')!='')
begin
set @strWhere +=' AND DictType='''+@DictType+''''
end
SET @sql=' select @totalRecord=COUNT(*) from DictInfo WHERE 1=1 '+@strWhere
exec sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT
--print(@totalCount)
Set @sql='Select *from (
Select ROW_NUMBER() over(order by DictType Desc ) as OrderDictType,* From DictInfo
) t where OrderDictType Between '+CAST(@pageIndex as CHAR)+' AND '+CAST(@pageSize as CHAR)+' '+@strWhere
exec (@sql)
select @totalCount=@totalRecord
return @totalCount
END
在sqlserver2008上面执行存储过程是对的,
但是用调试工具执行的结果是:exec DictInfoList @pageIndex=0,@pageSize=10,@dictType=N'',@totalCount=2
请高手解答这是为什么?