domingo, 17 de julio de 2011

Store Procedure que regresa un select oracle

Publicadas por Carlos Juan a la/s 3:22 p.m. Etiquetas: C#, oracle

1.) Crear un Ref Cursor que será retornado:

create or replace PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
END Types;

2.) Creando el Store procedure

create or replace
PROCEDURE getAllCity(p_recordset OUT types.cursor_type) AS
BEGIN
OPEN p_recordset FOR
select * from tbl_country;
END getAllCity;

3.) Código c# que llama el store procedure

string strconn = myDynconnStr;
OracleConnection conn = new OracleConnection(strconn);
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = conn;
objCmd.CommandText = "getAllCity";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter odr = new OracleDataAdapter(objCmd);
DataSet ds = new DataSet();
odr.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();