Using ObjectDataSource and GridView with Oracle

ASP.NET provides datasource control (SQLDataSource) for SQL Server, but not for Oracle. In order to take data from Oracle database and bind it to Gridview we can use ObjectDataSource. ObjectDataSource takes data from a method provided by a class.

Creating Data Access Layer - PayrollDAL

Following are the steps to create a class - PayrollDAL, which provides GetJobs() method to return rows from JOBS table as a DataTable.

Create a new empty website using Visual Studio.NET.
Select Website -> Add Reference
From Add Reference dialog box, select .NET tab. Select System.Data.OracleClient library and click on Ok button to add it to website. This step is required as this assembly is not included by default.

Add a class using Website->Add New Item. Select Class as the template and enter the PayrollDAL as the name of the class. The class is automatically placed in App_Code folder of the website. Type the following code in PayrollDAL.cs.

using System;
using System.Data;
using System.Data.OracleClient;  // required for oracle

public class PayrollDAL
{
    public DataTable GetJobs()
    {
        // using HR account of Oracle10g. 
        OracleConnection con = new OracleConnection("uid=hr;pwd=hr;server=localhost");
        OracleDataAdapter da = new OracleDataAdapter("select * from jobs", con);
        DataSet ds = new DataSet();
        da.Fill(ds, "jobs");
        return ds.Tables["jobs"];  // return a DataTable from DataSet
    }
}

Creating ASP.NET Page

After PayrollDAL class is created, take the following steps to create an asp.net page with a GridView and ObjectDataSource controls. ObjectDataSource takes data from PayrollDAL class and GridView is bound to ObjectDataSource.

Add Webform using WebSite->Add New Item and select Web Form in template. Enter jobs.aspx as filename. As there is no code to write in asp.net page, we better use single file aspx (Uncheck Place code in separate file).

Place Gridview and ObjectDataSource controls and change attributes as shown below.

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>List Of Jobs</title>
</head>
<body>
   <form id="form1" runat="server">
    <h1>List Of Jobs</h1>
    <asp:GridView ID="GridView1" runat="server" 
        DataSourceID="ObjectDataSource1" 
        AllowPaging="True">
    </asp:GridView>
    
    <asp:ObjectDataSource ID="ObjectDataSource1" 
      TypeName="PayrollDAL" 
      SelectMethod ="GetJobs"
      runat="server">
    </asp:ObjectDataSource>
   </form>
</body>
</html>

Run the current page (jobs.aspx) using View In Browser from context menu. You must see details of jobs displayed in Gridview.