110,570
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 功能描述:设置分页存储过程的DbDataParameter[]
/// 说明:此方法为基础分页参数设置方法,开发者可以重写,也可以直接调用。
/// 特殊说明:此方法只能在数据实例为Oracle的时候使用。
/// </summary>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">每页的数量</param>
/// <param name="inputSqlString">输入的SQL语句字符串</param>
/// <param name="orderBy">要排序的字段名。格式:{Field1 [SortMode(DESC | ASC)]......,n [SortMode(DESC | ASC)]}</param>
/// <returns>DbCommand的参数集合</returns>
protected virtual DbParameter[] GetPagingDbDataParameters(int pageIndex, int pageSize, StringBuilder inputSqlString, string orderBy)
{
DbParameter[] parameters =
{
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER),
this.odpDotNetFactory.CreateInstance<DbParameter>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEPARAMETER)
};
Type oracleDbType = this.odpDotNetFactory.CreateInstance<Enum>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLEDBTYPE).GetType();
FieldInfo _refCursor = oracleDbType.GetField("RefCursor");
FieldInfo _Int32 = oracleDbType.GetField("Int32");
parameters[0].ParameterName = ":RESULTS";
parameters[0].GetType().GetProperty("OracleDbType").SetValue(parameters[0], _refCursor.GetValue(_refCursor), null);
parameters[0].Direction = ParameterDirection.Output;
parameters[1].ParameterName = ":TOTALRECORDCOUNT";
parameters[1].GetType().GetProperty("OracleDbType").SetValue(parameters[1], _Int32.GetValue(_Int32), null);
parameters[1].Direction = ParameterDirection.Output;
parameters[2].ParameterName = ":TOTALPAGECOUNT";
parameters[2].GetType().GetProperty("OracleDbType").SetValue(parameters[2], _Int32.GetValue(_Int32), null);
parameters[2].Direction = ParameterDirection.Output;
parameters[3].ParameterName = ":PAGEINDEX";
parameters[3].Value = pageIndex;
parameters[4].ParameterName = ":PAGESIZE";
parameters[4].Value = pageSize;
parameters[5].ParameterName = ":INPUTSQLSTRING";
parameters[5].Value = inputSqlString.ToString();
parameters[6].ParameterName = ":ORDERBY";
parameters[6].Value = orderBy;
return parameters;
“/”应用程序中的服务器错误。
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
说明: 执行当前 Web 请求期间,出现未经处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。
异常详细信息: Oracle.DataAccess.Client.OracleException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
源错误:
行 367: {
行 368: DbCommand cmd = CreateDbCommand(this.MainConnection, commandType, commandText, parameters);
行 369: dataReader = cmd.ExecuteReader(commandBehavior);
行 370: }
行 371: catch (DbException dex)
源文件: D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework\Helpers\AbstractClass\DbHelper.cs 行: 369
堆栈跟踪:
[OracleException (0x80004005): ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) +1316
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) +69
Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) +5449
Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) +69
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
ArchStar.Framework.Helpers.DbHelper.ExecuteReader(CommandType commandType, StringBuilder commandText, CommandBehavior commandBehavior, DbParameter[] parameters) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework\Helpers\AbstractClass\DbHelper.cs:369
[DbHelperException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored]
ArchStar.Framework.Helpers.DbHelper.ExecuteReader(CommandType commandType, StringBuilder commandText, CommandBehavior commandBehavior, DbParameter[] parameters) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework\Helpers\AbstractClass\DbHelper.cs:373
ArchStar.Framework.Helpers.DbHelper.ExecuteReader(CommandType commandType, StringBuilder commandText, DbParameter[] parameters) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework\Helpers\AbstractClass\DbHelper.cs:351
ArchStar.Framework.Helpers.DbHelper.ExecuteReader(StringBuilder commandText, DbParameter[] parameters) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework\Helpers\AbstractClass\DbHelper.cs:338
ArchStar.Framework.DataAccessLayer.BaseDataAccess`1.GetPagedData(Int32 pageIndex, Int32 pageSize, StringBuilder inputSqlString, String orderBy, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework.DataAccessLayer\BaseDataAccess.cs:198
[DataAccessLayerException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored]
ArchStar.Framework.DataAccessLayer.BaseDataAccess`1.GetPagedData(Int32 pageIndex, Int32 pageSize, StringBuilder inputSqlString, String orderBy, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\Archstar.Framework\ArchStar.Framework.DataAccessLayer\BaseDataAccess.cs:207
ArchStar.MES.Basic.DataAccessLayer.Material.MaterialDefaultMasterData.GetPagedData(IDictionary`2 where, Int32 pageIndex, Int32 pageSize, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\ArchStar.MES.Basic\ArchStar.MES.Basic.DataAccessLayer\Material\MaterialDefaultMasterData.cs:58
ArchStar.MES.Basic.BusinessLogicLayer.Material.MaterialDefaultMasterLogic.GetMaterialDefaultMastersByPartID(IList`1 partIDs, Int32 pageIndex, Int32 pageSize, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\ArchStar.MES.Basic\ArchStar.MES.Basic.BusinessLogicLayer\Material\MaterialDefaultMasterLogic.cs:73
[BusinessLogicLayerException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored]
ArchStar.MES.Basic.BusinessLogicLayer.Material.MaterialDefaultMasterLogic.GetMaterialDefaultMastersByPartID(IList`1 partIDs, Int32 pageIndex, Int32 pageSize, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\ArchStar.MES.Basic\ArchStar.MES.Basic.BusinessLogicLayer\Material\MaterialDefaultMasterLogic.cs:87
ArchStar.MES.Basic.BusinessLogicLayer.Material.MaterialMasterUpdateLogic.GetMaterialMasterUpdateUIDataSource(IList`1 partIDs, Int32 pageIndex, Int32 pageSize, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\ArchStar.MES.Basic\ArchStar.MES.Basic.BusinessLogicLayer\Material\MaterialMasterUpdateLogic.cs:91
[BusinessLogicLayerException: ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PROCEDURE_COMMONPAGING' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored]
ArchStar.MES.Basic.BusinessLogicLayer.Material.MaterialMasterUpdateLogic.GetMaterialMasterUpdateUIDataSource(IList`1 partIDs, Int32 pageIndex, Int32 pageSize, Int32& totalRecordCount, Int32& totalPageCount) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\ArchStar.MES.Basic\ArchStar.MES.Basic.BusinessLogicLayer\Material\MaterialMasterUpdateLogic.cs:95
ArchstarMES.WEB.Basic.Material.MaterialMasterUpdate.InnerDataBind() in D:\乔虎跃\Work\Project\ArchstarMES\trunk\Archstar.MES.WEB\Basic\Material\MaterialMasterUpdate.aspx.cs:124
ArchstarMES.WEB.Basic.Material.MaterialMasterUpdate.chklstMaterialParameter_SelectedIndexChanged(Object sender, EventArgs e) in D:\乔虎跃\Work\Project\ArchstarMES\trunk\Archstar.MES.WEB\Basic\Material\MaterialMasterUpdate.aspx.cs:88
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +116
System.Web.UI.WebControls.CheckBoxList.RaisePostDataChangedEvent() +133
System.Web.UI.WebControls.CheckBoxList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +13
System.Web.UI.Page.RaiseChangedEvents() +132
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1644
版本信息: Microsoft .NET Framework 版本:4.0.30319; ASP.NET 版本:4.0.30319.32559
<configuration>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Oracle.DataAccess"
publicKeyToken="89B483F429C47342"
culture="neutral" />
<bindingRedirect
oldVersion="2.112.2.0"
newVersion="2.112.1.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89B483F429C47342"/>
<bindingRedirect oldVersion="4.112.0.0-4.112.3.0" newVersion="2.112.1.0"/>
</dependentAssembly>
</assemblyBinding>
根本就不起作用。/*
功能描述:公共分页存储过程
编写人:Joe
编写日期:2013年06月27日
版权:Copyright © Archstarcn
*/
CREATE OR REPLACE PACKAGE PACKAGE_COMMONPAGING IS
TYPE DATASET IS REF CURSOR;
PROCEDURE PROCEDURE_COMMONPAGING
(
RESULTS OUT DATASET,--返回的结果集合
TOTALRECORDCOUNT OUT INT,--总记录数
TOTALPAGECOUNT OUT INT, --总页数
PAGEINDEX IN INT,--页索引
PAGESIZE IN INT,--每页的数量
INPUTSQLSTRING IN VARCHAR2,--输入的SQL语句字符串
ORDERBY IN VARCHAR2--要排序的字段名。格式:{Field1 [SortMode(DESC | ASC)]......,n [SortMode(DESC | ASC)]}
);
END PACKAGE_COMMONPAGING;
/
这是包头的类型, /// <summary>
/// 方法描述:重写父类创建Command对象的方法
/// 异常:ArchStar.Framework.Exceptions.DbHelperException
/// </summary>
/// <param name="dbConnection">数据库连接对象</param>
/// <param name="commandType">命令类型 默认:存储过程</param>
/// <param name="commandText">命令文本</param>
/// <param name="parameters">命令参数</param>
/// <returns>数据库命令对象</returns>
protected override DbCommand CreateDbCommand(DbConnection dbConnection, System.Data.CommandType commandType, StringBuilder commandText, params DbParameter[] parameters)
{
DbCommand cmd = null;
try
{
try
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
}
catch (InvalidOperationException ex)
{
throw new DbHelperException(ex.Message, ex);
}
cmd = factory.CreateInstance<DbCommand>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLECOMMAND);
using (cmd)
{
cmd.Connection = dbConnection;
cmd.CommandType = commandType;
cmd.CommandText = commandText.ToString();
cmd.CommandTimeout = this.CommandTimeout;
SetDbCommandParameters(cmd, parameters);
cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
}
}
catch (DbException ex)
{
throw new DbHelperException(ex.Message, ex);
}
return cmd;
}
红色就是绑定的,先加参数已经加进去了。BindBodyName 也设置成 true了。
就是DataReader 还是那个问题。 /// <summary>
/// 方法描述:重写父类创建Command对象的方法
/// 异常:ArchStar.Framework.Exceptions.DbHelperException
/// </summary>
/// <param name="dbConnection">数据库连接对象</param>
/// <param name="commandType">命令类型 默认:存储过程</param>
/// <param name="commandText">命令文本</param>
/// <param name="parameters">命令参数</param>
/// <returns>数据库命令对象</returns>
protected override DbCommand CreateDbCommand(DbConnection dbConnection, System.Data.CommandType commandType, StringBuilder commandText, params DbParameter[] parameters)
{
DbCommand cmd = null;
try
{
try
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
}
catch (InvalidOperationException ex)
{
throw new DbHelperException(ex.Message, ex);
}
cmd = factory.CreateInstance<DbCommand>(ManifestConst.ORACLE_DATAACCESS_CLIENT_ORACLECOMMAND);
cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
using (cmd)
{
cmd.Connection = dbConnection;
cmd.CommandType = commandType;
cmd.CommandText = commandText.ToString();
cmd.CommandTimeout = this.CommandTimeout;
SetDbCommandParameters(cmd, parameters);
}
}
catch (DbException ex)
{
throw new DbHelperException(ex.Message, ex);
}
return cmd;
}
/// <summary>
/// 数据库命令参数装载
/// </summary>
/// <param name="dbCommand">数据库命令对象</param>
/// <param name="parameters">数据库命令参数</param>
protected virtual void SetDbCommandParameters(IDbCommand dbCommand, params IDbDataParameter[] parameters)
{
if (dbCommand == null)
return;
if (parameters == null || parameters.Length == 0)
return;
foreach (var param in parameters)
dbCommand.Parameters.Add(param);
}
参数在这里加的。parameters[0].GetType().GetProperty("OracleDbType").SetValue(parameters[0], Enum.Parse(oracleDbType,"RefCursor"), null);
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Oracle.DataAccess"
publicKeyToken="89B483F429C47342"
culture="neutral" />
<bindingRedirect
oldVersion="2.112.2.0"
newVersion="2.112.1.0"/>
</dependentAssembly>
</assemblyBinding>
<Thread_UseAllCpuGroups enabled="true"/>
<GCCpuGroup enabled="true"/>
<gcServer enabled="true"/>
</runtime>
这是用的11G的客户端,由于10G客户端很多问题,因此不建议使用,而且高版本客户端兼容低版本服务端。
newVersion="2.112.1.0"是11g的64位版本号,而oldVersion="2.112.2.0"是11g的32位版本号,这里oldVersion就用开发用的版本号,而newVersion则使用客户安装的版本号,如果不清楚版本号,可以在注册表里查看,例如使用安装程序在安装时,检测注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.112.2.0分支,最后那个就是版本号,动态产生程序配置文件。