Forum Stats

  • 3,722,118 Users
  • 2,244,225 Discussions
  • 7,849,643 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

Entity framework core working on PC but not when published to web server - appsettings.json configu

user5716448
user5716448 Member Posts: 1,696 Silver Badge
edited June 2019 in ODP.NET

Hi,

Have a working CRUD application razor pages using .net core accessing a remote database when run on my pc works o.k.

However, after publishing this to web server shows error connecting to database with '' - something wrong with connection but not sure how to fix.

Steps in solution

startup.cs has

services.AddDbContext<RazorPagesMultipleContext>(options =>

                 //  options.UseSqlServer(Configuration.GetConnectionString("RazorPagesMultipleContext")));

                   options.UseOracle(Configuration.GetConnectionString("RazorPagesMultipleContext")));

razorpagesmultipecontext has

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        {

           

            optionsBuilder.UseOracle(@User Id = <uname>; Password = <pwd>; Data Source = <SID>.WORLD, opt => opt.UseOracleSQLCompatibility("11")); 

        }

and works fine when run on my own pc.

Have published the solution and copied across to web server but get message

Error.

An error occurred while processing your request.

Request ID: 0HLME1V180M3A:00000001

Development Mode

Swapping to Development environment will display more detailed information about the error that occurred.

Development environment should not be enabled in deployed applications, as it can result in sensitive information from exceptions being displayed to end users. For local debugging, development environment can be enabled by setting the ASPNETCORE_ENVIRONMENT environment variable to Development, and restarting the application.

tried amending web.config to set to Development but then get

500 - Internal server error.

     <aspNetCore processPath="dotnet" arguments=".\RazorPagesMultiple.dll" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout"

<environmentVariables>

    <environmentVariable name="ASPNETCORE_ENVIRONMENT" value="Development" />

  </environmentVariables>

/>

Had a look at the stdout and get below.

Hosting environment: Production

Content root path: D:\netcorepublished

Now listening on: http://127.0.0.1:12539

Application started. Press Ctrl+C to shut down.

warn: Microsoft.AspNetCore.HttpsPolicy.HttpsRedirectionMiddleware[3]

      Failed to determine the https port for redirect.

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]

      An error occurred using the connection to database '' on server '<SID>.WORLD'.

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out

   at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)

   at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)

   at Oracle.ManagedDataAccess.Client.OracleConnection.Open()

   at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)

Have below in appsettings.json and tried also putting below

Tried 1

{

    "Logging": {

        "LogLevel": {

            "Default": "Warning"

        }

    },

    "AllowedHosts": "*",

    "ConnectionStrings": {

        "RazorPagesMultipleContext";

    }

}

Tried 2

"RazorPagesMultipleContext": "User Id=dw;Password=m3l0n5;Data Source=(<SID>.WORLD, opt => opt.UseOracleSQLCompatibility("11"));"

{

    "Logging": {

        "LogLevel": {

            "Default": "Warning"

        }

    },

    "AllowedHosts": "*",

    "ConnectionStrings": {

        "RazorPagesMultipleContext": "Data Source=(DESCRIPTION=(ADDRESS=(COMMUNITY=<SID>.WORLD)(HOST=<server>)(PORT=1521))(CONNECT_DATA=(SID=<SID>)(GLOBAL_NAME=<SID>.WORLD)));User Id=<uname>;Password=<pwd>;"

    }

}

and even commenting it out altogether and without an appsettings.json file as would have thought would pick up connection from code itself.

any thoughts on why not connecting - server has other .net applications whci can see the dataqbase.

when set ASPNETCORE_ENVIRONMENT at server level display on screen below -

      Annotations:

        Oracle:ValueGenerationStrategy: IdentityColumn

        ProductVersion: 2.1.4-rtm-31024

        Relational:MaxIdentifierLength: 128

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

      Entity Framework Core 2.1.4-rtm-31024 initialized 'RazorPagesMultipleContext' using provider 'Oracle.EntityFrameworkCore' with options: OracleSQLCompatibility=11

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]

      An error occurred using the connection to database '' on server '<SID>.WORLD'.

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): Connection request timed out

