Logo 
Search:

Asp.net Articles

Submit Article
Home » Articles » Asp.net » DatabaseRSS Feeds

How to perform SELECT ALL Data function in Oracle

Posted By: Vivek Patel     Category: Asp.net     Views: 4660

This article will explain how to select all or read all data using Oracle and return record set. In simple words I am going to run a simple select query which will select all records from table and display in asp.net gridview.


Step 1: Create an Oracle Store Procedure to read all data from table.

CREATE OR REPLACE PROCEDURE Person_SelectAll
  (
           R1       OUT SYS_REFCURSOR
  )
 AS
  BEGIN

  OPEN R1 FOR
  
    Select * from Person
    Order by City Asc;                                   

  EXCEPTION
  WHEN OTHERS THEN
         raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

  END Person_SelectAll;
/


Step 2: Open VS.Net and Create Asp.net Website


Step 3:  Open Default.aspx Page and add Gridview Control 
<h2>
    Read All Records
</h2>
<p>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</p>



Step 4: Open Default.aspx.cs file and add below code
protected void Page_Load(object sender, EventArgs e)
{
    GridView1.DataSource = SelectAllData("Person_SelectAll");
    GridView1.DataBind();
}

private OracleConnection GetConnection()
{
    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    return new OracleConnection(strConnString);
}

private DataTable SelectAllData(string StoredProc)
{            
    OracleConnection oConn =  GetConnection();

    DataTable dtResult = new DataTable();            

    if (oConn != null)
    {
        OracleCommand oCmd = new OracleCommand();
        oCmd.CommandText = StoredProc; 
        oCmd.CommandType = CommandType.StoredProcedure;
        oCmd.Connection = oConn;

        try
        {
            oCmd.Parameters.Add("R1", OracleType.Cursor).Direction = ParameterDirection.Output;                    
            OracleDataAdapter oAdapter = new OracleDataAdapter(oCmd);
            oConn.Open();
            dtResult.Clear(); 
            oAdapter.Fill(dtResult);                        
        }
        finally
        {
            //Close Connection and Freeup Memory
            oConn.Close();
            oConn.Dispose();
        }
    }
    return dtResult;
}


Now, Lets understand above code step by step


1) PageLoad Method - Inside page load we are calling SelectAll method by passing Store Procedure name.  This will display data in Gridview.

2) GetConnection Method - Creates connection object and returns new connection object

3) SelectAllData Method - This method takes store procedure name to call.  In our example above we have passed Store Procedure name "Person_SelectAll".  This procedure runs a simple select statement which will return all records from person table ordered by city name.

Ones you are done with all steps try to run website and you will be able to see output as under
  
Share: 


Didn't find what you were looking for? Find more on How to perform SELECT ALL Data function in Oracle Or get search suggestion and latest updates.

Vivek Patel
Vivek Patel author of How to perform SELECT ALL Data function in Oracle is from United States. Vivek Patel says

I have started working in .Net Technology since its beta release and lucky to got chance to work on .Net 1.1, 2.0 and now working on .Net 3.5. I have worked in both C# and VB.Net for Asp.net Projects. I can also provide Sharepoint development needs. I can efficiently switch role from Tech Lead and Developer. I have comprehensive knowledge of Asp.net Development. I have been award Microsoft Most Valuable Award twice in Asp.net Technology.

Blog: http://dotnetguts.blogspot.com

 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!