locked
appending data from the oracle to grid using storedprocedure RRS feed

  • Question

  • User-455811767 posted

    Hi

    iam using asp.net and c# and oracle,

    in my app i created one stored procedure

    CREATE OR REPLACE
    PROCEDURE GetCustDetails(p_recordset1 OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN p_recordset1 FOR
      SELECT *
        FROM CUST_INFO_TABLE;
    END GetCustDetails;

    now i call this stored procedure in my app like this..

    OdbcCommand cmd = new OdbcCommand("GetCustDetails", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new OdbcParameter
            ("reccount", OdbcType.Cursor)).Direction = ParameterDirection.Output;

    here actually i need to give curser type,but i didn't have that datatype.

    but if i go for Oracle name space its available for me..
                  
                    objCmd.Parameters.Add("GetCustDetails", OracleType.Cursor).Direction = ParameterDirection.Output;

    i haven't find curser type in odbc,

            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "cust");
            GridView1.DataSource = ds.Tables["cust"];
            GridView1.DataBind();

    now which way i can solve my problem,mainly my intenstion is getting all the custmersdetails from the table through odbc,i know normal method like to sending query through odbcdataadapter,but i need to use stored procedure,

    please give me any samples available..

    regards
    ganaparthi

    Saturday, November 7, 2009 3:42 AM

All replies

  • User-1563925586 posted

    A simple and small code is required for resotring the backed up data in an application database. The following Vb.Net code I am using in my aaplication and for further help you can visit http://ram-a-singh.blogspot.com/2011/12/how-to-restore-backed-up-data-in-vbnet.html

    Try
                Dim portfolioPath As String = My.Application.Info.DirectoryPath
                If MessageBox.Show("Restoring the database will erase any changes you have made since you last backup. Are you sure you want to do this?", _
                            "Confirm Delete", _
                            MessageBoxButtons.OKCancel, _
                            MessageBoxIcon.Question, _
                            MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
                    'Restore the database from a backup copy.
                    FileCopy("C:\Backup\PIS.Mdb", portfolioPath & "\PIS.mdb")
                    MsgBox("Database Restoration Successful")
                End If
            Catch ex As Exception
                Dim MessageString As String = "Report this error to the system administrator: " & ControlChars.NewLine & ex.Message
                Dim TitleString As String = "Employee Master Details Data Load Failed"
                MessageBox.Show(MessageString, TitleString, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    Friday, December 23, 2011 12:10 PM
  • User269602965 posted

    Use ODP.NET (Oracle Data Access client) instead of ODBC.

    Much faster and more full featured.

    Example code getting RefCursor in ODP.NET

    VB.NET code in ASP.NET application
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
    IS
    BEGIN
      OPEN ListBirdNames FOR
        SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
    END;
    
    END {PACKAGENAME};
    /
    
    
    Saturday, December 24, 2011 11:39 PM