Search code examples
oracle-databaseentity-framework-coreconnection-string

How can I learn the credentials for the Connection String in optionsBuilder.UseOracle(@"User Id=<>;Password=<>;Data Source=<>")?


I want to connect from my codebase which is console C# project to an Oracle database.
I am using Entity Framework Core 6.0:

    using Microsoft.EntityFrameworkCore;
    Console.WriteLine("Hello, World!");
    BloggingContext oracleContext = new BloggingContext();
    Console.WriteLine(oracleContext.Database.CanConnect());
    public class BloggingContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(@"User Id=userid;Password=password;Data Source=localhost:1521/ORACLE19");
    }

The code compiled successfully but no connection is established,
I am getting False in the next output line when checking oracleContext.Database.CanConnect(). What credetials should I put into UseOracle("...") connection string, instead of those shown? I tried everything!
My Oracle database is on my local computer and working well, because I can connect with it through such connection tools as SQL*plus, RaizorSQL, PL/SQL Developer and see its contents.
How can I learn the right connection string for UseOracle("..."), using these tools or somehow otherwise? What are these User Id, Password, Data Source?


Solution

  • After two weeks of trials and errors I have eventually got the successful connection while acquiring knowledge about Oracle database system. The trick for the right connection string is as follows: the string for SERVICE_NAME should be one could see as the sqlplus answer to the command show parameter service_name
    as EdStevens kindly pointed out or looked up in the file tnsnames.ora. On my system it is oracle19.errdonald.net. But in this case the name of the user for User Id should start with prefix C## because it is supposed to be a common user in the Oracle system container CDB. Otherwise, if the user is local and created in a pluggable database pdb, suppose its name is pdb1, then the string for SERVICE_NAME should start with pdb1 prefix. And the most crucial thing for me was to discover that the Password for the user has to contain no less than two capital letters to be valid! So the working code looks like this on my system:

    using Microsoft.EntityFrameworkCore;
    Console.WriteLine("Hello, World!");
    BloggingContext oracleContext = new BloggingContext();
    Console.WriteLine(oracleContext.Database.CanConnect());
    public class BloggingContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseOracle(@"User Id=C##blog;Password=*********SS;Data Source=localhost:1521/oracle19.errdonald.net");
    }
    

    Or,the connection string might be:

    optionsBuilder.UseOracle(@"User Id=blog;Password=*********SS;Data Source=localhost:1521/pdb1.errdonald.net");
    

    if the user blog is in the pluggable database pdb1.
    *******SS means that the password may be whatsoever but contain no less than 2 capital letters, SS, for example and be no shorter than 9 characters.