Connection String Help

Jose Saiz

Is there a way that I can have my connection string either on the appsettings.json and retrieve it from the appsettings.json instead of having on every model datalayer as it reads on my class? Please provide sample code how to retrieve from the appsettings.json and use it my datalayer. This is the Models-->C# Code

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace NetstairHelpdeskV5.Models
{
    public class TicketDataAccessLayer
    {   
        //To View all tickets details    
        public IEnumerable GetAllTickets()
        {
            List lstticket = new List();
            
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {

                string sqlQuery = "spGetTicketData";
                SqlCommand cmd = new SqlCommand(sqlQuery, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TicketId", SqlDbType.Int)).Value = 0;

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Ticket ticket = new Ticket();

                    ticket.ID = Convert.ToInt32(rdr["ID"]);
                    ticket.Business_name = rdr["businees_name"].ToString();
                    ticket.Telephone = rdr["business_phone"].ToString();
                    ticket.Location = rdr["business_location"].ToString();
                    ticket.Contact_Person = rdr["contact_person"].ToString();
                    ticket.Personal_Email = rdr["contact_email"].ToString();
                    ticket.Priority = rdr["priority"].ToString();
                    ticket.Category = rdr["category"].ToString();
                    ticket.Assigned_To = rdr["assigned"].ToString();
                    ticket.Number = rdr["ticket_number"].ToString();
                    ticket.Status = rdr["status"].ToString();

                    lstticket.Add(ticket);
                }
                con.Close();
            }
            return lstticket;
        }

        //To Add new ticket record    
        public void AddTicket(Ticket ticket)
        {
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand("spInsertUpdateTickets", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@ID", ticket.ID);
                cmd.Parameters.AddWithValue("@businessname", ticket.Business_name);
                cmd.Parameters.AddWithValue("@businessphone", ticket.Telephone);
                cmd.Parameters.AddWithValue("@businesslocation", ticket.Location);
                cmd.Parameters.AddWithValue("@contactperson", ticket.Contact_Person);
                cmd.Parameters.AddWithValue("@contactemail", ticket.Personal_Email);
                cmd.Parameters.AddWithValue("@priority", ticket.Priority);
                cmd.Parameters.AddWithValue("@category", ticket.Category);
                cmd.Parameters.AddWithValue("@assigned", ticket.Assigned_To);
                cmd.Parameters.AddWithValue("@ticketnumber", ticket.Number);
                cmd.Parameters.AddWithValue("@status", ticket.Status);
                cmd.Parameters.AddWithValue("@nextId", ticket.Personal_Email);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        //To Update the records of a particluar ticket  
        public void UpdateTicket(Ticket ticket)
        {
            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand("spInsertUpdateTickets", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@ID", ticket.ID);
                cmd.Parameters.AddWithValue("@businessname", ticket.Business_name);
                cmd.Parameters.AddWithValue("@businessphone", ticket.Telephone);
                cmd.Parameters.AddWithValue("@businesslocation", ticket.Location);
                cmd.Parameters.AddWithValue("@contactperson", ticket.Contact_Person);
                cmd.Parameters.AddWithValue("@contactemail", ticket.Personal_Email);
                cmd.Parameters.AddWithValue("@priority", ticket.Priority);
                cmd.Parameters.AddWithValue("@category", ticket.Category);
                cmd.Parameters.AddWithValue("@assigned", ticket.Assigned_To);
                cmd.Parameters.AddWithValue("@ticketnumber", ticket.Number);
                cmd.Parameters.AddWithValue("@status", ticket.Status);
                cmd.Parameters.AddWithValue("@nextId", ticket.Personal_Email);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        //Get the details of a particular ticket  
        public Ticket GetTicketData(int? id)
        {
            Ticket ticket = new Ticket();

            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {  
                string sqlQuery = "[dbo].[spGetTicketData] " + id;
                SqlCommand cmd = new SqlCommand(sqlQuery, con);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ticket.ID = Convert.ToInt32(rdr["ID"]);
                    ticket.Business_name = rdr["businees_name"].ToString();
                    ticket.Telephone = rdr["business_phone"].ToString();
                    ticket.Location = rdr["business_location"].ToString();
                    ticket.Contact_Person = rdr["contact_person"].ToString();
                    ticket.Personal_Email = rdr["contact_email"].ToString();
                    ticket.Priority = rdr["priority"].ToString();
                    ticket.Category = rdr["category"].ToString();
                    ticket.Assigned_To = rdr["assigned"].ToString();
                    ticket.Number = rdr["ticket_number"].ToString();
                    ticket.Status = rdr["status"].ToString();                    
                }
            }
            return ticket;
        }

        //To Delete the record on a particular ticket  
        public void DeleteTicket(int? id)
        {

            using (SqlConnection con = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand("spDeleteTicket", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@TicketId", id);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        static private string GetConnectionString()
        {   
            return "Data Source=..\SQLExpress;Initial Catalog=HelpdeskDb;Integrated Security=True;";
        }
    }
}

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.AddControllersWithViews();
        }

Thank you.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted answer

Hey Jose Saiz, you're trying to initialize the objticket field using _configuration in the field initializer, which is not allowed. Instead, you should initialize objticket in the constructor after _configuration has been assigned.

using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using NetstairHelpdeskV5.Models;

namespace NetstairHelpdeskV5.Controllers
{
    public class TicketController : Controller
    {
        private readonly IConfiguration _configuration;
        private readonly TicketDataAccessLayer objticket;

        public TicketController(IConfiguration configuration)
        {
            _configuration = configuration;
            objticket = new TicketDataAccessLayer(_configuration);
        }

        public IActionResult Index()
        {
            List lstTicket = objticket.GetAllTickets();
            return View(lstTicket);
        }

        // Other action methods...
    }
}

Jose Saiz2y ago
Thank you Amit Mohanty, one more time for all your help, I really appreciate helping the learning curve of ASP.NET Core, I was almost giving up on it, and continue with what I already know very well, the ASP.NET Webform. I also thanks Jaimin Shethiya for all his input and sample help. I would like to ask you guys a question that already know the answer the different between ASP.NET WEB-Form and MVC Core, I understand the that .NET CORE is universal, meaning you can program for any platform, Window, Linux, Mobile etc. but the MVC, it is just more for team developing better use of javascript than the Webform, my question is what is the advantange in perfomance and speed using MVC vs Webform and of course I develop for Windows platform, and dont' need or worry about for now on the other platforms(Linux, Mac, Mobile etc), So my question is can anyone really tell me if the speed and performance using MVC is better than WebForm, because I've seen big web application developed in MVC that the speed and performance it not acceptable, and again I am only referring to Windows platform, I have not seen one for other platform other than windows yet.

Thank you guys.
+2
Jaimin Shethiya2y ago
Hello Jose

You miss the resolve the configuration part, you can use below code for that.

private readonly ICOnfiguration _configuration;

public TicketController(IConfiguration configuration)

{

_configuration = configuration;

}
+1

Jose Saiz2y ago

Hi Amit, thank you for the response, I tried your approach and get an error message while building, this is my code

error CS0236: A field initializer cannot reference the non-static field, method, or property 'TicketController._configuration'

warning CS0649: Field 'TicketController._configuration' is never assigned to, and will always have its default value null

Am I missing something?

using System.Collections;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using NetstairHelpdeskV5.Models;

namespace NetstairHelpdeskV5.Controllers
{
    public class TicketController : Controller
    {
        
        private readonly IConfiguration _configuration;

        //- Throws an error while building the code here.  
        //-error CS0236: A field initializer cannot reference the non-static field, method, or 
        // property 'TicketController._configuration'
        //-warning CS0649: Field 'TicketController._configuration' is never assigned to, and will 
        //-always have its default value null  

        TicketDataAccessLayer objticket = new TicketDataAccessLayer(_configuration);

        public IActionResult Index()
        {
            List lstTicket = new List();            
            lstTicket = objticket.GetAllTickets().ToList();

            return View(lstTicket);
        }
        [HttpGet]
        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Create([Bind] Ticket ticket)
        {
            if (ModelState.IsValid)
            {
                objticket.AddTicket(ticket);
                return RedirectToAction("Index");
            }
            return View(ticket);
        }

        [HttpGet]
        public IActionResult Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }
            Ticket ticket = objticket.GetTicketData(id);

            if (ticket == null)
            {
                return NotFound();
            }
            return View(ticket);
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Edit(int id, [Bind] Ticket ticket)
        {
            if (id != ticket.ID)
            {
                return NotFound();
            }
            if (ModelState.IsValid)
            {
                objticket.UpdateTicket(ticket);
                return RedirectToAction("Index");
            }
            return View(ticket);
        }

        [HttpGet]
        public IActionResult Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }
            Ticket ticket = objticket.GetTicketData(id);

            if (ticket == null)
            {
                return NotFound();
            }
            return View(ticket);
        }

        [HttpGet]
        public IActionResult Delete(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }
            Ticket ticket = objticket.GetTicketData(id);

            if (ticket == null)
            {
                return NotFound();
            }
            return View(ticket);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public IActionResult DeleteConfirmed(int? id)
        {
            objticket.DeleteTicket(id);
            return RedirectToAction("Index");
        }
    }
}

ASP.NET WEB-Form was so much easier dealing with the Web.Config and ConnectionString.

Thanks Again.

Amit Mohanty2y ago

First, ensure you have your connection string in the appsettings.json file. It should look something like this:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=..\\SQLExpress;Initial Catalog=HelpdeskDb;Integrated Security=True;"
  }
}

