How to retrieve connection string in class from .NET Core 2.1 Web API w/o Entity Framework

Question

I have a .NET Core 2.1 Web API service and I wish to retrieve the connection string from the appsettings.json file and use it in a separate database class I wrote. I don't need or want to use Entity Framework, unfortunately, all MS docs only show EF examples. I've tried about 10 different techniques, though can't seem to get anywhere.

Here's the appsettings.json file:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=MAXIMUS,61433;Database=Geolocation;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

And the startup.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;

namespace GeoLocationService1
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseHsts();
            }

            //app.UseHttpsRedirection();
            app.UseMvc();
        } 
    }
}

The class I'm using to grab results from a stored procedure:

using Microsoft.Extensions.Configuration;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;

namespace GeoLocationService1
{
    [DataObject(true)]
    public class ip2location_data
    {
        public static string GetConnStr()
        {
            // ?????
            string dbconn = ""; //need to somehow get the connection string from the appsettings.json file
            return dbconn;
        }

        public static string LastErrorMsg = string.Empty;

        [DataObjectMethod(DataObjectMethodType.Select, false)]
        public static ip2location GetGeoLocationFromIP(string IPAddress)
        {
            ip2location O = new ip2location();

            using (SqlConnection conn = new SqlConnection(GetConnStr()))
            {
                using (SqlCommand cmd = new SqlCommand("GetGeoLocationFromIP", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 30;
                    cmd.Parameters.AddWithValue("@IPAddress", IPAddress);

                    conn.Open();

                    try
                    {
                        using (SqlDataReader rs = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if (rs.Read())
                            {
                                O.country_code = rs.GetString(rs.GetOrdinal(O.fld_country_code));
                                O.country_name = rs.GetString(rs.GetOrdinal(O.fld_country_name));
                                O.region_name = rs.GetString(rs.GetOrdinal(O.fld_region_name));
                                O.city_name = rs.GetString(rs.GetOrdinal(O.fld_city_name));
                                O.zip_code = rs.GetString(rs.GetOrdinal(O.fld_zip_code));
                                O.time_zone = rs.GetString(rs.GetOrdinal(O.fld_time_zone));
                            }
                        }
                        LastErrorMsg = string.Empty;                   
                    }
                    catch (Exception ex)
                    {
                        LastErrorMsg = ex.Message;
                    }
                }
            }

            return O;
        }
    }
}

I'm thinking that maybe it would be better to just keep the connection string in a separate text file and read it that way - but would rather do it the proper way. Any help is appreciated (yes, I'm new to .NET Core, but hadn't seen a tutorial that wasn't dependent on EF).

Answer 1

You need to import:

using Microsoft.Extensions.Configuration;

Write following code in your getConnStr:

 IConfigurationBuilder builder = new ConfigurationBuilder()
             .SetBasePath(Directory.GetCurrentDirectory())
             .AddJsonFile("appsettings.json");

 IConfiguration Configuration = builder.Build();
 return Configuration["ConnectionStrings:DefaultConnection"];