<

ASP.Net to Oracle Connectivity

Published on
10,434 Points
3,234 Views
2 Endorsements
Last Modified:
ASP.Net to Oracle Connectivity

Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client connecting to an Oracle database server.This article contain very basic and simple steps for the connectivity.

I came across this article : http://msdn.microsoft.com/en-us/library/ms971506.aspx which helped enourmously, and used quite a bit of it in achieving my own solution. The code and conetent below is a combination from the MS article and my own work in making it work.

It is assumed that developers know the concept of TNSNAMe.ora file on Oracle /admin/Netwrok folder where connetions for the database with IP and Port are declared. the same database connection need to use here.

Objective :

1. Connect to Oracle Database
2. Retrieve the data from database

1. Define connectionstring to Web.Config
<appSettings>
       <add key="conStr" value="Data Source=Name;User Id=UID; Password=Password"></add>
 </appSettings>

Open in new window


2. Import namespace for oracleconnections
Add a reference to System.Data.OracleClient.dll—the Microsoft .NET Framework Data Provider for Oracle—to your project
using System.Data.OracleClient;

Open in new window


3 .Define Oracle connection object

OracleConnection oraconn = null;

Open in new window


4.Code Behind Add function for connect to database
 
private bool ConnectDatabase()
	{
		oraconn = new OracleConnection(ConfigurationSettings.AppSettings["conStr"]);
		
		if (oraconn.State == System.Data.ConnectionState.Closed)
		{
			oraconn.Open();
		}
		return true;
	}

Open in new window


5. Function to call command from database
Create an OracleCommand object. Set its Connection property to the connection created . Set its  CommandType property to CommandType.Text
private string GetData(string ID)
	{
		try
		{
			OracleDataReader drec;
			OracleCommand cmd = new OracleCommand("Select colname from tablename where ID = '" + ID + "'", oraconn);
			drec = cmd.ExecuteReader();
			drec.Read();

			if ( drec.HasRows )
			{
				return drec.GetValue(0).ToString();
							}
			else
				return "E:No Data Present";
           		
		}
		catch (Exception ex)
		{
			return ex.Message;
		}
	}

Open in new window


6. Function to call Procedure from database
Create an OracleCommand object. Set its Connection property to the connection created . Set its  CommandType property to CommandType.StoredProcedure
private string GetDataBySP(string ID)
	{
		try
		{
			OracleDataReader drec;
			OracleCommand cmd = new OracleCommand("SP_NAME", oraconn);
			cmd.CommandType = CommandType.StoredProcedure ; 
			drec = cmd.ExecuteReader();
			drec.Read();

			if ( drec.HasRows )
			{
				return drec.GetValue(0).ToString();
			}
			else
				return "E:No Data Present";
           		
		}
		catch (Exception ex)
		{
			return ex.Message;
		}
	}

Open in new window


7. Call above functions from Code

ConnectDatabase();
        GetData("1");

Open in new window

     

8. Remember to close the connection when you are done
oraconn.Close();  

9. If you are retrieving a result set, create a DataSet, DataTable, or DataReader
Open the connection and execute the stored procedure using one of the Execute methods of the OracleCommand object shown below:
1.ExecuteReader : Builds an OracleDataReader by executing a stored procedure that returns a result set
2.ExecuteNonQuery : Executes a query or procedure that does not return a result set returning the number of rows affected.
3.ExecuteOracleNonQuery : Executes a query, returning the number of rows affected.
This method also uses an OracleString parameter to return the row id for the last row modified by an UPDATE, INSERT, or DELETE query.
4.ExecuteScalar: Executes a query or procedure and returns either the return value or the value of the first column of the first row of the result set as a .NET Framework data type.
5.ExecuteOracleScalar : Executes a query or procedure and returns either the return value or the value of the first column of the first row of the result set as an OracleType data type
The Oracle .NET data provider makes it easy to execute stored procedures and access the return values, whether they are one or more scalar values or result sets. You can use Oracle procedures together with the OracleDataAdapter to fill a DataSet, work with the disconnected data, and update the changes to the Oracle database at a later time.

You can also go thorugh the Article for Asp.net to SQl connectivity, which is helpul for new deveopers.

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_218-Tutorial-for-new-developers-in-Asp-net-connectivity-with-Sql-server-for-vb-C-and-fill-the-Datagrid-or-Gridview.html
2
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
Specifying username and password:
"Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated
Security=no;"
This one works only with Oracle 8i release 3 or later

Declare the OracleConnection:

C#:
using System.Data.OracleClient;
OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString = "my connectionstring";
oOracleConn.Open();

VB.NET:
Imports System.Data.OracleClient
Dim oOracleConn As OracleConnection = New OracleConnection()
oOracleConn.ConnectionString = "my connectionstring"
oOracleConn.Open()
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month