In this article, we will learn how to connect oracle database in asp.net using c#, so for this, you will need following Visual Studio 2012 or Higher, I am using Visual Studio 2017 community, other than this, download these two files from oracle to support Oracle database in .NET. Although this examples is shown using ASP.NET Web-Forms, but you can use same code in Console app and .NET MVC application, for .NET Core, you will have to use .NET Core Nuget page.

  1. Oracle Developers tools for .NET : https://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
  2. Oracle Database (I hope you alread have this, if not download it from here https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html)

OR You can also use Oracle.ManagedDataAccess NuGet package too (.NET >= 4.0, database >= 10g Release 2) and for .NET Core you can use Oracle.ManagedDataAccess.Core, but in this post example, we are using above Oracle developer tools for .NET and Database for sample purposes.

Once you have all the prerequisite, we can proceed to next steps.

Step 1: Create a new ASP.NET Empty web-application project in your Visual Studio, by navigating to File -> New -> Project -> Select "ASP.NET web-application (Right pane)" & "Web (Left-Pane)" -> Name your project "OracleInASPNET" -> Click "OK" -> Select "Empty"  project temaplet and Click "OK"

Step 2: Create a Web-Form named as "Default.aspx" (Right-click Solution, Click "Add"-> Select "Web-Form" from options )

Step 3: Add a GridView and a button in the above created web-form, as show below

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="OracleInASPNET.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server">
            </asp:GridView>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
        </div>
    </form>
</body>
</html>

Step 4: Now we will add Oracle assemblies in our project, so right-click on "reference" and then add the following dll, as shown in the image below

add-oracle-refernces-asp-net-min.png

Once you have added the DLL refernce, you need to Find "HostName" if your Oracle Database, so here is the Command for it

select sys_context('USERENV','SERVER_HOST') from dual

I was already connected to database to I used this command

SELECT  host_name
FROM    v$instance

get-host-name-oracle-for-asp-net-min.png

And here is my database, for which we will fetch records and display in the GridView

sql-developer-in-asp-net-web-forms-connected-min.png

So, the Database name = "Books" (Service_name) and table name is "BooksTable".

Step 5: once you have got all the details like Host_Name, Service Name, table name and password, you can now add the C# code to fetch the records in Datatable and fill in GridView to display it, here is the C# code for "default.aspx.cs"

using System;
using System.Data;
using System.Data.OracleClient;


namespace OracleInASPNET
{
    public partial class Default : System.Web.UI.Page
    {
        string oradb = "Data Source=(DESCRIPTION =" +
            "(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-4K41ID9)(PORT = 1521))" + 
            "(CONNECT_DATA =" + "(SERVER = DEDICATED)" + 
            "(SERVICE_NAME = books)));" + 
            "User Id=SYSTEM;Password=YourPassword;";
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            OracleConnection Con = new OracleConnection(oradb);
            Con.Open();
            DataTable tab = new DataTable();
            OracleDataAdapter da = new OracleDataAdapter("select * from BOOKSTABLE", Con);
            da.Fill(tab);
            GridView1.DataSource = tab;
            GridView1.DataBind();
            Con.Close();
            Con.Dispose();
        }
    }
}

You might also like to read:

SQL server connection string examples in C# & XML

Creating C# Custom Exception (With Console application example)

Connect to SQL Server in C# (example using Console application)

Now, you can build and execute it in browser, you will see output as below

using-oracle-database-in-asp-net-c-sharp-min.gif

Note: It is better to download and use SQL developer than SQL Plus CLI command for .NET developer, as .NET developer usually use SQL server management and Oracle SQL develoepr is similar tool

That's it, we are done.