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;
上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] 下一页 没有相关教程
|