Oracle Connection from dependency injection in ASP.NET Core MVC using C#

Question

I'm learning how to program the database connection in ASP.NET Core MVC using C#. I am definitely not familiar, I can see stuff online but I don't understand it.

Here is my code - this is what my appsettings.json looks like:

"ConnectionStrings": {
    "DBConn": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTSERVER)     (PORT=0001))(CONNECT_DATA=(SERVICE_NAME=TEST.TEST.TEST)));User Id=Test_ID;Password=PassID;"
}

Now in my program.cs, I saw online something like this so I tried it:

builder.Services.AddTransient<IDbConnection>((sp) =>
    new OracleConnection(builder.Configuration.GetConnectionString("DBConn")));

In my controller where I'm going to test if I have connection to the Oracle database:

   using Oracle.ManagedDataAccess.Client;

   public class HomeController : Controller
   {
       private readonly ILogger<HomeController> _logger;
       private static Logger _nlogger = LogManager.GetCurrentClassLogger();      
       protected IDbConnection _connection;

       public HomeController(ILogger<HomeController> logger, Logger nlogger, IDbConnection connection)
       {
          _logger = logger;
          _nlogger = nlogger;
          _connection = connection;
       }

       public IActionResult Index()
       {
           try
           {
               using (_connection) 
               {
                   OracleCommand cmd = new OracleCommand();
                   cmd.Connection = _connection.;
                   cmd.CommandText = "pkgTest.TestSP";
                   cmd.CommandType = CommandType.StoredProcedure;

                   cmd.Parameters.Add("pi_app_cd", OracleDbType.Varchar2).Value = cID.APP_CODE;
                   cmd.Parameters.Add("po_refcsr", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

                   conn.Open();

                   using (OracleDataReader reader = objCmd.ExecuteReader())
                   {
                       while (reader.Read())
                       {
                            rowCNT++;
                       }

                       reader.Close();
                       reader.Dispose();
                   }

                   objCmd.Dispose();
                   conn.Close();
                   conn.Dispose();
              }
    
              ViewData["Title"] = "Home";
         }
         catch (Exception ex) 
         {
             _logger.LogError(ex.Message);
         }

         return View();
    }
}

My code wouldn't build because the connection is definitely wrong. I need an oracle connection but the one I'm trying to put is an IDbConnection. I don't know how use the dependency injection (if that's the right term). Maybe someone has input about this on how can I proceed? Thank you!

Connection classess generally are not good candidates for dependency injection. First, a class might need multiple connection objects, so injecting IDbConnectionFactory is a better choice. Second, the point of DI is to be agnostic to the implementation, but you're using OracleDbType.RefCursor which binds you quite tightly you to Oracle's provider, anyway. Forget about injecting the connection. I suggest looking into an OR/M for data access instead of using ODP.NET directly if you want a good abstraction. — madreflection
– madreflection, Commented Nov 27, 2024 at 15:49
Sorry, O/RM, slash was in the wrong place. It's Object-Relational Mapper. Dapper, Entity Framework, and NHibernate are common O/RMs in .NET. — madreflection
– madreflection, Commented Nov 27, 2024 at 16:53
The following may be of interest: stackoverflow.com/a/74576869/10024425 (see Option 2). — It all makes cents
– It all makes cents, Commented Nov 27, 2024 at 16:54
I just need a direct way to connect to the Oracle database, I just need the easiest one as long as it will read the connection string from the json file. — Ann
– Ann, Commented Nov 27, 2024 at 16:55

It all makes cents · Accepted Answer · 2024-11-28 01:13:00Z

The following shows how one can retrieve a database connection string from appsettings.json in a ASP.NET Core Web App (Model-View-Controller) (.NET 8) project.

appSettings.json

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "DBConnectionStr": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTSERVER)     (PORT=0001))(CONNECT_DATA=(SERVICE_NAME=TEST.TEST.TEST)));User Id=Test_ID;Password=PassID;"
    }
}

Pre-requisites:

Download/Install NuGet package: Microsoft.Extensions.Configuration (v8.0.0)

