appending data from the oracle to grid using storedprocedure RSS

• • Reply
  

  ganaparthi

  Participant

  1266 Points

  330 Posts

  appending data from the oracle to grid using storedprocedure

  Nov 07, 2009 07:42 AM|LINK

  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

  Regards,
  Srinivas Ganaparthi,
  'All things are difficult before they are easy'
  My Blog
• • Reply
  

  sirasingh

  Member

  8 Points

  4 Posts

  Re: appending data from the oracle to grid using storedprocedure

  Dec 23, 2011 04:10 PM|LINK

  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

  <div>

  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

  </div> <div></div>
• • Reply
  

  Lannie

  Contributor

  3724 Points

  726 Posts

  Re: appending data from the oracle to grid using storedprocedure

  Dec 25, 2011 03:39 AM|LINK

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