locked
Problem connecting C# app with Oracle RRS feed

  • Question

  • User-777989632 posted

    WebConfig:

    <add name="OraCnnStr" providerName="System.Data.OracleClient" connectionString="Data Source=ORIONMIS;Persist Security Info=True;User ID=ORNRO;Password=READONLYORION;"/>

    Code:

    string ConnectionString = Convert.ToString(ConfigurationManager.ConnectionStrings["OraCnnStr"]);
    DbProviderFactory Dbfactory = ConnectionClass.GetDbFactory();
    DbConnection conn = Dbfactory.CreateConnection();  //ERROR_1
    conn.ConnectionString = ConnectionString;
    conn.Open(); //ERROR_2
    return conn;

    Initial error is ERROR_1

    conn.ServervVersion threw an exception of type system.invalidoperationexception

    another error is ERROR_2

    ORA-12154: TNS:could not resolve the connect identifier specified

    same code is working fine with MS SQL Server 2008R2 but giving error when connecting with Oracle 10g? Can any one help?

    Wednesday, October 9, 2013 7:12 AM

All replies

  • User269602965 posted

    System.Data.OracleClient has been deprecated (discontinued) by Microsoft.

    Use OracleDataAccess.dll

    Install the Oracle ODP.NET Client installation (latest version can connect to Oracle 10g, 11g, and 12c databases), configure the tnsnames.ora to match your connection string

    If the database is remote, ensure there are no firewall issues with the Oracle tns listening port set up on client installation.

    sample code to insert data

    web.config
    
      <connectionStrings>
        <add name="OraConnStr" connectionString="Data Source={YOUR_ORACLE_SID};Persist Security Info=True;User Id={SCHEMA};Password={Password}" providerName="Oracle.DataAccess.Client">
        </add>
      </connectionStrings>
      
      <namespaces>
        <clear />
        <add namespace="Oracle.DataAccess.Client" />
        <add namespace="Oracle.DataAccess.Types" />
      </namespaces>
    
    '***********************************************'
    ' sample code behind page to update and insert data    '
    
    Imports System.Xml.Linq.XElement
    
      Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal, ByVal strDescription As String, ByVal dateDateClosed As DateTime)
        ' Insert Quantity into new row Units table'
        Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
        Try
          Dim SQL =
          <SQL>
          INSERT INTO {YOURSCHEMANAME}.UNITS
            (UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) 
          VALUES 
            (UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)
          </SQL>
          Using conn As New OracleConnection(OraConnStr)
            Using cmd As New OracleCommand(SQL.Value, conn)
              cmd.Parameters.Clear()
              cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
              cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input)
              cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
              conn.Open()
              cmd.ExecuteNonQuery()
            End Using
          End Using
        Catch ex As Exception
          AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
        End Try
    End Sub
      

    Thursday, October 10, 2013 10:39 PM