logo

C# How to call a stored procedure with executesqlAsync using code first

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
s