ASP.Net to Oracle Connectivity

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>
                      

Select all Open in new window

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

using System.Data.OracleClient;
                      

Select all Open in new window

3 .Define Oracle connection object

OracleConnection oraconn = null;
                      

Select all 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;
                      	}
                      

Select all 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;
                      		}
                      	}
                      

Select all 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.StoredProcedur e

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;
                      		}
                      	}
                      

Select all Open in new window

7. Call above functions from Code

ConnectDatabase();
                              GetData("1");
                      

Select all 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:
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.

https://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