存储过程
-------------------------------------------------------------------------------
-- ProductGetAllList_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE ProductGetAllList_ListByPage
(
@nCurrentPage int,
@nPageSize int,
@totalNumResults int output)
AS
CREATE TABLE #ProductCategoryTempTable
(
rowid int IDENTITY PRIMARY KEY,
productid char(10) NOT NULL,
Subject varchar(250) NULL,
Pic varchar(250) NULL,
MarketPrice int NULL,
PerPrice int NUll
)
-- copy the search results into the temporary table INSERT INTO #ProductCategoryTempTable (productid, Subject,Pic,MarketPrice,PerPrice)
SELECT id, Subject,Pic,MarketPrice,PerPrice
FROM Product
ORDER BY id desc -- always return the total number of items found in the search
set @totalNumResults = 100 -- calculate the current page
DECLARE @nFirstPageRecord int
DECLARE @nLastPageRecord int
SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1) -- select the correct page of data with the given page size
SELECT productid, Subject,Pic,MarketPrice,PerPrice
FROM #ProductCategoryTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)Print @totalNumResults
GO@totalNumResults这个变量 查询分析器调试为10但是到了C#业务层public SqlDataReader GetAllList ( int pageSize, int pageIndex,out int totalNumResult )
{
SqlDataReader dataReader = null;
totalNumResult = 0;
try
{
// create data object and params
Database data = new Database();
SqlParameter[] prams = {
data.MakeInParam("@nCurrentPage", SqlDbType.Int, 4, pageIndex),
data.MakeInParam("@nPageSize", SqlDbType.Int, 4, pageSize),
data.MakeOutParam("@totalNumResults", SqlDbType.Int, 4)
};
// run the stored procedure
data.RunProc("ProductGetAllList_ListByPage", prams, out dataReader);
totalNumResult =(int)prams[2].Value;
return dataReader;
} catch (Exception ex)
{
Error.Log(ex.ToString());
throw new Exception("项目信息读取出错!",ex);
}
finally
{
dataReader = null;
}
}totalNumResult =((int)prams[2].Value出错 显示对象为引用我查了一下 是这个变量没有值prams[2]
不知道为什么哪个大哥帮我看一下
-------------------------------------------------------------------------------
-- ProductGetAllList_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE ProductGetAllList_ListByPage
(
@nCurrentPage int,
@nPageSize int,
@totalNumResults int output)
AS
CREATE TABLE #ProductCategoryTempTable
(
rowid int IDENTITY PRIMARY KEY,
productid char(10) NOT NULL,
Subject varchar(250) NULL,
Pic varchar(250) NULL,
MarketPrice int NULL,
PerPrice int NUll
)
-- copy the search results into the temporary table INSERT INTO #ProductCategoryTempTable (productid, Subject,Pic,MarketPrice,PerPrice)
SELECT id, Subject,Pic,MarketPrice,PerPrice
FROM Product
ORDER BY id desc -- always return the total number of items found in the search
set @totalNumResults = 100 -- calculate the current page
DECLARE @nFirstPageRecord int
DECLARE @nLastPageRecord int
SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1) -- select the correct page of data with the given page size
SELECT productid, Subject,Pic,MarketPrice,PerPrice
FROM #ProductCategoryTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)Print @totalNumResults
GO@totalNumResults这个变量 查询分析器调试为10但是到了C#业务层public SqlDataReader GetAllList ( int pageSize, int pageIndex,out int totalNumResult )
{
SqlDataReader dataReader = null;
totalNumResult = 0;
try
{
// create data object and params
Database data = new Database();
SqlParameter[] prams = {
data.MakeInParam("@nCurrentPage", SqlDbType.Int, 4, pageIndex),
data.MakeInParam("@nPageSize", SqlDbType.Int, 4, pageSize),
data.MakeOutParam("@totalNumResults", SqlDbType.Int, 4)
};
// run the stored procedure
data.RunProc("ProductGetAllList_ListByPage", prams, out dataReader);
totalNumResult =(int)prams[2].Value;
return dataReader;
} catch (Exception ex)
{
Error.Log(ex.ToString());
throw new Exception("项目信息读取出错!",ex);
}
finally
{
dataReader = null;
}
}totalNumResult =((int)prams[2].Value出错 显示对象为引用我查了一下 是这个变量没有值prams[2]
不知道为什么哪个大哥帮我看一下
-----------------------------------
错误应该是((int)prams[2]没有new,Database代码贴出来,是它里面有问题
-----------------------------------------------------------
里面是什么?正确吗?
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace EasyShop
{
/// <summary>
/// 通用的数据库处理类,通过ado.net与数据库连接
/// </summary>
public class Database : IDisposable
{
// 连接数据源
private SqlConnection con;
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="dataReader">返回存储过程返回值</param>
public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <param name="dataReader">存储过程所需参数</param>
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
//return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// 创建一个SqlCommand对象以此来执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <param name="prams">存储过程所需参数</param>
/// <returns>返回SqlCommand对象</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确认打开连接
Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // 依次把参数传入存储过程
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 加入返回参数
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
} /// <summary>
/// 打开数据库连接.
/// </summary>
private void Open()
{
// 打开数据库连接
if (con == null)
{
con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
}
if(con.State ==System.Data.ConnectionState.Closed)
con.Open(); } /// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (con != null)
con.Close();
} /// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (con != null)
{
con.Dispose();
con = null;
}
} /// <summary>
/// 传入输入参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param></param>
/// <param name="Size">参数大小</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
} /// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
} /// <summary>
/// 传入返回值参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeReturnParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
}
/// <summary>
/// 生成存储过程参数
/// </summary>
/// <param name="ParamName">存储过程名称</param>
/// <param name="DbType">参数类型</param>
/// <param name="Size">参数大小</param>
/// <param name="Direction">参数方向</param>
/// <param name="Value">参数值</param>
/// <returns>新的 parameter 对象</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param; if(Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType); param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value; return param;
}
}
}
totalNumResult =(int)prams[2].Value;
return dataReader;
这里好像如果是返回reader那么totalNumResult就返回不了值
大家帮我看一下