· Share on TwitterShare on Facebook

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited May 2019

    Did your PC have a tnsnames.ora (and perhaps a sqlnet.ora) file(s) that resolved the Oracle Net alias that you were using there?

    Otherwise, I don't see how Data Source = <SID>.WORLD would resolve to your Oracle connection descriptor there. If so, then make sure to copy these files over from your PC to your web server and make sure they are in a location ODP.NET Core looks for them:

    1. Directory set in OracleConfiguration.TnsAdmin property
    2. Directory of the running ODP.NET Core assembly
    3. Current working directory
    user5716448
    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited May 2019

    Thanks alot for information.

    Yes - I have now copied the tnsnames.ora and sqlnet.ora files into the same area as the .dll and works o.k.

    I already had a TNS_ADMIN environment variable set up to C:\Oracle\product\11.2.0\client_1 at server level with tnsnames + sqlnet ora files in C:\Oracle\product\11.2.0\client_1\network\admin

    where is the OracleConfiguration.TnsAdmin as ideally for future projects don't want to have to copy tnsnames.ora and sqlnet.ora to area where .dll is?

    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited May 2019

    Changed environment variable TNS_ADMIN to add \network\admin and restarted and now o.k -don't need  to copy tnsames.ora and sqlnet.ora to every project.

    Thanks

    · Share on TwitterShare on Facebook
  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited May 2019

    Long term, please use OracleConfiguration.TnsAdmin. The environment variable, TNS_ADMIN, is not officially supported. It happens to work because Oracle hasn't removed the code when porting over from managed ODP.NET.

    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited May 2019

    Thanks for update.

    Could you please provide further instructions how to use

    OracleConfiguration.TnsAdmin

    Also whilst have got the CRUD working with code below inside the context this means the build code has the connection within it which menas less portable than having the connection defined just inside the appsettings.json file.

    How can we set up the connection within the appsettings.json file instead?

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

            {

              

                optionsBuilder.UseOracle(@User Id = <uname>; Password = <pwd>; Data Source = <SID>.WORLD, opt => opt.UseOracleSQLCompatibility("11"));

            }

    · Share on TwitterShare on Facebook
  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited May 2019

    Here's a sample on using OracleConfiguration class:

    https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/OracleConfigurationClass.html#GUID-F1F71D03-CBB8-4173-A883-0878DA84D11A

    Here's a description of how to use the TnsAdmin property:

    https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/ConfigurationTnsAdmin.html#GUID-30FD…

    Here's an example of setting up the connection string in the appsettings.json

    https://docs.microsoft.com/en-us/ef/core/miscellaneous/connection-strings

    user5716448
    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited May 2019

    Hi,

    Thanks for reply.

    Assume set the tnsadmin in the context class?

    Also having an issue setting the actual value as tried below without comment but gave error.

    public static string TnsAdmin { get; set; }

    //TnsAdmin = "C:\Oracle\product\11.2.0\client_1\network\admin";

    Re the appsettings.json - do you have an example for orcale specifically.

    whilst putting below in context is o.k getting errors when try and put this as connection in the appsetting.json string.

    (@User Id = <uname>; Password = <pwd>; Data Source = <SID>, opt => opt.UseOracleSQLCompatibility("11")); 

    Thanks

    · Share on TwitterShare on Facebook
  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited May 2019

    TnsAdmin is a property of the OracleConfiguration class. You can choose any of the following methods to have ODP.NET Core (and Oracle EF Core) locate the *.ora files.

    1. Directory set in OracleConfiguration.TnsAdmin property
    2. Directory of the running ODP.NET assembly
    3. Current working directory

    Some customers place the entire connect descriptor in the Data Source attribute to avoid having a tnsnames.ora file at all.

    If you're trying to put the entire extension method in appsetting.json, I don't know if that is possible. If you can find a way to make it work with SqlClient, then it likely will work with ODP.NET as well.

    user5716448
    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited May 2019

    Thanks for update - can put connection in data source for time being if not possible to set connection in appsettings.json

    · Share on TwitterShare on Facebook
  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,772 Employee
    edited June 2019

    Here's sample code using appsettings.json with ODP.NET Entity Framework:

    https://github.com/oracle/dotnet-db-samples/tree/master/samples/dotnet-core/ef-core/dependency-injection

    user5716448
    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited June 2019

    Thanks for update

    However, when try and use optionsBuilder.UseOracle(Configuration.GetConnectionString("DefaultConnection"));//, opt => opt.UseOracleSQLCompatibility("11") ); 

    with appsettings.json changed to below get compile errors

    appssetttings.json

    {

      "Logging": {

       "LogLevel": {

       "Default": "Warning"

      }

      },

      "AllowedHosts": "*","ConnectionStrings": {

       "DefaultConnection": "User Id=<username>;Password=<pwd>;Data Source=<SID>.WORLD;"

      }

    }

    How could we use appsettings.json in example below?

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

      {

       optionsBuilder.UseOracle(@User Id = <username>; Password = <pwd>; Data Source = (DESCRIPTION =

      (ADDRESS_LIST =

      (ADDRESS =

      (COMMUNITY = <SID>.world)

      (PROTOCOL = TCP)

      (Host = <server>)

      (Port = <port>)

      )

      )

      (CONNECT_DATA =

      (SID = <SID>)

      (GLOBAL_NAME = <SID>.world)

      ))", opt => opt.UseOracleSQLCompatibility("11")); 


      


       //optionsBuilder.UseOracle(Configuration.GetConnectionString("DefaultConnection"));//, opt => opt.UseOracleSQLCompatibility("11") ); 


      }


      


       //optionsBuilder.UseOracle(Configuration.GetConnectionString("DefaultConnection"));//, opt => opt.UseOracleSQLCompatibility("11") ); 


    · Share on TwitterShare on Facebook
  • user5716448
    user5716448 Member Posts: 1,696 Silver Badge
    edited June 2019

    Changed startup.cs as per below and commented out connection string in

      options.UseOracle(Configuration.GetConnectionString("DefaultConnection"), opt => opt.UseOracleSQLCompatibility("11"))); 

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

    and now looks to be working using appsettings.json connection string o.k.

    Thanks again.

    · Share on TwitterShare on Facebook
Sign In or Register to comment.