下面是我写的,符合你的问题,使用输出参数的方式得到数据数量,CREATE PROCEDURE [GetCustomersDataPage] @PageIndex INT, @PageSize INT, @RecordCount INT OUT, @PageCount INT OUTASSELECT @RecordCount = COUNT(*) FROM CustomersSET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)DECLARE @SQLSTR NVARCHAR(1000)IF @PageIndex = 0 OR @PageCount <= 1 SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID DESC'ELSE IF @PageIndex = @PageCount - 1 SET @SQLSTR =N' SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'ELSE SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+' CustomerID, CompanyName,Address,Phone FROM Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC'EXEC (@SQLSTR) GO 具体程序:http://blog.csdn.net/zhzuo/archive/2004/10/28/156647.aspx
sampleCMD.CommandType = CommandType.StoredProcedure;SqlParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
sampParm.Direction = ParameterDirection.ReturnValue;sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);
sampParm.Value = "Sample Value";sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);
sampParm.Direction = ParameterDirection.Output;nwindConn.Open();SqlDataReader sampReader = sampleCMD.ExecuteReader();Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1));while (sampReader.Read())
{
Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));
}sampReader.Close();
nwindConn.Close();Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters["RETURN_VALUE"].Value);
具体程序:http://blog.csdn.net/zhzuo/archive/2004/10/28/156647.aspx
DataReaderObj.Close();