用ADO.NET访问Oracle9i存储过程(下)
添加时间: 2008-4-23 23:11:36 作者: Oracle指导 阅读次数:19 来源: http://www.d9soft.com
使用 DataAdapter 更新 Oracle
当您使用 REF CURSOR 参数填充 DataSet 时，不能简单地使用 OracleDataAdapter 的 Update() 方法。这是因为在执行存储过程时，Oracle 不能提供确定表名和列名所需的信息。要使用 DataAdapter 的 Update() 方法，您必须创建在基础表中更新、插入和删除记录的过程。该方法类似于在 SQL Server 中使用的方法。
本节说明如何生成一个可以处理所需的创建、检索、更新和删除操作的包，以便能够从 Oracle 数据库中检索 LOCATION 数据，也能够将对 DataSet 数据所做的不连续更改重新更新到 Oracle 数据库。包头如下所示：
CREATE OR new PACKAGE CRUD_LOCATIONS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR);
PROCEDURE UpdateLocations (
p_location_id IN NUMBER,
p_street_address IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_city IN VARCHAR2,
p_state_province IN VARCHAR2,
p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (
p_location_id OUT NUMBER,
p_street_address IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_city IN VARCHAR2,
p_state_province IN VARCHAR2,
p_country_id IN CHAR);
END CRUD_LOCATIONS;
包正文如下所示：
CREATE OR new PACKAGE BODY CRUD_LOCATIONS AS
-- retrieve all LOCATION records
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
OPEN cur_Locations FOR
SELECT * FROM LOCATIONS;
END GetLocations;
-- update a LOCATION record
PROCEDURE UpdateLocations (
p_location_id IN NUMBER,
p_street_address IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_city IN VARCHAR2,
p_state_province IN VARCHAR2,
p_country_id IN CHAR)
IS
BEGIN
UPDATE LOCATIONS
SET
STREET_ADDRESS = p_street_address,
POSTAL_CODE = p_postal_code,
CITY = p_city,
STATE_PROVINCE = p_state_province,
COUNTRY_ID = p_country_id
WHERE
LOCATION_ID = p_location_id;
END UpdateLocations;
-- delete a LOCATION record
PROCEDURE DeleteLocations (p_location_id IN NUMBER)
IS
BEGIN
DELETE FROM LOCATIONS
WHERE LOCATION_ID = p_location_id;
END DeleteLocations;
-- insert a LOCATION record
PROCEDURE InsertLocations
(
p_location_id OUT NUMBER,
p_street_address IN VARCHAR2,
p_postal_code IN VARCHAR2,
p_city IN VARCHAR2,
p_state_province IN VARCHAR2,
p_country_id IN CHAR
)
AS
BEGIN
INSERT INTO LOCATIONS (
LOCATION_ID,
STREET_ADDRESS,
POSTAL_CODE,
CITY,
STATE_PROVINCE,
COUNTRY_ID)
VALUES (
LOCATIONS_SEQ.NEXTVAL,
p_street_address,
p_postal_code,
p_city,
p_state_province,
p_country_id
);
SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;
END InsertLocations;
END CRUD_LOCATIONS;
下面的代码定义了一个 DataAdapter，从而使用上述包中定义的过程来创建、检索、更新和删除支持 DataAdapter 的数据。DataAdapter 既可用来将数据检索到 DataSet 中，也可用来将对 DataSet 所做的更改更新到 Oracle 数据库中。
// define the connection string
String connString = "Data Source=oracledb;User Id=UserID;Password=Password;";
// create the data adapter
OracleDataAdapter da = new OracleDataAdapter();
// define the select command for the data adapter
OracleCommand selectCommand =
new OracleCommand("CRUD_LOCATIONS.GetLocations",
new OracleConnection(connString));
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.Add("cur_Locations",
OracleType.Cursor).Direction = ParameterDirection.Output;
da.SelectCommand = selectCommand;
// define the udpate command for the data adapter
OracleCommand updateCommand =
new OracleCommand("CRUD_LOCATIONS.UpdateLocations",
new OracleConnection(connString));
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
"LOCATION_ID");
用ADO.NET访问Oracle9i存储过程(下)(2) 第 [1] [2] [3] [4] 下一页
上一篇文章: 用ADO.NET访问Oracle9i存储过程(上) 下一篇文章: Oracle冷备份的不完全恢复(上)
相关文章: