locked
VS 2013 MVC5 web app cannot connect to oracle with TNS connection string in web.config RRS feed

  • Question

  • User1868852945 posted


    I have VS 2013 MVC5 web app that does not use EF or DbContext - instead I have manually coded Model and Controller classes with a Repository class that connects and gets data  via ADO.NET from a SQL Server stored procedure. The connectionString is in web.config and this works fine when connecting to SQL Server.  


    Next I add Oracle.ManagedDataAccess.Client to the project and I should be able to change just a few lines of code to connect to oracle instead of SQL Server.  Both databases have the exact same result set that fits the Model and they both have the same stored procedure

    Below is the Repository class and web.config entries with the SQL Server code commented out and the oracle code highlighted bold.
    When I run the page with the code set for oracle I get error when opening the connection...
    Exception Details: OracleInternal.Network.NetworkException: ORA-12154: TNS:could not resolve the connect identifier specified
    Testing for  TNS TEST1 with myAccount/t works fine for sqlplus.
    Thanks if you can advise how to make the oracle connection work.

    Repository class (top portion):

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Web.Mvc;
    using Oracle.ManagedDataAccess;

    namespace myProj.Repository
    {
    public class SchemaTreeRepository
    {
    //private SqlConnection con;
    private Oracle.ManagedDataAccess.Client.OracleConnection con;
    private void connection()
    {
    //string constr = ConfigurationManager.ConnectionStrings["ConnectionSS"].ToString();
    string constr = ConfigurationManager.ConnectionStrings["ConnectionOracle"].ToString();
    //con = new SqlConnection(constr);
    con = new Oracle.ManagedDataAccess.Client.OracleConnection(constr);

    }
    public List<SchemaTreeModel> GetSchemaTree()
    {
    connection();
    List<SchemaTreeModel> SchemaTreeList = new List<SchemaTreeModel>();

    SqlCommand com = new SqlCommand("get_schema_tree");
    //SqlCommand com = new SqlCommand("get_schema_tree", con);
    com.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();

    con.Open();

    web.config connect string entries:
    <connectionStrings>
    <add name="ConnectionSS" providerName="System.Data.SqlClient" connectionString="Data Source=JAYDESKTOP;Initial Catalog=myCatlg;Integrated Security=True;User Id=Jay;Password=t;MultipleActiveResultSets=True" />
    <add name="ConnectionOracle" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=myAccount;Password=t;Data Source=TEST1" />
    </connectionStrings>

    Wednesday, June 21, 2017 4:48 PM

Answers

  • User1868852945 posted

    This connecion entry in web.config fixed the open connection error.

    <add name="ConnectionOracle" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=//J01DESKTOP:1521/TEST1;User Id=myAccount;Password=t" />

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 22, 2017 1:26 AM