c# - 在 Asp.net MVC 列表中获取 SQL 游标获取的数据

Question

我为每个循环嵌套了多个查询，其中包含数百万条记录，以处理来自 asp.net MVC 的每次循环迭代的查询然后我创建了一个游标，而不是从操作中执行多个查询，但无法检索获取的行在列表/变量中。

SQL：

ALTER proc [dbo].[sp_get_AllofPostings]
    @comp nvarchar(max),
    @TNumber int
    as
    begin
    
    DECLARE @Id NVARCHAR(128), @AccountCode varchar(max), @TrasactionNumber int, @LedgerNumber int, @RefCode varchar(50), @IsDebit bit, @Amount float, @pcomp nvarchar(max), @CreateDate datetime, @status bit
    -- declare cursor
    DECLARE postings_cursor CURSOR FOR
      SELECT *  FROM Postings where TrasactionNumber = @TNumber and CoId=@comp and Amount IS NOT NULL 
    OPEN postings_cursor;
    
    FETCH NEXT FROM postings_cursor INTO @Id, @AccountCode, @TrasactionNumber, @LedgerNumber, @RefCode, @IsDebit, @Amount, @pcomp, @CreateDate, @status
    WHILE @@FETCH_STATUS = 0
        BEGIN
        --do somthing here
        Declare @LAccountName nvarchar(max), @LGLAccount int, @GLName nvarchar(max), @Gaccountcode varchar(50)
            SET @LAccountName = (select AccountName from Ledgers where CoId=@comp and AccountCode=@AccountCode)
            SET @LGLAccount =   (select GLAccount from Ledgers where CoId=@comp and AccountCode=@AccountCode)
            SET @GLName =       (select GLName from GLAccounts where CoId=@comp and Keyid = @LGLAccount)
            SET @Gaccountcode = (select Name from Groups where CoId=@comp and Code = @AccountCode)
        FETCH NEXT FROM postings_cursor INTO @Id, @AccountCode, @TrasactionNumber, @LedgerNumber, @RefCode, @IsDebit, @Amount, @pcomp, @CreateDate, @status
        END;
    CLOSE postings_cursor;
    DEALLOCATE postings_cursor;
END

C＃：

var posting = db.sp_get_AllofPostings(comp, item.Number);

标签： c#sqldatabase-performance