Oracle and .NET stored procedures returning dataset...

[Ricardo Magalhaes]

Hi,

How can I return a DataSet from oracle stored procedure(that returns for
example, the customers from Brasil) to my ASP.NET application

In Sql server is easy.. and seens like this:

------------ into sql

Procedure SP_cursos (@categoriaID Int)

As

Select ds_curso from curso where ID_Categoria = @categoriaID

------------------------ into my .net class

Dim dsCursos As New DataSet
Dim daCurso As New OleDbDataAdapter
daCurso.SelectCommand = New OleDbCommand
daCurso.SelectCommand.Connection = objConn
daCurso.SelectCommand.CommandText = "SP_Cursos"
daCurso.SelectCommand.CommandType = CommandType.StoredProcedure


Dim objParam1 As New OleDbParameter("@categoriaID", SQLDBType.Int )
objParam1.Direction = ParameterDirection.Input
daCurso.SelectCommand.Parameters.Add(objParam1)

daCurso.Fill(dsCursos, "cursor")

ddlMiniCursoOpcao1.DataSource = dsCursos
ddlMiniCursoOpcao1.DataSource = dsCursos.Tables(0)
ddlMiniCursoOpcao1.DataTextField =
dsCursos.Tables(0).Columns("ds_curso").ColumnName.ToString()
ddlMiniCursoOpcao1.DataValueField =
dsCursos.Tables(0).Columns("cd_curso").ColumnName.ToString()
ddlMiniCursoOpcao1.DataBind()

Best Regards,

Ricardo Magalhães

 

[Sahil Malik [MVP]]

For Oracle, you need to return a REF CURSOR. Rest of the usage remains the
same.

 

[Ricardo Magalhaes]

Thanks Sahil,

Could you give me some example ?

Regards
Ricardo

 

[Paul Clement]

¤ Thanks Sahil,
¤
¤ Could you give me some example ?
¤

See the following MS KB article:

How To Return an Oracle Ref Cursor to a .NET DataReader Object by Using the .NET Managed Provider
for Oracle
http://support.microsoft.com/default.aspx?scid=kb;en-us;322160


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ricardo Magalhaes]

Paul,

Tanks a lot. It works nice...

Regards,

Ricardo Magalhães