Oracle存储过程:
包头:create or replace package BindDataList is
Type T_DataList Is Ref Cursor;
PROCEDURE DataList_Get
( T_TableName In Varchar,
G_GetFields In Varchar,
S_StrWhere In Varchar,
O_OrderBy In Varchar,
D_DataList OUT T_DataList
);
end BindDataList;包体:
create or replace package body BindDataList is
PROCEDURE DataList_Get
( T_TableName In Varchar, -------表名
G_GetFields In Varchar, -------列名
S_StrWhere In Varchar, -----Where条件
O_OrderBy In Varchar, --------排序
D_DataList OUT T_DataList
) As S_Sql Varchar2(2000);
Begin
S_Sql :=
' Select Distinct ' || G_GetFields ||
' From ' || T_TableName ||
' Where ' || S_StrWhere;
If O_OrderBy Is Not Null Then
S_Sql :=
S_Sql || ' Order By ' || O_OrderBy;
End If;
Open D_DataList For S_Sql;End DataList_Get;
End BindDataList;
中间层:
#region //绑定作业公司下拉列表
public DataTable GetCompanyList()
{
DataTable dt = new DataTable();
OracleParameter[] Parameters = new OracleParameter[5];
Parameters[0] = new OracleParameter("T_TableName", OracleType.VarChar);
Parameters[0].Value = "CompanyID";
Parameters[0].Direction = ParameterDirection.Input;
Parameters[1] = new OracleParameter("G_GetFields", OracleType.VarChar);
Parameters[1].Value = "CompName,Company";
Parameters[1].Direction = ParameterDirection.Input;
Parameters[2] = new OracleParameter("S_StrWhere", OracleType.VarChar);
Parameters[2].Value = "CompanyID.Company!='*'";
Parameters[2].Direction = ParameterDirection.Input;
Parameters[3] = new OracleParameter("O_OrderBy", OracleType.VarChar);
Parameters[3].Value = "Company";
Parameters[3].Direction = ParameterDirection.Input;
Parameters[4] = new OracleParameter("T_DataList", OracleType.Cursor);
Parameters[4].Direction = ParameterDirection.Output;
dt = zjpisCommon.DataAccess.ExecuteDataTable(zjpisCommon.DataAccess.GetConnStr(), CommandType.StoredProcedure, "BindDataList.DataList_Get", Parameters);
return dt;
}
#endregion页面调用: //绑定作业公司
void BindCompany()
{
ShipDynamicInfoSearch dis = new ShipDynamicInfoSearch();
DataTable dt = dis.GetCompanyList();
//AddNewRow("CompName");
this.ddCompany.DataSource = dt.DefaultView;
this.ddCompany.DisplayMember = "CompName";
this.ddCompany.ValueMember = "Company";
this.ddCompany.SelectedText = "请选择";
}错误信息:找不到BindDataList包头,错误数据类型或参数错误
包头:create or replace package BindDataList is
Type T_DataList Is Ref Cursor;
PROCEDURE DataList_Get
( T_TableName In Varchar,
G_GetFields In Varchar,
S_StrWhere In Varchar,
O_OrderBy In Varchar,
D_DataList OUT T_DataList
);
end BindDataList;包体:
create or replace package body BindDataList is
PROCEDURE DataList_Get
( T_TableName In Varchar, -------表名
G_GetFields In Varchar, -------列名
S_StrWhere In Varchar, -----Where条件
O_OrderBy In Varchar, --------排序
D_DataList OUT T_DataList
) As S_Sql Varchar2(2000);
Begin
S_Sql :=
' Select Distinct ' || G_GetFields ||
' From ' || T_TableName ||
' Where ' || S_StrWhere;
If O_OrderBy Is Not Null Then
S_Sql :=
S_Sql || ' Order By ' || O_OrderBy;
End If;
Open D_DataList For S_Sql;End DataList_Get;
End BindDataList;
中间层:
#region //绑定作业公司下拉列表
public DataTable GetCompanyList()
{
DataTable dt = new DataTable();
OracleParameter[] Parameters = new OracleParameter[5];
Parameters[0] = new OracleParameter("T_TableName", OracleType.VarChar);
Parameters[0].Value = "CompanyID";
Parameters[0].Direction = ParameterDirection.Input;
Parameters[1] = new OracleParameter("G_GetFields", OracleType.VarChar);
Parameters[1].Value = "CompName,Company";
Parameters[1].Direction = ParameterDirection.Input;
Parameters[2] = new OracleParameter("S_StrWhere", OracleType.VarChar);
Parameters[2].Value = "CompanyID.Company!='*'";
Parameters[2].Direction = ParameterDirection.Input;
Parameters[3] = new OracleParameter("O_OrderBy", OracleType.VarChar);
Parameters[3].Value = "Company";
Parameters[3].Direction = ParameterDirection.Input;
Parameters[4] = new OracleParameter("T_DataList", OracleType.Cursor);
Parameters[4].Direction = ParameterDirection.Output;
dt = zjpisCommon.DataAccess.ExecuteDataTable(zjpisCommon.DataAccess.GetConnStr(), CommandType.StoredProcedure, "BindDataList.DataList_Get", Parameters);
return dt;
}
#endregion页面调用: //绑定作业公司
void BindCompany()
{
ShipDynamicInfoSearch dis = new ShipDynamicInfoSearch();
DataTable dt = dis.GetCompanyList();
//AddNewRow("CompName");
this.ddCompany.DataSource = dt.DefaultView;
this.ddCompany.DisplayMember = "CompName";
this.ddCompany.ValueMember = "Company";
this.ddCompany.SelectedText = "请选择";
}错误信息:找不到BindDataList包头,错误数据类型或参数错误
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货