Oracle and .NET stored procedures returning dataset...

Discussion in 'ASP .Net' started by Ricardo Magalhaes, Jun 21, 2005.

  1. 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
     
    Ricardo Magalhaes, Jun 21, 2005
    #1
    1. Advertising

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

    --

    - Sahil Malik [MVP]
    Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
    ----------------------------------------------------------------------------
    ---------------

    "Ricardo Magalhaes" <> wrote in message
    news:...
    > 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], Jun 21, 2005
    #2
    1. Advertising

  3. Thanks Sahil,

    Could you give me some example ?

    Regards
    Ricardo






    "Sahil Malik [MVP]" <> escreveu na mensagem
    news:%...
    > For Oracle, you need to return a REF CURSOR. Rest of the usage remains the
    > same.
    >
    > --
    >
    > - Sahil Malik [MVP]
    > Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
    > ----------------------------------------------------------------------------
    > ---------------
    >
    > "Ricardo Magalhaes" <> wrote in message
    > news:...
    >> 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
    >>
    >>

    >
    >
     
    Ricardo Magalhaes, Jun 21, 2005
    #3
  4. Ricardo Magalhaes

    Paul Clement Guest

    On Tue, 21 Jun 2005 13:59:16 -0300, "Ricardo Magalhaes" <> wrote:

    ¤ 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)
     
    Paul Clement, Jun 21, 2005
    #4
  5. Paul,

    Tanks a lot. It works nice...

    Regards,

    Ricardo Magalhães


    "Paul Clement" <> escreveu na
    mensagem news:...
    > On Tue, 21 Jun 2005 13:59:16 -0300, "Ricardo Magalhaes"
    > <> wrote:
    >
    > ¤ 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, Jun 21, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brew
    Replies:
    0
    Views:
    933
  2. Curtis
    Replies:
    4
    Views:
    14,810
    steve
    Jun 26, 2007
  3. Vance Heron

    Oracle Stored Procedures - Out Vars

    Vance Heron, Mar 26, 2006, in forum: Ruby
    Replies:
    1
    Views:
    104
    KUBO Takehiro
    Mar 28, 2006
  4. sam944

    Oracle Stored Procedures

    sam944, Jun 23, 2006, in forum: Ruby
    Replies:
    7
    Views:
    271
    Jeffrey Hulten
    Jun 27, 2006
  5. RU
    Replies:
    2
    Views:
    211
Loading...

Share This Page