How to write connection string for my asp.net core mvc

Question

Although I looked at many solutions but I can't seem to apply or understand how to use connection string in my asp.net core mvc program.

This is my appsettings.json file:

{
  "Logging":{
    "Debug":{
      "LogLevel":{
        "Default":"Information"
      }
    },
    "AllowedHosts":"*",
    "ConnectionString":{
      "connectionString":"Server=W1571415\\MSSQLSERVER01;Database=***********;UserId=*********;Password=***********;"
    }
  },
  "dependencies":{
    "Microsoft.Extensions.Caching.Memory":"1.0.0",
    "Microsoft.AspNetCore.Session":"1.0.0"
  }
}

My startup.cs file code:

using HospitalApp.Infrastructure;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Localization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Razor;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Globalization;

//This file is for app behaviour
namespace HospitalApp
{
    /// <summary>
    /// Startup class contains the methods scuh as ConfigureServices which are used to configure the environment in which the application is running.
    /// </summary>
    public class Startup
    {
        private readonly ILogger _logger;

        /// <summary>
        /// The control from the Program.cs when it encounters the .UseStartup() comes here.It uses the parameters configuration of the type IConfiguration and logger of the type ILogger.
        /// </summary>
        public Startup(IConfiguration configuration, ILogger<Startup> logger)
        {
            Configuration = configuration;
            _logger = logger;
        }

        /// <summary>
        /// This method Configuration is of the type IConfiguration.
        /// </summary>
        public IConfiguration Configuration { get; }

        /// <summary>
        /// This method gets called by the runtime. Use this method to add services to the container.
        /// </summary>
        /// <param name="services">services is of the type IServiceCollection which is used specify the contract of collection to service descriptors.</param>
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            ////services.AddSession(so =>
            ////{
            ////    so.IdleTimeout = TimeSpan.FromSeconds(60);
            ////});

            services.AddLocalization(options => options.ResourcesPath = "Resources");
            services.AddMvc()
                        .SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
                        .AddViewLocalization(
            LanguageViewLocationExpanderFormat.Suffix,
            options => { options.ResourcesPath = "Resources"; })
                        .AddDataAnnotationsLocalization();

            //dependency injection
            services.AddSingleton<IDbRepository, DbRepository>();
           
            //_logger.LogInformation("Added TodoRepository to services");
            services.AddDistributedMemoryCache(); // Adds a default in-memory implementation of IDistributedCache
            services.AddSession();


           
        }

        /// <summary>
        /// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        /// </summary>
        /// <param name="app">This is provides mechanism to confifure application's request pipeline.</param>
        /// <param name="env">Provides information about the webhsoting environment an application is running in.</param>
        /// <param name="loggerFactory">Represent a type used to configure the logging system.</param>
        /// <param name="logger">It is of the type ILogger which is a generic interface for logger.</param>
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, ILogger<Startup> logger)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                loggerFactory.AddFile("Logs/mylog-{Date}.txt");
                _logger.LogInformation("In Development environment");
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseCookiePolicy();
            app.UseSession();

            var cultures = new List<CultureInfo>
            {
                new CultureInfo("en"),
                new CultureInfo("pt")
            };

            app.UseRequestLocalization(options => {
                options.DefaultRequestCulture = new RequestCulture("en-US");
                options.SupportedCultures = cultures;
                options.SupportedUICultures = cultures;
            });


            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

And I'm not using a DBContext file so instead of that here is my Infrastrucutre file which contains the files IDbRepostiory and DbRepository. and I have to use this connection string in DbRepostiory file. So, how to go about it ?

 namespace HospitalApp.Infrastructure
{

    /// <summary>
    /// This class is used to establish a data connection with the MS SQL Server.
    
    /// The connectionString specified here stores the Database name and the Data Source specifies the server name.
    /// </summary>
    public class DbRepository : IDbRepository
    {
        /// HERE I NEED TO SPECIFY THE CONNECTION STRING FROM APPSETTING.JSON FILE.

        //string connectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HospitalDummy;Data Source=W1571415\\MSSQLSERVER01;Application Name=Hospital";
        string connectionString = @"Server=W1571415\MSSQLSERVER01;Database=HospitalDummy;User Id=hospitaluser;Password=abc@123;";

