最近研究結算程式，發現要大量的更新資料，還是得寫資料庫的SP，於是就研究了寫法，也順便把整個流程寫成小程式，程式碼如下：

public class SettlementManager
{
    public static SettlementManager Instance = new SettlementManager();

    private SettlementLib _settlementLib;

    private SettlementManager() 
    {
        _settlementLib = new SettlementLib();
        _sqlConnection = new SqlConnection(SQLConnectionString);
    }

    private SqlConnection _sqlConnection = null;
    public static string SQLConnectionString;

    public async Task StartFlow()
    {
        _sqlConnection.ConnectionString = SQLConnectionString;
        _sqlConnection.Open();
        var list = await _settlementLib.PullDatas(_sqlConnection);
        await _settlementLib.HandleStockList(list);
        await _settlementLib.UpdateWinloss();
        Task.WaitAll();
        await _settlementLib.UpdateDatas(_sqlConnection);
        _sqlConnection.Close();
    }
}

上面的類別只有流程，實際的執行方式如下：

public class SettlementLib
{
    private ConcurrentDictionary<string, decimal> _memberWinloss;
    private ConcurrentQueue<Stock> _queue;
    private bool _continue = false;

    public SettlementLib() 
    {
        _memberWinloss = new ConcurrentDictionary<string, decimal>();
        _queue = new ConcurrentQueue<Stock>();
    }

    public async Task UpdateDatas(SqlConnection sqlConnection)
    {
        string tableName = "StockTemp";
        List<TempStockData> data = new List<TempStockData>();
        foreach (var item in _memberWinloss)
        {
            TempStockData tempStockData = new TempStockData
            { 
                Id = item.Key,
                Name = item.Key,
                Amount = 0,
                Winloss = item.Value
            };
            data.Add(tempStockData);
        }
        var table = DBTool.CreateDataTable(data);
        DBTool.SqlBulkCopy(sqlConnection, table, tableName);

        //exec db SP
        var result = await sqlConnection.QueryAsync("dbo.UpdateWinlossDatasToUsers", commandType: CommandType.StoredProcedure);
    }

    public async Task UpdateWinloss()
    {
        bool flag = true;
        while (flag)
        {
            if (_queue.TryDequeue(out Stock stock))
            {
                if(!_memberWinloss.ContainsKey(stock.Id))
                {
                    _memberWinloss.TryAdd(stock.Id, 0);
                }
                _memberWinloss[stock.Id] += stock.Winloss;
            }
            else 
            {
                flag = false; 
            }
        }
        await Task.Delay(100);
    }

    public async Task HandleStockList(List<Stock> stocks)
    {
        Parallel.ForEach(stocks, stock =>
        {
            if (stock.IsWin)
            {
                stock.Winloss = (decimal)stock.Odd * stock.Amount;
            }

            _queue.Enqueue(stock);
        });
        await Task.Delay(100);
        _continue = false;
    }

    public async Task<List<Stock>> PullDatas(SqlConnection sqlConnection)
    {
        var data = new List<Stock>();
        //exec db SP
        var result = await sqlConnection.QueryAsync<Stock>("dbo.PullUnsettleStocks", commandType: CommandType.StoredProcedure);
        foreach (Stock stock in result)
        {
            data.Add(stock);
        }
        return data;
    }

最後是更新用的SP：

USE [BulkCopyDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateWinlossDatasToUsers]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	UPDATE [BulkCopyDemo].[dbo].[Member]
    SET 
        Credit = Credit + Stock.Winloss
    FROM [BulkCopyDemo].[dbo].[Member]
    INNER JOIN [BulkCopyDemo].[dbo].[StockTemp] Stock
    ON Stock.Id = [BulkCopyDemo].[dbo].[Member].Id

	TRUNCATE TABLE [BulkCopyDemo].[dbo].[StockTemp]
END

流程就是：

  • 執行 PullDatas 抓取未結算資料
  • 執行 HandleStockList 結算金額並放進 queue
  • 執行 UpdateWinloss 把結算金額更新回會員的錢包
  • 執行 UpdateDatas 利用 SP 大量更新資料

完整程式碼請參考下列連結：

GitHub位置

參考資料