ODP.NET:
引用：
using Oracle.DataAccess; //Oracle g 11.2.0
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
//下载 http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html
//引用：D:\app\geovindu\product\11.2.0\dbhome_1\ODP.NET\bin
//用法参考
//
//http://docs.oracle.com/cd/B28359_01/appdev.111/b28844/procedures_dot_net.htm
//http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDataAdapterClass.htm //.net 4.0
//https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm
数据库连接字符串：
public string connectionString = @"DATA SOURCE=oracle11g;USER ID=geovin;password=geovindu;";
/// <summary>
/// 20160918 涂聚文
/// Geovin Du
/// </summary>
public class BookKindListDAL : IBookKindList
{ ///<summary>
/// 追加记录
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int InsertBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
new OracleParameter("temParent",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 追加记录返回
/// </summary>
/// <param name="authorList"></param>
/// <param name="authorID"></param>
/// <returns></returns>
public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)
{
bookKindLID = 0;
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),
new OracleParameter("temParent",OracleDbType.Int32,4),
new OracleParameter("temId",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
par[2].Direction = ParameterDirection.Output;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);
if (ret > 0)
{
bookKindLID =int.Parse(par[2].Value.ToString());
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
///修改记录
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int UpdateBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("BookKindID",OracleDbType.Int32,4),
new OracleParameter("BookKindName",OracleDbType.NVarchar2,1000),
new OracleParameter("BookKindParent",OracleDbType.Int32,4),
};
par[0].Value = bookKindList.BookKindID;
par[1].Value = bookKindList.BookKindName;
par[2].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 删除记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public bool DeleteBookKindList(int bookKindID)
{
bool ret = false;
try
{
OracleParameter par = new OracleParameter("BookKindID", bookKindID);
int temp = 0;
temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", CommandType.StoredProcedure, par);
if (temp != 0)
{
ret = true;
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 查询记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public BookKindListInfo SelectBookKindList(int bookKindID)
{
BookKindListInfo bookKindList = null;
try
{
OracleParameter par = new OracleParameter("BookKindID", bookKindID);
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))
{
if (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0; }
}
}
catch (OracleException ex)
{
throw ex;
}
return bookKindList;
} ///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public List<BookKindListInfo> SelectBookKindListAll()
{
List<BookKindListInfo> list = new List<BookKindListInfo>();
BookKindListInfo bookKindList = null;
try
{
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;
list.Add(bookKindList); }
}
}
catch (OracleException ex)
{
throw ex;
}
return list;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public DataTable SelectBookKindListDataTableAll()
{
DataTable dt = new DataTable();
try
{
using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
dt = reader; }
}
catch (OracleException ex)
{
throw ex;
}
return dt;
} }
System.Data.OracleClient(.net 4.0)
引用：
using System.Collections;
using System.Data;
using System.Configuration;
using System.Data.OracleClient;//.net 4.0 //用法参考
//https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter(v=vs.110).aspx
//http://blog.csdn.net/chinawn/article/details/336904
//C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.OracleClient.dll
数据库连接字符串：
public string connectionString = @"Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle11g)));user id=geovin;password=geovindu;Persist Security Info=True;";
/// <summary>
/// 20160918 涂聚文
/// Geovin Du
/// </summary>
public class BookKindListDAL : IBookKindList
{ ///<summary>
/// 追加记录
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int InsertBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleType.NVarChar,1000),
new OracleParameter("temParent",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
/// <summary>
/// 追加记录返回
/// </summary>
/// <param name="authorList"></param>
/// <param name="authorID"></param>
/// <returns></returns>
public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)
{
bookKindLID = 0;
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("temTypeName",OracleType.NVarChar,1000),
new OracleParameter("temParent",OracleType.Number,4),
new OracleParameter("temId",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindName;
par[1].Value = bookKindList.BookKindParent;
par[2].Direction = ParameterDirection.Output;
ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);
if (ret > 0)
{
bookKindLID =int.Parse(par[2].Value.ToString());
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
///修改记录
///</summary>
///<param name="BookKindListInfo"></param>
///<returns></returns>
public int UpdateBookKindList(BookKindListInfo bookKindList)
{
int ret = 0;
try
{
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("BookKindID",OracleType.Number,4),
new OracleParameter("BookKindName",OracleType.NVarChar,1000),
new OracleParameter("BookKindParent",OracleType.Number,4),
};
par[0].Value = bookKindList.BookKindID;
par[1].Value = bookKindList.BookKindName;
par[2].Value = bookKindList.BookKindParent;
ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 删除记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public bool DeleteBookKindList(int bookKindID)
{
bool ret = false;
try
{
OracleParameter par = new OracleParameter("BookKindID", bookKindID);
int temp = 0;
temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", CommandType.StoredProcedure, par);
if (temp != 0)
{
ret = true;
}
}
catch (OracleException ex)
{
throw ex;
}
return ret;
}
///<summary>
/// 查询记录
///</summary>
///<param name="bookKindIDInfo"></param>
///<returns></returns>
public BookKindListInfo SelectBookKindList(int bookKindID)
{
BookKindListInfo bookKindList = null;
try
{
OracleParameter par = new OracleParameter("BookKindID", bookKindID);
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))
{
if (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0; }
}
}
catch (OracleException ex)
{
throw ex;
}
return bookKindList;
} ///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public List<BookKindListInfo> SelectBookKindListAll()
{
List<BookKindListInfo> list = new List<BookKindListInfo>();
BookKindListInfo bookKindList = null;
try
{
using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;
list.Add(bookKindList); }
}
}
catch (OracleException ex)
{
throw ex;
}
return list;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public DataTable SelectBookKindListDataTableAll()
{
DataTable dt = new DataTable();
try
{
using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))
{
dt = reader; }
}
catch (OracleException ex)
{
throw ex;
}
return dt;
} }
System.Data.OleDb
string connString = "Provider=OraOLEDB.Oracle.1;User ID=geovin;Password=geovindu;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle11g)))";
OleDbConnection conn = new OleDbConnection(connString);
try
{
conn.Open();
MessageBox.Show(conn.State.ToString());
DataTable dt = conn.GetSchema(this.comboBox1.Text.Trim());
this.dataGridView1.DataSource = dt;
this.textBox1.Text = GetColumnNames(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
oracle package sql:
/**创建一个名为pkgBookKinds的包查所表中所有内容**/
create or replace package pkg_BookKinds is
--定义一个公有的游标类型cursor_pdt
--ref 可以在程序间传递结果集
--一个程序里打开游标变量，在另外的程序里处理数据
type cursor_pdt is ref cursor;
--声明一个存储过程 ,游标类型参数为输出类型
procedure proc_GetAllBookKind(cur_set out cursor_pdt);
end pkg_BookKinds; /**创建一个包体**/
create or replace package body pkg_BookKinds is
--实现包中没有实现的存储过程
procedure proc_GetAllBookKind(cur_set out cursor_pdt) as
begin
--打开游标，由于定义游标时使用ref处理游标可以推迟到客户端
open cur_set for select * from BookKindList;
end;
end; /**使用过程测试定义的存储过程**/
declare
--定义游标类型的变量
cur_set pkg_BookKinds.cursor_pdt;
--定义行类型
pdtrow BookKindList%rowtype;
begin
--执行存储过程
pkg_BookKinds.proc_GetAllBookKind(cur_set);
--遍历游标中的数据
LOOP
--取当前行数据存入pdtrow
FETCH cur_set INTO pdtrow;
--如果未获取数据就结束循环
EXIT WHEN cur_set%NOTFOUND;
--输出获取到的数据
DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||'，'||pdtrow.BookKindName);
END LOOP;
CLOSE cur_set;
end;
--创建包以游标的形式返回BookKindList的结果集
create or replace package pkg_BookKindList is
-- Author : geovindu
type mycur is ref cursor;
procedure fun_GetRecords(cur_return out mycur);
end pkg_BookKindList; create or replace package body pkg_BookKindList is
-- Function and procedure implementations
procedure fun_GetRecords(cur_return out mycur)
is
begin
open cur_return for select * from BookKindList; end fun_GetRecords; end pkg_BookKindList; declare
--定义游标类型的变量
cur_return pkg_BookKindList.mycur;
--定义行类型
pdtrow BookKindList%rowtype;
begin
--执行存储过程
pkg_BookKindList.fun_GetRecords(cur_return);
--遍历游标中的数据
LOOP
--取当前行数据存入pdtrow
FETCH cur_return INTO pdtrow;
--如果未获取数据就结束循环
EXIT WHEN cur_return%NOTFOUND;
--输出获取到的数据
DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||'，'||pdtrow.BookKindName);
END LOOP;
CLOSE cur_return;
end;
C# 3.5 调用查询:
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form3_Load(object sender, EventArgs e)
{
BindGridView();
}
/// <summary>
///
/// </summary>
private void BindGridView()
{
OracleConnection conn = new OracleConnection(connectionString);
//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
OracleCommand comm = new OracleCommand("pkg_BookKindList.fun_GetRecords", conn);
comm.Parameters.Add("cur_return", OracleType.Cursor).Direction = ParameterDirection.Output;
comm.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(comm))
{ da.Fill(ds);
}
this.dataGridView1.DataSource = ds.Tables[0].DefaultView; }
/// <summary>
///
/// </summary>
private void BindGridViewOther()
{
OracleConnection conn = new OracleConnection(connectionString);
//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString
OracleCommand comm = new OracleCommand("pkg_BookKindList.fun_GetRecords", conn);
comm.CommandType = CommandType.StoredProcedure;
//定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
OracleParameter cur_set = new OracleParameter("cur_return", OracleType.Cursor);
//设置参数为输出类型
cur_set.Direction = ParameterDirection.Output;
//添加参数
comm.Parameters.Add(cur_set);
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(comm))
{ da.Fill(ds);
}
this.dataGridView1.DataSource = ds.Tables[0].DefaultView; }
Oracle sql:
---某条记录的字段
drop PROCEDURE proc_Select_BookKindName; CREATE OR REPLACE PROCEDURE proc_Select_BookKindName(kind_id IN BookKindList.BookKindID%type) AS
--声明语句段
v_name varchar2(20);
BEGIN
--执行语句段
SELECT o.BookKindName INTO v_name FROM BookKindList o where o.BookKindID=kind_id;
dbms_output.put_line(v_name);
EXCEPTION
--异常处理语句段
WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND');
END; --测试
begin
proc_Select_BookKindName(1);
end; ---一条记录
--创建包:
create or replace package pack_BookKindId is
type cur_BookKindId is ref cursor;
end pack_BookKindId;
--创建存储过程
create or replace procedure proc_curBookKindId(p_id in number,p_cur out pack_BookKindId.cur_BookKindId)
is
v_sql varchar2(400);
begin if p_id = 0 then
open p_cur for select * from BookKindList;
else
v_sql := 'select * from BookKindList where BookKindID =: p_id';
open p_cur for v_sql using p_id;
end if;
end proc_curBookKindId; --测试查询一条记录存储过程
-- Test statements here
set serveroutput on
declare
v_id number := 1; --0 时,所有记录
v_row BookKindList%rowtype; --注意这里是表名
p_cur pack_BookKindId.cur_BookKindId;
begin
proc_curBookKindId(v_id, p_cur);
loop
fetch p_cur into v_row;
exit when p_cur%notfound;
DBMS_OUTPUT.PUT_LINE(v_row.BookKindName||'='||v_row.BookKindID);
end loop;
close p_cur;
end;
ODP.NET:
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public List<BookKindListInfo> SelectBookKindListAll()
{
List<BookKindListInfo> list = new List<BookKindListInfo>();
BookKindListInfo bookKindList = null;
try
{
//定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
//设置参数为输出类型
cur_set.Direction = ParameterDirection.Output;
//OracleHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "pkg_Select_BookKindListAll.proc_Select_BookKindListAll", cur_set)
using (OracleDataReader reader = OracleHelper.GetReader("pkg_Select_BookKindListAll.proc_Select_BookKindListAll", CommandType.StoredProcedure, cur_set))
{
while (reader.Read())
{
bookKindList = new BookKindListInfo();
string s = reader["BookKindID"].ToString();
bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;
bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";
bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;
list.Add(bookKindList); }
}
}
catch (OracleException ex)
{
throw ex;
}
return list;
}
///<summary>
/// 查询所有记录
///</summary>
///<returns></returns>
public DataTable SelectBookKindListDataTableAll()
{
DataTable dt = new DataTable();
try
{
//定义参数，注意参数名必须与存储过程定义时一致，且类型为OracleType.Cursor
OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);
//设置参数为输出类型
cur_set.Direction = ParameterDirection.Output;
//添加参数
//comm.Parameters.Add(cur_set);
using (DataTable reader = OracleHelper.GetTable("pkg_Select_BookKindListAll.proc_Select_BookKindListAll", CommandType.StoredProcedure, cur_set))
{
dt = reader; }
}
catch (OracleException ex)
{
throw ex;
}
return dt;
}