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