Net Core Connection String Dapper visual studio 2017

Question

I am trying to get a Connection String set up in my .Net Core application but i keep getting the error:

System.NullReferenceException: 'Object reference not set to an instance of an object.'

I have tried adding the following to appsettings.json:

"ConnectionStrings": {

"Analysis": "Server=DESKTOP-MYSERVER;Database=MYDATABASE;User Id=sa; Password=Password123;Provider=System.Data.SqlClient;Trusted_Connection=True;MultipleActiveResultSets=true;Pooling=false;"
}

I also tried using web.config like I used to before .Net Core:

<connectionStrings>
<add name="Analysis" providerName="System.Data.SqlClient" 
     connectionString="Server=DESKTOP-MYSERVER;Database=MYDATABASE;User Id=sm;Password=Password123;"/>

Then in c# i have:

public List<DapperTest> ReadAll()
    {
        var data = new List<DapperTest>();
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Analysis"].ConnectionString))
        {
             data = db.Query<DapperTest>("select * from testTable").ToList();
        }

        return data;
    }

Both ways give me the exception of:

System.NullReferenceException: 'Object reference not set to an instance of an object.'

I have used the following resources:

.Net CORE Dapper Connection String?

https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/intro

Get connection string from App.config

But I am missing something. I have only set up connection strings once and it was not in .Net Core so it could be obvious to others.

.NET core does configuration differently. Do you have an `appsettings.json` ? The recommended way is to load the appsettings into your `IConfigurationRoot` object and inject that into your class, and get the connection string from there — Jonesopolis, Sep 13 '17 at 01:35
@Shyju I am getting the object is null on the following line in the c# code: `using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Analysis"].ConnectionString))` — JoeG, Sep 13 '17 at 01:39
@Jonesopolis I did try to add the connection string to appsettings.json as shown in my code above. I have not yet tried to load it via `IConfigurationRoot ` — JoeG, Sep 13 '17 at 01:42
@Jonesopolis would I want to use the [Options Pattern](https://andrewlock.net/how-to-use-the-ioptions-pattern-for-configuration-in-asp-net-core-rc2/) for this? — JoeG, Sep 13 '17 at 02:15

Yared · Accepted Answer · 2017-09-13T03:08:57.197

7

If you are using appsettings.json, create a simple POCO class to model your connection string configurations like this:

public class ConnectionConfig
{
        public string Analysis {get;set;}
}

Add this line in ConfigureServices method in Startup.cs

services.Configure<ConnectionConfig>(Configuration.GetSection("ConnectionStrings"));

Data service class

class YourClass{
    private string _connectionString;

    YourClass(string connectionString){
       _connectionString = connectionString;
    }

    //Your implementation
    public List<DapperTest> ReadAll()
    {
        var data = new List<DapperTest>();
        using (IDbConnection db = new SqlConnection(_connectionString)
        {
            data = db.Query<DapperTest>("select * from testTable").ToList();
        }

       return data;
   }
}

Inject IOptions<ConnectionConfig> in your controller constructor .

class YourController : Controller{
   YourClass _testing;

   YourController(IOptions<ConnectionConfig> connectionConfig){
       var connection = connectionConfig.Value;
       string connectionString = connection.Analysis;
       _testing = new YourClass(connectionString );
    }
   public IActionResult Index() { 
        var testingData = _testing.ReadAll(); 
        return View(); 
     }
 }

edited Sep 13 '17 at 03:08

answered Sep 13 '17 at 02:28

Yared

2,206
1
21
30

This is a huge help. One simple follow up question though: how would I pass in a argument now when calling this in a controller (specifically what type because I am unfamiliar with `IOptions`)? For example what would I pass in as argument here: `public IActionResult Index() { var testing = new YourClass(); var testingData = testing.ReadAll(); return View(); }` – JoeG Sep 13 '17 at 02:53
1

You can also inject IOptions in your controller's constructor and do the same like I did for YourClass – Yared Sep 13 '17 at 03:00
Now added controller implementation of IOptions – Yared Sep 13 '17 at 03:19
I am working at it now and With your implementation I am still running into the error: "Cannot convert from 'string' to IOptions" right here: `_testing = new DataBaseData(connectionString)` – JoeG Sep 13 '17 at 03:23
Are you sure adding this line, `string connectionString = connection.Analysis;` ? Show me your implementations. it is hard to get the error from the above piece – Yared Sep 13 '17 at 03:39
Yes I have that line added – JoeG Sep 13 '17 at 03:45
I got it, it looks like i had this line wrong ` YourClass(string connectionString){ _connectionString = connectionString; } ` in `YourClass` I had it taking a `IOptions` for a type. Thank you very much for your help sir. This is the clearest solution I could have been given! – JoeG Sep 13 '17 at 03:51
Does this apply to .net core 2 and above? – niico May 24 '18 at 10:06

Net Core Connection String Dapper visual studio 2017

1 Answers1

Linked