        /// <summary>
        /// This function is used to display all the Patients data.  
        /// The patient's data is taken in the form of a list.
        /// SqlConnection is used to specify the connection of the connectionString with the Database.
        /// Here the Stored procedure spGetAllPatients is taken which is used to display the Patients details.
        /// </summary>
        /// <returns>It returns the details of the patient's in the form of a List.</returns>
        public List<Patient> GetAllPatients()
        {
            List<Patient> lstpatient = new List<Patient>();
          
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand("dbo.spGetAllPatients", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Patient patient = new Patient();
                        try
                        {
                            patient.PatientId = Convert.ToInt32(rdr["PatientId"]);
                            if (rdr["FullName"] != DBNull.Value)
                                patient.FullName = Convert.ToString(rdr["FullName"]);
                            if (rdr["Ailment"] != DBNull.Value)
                                patient.Ailment = Convert.ToString(rdr["Ailment"]);
                            if (rdr["Gender"] != DBNull.Value)
                                patient.Gender = Convert.ToString(rdr["Gender"]);
                            if (rdr["Status"] != DBNull.Value)
                                patient.Status = Convert.ToString(rdr["Status"]);
                            count = count + 1;
                            
                            if (patient.Status == "Active")
                            {
                                lstpatient.Add(patient);
                            }
                        }
                        catch (Exception e)
                        {
                           Console.WriteLine("Records not displayed properly. ",e);
                        }
                    }

                    con.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine("Failure in getting records properly. ", e);
                }
            }
}

This is the IDbRepostiory file for reference:

public interface IDbRepository
{
    /// <summary>
    /// This function is used to get the list of all Patients.
    /// </summary>
    /// <returns>It has a return type of list i.e. the Patient data that will be returned will be in the form of a list.</returns>
    List<Patient> GetAllPatients();
}

I test by using your code and it works well.Which line did you make error?Please make sure that your server name,Database name,User Id and password is right. — Rena
– Rena, Commented Oct 14, 2019 at 3:29

Michael · Accepted Answer · 2019-10-14 14:38:22Z

The overview for this is

i. Ensure that your DbRepository implements an interface (IDbRepository, done) so it can be called as a service, and contains a constructor which is used to receive any config data

ii. Access appsettings.json from Startup.cs to retrieve the json config data

iii. From Startup.cs instantiate DbRepository as an IDbRepository service and pass the connection string as a parameter

If DbRepository inherited from DbContext, you could add a constructor like this

public DbRepository(DbContextOptions<DbRepository> options) : base(options) 
{
}

Alternativelty, here is how you can manually create a constructor, and for good measure I have added a test method GetConnectionString()

public class DbRepository : IDbRepository
{
    private string _connectionString;

    public DbRepository(string connection)
    {
        _connectionString = connection;
    }

    public string GetConnectionString()
    {
        return _connectionString;
    }
}

In Statup.cs see how the constructor accesses appsetting.json and stores the data in the Configuration field

See how ConfigureServices calls the IDbRepository service and passes the parameter

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using HospitalApp.Infrastructure;

namespace Test
{
    public class Startup
    {
        public IConfiguration Configuration { get; }

        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .AddEnvironmentVariables();
                Configuration = builder.Build();
        }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            string connectionString = Configuration["ConnectionString:connectionString"];
            //  this is how a class inheriting dbcontext can be called
            //  services.AddDbContext<DbRepository>(options => options.UseSqlServer(connectionString));
            //  or call IDbRepository as a custom service
            services.AddScoped<IDbRepository>(repository => new DbRepository(connectionString));
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env) { //  ... }
    }
}

Imagine if your HomeController looked something like this, accepting an instance of the IDbRepository service through it's constructor, so it could access the repository and pass the connection string into the ViewBag

public class HomeController : Controller
{
    private readonly IDbRepository _dbrepository;

    public HomeController(IDbRepository dbrepository)
    {
        _dbrepository = dbrepository;
    }

    [HttpGet]
    public ViewResult Home()
    {
        ViewBag.ConnectionString = _dbrepository.GetConnectionString();
        return View("Home");
    }
}

And Home.cshtml just needs to contain the following to demonstrate success

<h1>ConnectionString</h1>
<b>@ViewBag.ConnectionString</b>

Confirmed in a test project - first time I have created a service which accepts a parameter (previously just used dbcontext) so it was good to look into this - hope this helps!

Here is my appsettings.json

{
    "Logging": {
        "LogLevel": {
            "Default": "Information"
        }
    },
    "AllowedHosts": "*",
    "ConnectionString": {
        "connectionString": "Server=W1571415\\MSSQLSERVER01;Database=***********;UserId=*********;Password=***********;"
    },
    "dependencies": {
        "Microsoft.Extensions.Caching.Memory": "1.0.0",
        "Microsoft.AspNetCore.Session": "1.0.0"
    }
}

Collectives™ on Stack Overflow

How to write connection string for my asp.net core mvc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related