Overview: This code snippet demonstrates how to call a stored procedure that transacts data. The stored procedure usp_RollupGeneralLedgerBalance receives three arguments: accountid, fiscalperiod, fiscalyear and using an update select tsql rolls up the detailed general ledger transaction into the balance table. The TSQL update statement builds a join that aggregates the sum amount on accountid and fiscal period and fiscalyear and ledger type
C# ExecuteSqlCommandAsync
public async Task<bool> UpdateBalanceByAccountId(long? accountId, int? fiscalYear, int? fiscalPeriod)
{
try
{
SqlParameter param1 = new SqlParameter("@AccountId", accountId);
SqlParameter param2 = new SqlParameter("@FiscalPeriod", fiscalPeriod);
SqlParameter param3 = new SqlParameter("@FiscalYear", fiscalYear);
//params Object[] parameters;
var result = await _dbContext.Database.ExecuteSqlCommandAsync("usp_RollupGeneralLedgerBalance @AccountId, @FiscalPeriod, @FiscalYear", param1, param2, param3);
return true;
}
catch (Exception ex) { throw new Exception(GetMyMethodName(), ex); }
}
Stored Procedure using the update select pattern
alter procedure usp_RollupGeneralLedgerBalance
(
@AccountId as varchar(20),
@FiscalPeriod as int,
@FiscalYear as int
)
as
declare @Count as int;
select @Count=count(*) from generalledgerbalance
where accountid=@AccountId and fiscalyear=@FiscalYear and fiscalperiod=@FiscalPeriod
begin transaction
if @Count>0
begin
update GeneralLedgerBalance
set amount=accountbalances.amount
from
(
select accountid, fiscalperiod, fiscalyear, ledgertype, Sum(amount) Amount
from generalledger gl
where
fiscalyear=@FiscalYear and fiscalperiod=@FiscalPeriod
group by accountid, fiscalperiod, fiscalyear,ledgertype
) accountbalances
where
GeneralLedgerBalance.accountid=accountbalances.accountid
and
GeneralLedgerBalance.AccountId=@AccountId
end
else
begin
insert into GeneralLedgerBalance
(accountid
,LedgerType
,FiscalYear
,FiscalPeriod
,Amount
)
select accountId,
ledgertype,fiscalyear,
fiscalperiod
,sum(Amount) Amount from generalledger
group by fiscalyear, accountid, fiscalperiod,ledgertype
having accountid=@AccountId and fiscalyear=@FiscalYear and fiscalperiod=@FiscalPeriod
end
commit transaction