Base solution for your next web application
Open Closed

How to get connection string to use dapper for linq left join in app service? #9790


User avatar
0
ISTeam created

App info

  • What is your product version? .NET Core MVC & jQuery 8.3
  • What is your product type (Angular or MVC)? MVC
  • What is product framework type (.net framework or .net core)? .NET Core

Issue

I need to perform left out join with group by and sum between two tables/repository using dapper and raw SQL query. But I am not able to get connection string to use dapper module in the Application project in WidgetAppService.

private readonly IConfiguration _appConfiguration;

is not getting initialized with appsettings.json data and so I am not able to get the connection string information in the Application project. Is it possible? If yes, how?

`string sqlQuery = $";WITH CTE_POItems AS(SELECT SONo, SUM(ISNULL(Quantity, 0)) AS Quantity, SUM(ISNULL(Amount, 0)) AS Amount FROM app_PurchaseOrderItem GROUP BY SONo), CTE_POS AS(SELECT DATEPART(MONTH, P.OrderDate) AS Month, SUM(ISNULL(I.Amount,0)) AS OrdersValue, SUM(ISNULL(I.Quantity, 0)) AS Units, COUNT(ISNULL(I.SONo, 0)) AS TotalSOs FROM app_PurchaseOrder P LEFT JOIN CTE_POItems I ON P.SONo = I.SONo WHERE P.CustomerNo IN({associatedClients}) AND P.OrderDate BETWEEN '{CurrentYearStartDate.ToShortDateString()}' AND '{TomorrowDate.ToShortDateString()}' GROUP BY DATEPART(MONTH, P.OrderDate)) SELECT* FROM CTE_POS";

        using (SqlConnection db = new SqlConnection(***DefaultConnString***))
        {
            db.Open();
            data.AddRange(db.Query<OrdersInLastNMonths>(sqlQuery));
            db.Close();
        }`

Thanks.


2 Answer(s)
  • User Avatar
    0
    ISTeam created

    I found the answer from ticket #9235.

    Is there any other way to get it or the one mentioned is the best way?

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ISTeam

    Best way to get the connection string is using IConnectionStringResolver. If you are using this, it is fine :).