Cannot seem connect to SQL Server through ASP.Net Core (keeps returning a null)

Question

I am trying to connect to my local SQL Server through the Configuration Manager via an n-tier architecture design. In short, I want to query data from my local database, serialize it from a DataSet to a JSON, and have it being read in a JSON format so that I can listen to it from my Angular side.

I tested my n-tier by implementing it in a console app and the result is as it should be, but throwing that exact n-tier into my ASP.NET Core build and it keeps returning

System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

At first I implemented web.config because I did not know of appsettings.json, but when I tried to call ConnectionString from that json file through a GET, it keeps returning null (can be anything).

At the BLL class, I tried removing the controller inheritance, but then no value gets shown.

I also tried that whole Startup method, but that required too many dependencies.

Folder structure:

Controller -> N-Tier -> DBConn;DAL;BLL;

appsettings.json

"ConnectionStrings": 
  { "Local_One": "Data source=localhost;Initial Catalog=VAS; User ID=user101; Password=password1;Trusted_Connection=True;MultipleActiveResultSets=true;" },


public class DBConn
{
    public string ConnectionString()
    {
        //Also where the error gets caught
        return ConfigurationManager.ConnectionStrings["Local_One"].ConnectionString;
    }
}

public class DAL

public class DAL
{
    SqlConnection conn = new SqlConnection(new DBConn().ConnectionString());
    public string query = null;
    SqlDataAdapter da = new SqlDataAdapter();
    DataSet ds = new DataSet();
    int i = 0;

    public DataSet GetAllUserTypes()
    {
        if(conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }

        try
        {
            query = "sp_GETUSERTYPES"; //calls a stored procedure
            da.SelectCommand = new SqlCommand(query,conn);
            da.Fill(ds);
            ds.Tables[0].TableName = "USER TYPES";
            da.Dispose();
            conn.Close();
        }
        catch(SqlException e)
        {
        }

        return ds;
    }
}

public class BLL

[Produces("application/json")]
[Route("api/N-Tier/BLL/[action]")]//localhost:5000/api/N-Tier/BLL/GetAllUserTypes
public class BLL:Controller
{
    DAL dal = new DAL();

    public string GetAllUserTypes()
    {
        return JsonConvert.SerializeObject(dal.GetAllUserTypes(),Formatting.Indented);//using Newtonsoft.Json;
    }
}

Expected result (which I got from a Console App.config):

{
  "USERS": [
    {
      "userTypeID": 1,
      "userTypeDescription": "Registered_User"
    },
    {
      "userTypeID": 2,
      "userTypeDescription": "Administrator"
    },
    {
      "userTypeID": 3,
      "userTypeDescription": "Moderator"
    }
  ]
}

Error message:

System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

Ikacho · Accepted Answer · 2019-08-14 21:34:12Z

1

In asp.net Core you shouldn't use ConfigurationManager to from appsettings.json, that's why you get null. Instead:

In Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    ...
    // Add the whole configuration object here.
    services.AddSingleton<IConfiguration>(Configuration);
}

In your controller add a field for the configuration and a parameter for it on a constructor

private readonly IConfiguration configuration;

public HomeController(IConfiguration config) 
{
    configuration = config;
}

Now later in your view code you can access it like:

connectionString = configuration.GetConnectionString("Local_One");

Original source

answered Aug 14, 2019 at 21:34

Ikacho

563 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot seem connect to SQL Server through ASP.Net Core (keeps returning a null)

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