How to Display data in Gridview

Question

User134074001 posted

Hi Friends,

I have a Gridview and I want to display data in the Gridview, BUT by using a ORACLE DATABASE. I have already connected the database to Visual studio 2013.

I am using ORACLE Xpress Edition 10g. Now

when I use this code

public partial class Gridview : System.Web.UI.Page
{
string cnstr = ConfigurationManager.ConnectionStrings["oradb"].ConnectionString.ToString();
OracleCommand cmd;
OracleDataAdapter oda;
DataSet ds = new DataSet();
OracleConnection cn;
protected void Page_Load(object sender, EventArgs e)
{
cn = new OracleConnection(cnstr);
cmd = new OracleCommand("select * from EMPLOYEES", cn);
cn.Open();
oda = new OracleDataAdapter(cmd);
oda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}

Web.Config

<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5">
<assemblies>
<add assembly="System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
</assemblies>
</compilation>
<httpRuntime targetFramework="4.5"/>
</system.web>
<connectionStrings>
<add name="oradb" connectionString="Data Source=SHAMMATZZ;User ID=hr;Password=scott;Unicode=True" providerName="System.Data.OracleClient"/>

<!--<add name="oradb" connectionString="Data Source=(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SHAMMATZZ )(PORT=1857)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));
User Id=hr ;Password=scott" providerName="System.Data.OracleClient"/>-->
</connectionStrings>
</configuration>

GIVES ME THIS ERROR:-   ORA-12154: TNS:could not resolve the connect identifier specified 

It would be nice if you could help me.

Cheers,

Aniruddha

Answer 1

User269602965 posted

System.Data.OracleClient was deprecated a long time ago.

Download and install the Oracle ODP.NET driver and client.

http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

You have three options

Unmanaged driver 32-bit Classic ODAC Oracle Data Access Client

Unmanaged driver 64-bit Classic ODAC Oracle Data Access Client

new Managed driver 32-64-bit with client built into the driver, just add the Oracle TNSNAMES.ORA file to you application /BIN folder and the driver with local reference to /BIN folder and connect.

Of course some reading is required to set this up correctly.

http://docs.oracle.com/database/121/TDDDG/toc.htm

http://docs.oracle.com/database/121/ODPNT/toc.htm

The managed driver works in ASP.NET, WPF, and console applications, I use it often.  The exception is being new they still are not fully FIPS-140 compliant if you have that enabled in your environment and EF6 support may still be buggy if you hawk the forums for problems..  But for every day connection, select, update, insert, delete, show stuff in GRIDS, it works well.

The unmanaged driver is legacy, and relies more on VERSION and BITNESS compatibility, GAC installation of policies, and installation of the Oracle client in addition to the drivers.  It also works very well and having been around many years, less buggy for advanced features.

' Get Oracle dataset into .NET dataset for use in DataGrid
Imports System.Xml.Linq.XElement

Try
	Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString
	Dim SQL = _
		<SQL>
		 SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE
		 FROM AER2.VW_COUNT_CONTRACTS
	</SQL>
	Using conn As New OracleConnection(connectionString)
		Using cmd As New OracleCommand(SQL.Value, conn)
			conn.Open()
			Using oda As New OracleDataAdapter(cmd)
				Dim ds As New DataSet()
				oda.Fill(ds)
				Me.RadGrid1.DataSource = ds
				Me.RadGrid1.MasterTableView.DataSource = ds
			End Using
		End Using
	End Using
Catch ex As Exception
	AppCalls.WriteToEventLog(ex, "Selecting VW_COUNT_CONTRACTS", "ContractAdjust.aspx.vb")
End Try