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.
