.net连接oracle数据库
using System.Data.OracleClient;
using System.Data;
private void oracle_dataset()
{
string ConnectionString = "Data Source=sky;user=system;password=manager;";//写连接串
OracleConnection conn = new OracleConnection(ConnectionString);//创建一个新连接
try
{
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from MyTable";
OracleDataReader odr = cmd.ExecuteReader();
while (odr.Read())//读取数据，如果odr.Read()返回为false的话，就说明到记录集的尾部了
{
Response.Write(odr.GetOracleString(1).ToString());//输出字段 "select * from MyTable"; 有多种类型 ，索引要匹配，感觉不如odr[1].ToString()好用
}
odr.Close();
}
catch (Exception ee)
{
Response.Write(ee.Message); //如果有错误，输出错误信息
}
finally
{
conn.Close(); //关闭连接
}
}
//OracleDataAdapter的使用
1.public void oracle_dataset()
{
string ConnectionString = "Data Source=mine;user=sys;password=sys;"; //写连接串
OracleConnection conn = new OracleConnection(ConnectionString); //创建一个新连接
OracleCommand cmd = new OracleCommand("select * from fjdl.t_rights", conn);
DataSet ds = new DataSet();
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
oda.Fill(ds);
conn.Close();
DataGrid1.DataSource = ds.Tables[0].DefaultView;
DataGrid1.DataBind();
}
2.public void oracle_dataset()
{
System.Data.OracleClient.OracleConnection oracle1 = new System.Data.OracleClient.OracleConnection("user id=system;data source=rick;password=system");
string sqlText = "select * from scott.tAdmin";
System.Data.DataSet set1 = new DataSet();
System.Data.OracleClient.OracleDataAdapter Ada1 = new System.Data.OracleClient.OracleDataAdapter(sqlText, oracle1);
Ada1.Fill(set1, "oracle_data1");
this.DataGrid3.DataSource = set1.Tables["oracle_data1"].DefaultView;
this.DataGrid3.DataBind();
}
===========================================
在.NET中调用Oracle9i存储过程经验总结
在.NET中调用Oracle9i存储过程可以用多个数据提供者，比如OralceClient，OleDb。本文将用OralceClient为例。.NET语言用C#。
一、调用一个带输入、输出参数的存储过程
首先，在Oralce中创建存储过程如下：
create or replace procedure GetRecords(name_out out varchar2,age_in in varchar2) as
begin
select NAME into name_out from test where AGE = age_in;
end;
然后，在C#项目中作如下调用：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "getrecords";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("name_out",OracleType.VarChar,20);
cmd.Parameters["name_out"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("age_in",21);
try
{
cn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("Name is:{0}",cmd.Parameters["name_out"].Value.ToString());
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
以上是很平常的调用方法，但是我在做这个示例程序的时候，却不是那么一帆风顺。这里要指出OracleClient的一个不尽如人意之处，就是对于参数名称，必须和存储过程定义中的参数同名，否则就会报错。比如将代码中的“name_out”改为别的名称的话，就会报异常。但我试着用OleDb代替OralceClient，却没有这个问题。不知道在新版本的数据提供程序中会否改进这一点？
二、调用不返回数据的存储过程
首先，在Oralce中创建存储过程如下：
create or replace procedure insertRecord(UserID in varchar2,
UserName in varchar2,
UserAge in varchar2) is
begin
insert into test values (UserID, UserName, UserAge);
end;
然后，在C#项目中作如下调用：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "insertRecord";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("UserID","007");
cmd.Parameters.Add("UserName","Dell");
cmd.Parameters.Add("UserAge","40");
try
{
cn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("Record inserted!");
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
不返回数据的存储过程一般有Delete, Insert, Update等。虽然它们的SQL语句不同，但是调用方面都是一样的。只要传入正确的参数，一般不会出什么问题。不过还是要注意，在使用OralceClient作为数据提供者的时候，参数名称一定要和存储过程定义中的一致！至于参数添加的顺序倒是无所谓的，因为有参数名称作为区别。
三、IDENTITY 和 SEQUENCE
Sql Server中，定义一个列为递增列很容易，但我在Oracle中却怎么也找不到设置的方法。不过查了点资料后知道Oracle中有个叫Sequence的对象，产生一个唯一的序列号，类似于Sql Server中的IDENTITY。于是，我做了如下实验：
首先，在Oracle中创建了一个名为TEST_SEQ的Sequence对象，SQL语句如下：
create sequence TEST_SEQ
minvalue 100
maxvalue 999
start with 102
increment by 1
nocache;
语法应该是比较易懂的，最小最大值分别用minvalue,maxvalue表示，初始值是102（这个数字是动态变化的，我创建的时候设的是100，后因插入了2条数据后就自动增加了2），increment当然就是步长了。在PL/SQL中可以用test_seq.nextval访问下一个序列号，用test_seq.currval访问当前的序列号。
定义完了Sequence，接下来就是创建一个存储过程InsertRecordWithSequence：
--这次我修改了test表的定义，和前面的示例不同。其中，UserID是PK。
create or replace procedure InsertRecordWithSequence(UserID out number,
UserName in varchar2,
UserAge in number)
is
begin
insert into test(id, name, age) --插入一条记录，PK值从Sequece获取
values(test_seq.nextval, UserName, UserAge);
/*返回PK值。注意Dual表的用法*/
select test_seq.currval into UserID from dual;
end InsertRecordWithSequence;
接下来，就是在C#中进行调用了：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "InsertRecordWithSequence";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("UserID",OracleType.Number);
cmd.Parameters["UserID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("UserName","Jack");
cmd.Parameters.Add("UserAge",40);
try
{
cn.Open();
int rowAffected = cmd.ExecuteNonQuery();
Console.WriteLine("{0}行已插入。",rowAffected);
Console.WriteLine("插入行的ID为：{0}",cmd.Parameters["UserID"].Value.ToString());
cn.Close();
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
使用Sequece对象可以很容易地创建唯一序列，在存储过程中的调用也十分方便，只要sequence_name.nextval以及sequence.currval就能得到下一个以及当前的序列值。倒是Dual表值得注意。
四、使用DataReader读取返回的结果集
为了让存储过程返回结果集，必须定义一个游标变量作为输出参数。这和Sql Server中有着很大的不同！并且还要用到Oracle中“包”（Package）的概念，似乎有点繁琐，但熟悉后也会觉得很方便。
关于“包”的概念，有很多内容可以参考，在此就不赘述了。首先，我创建了一个名为TestPackage的包，包头是这么定义的：
create or replace package TestPackage is
type mycursor is ref cursor; -- 定义游标变量
procedure GetRecords(ret_cursor out mycursor); -- 定义过程，用游标变量作为返回参数
end TestPackage;
包体是这么定义的：
create or replace package body TestPackage is
/*过程体*/
procedure GetRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end GetRecords;
end TestPackage;
已经万事具备了，让我们前台调用试试：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.GetRecords"; //注意调用方法
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ret_cursor",OracleType.Cursor); //注意这里的类型
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
cn.Open();
OracleDataReader dr = cmd.ExecuteReader();
int i = 1;
while( dr.Read() )
{
Console.WriteLine("Record {0}:",i++);
Console.WriteLine("ID:{0} Name:{1} Age:{2}",
dr.GetOracleNumber(0),
dr.GetOracleString(1),
dr.GetOracleNumber(2));
Console.WriteLine();
}
dr.Close(); //用完DataReader对象要记得及时关闭
cn.Close(); //DataReader对象未关闭之前，不能关闭连接
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
请看结果：
Record 1:
ID:100 Name:Tony Age:23
Record 2:
ID:101 Name:Jack Age:40
------------------End-------------------
小结：
包是Oracle特有的概念，Sql Server中找不到相匹配的东西。在我看来，包有点像VC++的类，包头就是.h文件，包体就是.cpp文件。包头只负责定义，包体则负责具体实现。如果包返回多个游标，则DataReader会按照您向参数集合中添加它们的顺序来访问这些游标，而不是按照它们在过程中出现的顺序来访问。可使用DataReader的NextResult()方法前进到下一个游标。
五、用返回的结果集填充DataSet
ADO.NET舍弃了原来ADO中RecordSet的概念，而使用全新的DataSet来替代。DataSet可以提供更加强大的功能！有了前面的基础，利用ADO.NET从Oralce返回DataSet也是非常简单的。主要思想就是用DataAdapter接收返回的游标，再用DataAdapter的Fill()方法填充数据集。在下面的例子中，我仍用了前一个例子中建好的TestPackage包。
下面是前台调用主要代码：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.GetRecords";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
cn.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"TestTable");
cn.Close();
for( int i = 0;i <= ds.Tables["TestTable"].Rows.Count-1;i++ )
{
string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();
string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();
string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();
Console.WriteLine("Record {0}:",i+1);
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
}
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
小结：
程序调用后的结果和刚才用DataReader调用的结果一样。这里只说明怎样利用ADO.NET调用Oracle存储过程，以及怎样填充至数据集中。至于怎样操纵DataSet，不是本文的讨论范围。有兴趣的读者可以参考MSDN以及相关书籍。
六、用DataAdapter更新数据库
通常用DataAdapter取回DataSet，将会对DataSet进行一些修改，继而更新数据库（如果只是为了获取数据，微软推荐使用DataReader代替DataSet）。然而，通过存储过程更新数据库，并不是那么简单，不能简单地通过DataAdapter的Update()方法进行更新。必须手动为DataAdapter添加InsertCommand, DeleteCommand, UpdateCommand，因为存储过程对这些操作的细节是不知情的，必须人为给出。
为了达成这个目标，我完善了之前的TestPackage包，包头如下：
create or replace package TestPackage is
type mycursor is ref cursor;
procedure UpdateRecords(id_in in number,newName in varchar2,newAge in number);
procedure SelectRecords(ret_cursor out mycursor);
procedure DeleteRecords(id_in in number);
procedure InsertRecords(name_in in varchar2, age_in in number);
end TestPackage;
包体如下：
create or replace package body TestPackage is
procedure UpdateRecords(id_in in number, newName in varchar2, newAge in number) as
begin
update test set age = newAge, name = newName where id = id_in;
end UpdateRecords;
procedure SelectRecords(ret_cursor out mycursor) as
begin
open ret_cursor for select * from test;
end SelectRecords;
procedure DeleteRecords(id_in in number) as
begin
delete from test where id = id_in;
end DeleteRecords;
procedure InsertRecords(name_in in varchar2, age_in in number) as
begin
insert into test values (test_seq.nextval, name_in, age_in);
--test_seq是一个已建的Sequence对象，请参照前面的示例
end InsertRecords;
end TestPackage;
前台调用代码如下，有点繁琐，请耐心阅读：
string connectionString = "Data Source=YXZHANG;User ID=YXZHANG;Password=YXZHANG";
string queryString = "TestPackage.SelectRecords";
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ret_cursor",OracleType.Cursor);
cmd.Parameters["ret_cursor"].Direction = ParameterDirection.Output;
try
{
cn.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"TestTable");
cn.Close();
int count = ds.Tables["TestTable"].Rows.Count;
/*打印原始记录*/
Console.WriteLine("Old Records is:");
for( int i = 0;i <= count - 1;i++ )
{
string id = ds.Tables["TestTable"].Rows[i]["ID"].ToString();
string name = ds.Tables["TestTable"].Rows[i]["NAME"].ToString();
string age = ds.Tables["TestTable"].Rows[i]["AGE"].ToString();
Console.WriteLine("Record {0}:",i+1);
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
}
da.SelectCommand = cmd; //为DataAdapter指定SelectCommand
OracleCommand updateCmd = new OracleCommand("TestPackage.UpdateRecords",cn);
updateCmd.CommandType = CommandType.StoredProcedure;
updateCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");
updateCmd.Parameters.Add("newName",OracleType.VarChar,20,"NAME");
updateCmd.Parameters.Add("newAge",OracleType.Number,3,"AGE");
da.UpdateCommand = updateCmd; //为DataAdapter指定UpdateCommand
OracleCommand deleteCmd = new OracleCommand("TestPackage.DeleteRecords",cn);
deleteCmd.CommandType = CommandType.StoredProcedure;
deleteCmd.Parameters.Add("id_in",OracleType.Number,3,"ID");
da.DeleteCommand = deleteCmd; //为DataAdapter指定DeleteCommand
OracleCommand insertCmd = new OracleCommand("TestPackage.InsertRecords",cn);
insertCmd.CommandType = CommandType.StoredProcedure;
insertCmd.Parameters.Add("name_in",OracleType.VarChar,20,"NAME");
insertCmd.Parameters.Add("age_in",OracleType.Number,3,"AGE");
da.InsertCommand = insertCmd; //为DataAdapter指定InsertCommand
DataTable newTable = ds.Tables["TestTable"];
/*修改第一条记录*/
newTable.Rows[0]["AGE"] = 22;
newTable.Rows[0]["NAME"] = "John";
/*删除一条记录*/
newTable.Rows[2].Delete();
/*插入一条记录*/
DataRow newRow = newTable.NewRow();
newRow["NAME"] = "Bob";
newRow["AGE"] = 99;
newTable.Rows.Add(newRow);
cn.Open();
da.Update(newTable); //将更改更新到数据库
newTable.Clear(); //清空DataTable
da.Fill(newTable); //获取更改后的数据
cn.Close();
/*打印新记录*/
Console.WriteLine("New Records is:");
for( int i = 0;i <= newTable.Rows.Count - 1;i++ )
{
string id = newTable.Rows[i]["ID"].ToString();
string name = newTable.Rows[i]["NAME"].ToString();
string age = newTable.Rows[i]["AGE"].ToString();
Console.WriteLine("Record {0}:",i+1);
Console.WriteLine("ID:{0}\tName:{1}\tAge:{2}\n",id,name,age);
}
}
catch( OracleException ex )
{
Console.WriteLine("Exception occurred!");
Console.WriteLine("The exception message is:{0}",ex.Message.ToString());
}
finally
{
Console.WriteLine("------------------End-------------------");
}
运行结果如下：
Old Records is:
Record 1:
ID:100 Name:Tony Age:23
Record 2:
ID:101 Name:Jack Age:34
Record 3:
ID:103 Name:newAdd Age:100
New Records is:
Record 1:
ID:100 Name:John Age:22
Record 2:
ID:101 Name:Jack Age:34
Record 3:
ID:104 Name:Bob Age:99
------------------End-------------------
小结：
使用Update()方法更新数据库是非常有用的，但是只局限于单个表，对于表连接的情况，还是直接用OracleCommand的ExcuteNonQuery()方法去执行更新操作比较方便。
以下为Oracle方面的内容补充：
ORACLE段：
首先在ORACLE建立PACKAGE和PACKAGE BODY，将在这里面定义函数和存储过程返回结果集。
1：建立PACKAGE：
CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt(mycs out mytype);
function f_get(str in varchar2)
return varchar2;
end;
/
说明：其实PACKAGE只是个声明罢了。我们在这里定义了一个存储过程返回结集和一个函数，返回字符串。
2：建立PACKAGE BODY：
CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt(mycs out mytype)
is
begin
open mycs for select * from test;
end p_wt;
function f_get(str varchar2)
return varchar2
is
str_temp varchar2(100) := 'good luck!';
begin
str_temp := str_temp || str;
return str_temp;
end f_get;
end pk_wt;
/
说明：这里建立PACKAGE BODY是具体的说明和使用，将采用什么方式实现。。
C#段：
在C#中代码将分为两部分，一部分是使用函数，另外一部分是使用结果集。
定义一个连接，从WEBCONFIG里去取得：
private OracleConnection orcn=new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["scott"]);
C#调用ORACLE函数：
OracleCommand cmd=new OracleCommand("pk_wt.f_get",orcn);
cmd.CommandType=CommandType.StoredProcedure;
OracleParameter p1=new OracleParameter("str",OracleType.VarChar,10);
p1.Direction=System.Data.ParameterDirection.Input;
p1.Value=this.TextBox1.Text;
OracleParameter p2=new OracleParameter("result",OracleType.VarChar,100);
p2.Direction=System.Data.ParameterDirection.ReturnValue;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
orcn.Open();
cmd.ExecuteNonQuery();
orcn.Close();
this.Button_function.Text=p2.Value.ToString();
其中RESULT是系统自定义的函数返回变量，特别要注意的是，函数的参数的返回类型要指定，另外就是COMMAND类型也需要指定，另外和一般的存储过程没什么差别。
C#调用ORACLE返回结果集：
OracleCommand cmd=new OracleCommand("pk_wt.p_wt",orcn);
cmd.CommandType=CommandType.StoredProcedure;
OracleParameter p1=new OracleParameter("mycs",OracleType.Cursor);
p1.Direction=System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p1);
OracleDataAdapter da=new OracleDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds,"test");
this.DataGrid1.DataSource=ds;
this.DataGrid1.DataBind();
创建Oracle存储过程
=====================================================================================================
Oracle Package // Create Oracle Package on the Oracle Server
-------------------------------------------------------
CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR);
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
-------------------------------------------------------
Oracle Package Body //Create Oracle Package Body on the Oracle Server
-------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN s
THEN
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = N_EMPNO;
ELSE
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
IO_CURSOR := V_CURSOR;
END OPEN_ONE_CURSOR;
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN
OPEN V_CURSOR1 FOR SELECT * FROM EMP;
OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END OPEN_TWO_CURSORS;
END CURSPKG;
/
===============