Now, modify your TicketDataAccessLayer class to read the connection string from appsettings.json.

using Microsoft.Extensions.Configuration;

namespace NetstairHelpdeskV5.Models
{
    public class TicketDataAccessLayer
    {
        private readonly IConfiguration _configuration;

        public TicketDataAccessLayer(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        //To View all tickets details
        public IEnumerable GetAllTickets()
        {
            List lstticket = new List();

            using (SqlConnection con = new SqlConnection(GetConnectionString(_configuration)))
            {
                // Rest of your code...
            }
        }

        // Rest of your methods...

        static private string GetConnectionString(IConfiguration configuration)
        {
            return configuration.GetConnectionString("DefaultConnection");
        }
    }
}

Jaimin Shethiya2y ago

Here is the another approach

public void ConfigureServices(IServiceCollection services)
{
     services.AddControllersWithViews();
     services.AddSingleton(Configuration);
}

Add below code in your class file.

public class TicketDataAccessLayer
{
    private readonly IConfiguration _configuration;
    public TicketDataAccessLayer(IConfiguration configuration)
    {
         _configuration = configurtion;
    }

    private string GetConnectionString()
    {
        return _configuration.GetConnectionString("DefaultConnectionString"))
    }
}

Jaimin Shethiya2y ago

Please refer the below code and use in your application.

In startup file add below code

public IConfiguration Configuration { get; }
public Startup(IConfiguration configuration) 
{
    Configuration = configuration;
}

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnectionString")));
}

Add in appsettings.json file

{
  "ConnectionStrings": {
    "DefaultConnectionString": "..."
  },
}

Jignesh Kumar2y ago
Please refer this one,

using IConfiguration , you can access appsettings valies,

https://www.c-sharpcorner.com/article/reading-values-from-appsettings-json-in-asp-net-core/
+3