In Solution Explorer, expand <project name>
Right-click <project name> and select Manage NuGet Packages...
Click Browse tab
In the search box, type: Microsoft.Extensions.Configuration
Scroll to the top, and select Microsoft.Extensions.Configuration
Select desired version (ex: 8.0.0), and click Install
If prompted Visual Studio is about to make changes to this solution. Click OK to proceed with the changes listed below..., click OK

Add using directive:

using Microsoft.Extensions.Configuration;

Option 1:

Modify HomeController.cs as shown below:

public class HomeController : Controller
{
    private readonly IConfiguration _configuration;
    private readonly ILogger<HomeController> _logger;

    //add 'IConfiguration config' to the constructor
    public HomeController(IConfiguration config, ILogger<HomeController> logger)
    {
        _configuration = config;
        _logger = logger;

        //option1
        string? dbConnectionStr = _configuration.GetConnectionString("DBConnectionStr");
        Debug.WriteLine($"HomeController (iconfig): {dbConnectionStr}");

        //option 2
        DBConnectionOptions? options = new DBConnectionOptions();
        options =  _configuration.GetSection(DBConnectionOptions.SectionName).Get<DBConnectionOptions>();
        string? dbConnectionStr2 = options?.DBConnectionStr;
        Debug.WriteLine($"HomeController (options): {dbConnectionStr2}");
    }

    //Additional code/methods here
}

Option 2:

Additional pre-requisites:

Download/Install NuGet package: Microsoft.Extensions.Options (v8.0.2)

Create a class (name: DBConnectionOptions)

public class DBConnectionOptions
{
    public const string SectionName = "ConnectionStrings";

    //name from appsettings.json
    public string? DBConnectionStr { get; set; }
}

In Program.cs, add:

builder.Services.Configure<DBConnectionOptions>(builder.Configuration.GetSection(DBConnectionOptions.SectionName));

Program.cs (full code):

public class Program
{
    public static void Main(string[] args)
    {
        var builder = WebApplication.CreateBuilder(args);

        //added
        builder.Services.Configure<DBConnectionOptions>(builder.Configuration.GetSection(DBConnectionOptions.SectionName));

        // Add services to the container.
        builder.Services.AddControllersWithViews();

        var app = builder.Build();

        // Configure the HTTP request pipeline.
        if (!app.Environment.IsDevelopment())
        {
            app.UseExceptionHandler("/Home/Error");
        }
        app.UseStaticFiles();

        app.UseRouting();

        app.UseAuthorization();

        app.MapControllerRoute(
            name: "default",
            pattern: "{controller=Home}/{action=Index}/{id?}");

        app.Run();
    }
}

Then in HomeController.cs, add using directive:

using Microsoft.Extensions.Options;

and modify the code as shown below:

public class HomeController : Controller
{
    private readonly DBConnectionOptions _dbConnectionOptions;
    private readonly ILogger<HomeController> _logger;

    //add 'IOptions<DBConnectionOptions> dbOptions' to the constructor
    public HomeController(IOptions<DBConnectionOptions> dbOptions, ILogger<HomeController> logger)
    {
        _dbConnectionOptions = dbOptions.Value;
        _logger = logger;

        Debug.WriteLine($"HomeController (iOptions): {_dbConnectionOptions.DBConnectionStr}");
    }

    //Additional code/methods here

}

Option 3:

In HomeController.cs:

public class HomeController : Controller
{
    private readonly ILogger<HomeController> _logger;
    private string? _dbConnStr = string.Empty;

    public HomeController(ILogger<HomeController> logger)
    {
        _logger = logger;

        //create new instance
        Microsoft.Extensions.Configuration.ConfigurationBuilder builder = new ConfigurationBuilder();
        builder.SetBasePath(Directory.GetCurrentDirectory());
        builder.AddJsonFile("appsettings.json");

        IConfigurationRoot configuration = builder.Build();
        _dbConnStr = configuration.GetConnectionString("DBConnectionStr");

        Debug.WriteLine($"_connStr: {_dbConnStr}");
    }
                       
    //Additional code/methods here

}

Resources:

Collectives™ on Stack Overflow

Oracle Connection from dependency injection in ASP.NET Core MVC using C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related