appending data from the oracle to grid using storedprocedure

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

Answer 1

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

Answer 2

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};
/