語言

逐步解說:使用 BatchBlock 和 BatchedJoinBlock 以改善效率

TPL 數據流連結庫提供 System.Threading.Tasks.Dataflow.BatchBlock<T> 和 System.Threading.Tasks.Dataflow.BatchedJoinBlock<T1,T2> 類別,讓您可以從一或多個來源接收和緩衝數據,然後將該緩衝數據傳播為一個集合。 當您從一或多個來源收集數據,然後以批次方式處理多個數據元素時,此批處理機制很有用。 例如,請考慮使用數據流將記錄插入資料庫中的應用程式。 如果同時插入多個專案,而不是循序插入多個專案,這項作業可能會更有效率。 本文件說明如何使用 類別 BatchBlock<T> 來提升這類資料庫插入作業的效率。 它也描述如何使用 類別 BatchedJoinBlock<T1,T2> 來擷取結果,以及程式從資料庫讀取時所發生的任何例外狀況。

備註

TPL 資料流函式庫(命名 System.Threading.Tasks.Dataflow 空間)包含在 .NET 6 及更新版本中。 對於 .NET Framework 和 .NET Standard 專案,你需要安裝 📦 System.Threading.Tasks.Dataflow NuGet 套件。

先決條件

  1. 開始本逐步解說之前,請先閱讀 數據流 檔中的 [聯結區塊] 區段。

  2. 安裝 .NET 10 SDK。 使用開發資料庫,而非生產資料庫。

建立 SQL 資料庫

在以下平台建立或連接 SQL 資料庫:

將 SQL_CONNECTION_STRING 環境變數設為開發資料庫的 連接字串。 例如,使用 Server=<server>,<port>;Database=<database>;User ID=<user_id>;Password=<password>;Encrypt=true;TrustServerCertificate=false。 在你的本機環境中設定實際的機密值,而不是在原始碼中設定。

對於帶有自簽憑證的本地開發容器,僅針對該開發連線使用 TrustServerCertificate=true。 對於生產連線,請使用受信任的伺服器憑證,並保持憑證驗證啟用。

此範例在 tempdb 中建立一個唯一命名的全域暫存資料表。 開放連線會讓資料表對工作連線保持可用。 程式只會在示範間重置自己的表格,並在示範結束時將其丟棄。 你不需要 Northwind 或 SQL Server Compact。

建立主控台應用程式

  1. 在 Visual Studio 中,建立 Visual C# 或 Visual Basic 控制台應用程式 專案。 在本檔案中,項目名為 DataflowBatchDatabase。

  2. 目標是 .NET 10,並安裝 Microsoft.Data.SqlClient NuGet 套件。 若要使用 Microsoft Entra ID 驗證搭配 Microsoft.Data.SqlClient 7 及更新版本,另請安裝 Microsoft.Data.SqlClient.Extensions.Azure。 資料流 API 包含於 .NET 10。

  3. 請將 Program.cs 中產生的程式碼(Visual Basic 為 Program.vb)替換為完整範例,或依照本逐步指南中的步驟進行組裝。 從這些 using 語句開始(在 Visual Basic 中為 Imports)。

    using System;
    using System.Collections.Generic;
    using Microsoft.Data.SqlClient;
    using System.Diagnostics;
    using System.Threading.Tasks.Dataflow;
    
    Imports System.Data
    Imports Microsoft.Data.SqlClient
    Imports System.Collections.Generic
    Imports System.Threading.Tasks.Dataflow
    
    
  4. 將下列數據成員新增至 類別 Program 。

    // The number of employees to add to the database.
    // TODO: Change this value to experiment with different numbers of
    // employees to insert into the database.
    static readonly int s_insertCount = 256;
    
    // The size of a single batch of employees to add to the database.
    // TODO: Change this value to experiment with different batch sizes.
    static readonly int s_insertBatchSize = 96;
    
    static readonly string s_employeeTable =
       "##DataflowEmployees_" + Guid.NewGuid().ToString("N");
    
    ' The number of employees to add to the database.
    ' TODO: Change this value to experiment with different numbers of 
    ' employees to insert into the database.
    Private Shared ReadOnly s_insertCount As Integer = 256
    
    ' The size of a single batch of employees to add to the database.
    ' TODO: Change this value to experiment with different batch sizes.
    Private Shared ReadOnly s_insertBatchSize As Integer = 96
    
    Private Shared ReadOnly s_employeeTable As String =
        "##DataflowEmployees_" & Guid.NewGuid().ToString("N")
    

定義員工類別

將 Program 類別新增至 Employee 類別。

// Describes an employee. Each property maps to a
// column in the sample employee table.
class Employee
{
   public int EmployeeID { get; set; }
   public string LastName { get; set; }
   public string FirstName { get; set; }

   // A random number generator that helps tp generate
   // Employee property values.
   static readonly Random s_rand = new Random(42);

   // Possible random first names.
   static readonly string[] s_firstNames =
          { "Tom", "Mike", "Ruth", "Bob", "John" };
   // Possible random last names.
   static readonly string[] s_lastNames =
          { "Jones", "Smith", "Johnson", "Walker" };

   // Creates an Employee object that contains random
   // property values.
   public static Employee Random()
   {
      return new Employee
      {
         EmployeeID = -1,
         LastName = s_lastNames[s_rand.Next() % s_lastNames.Length],
         FirstName = s_firstNames[s_rand.Next() % s_firstNames.Length]
      };
   }
}
' Describes an employee. Each property maps to a 
' column in the sample employee table.
Private Class Employee
    Public Property EmployeeID() As Integer
    Public Property LastName() As String
    Public Property FirstName() As String

    ' A random number generator that helps tp generate
    ' Employee property values.
    Private Shared ReadOnly s_rand As New Random(42)

    ' Possible random first names.
    Private Shared ReadOnly s_firstNames() As String =
        {"Tom", "Mike", "Ruth", "Bob", "John"}
    ' Possible random last names.
    Private Shared ReadOnly s_lastNames() As String =
        {"Jones", "Smith", "Johnson", "Walker"}

    ' Creates an Employee object that contains random 
    ' property values.
    Public Shared Function Random() As Employee
        Return New Employee With {
            .EmployeeID = -1,
            .LastName = s_lastNames(s_rand.Next() Mod s_lastNames.Length),
            .FirstName = s_firstNames(s_rand.Next() Mod s_firstNames.Length)
        }
    End Function
End Class

類別Employee包含三個屬性、 EmployeeIDLastName和 FirstName。 這些屬性對應臨時員工資料表中的 Employee ID、Last Name 和 First Name 欄位。 在此示範中,類別 Employee 也會定義 Random 方法,這個方法會 Employee 建立物件,該物件具有其屬性的隨機值。

定義員工資料庫作業

將 Program、InsertEmployees 和 GetEmployeeCount 方法新增到 GetEmployeeID 類別中。

// Adds new employee records to the database.
static void InsertEmployees(Employee[] employees, string connectionString)
{
   using (SqlConnection connection =
      new SqlConnection(connectionString))
   {
      try
      {
         // Create the SQL command.
         SqlCommand command = new SqlCommand(
            $"INSERT INTO [{s_employeeTable}] ([Last Name], [First Name])" +
            "VALUES (@lastName, @firstName)",
            connection);

         connection.Open();
         for (int i = 0; i < employees.Length; i++)
         {
            // Set parameters.
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@lastName", employees[i].LastName);
            command.Parameters.AddWithValue("@firstName", employees[i].FirstName);

            // Execute the command.
            command.ExecuteNonQuery();
         }
      }
      finally
      {
         connection.Close();
      }
   }
}

// Retrieves the number of entries in the sample employee table.
static int GetEmployeeCount(string connectionString)
{
   int result = 0;
   using (SqlConnection sqlConnection =
      new SqlConnection(connectionString))
   {
      SqlCommand sqlCommand = new SqlCommand(
         $"SELECT COUNT(*) FROM [{s_employeeTable}]", sqlConnection);

      sqlConnection.Open();
      try
      {
         result = (int)sqlCommand.ExecuteScalar();
      }
      finally
      {
         sqlConnection.Close();
      }
   }
   return result;
}

// Retrieves the ID of the first employee that has the provided name.
static int GetEmployeeID(string lastName, string firstName,
   string connectionString)
{
   using (SqlConnection connection =
      new SqlConnection(connectionString))
   {
      SqlCommand command = new SqlCommand(
         $"SELECT TOP (1) [Employee ID] FROM [{s_employeeTable}] " +
         "WHERE [Last Name] = @lastName AND [First Name] = @firstName ORDER BY [Employee ID]",
         connection);

      command.Parameters.Add("@lastName", System.Data.SqlDbType.NVarChar).Value = lastName;
      command.Parameters.Add("@firstName", System.Data.SqlDbType.NVarChar).Value = firstName;

      connection.Open();
      try
      {
         object result = command.ExecuteScalar();
         if (result == null)
            throw new KeyNotFoundException("No matching employee.");
         return (int)result;
      }
      finally
      {
         connection.Close();
      }
   }
}
' Adds new employee records to the database.
Private Shared Sub InsertEmployees(ByVal employees() As Employee, ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Try
            ' Create the SQL command.
            Dim command As New SqlCommand($"INSERT INTO [{s_employeeTable}] ([Last Name], [First Name]) VALUES (@lastName, @firstName)", connection)

            connection.Open()
            For i As Integer = 0 To employees.Length - 1
                ' Set parameters.
                command.Parameters.Clear()
                command.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = employees(i).LastName
                command.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = employees(i).FirstName

                ' Execute the command.
                command.ExecuteNonQuery()
            Next i
        Finally
            connection.Close()
        End Try
    End Using
End Sub

' Retrieves the number of entries in the sample employee table.
Private Shared Function GetEmployeeCount(ByVal connectionString As String) As Integer
    Dim result As Integer = 0
    Using sqlConnection As New SqlConnection(connectionString)
        Dim sqlCommand As New SqlCommand($"SELECT COUNT(*) FROM [{s_employeeTable}]", sqlConnection)

        sqlConnection.Open()
        Try
            result = CInt(Fix(sqlCommand.ExecuteScalar()))
        Finally
            sqlConnection.Close()
        End Try
    End Using
    Return result
End Function

' Retrieves the ID of the first employee that has the provided name.
Private Shared Function GetEmployeeID(ByVal lastName As String, ByVal firstName As String, ByVal connectionString As String) As Integer
    Using connection As New SqlConnection(connectionString)
        Dim query As String = $"SELECT TOP (1) [Employee ID] FROM [{s_employeeTable}] WHERE [Last Name] = @lastName AND [First Name] = @firstName ORDER BY [Employee ID]"
        Dim command As New SqlCommand(query, connection)
        command.Parameters.Add("@lastName", SqlDbType.NVarChar, 50).Value = lastName
        command.Parameters.Add("@firstName", SqlDbType.NVarChar, 50).Value = firstName

        connection.Open()
        Try
            Dim result = command.ExecuteScalar()
            If result Is Nothing Then
                Throw New KeyNotFoundException("No matching employee.")
            End If
            Return CInt(result)
        Finally
            connection.Close()
        End Try
    End Using
End Function

此 InsertEmployees 方法會將新的員工紀錄加入臨時資料表。 該 GetEmployeeCount 方法會取得該表格中的條目數量。 該 GetEmployeeID 方法會取得第一個擁有該名稱的員工識別碼,若無員工匹配則拋出 KeyNotFoundException 。 每個方法都取用你的開發資料庫的 連接字串。

將員工數據新增至資料庫而不使用緩衝

將 Program 和 AddEmployees 方法新增至 PostRandomEmployees 類別。

// Posts random Employee data to the provided target block.
static void PostRandomEmployees(ITargetBlock<Employee> target, int count)
{
   Console.WriteLine($"Adding {count} entries to Employee table...");

   for (int i = 0; i < count; i++)
   {
      target.Post(Employee.Random());
   }
}

// Adds random employee data to the database by using dataflow.
static void AddEmployees(string connectionString, int count)
{
   // Create an ActionBlock<Employee> object that adds a single
   // employee entry to the database.
   var insertEmployee = new ActionBlock<Employee>(e =>
      InsertEmployees(new Employee[] { e }, connectionString));

   // Post several random Employee objects to the dataflow block.
   PostRandomEmployees(insertEmployee, count);

   // Set the dataflow block to the completed state and wait for
   // all insert operations to complete.
   insertEmployee.Complete();
   insertEmployee.Completion.Wait();
}
' Posts random Employee data to the provided target block.
Private Shared Sub PostRandomEmployees(ByVal target As ITargetBlock(Of Employee), ByVal count As Integer)
    Console.WriteLine("Adding {0} entries to Employee table...", count)

    For i As Integer = 0 To count - 1
        target.Post(Employee.Random())
    Next i
End Sub

' Adds random employee data to the database by using dataflow.
Private Shared Sub AddEmployees(ByVal connectionString As String, ByVal count As Integer)
    ' Create an ActionBlock<Employee> object that adds a single
    ' employee entry to the database.
    Dim insertEmployee = New ActionBlock(Of Employee)(Sub(e) InsertEmployees(New Employee() {e}, connectionString))

    ' Post several random Employee objects to the dataflow block.
    PostRandomEmployees(insertEmployee, count)

    ' Set the dataflow block to the completed state and wait for 
    ' all insert operations to complete.
    insertEmployee.Complete()
    insertEmployee.Completion.Wait()
End Sub

方法 AddEmployees 會使用數據流,將隨機員工數據新增至資料庫。 它會建立 ActionBlock<TInput> 物件,該物件會呼叫 InsertEmployees 方法,將員工條目新增至資料庫。 方法 AddEmployees 接著會呼叫 PostRandomEmployees 方法,將多個 Employee 物件張貼至 ActionBlock<TInput> 物件。 方法 AddEmployees 接著會等候所有插入作業完成。

使用緩衝將員工數據新增至資料庫

將 Program 方法新增至 AddEmployeesBatched 類別。

// Adds random employee data to the database by using dataflow.
// This method is similar to AddEmployees except that it uses batching
// to add multiple employees to the database at a time.
static void AddEmployeesBatched(string connectionString, int batchSize,
   int count)
{
   // Create a BatchBlock<Employee> that holds several Employee objects and
   // then propagates them out as an array.
   var batchEmployees = new BatchBlock<Employee>(batchSize);

   // Create an ActionBlock<Employee[]> object that adds multiple
   // employee entries to the database.
   var insertEmployees = new ActionBlock<Employee[]>(a =>
      InsertEmployees(a, connectionString));

   // Link the batch block to the action block.
   batchEmployees.LinkTo(insertEmployees);

   // When the batch block completes, set the action block also to complete.
   batchEmployees.Completion.ContinueWith(delegate { insertEmployees.Complete(); });

   // Post several random Employee objects to the batch block.
   PostRandomEmployees(batchEmployees, count);

   // Set the batch block to the completed state and wait for
   // all insert operations to complete.
   batchEmployees.Complete();
   insertEmployees.Completion.Wait();
}
' Adds random employee data to the database by using dataflow.
' This method is similar to AddEmployees except that it uses batching
' to add multiple employees to the database at a time.
Private Shared Sub AddEmployeesBatched(ByVal connectionString As String, ByVal batchSize As Integer, ByVal count As Integer)
    ' Create a BatchBlock<Employee> that holds several Employee objects and
    ' then propagates them out as an array.
    Dim batchEmployees = New BatchBlock(Of Employee)(batchSize)

    ' Create an ActionBlock<Employee[]> object that adds multiple
    ' employee entries to the database.
    Dim insertEmployees = New ActionBlock(Of Employee())(Sub(a) Program.InsertEmployees(a, connectionString))

    ' Link the batch block to the action block.
    batchEmployees.LinkTo(insertEmployees)

    ' When the batch block completes, set the action block also to complete.
    batchEmployees.Completion.ContinueWith(Sub() insertEmployees.Complete())

    ' Post several random Employee objects to the batch block.
    PostRandomEmployees(batchEmployees, count)

    ' Set the batch block to the completed state and wait for 
    ' all insert operations to complete.
    batchEmployees.Complete()
    insertEmployees.Completion.Wait()
End Sub

這個方法類似 AddEmployees,不同之處在於它也會使用 BatchBlock<T> 類別在將這些對象傳送至 Employee 物件之前,先緩衝處理多個ActionBlock<TInput>物件。 因為 BatchBlock<T> 類別會將多個元素傳播成一個集合,ActionBlock<TInput> 物件會被修改以便對 Employee 對象的陣列進行操作。 如同 方法 AddEmployees , AddEmployeesBatched 呼叫 PostRandomEmployees 方法以張貼多個 Employee 物件;不過, AddEmployeesBatched 將這些物件張貼至 BatchBlock<T> 物件。 方法 AddEmployeesBatched 也會等候所有插入作業完成。

使用緩衝聯結從資料庫讀取員工數據

將 Program 方法新增至 GetRandomEmployees 類別。

// Displays information about several random employees to the console.
static void GetRandomEmployees(string connectionString, int batchSize,
   int count)
{
   // Create a BatchedJoinBlock<Employee, Exception> object that holds
   // both employee and exception data.
   var selectEmployees = new BatchedJoinBlock<Employee, Exception>(batchSize);

   // Holds the total number of exceptions that occurred.
   int totalErrors = 0;

   // Create an action block that prints employee and error information
   // to the console.
   var printEmployees =
      new ActionBlock<Tuple<IList<Employee>, IList<Exception>>>(data =>
      {
         // Print information about the employees in this batch.
         Console.WriteLine("Received a batch...");
         foreach (Employee e in data.Item1)
         {
            Console.WriteLine($"Last={e.LastName} First={e.FirstName} ID={e.EmployeeID}");
         }

         // Print the error count for this batch.
         Console.WriteLine($"There were {data.Item2.Count} errors in this batch...");

         // Update total error count.
         totalErrors += data.Item2.Count;
      });

   // Link the batched join block to the action block.
   selectEmployees.LinkTo(printEmployees);

   // When the batched join block completes, set the action block also to complete.
   selectEmployees.Completion.ContinueWith(delegate { printEmployees.Complete(); });

   // Try to retrieve the ID for several random employees.
   Console.WriteLine("Selecting random entries from Employees table...");
   for (int i = 0; i < count; i++)
   {
      try
      {
         // Create a random employee.
         Employee e = Employee.Random();

         // Try to retrieve the ID for the employee from the database.
         e.EmployeeID = GetEmployeeID(e.LastName, e.FirstName, connectionString);

         // Post the Employee object to the Employee target of
         // the batched join block.
         selectEmployees.Target1.Post(e);
      }
      catch (KeyNotFoundException e)
      {
         // GetEmployeeID throws KeyNotFoundException when there is
         // no such employee with the given name. When this happens,
         // post the Exception object to the Exception target of
         // the batched join block.
         selectEmployees.Target2.Post(e);
      }
   }

   // Set the batched join block to the completed state and wait for
   // all retrieval operations to complete.
   selectEmployees.Complete();
   printEmployees.Completion.Wait();

   // Print the total error count.
   Console.WriteLine($"Finished. There were {totalErrors} total errors.");
}
' Displays information about several random employees to the console.
Private Shared Sub GetRandomEmployees(ByVal connectionString As String, ByVal batchSize As Integer, ByVal count As Integer)
    ' Create a BatchedJoinBlock<Employee, Exception> object that holds
    ' both employee and exception data.
    Dim selectEmployees = New BatchedJoinBlock(Of Employee, Exception)(batchSize)

    ' Holds the total number of exceptions that occurred.
    Dim totalErrors As Integer = 0

    ' Create an action block that prints employee and error information
    ' to the console.
    Dim printEmployees = New ActionBlock(Of Tuple(Of IList(Of Employee), IList(Of Exception)))(
        Sub(data)
            ' Print information about the employees in this batch.
            ' Print the error count for this batch.
            ' Update total error count.
            Console.WriteLine("Received a batch...")
            For Each e As Employee In data.Item1
                Console.WriteLine("Last={0} First={1} ID={2}", e.LastName, e.FirstName, e.EmployeeID)
            Next e
            Console.WriteLine("There were {0} errors in this batch...", data.Item2.Count)
            totalErrors += data.Item2.Count
        End Sub)

    ' Link the batched join block to the action block.
    selectEmployees.LinkTo(printEmployees)

    ' When the batched join block completes, set the action block also to complete.
    selectEmployees.Completion.ContinueWith(Sub() printEmployees.Complete())

    ' Try to retrieve the ID for several random employees.
    Console.WriteLine("Selecting random entries from Employees table...")
    For i As Integer = 0 To count - 1
        Try
            ' Create a random employee.
            Dim e As Employee = Employee.Random()

            ' Try to retrieve the ID for the employee from the database.
            e.EmployeeID = GetEmployeeID(e.LastName, e.FirstName, connectionString)

            ' Post the Employee object to the Employee target of 
            ' the batched join block.
            selectEmployees.Target1.Post(e)
        Catch e As KeyNotFoundException
            ' GetEmployeeID throws KeyNotFoundException when there is
            ' no such employee with the given name. When this happens,
            ' post the Exception object to the Exception target of
            ' the batched join block.
            selectEmployees.Target2.Post(e)
        End Try
    Next i

    ' Set the batched join block to the completed state and wait for 
    ' all retrieval operations to complete.
    selectEmployees.Complete()
    printEmployees.Completion.Wait()

    ' Print the total error count.
    Console.WriteLine("Finished. There were {0} total errors.", totalErrors)
End Sub

此方法會將隨機員工的相關信息列印到主控台。 它會建立數個隨機 Employee 物件,並呼叫 GetEmployeeID 方法來擷取每個物件的唯一標識符。 GetEmployeeID 方法會在沒有符合指定名字和姓氏的相符員工時擲回例外狀況,因此 GetRandomEmployees 方法會利用 BatchedJoinBlock<T1,T2> 類別來儲存成功呼叫的 Employee 物件,以及儲存呼叫失敗的 GetEmployeeID 物件。 ActionBlock<TInput>物件在此範例中作用於Tuple<T1,T2>物件,該物件包含Employee物件清單和Exception物件清單。 當收到的BatchedJoinBlock<T1,T2>和Employee物件計數的總和等於批次大小時,Exception對象會傳播出此數據。

完整範例

下列範例顯示完整的程序代碼。 方法 Main 會比較執行批次資料庫插入所需的時間,以及執行非批次資料庫插入的時間。 它也示範如何使用緩衝聯結從資料庫讀取員工數據,並報告錯誤。

兩個插入示範都是從零列開始,結束時有256列。 最後的示範會插入 16 列並執行 10 次查找。 每批次會重複使用一個連線來執行插入作業;它仍然會對每位員工執行一個指令。 經過的時間取決於你的伺服器和網路,並非一般效能保證。

using System;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks.Dataflow;

// Demonstrates how to use batched dataflow blocks to improve
// the performance of database operations.
namespace DataflowBatchDatabase
{
   class Program
   {
      // The number of employees to add to the database.
      // TODO: Change this value to experiment with different numbers of
      // employees to insert into the database.
      static readonly int s_insertCount = 256;

      // The size of a single batch of employees to add to the database.
      // TODO: Change this value to experiment with different batch sizes.
      static readonly int s_insertBatchSize = 96;

      static readonly string s_employeeTable =
         "##DataflowEmployees_" + Guid.NewGuid().ToString("N");

      // Describes an employee. Each property maps to a
      // column in the sample employee table.
      class Employee
      {
         public int EmployeeID { get; set; }
         public string LastName { get; set; }
         public string FirstName { get; set; }

         // A random number generator that helps tp generate
         // Employee property values.
         static readonly Random s_rand = new Random(42);

         // Possible random first names.
         static readonly string[] s_firstNames =
                { "Tom", "Mike", "Ruth", "Bob", "John" };
         // Possible random last names.
         static readonly string[] s_lastNames =
                { "Jones", "Smith", "Johnson", "Walker" };

         // Creates an Employee object that contains random
         // property values.
         public static Employee Random()
         {
            return new Employee
            {
               EmployeeID = -1,
               LastName = s_lastNames[s_rand.Next() % s_lastNames.Length],
               FirstName = s_firstNames[s_rand.Next() % s_firstNames.Length]
            };
         }
      }

      // Adds new employee records to the database.
      static void InsertEmployees(Employee[] employees, string connectionString)
      {
         using (SqlConnection connection =
            new SqlConnection(connectionString))
         {
            try
            {
               // Create the SQL command.
               SqlCommand command = new SqlCommand(
                  $"INSERT INTO [{s_employeeTable}] ([Last Name], [First Name])" +
                  "VALUES (@lastName, @firstName)",
                  connection);

               connection.Open();
               for (int i = 0; i < employees.Length; i++)
               {
                  // Set parameters.
                  command.Parameters.Clear();
                  command.Parameters.AddWithValue("@lastName", employees[i].LastName);
                  command.Parameters.AddWithValue("@firstName", employees[i].FirstName);

                  // Execute the command.
                  command.ExecuteNonQuery();
               }
            }
            finally
            {
               connection.Close();
            }
         }
      }

      // Retrieves the number of entries in the sample employee table.
      static int GetEmployeeCount(string connectionString)
      {
         int result = 0;
         using (SqlConnection sqlConnection =
            new SqlConnection(connectionString))
         {
            SqlCommand sqlCommand = new SqlCommand(
               $"SELECT COUNT(*) FROM [{s_employeeTable}]", sqlConnection);

            sqlConnection.Open();
            try
            {
               result = (int)sqlCommand.ExecuteScalar();
            }
            finally
            {
               sqlConnection.Close();
            }
         }
         return result;
      }

      // Retrieves the ID of the first employee that has the provided name.
      static int GetEmployeeID(string lastName, string firstName,
         string connectionString)
      {
         using (SqlConnection connection =
            new SqlConnection(connectionString))
         {
            SqlCommand command = new SqlCommand(
               $"SELECT TOP (1) [Employee ID] FROM [{s_employeeTable}] " +
               "WHERE [Last Name] = @lastName AND [First Name] = @firstName ORDER BY [Employee ID]",
               connection);

            command.Parameters.Add("@lastName", System.Data.SqlDbType.NVarChar).Value = lastName;
            command.Parameters.Add("@firstName", System.Data.SqlDbType.NVarChar).Value = firstName;

            connection.Open();
            try
            {
               object result = command.ExecuteScalar();
               if (result == null)
                  throw new KeyNotFoundException("No matching employee.");
               return (int)result;
            }
            finally
            {
               connection.Close();
            }
         }
      }

      // Posts random Employee data to the provided target block.
      static void PostRandomEmployees(ITargetBlock<Employee> target, int count)
      {
         Console.WriteLine($"Adding {count} entries to Employee table...");

         for (int i = 0; i < count; i++)
         {
            target.Post(Employee.Random());
         }
      }

      // Adds random employee data to the database by using dataflow.
      static void AddEmployees(string connectionString, int count)
      {
         // Create an ActionBlock<Employee> object that adds a single
         // employee entry to the database.
         var insertEmployee = new ActionBlock<Employee>(e =>
            InsertEmployees(new Employee[] { e }, connectionString));

         // Post several random Employee objects to the dataflow block.
         PostRandomEmployees(insertEmployee, count);

         // Set the dataflow block to the completed state and wait for
         // all insert operations to complete.
         insertEmployee.Complete();
         insertEmployee.Completion.Wait();
      }

      // Adds random employee data to the database by using dataflow.
      // This method is similar to AddEmployees except that it uses batching
      // to add multiple employees to the database at a time.
      static void AddEmployeesBatched(string connectionString, int batchSize,
         int count)
      {
         // Create a BatchBlock<Employee> that holds several Employee objects and
         // then propagates them out as an array.
         var batchEmployees = new BatchBlock<Employee>(batchSize);

         // Create an ActionBlock<Employee[]> object that adds multiple
         // employee entries to the database.
         var insertEmployees = new ActionBlock<Employee[]>(a =>
            InsertEmployees(a, connectionString));

         // Link the batch block to the action block.
         batchEmployees.LinkTo(insertEmployees);

         // When the batch block completes, set the action block also to complete.
         batchEmployees.Completion.ContinueWith(delegate { insertEmployees.Complete(); });

         // Post several random Employee objects to the batch block.
         PostRandomEmployees(batchEmployees, count);

         // Set the batch block to the completed state and wait for
         // all insert operations to complete.
         batchEmployees.Complete();
         insertEmployees.Completion.Wait();
      }

      // Displays information about several random employees to the console.
      static void GetRandomEmployees(string connectionString, int batchSize,
         int count)
      {
         // Create a BatchedJoinBlock<Employee, Exception> object that holds
         // both employee and exception data.
         var selectEmployees = new BatchedJoinBlock<Employee, Exception>(batchSize);

         // Holds the total number of exceptions that occurred.
         int totalErrors = 0;

         // Create an action block that prints employee and error information
         // to the console.
         var printEmployees =
            new ActionBlock<Tuple<IList<Employee>, IList<Exception>>>(data =>
            {
               // Print information about the employees in this batch.
               Console.WriteLine("Received a batch...");
               foreach (Employee e in data.Item1)
               {
                  Console.WriteLine($"Last={e.LastName} First={e.FirstName} ID={e.EmployeeID}");
               }

               // Print the error count for this batch.
               Console.WriteLine($"There were {data.Item2.Count} errors in this batch...");

               // Update total error count.
               totalErrors += data.Item2.Count;
            });

         // Link the batched join block to the action block.
         selectEmployees.LinkTo(printEmployees);

         // When the batched join block completes, set the action block also to complete.
         selectEmployees.Completion.ContinueWith(delegate { printEmployees.Complete(); });

         // Try to retrieve the ID for several random employees.
         Console.WriteLine("Selecting random entries from Employees table...");
         for (int i = 0; i < count; i++)
         {
            try
            {
               // Create a random employee.
               Employee e = Employee.Random();

               // Try to retrieve the ID for the employee from the database.
               e.EmployeeID = GetEmployeeID(e.LastName, e.FirstName, connectionString);

               // Post the Employee object to the Employee target of
               // the batched join block.
               selectEmployees.Target1.Post(e);
            }
            catch (KeyNotFoundException e)
            {
               // GetEmployeeID throws KeyNotFoundException when there is
               // no such employee with the given name. When this happens,
               // post the Exception object to the Exception target of
               // the batched join block.
               selectEmployees.Target2.Post(e);
            }
         }

         // Set the batched join block to the completed state and wait for
         // all retrieval operations to complete.
         selectEmployees.Complete();
         printEmployees.Completion.Wait();

         // Print the total error count.
         Console.WriteLine($"Finished. There were {totalErrors} total errors.");
      }

      static void Main()
      {
         string connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING");
         if (string.IsNullOrWhiteSpace(connectionString))
            throw new InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.");

         // Keep the global temporary table alive while worker connections use it.
         using var keeper = new SqlConnection(connectionString);
         keeper.Open();
         using var setup = new SqlCommand(
            $"CREATE TABLE [{s_employeeTable}] " +
            "([Employee ID] int IDENTITY PRIMARY KEY, " +
            "[Last Name] nvarchar(50) NOT NULL, [First Name] nvarchar(50) NOT NULL)", keeper);
         setup.ExecuteNonQuery();

         try
         {
            Stopwatch stopwatch = new Stopwatch();
            Console.WriteLine("Demonstrating non-batched database insert operations...");
            Console.WriteLine($"Original size of Employee table: {GetEmployeeCount(connectionString)}.");
            stopwatch.Start();
            AddEmployees(connectionString, s_insertCount);
            stopwatch.Stop();
            Console.WriteLine("New size of Employee table: {0}; elapsed insert time: {1} ms.",
               GetEmployeeCount(connectionString), stopwatch.ElapsedMilliseconds);
            Console.WriteLine();

            using var reset = new SqlCommand($"TRUNCATE TABLE [{s_employeeTable}]", keeper);
            reset.ExecuteNonQuery();

            Console.WriteLine("Demonstrating batched database insert operations...");
            Console.WriteLine($"Original size of Employee table: {GetEmployeeCount(connectionString)}.");
            stopwatch.Restart();
            AddEmployeesBatched(connectionString, s_insertBatchSize, s_insertCount);
            stopwatch.Stop();
            Console.WriteLine("New size of Employee table: {0}; elapsed insert time: {1} ms.",
               GetEmployeeCount(connectionString), stopwatch.ElapsedMilliseconds);
            Console.WriteLine();

            reset.ExecuteNonQuery();

            Console.WriteLine("Demonstrating batched join database select operations...");
            AddEmployeesBatched(connectionString, s_insertBatchSize, 16);
            GetRandomEmployees(connectionString, s_insertBatchSize, 10);
         }
         finally
         {
            using var cleanup = new SqlCommand($"DROP TABLE [{s_employeeTable}]", keeper);
            cleanup.ExecuteNonQuery();
         }
      }
   }
}

Imports System.Data
Imports Microsoft.Data.SqlClient
Imports System.Collections.Generic
Imports System.Threading.Tasks.Dataflow


' Demonstrates how to use batched dataflow blocks to improve
' the performance of database operations.
Namespace DataflowBatchDatabase
    Friend Class Program
        ' The number of employees to add to the database.
        ' TODO: Change this value to experiment with different numbers of 
        ' employees to insert into the database.
        Private Shared ReadOnly s_insertCount As Integer = 256

        ' The size of a single batch of employees to add to the database.
        ' TODO: Change this value to experiment with different batch sizes.
        Private Shared ReadOnly s_insertBatchSize As Integer = 96

        Private Shared ReadOnly s_employeeTable As String =
            "##DataflowEmployees_" & Guid.NewGuid().ToString("N")

        ' Describes an employee. Each property maps to a 
        ' column in the sample employee table.
        Private Class Employee
            Public Property EmployeeID() As Integer
            Public Property LastName() As String
            Public Property FirstName() As String

            ' A random number generator that helps tp generate
            ' Employee property values.
            Private Shared ReadOnly s_rand As New Random(42)

            ' Possible random first names.
            Private Shared ReadOnly s_firstNames() As String =
                {"Tom", "Mike", "Ruth", "Bob", "John"}
            ' Possible random last names.
            Private Shared ReadOnly s_lastNames() As String =
                {"Jones", "Smith", "Johnson", "Walker"}

            ' Creates an Employee object that contains random 
            ' property values.
            Public Shared Function Random() As Employee
                Return New Employee With {
                    .EmployeeID = -1,
                    .LastName = s_lastNames(s_rand.Next() Mod s_lastNames.Length),
                    .FirstName = s_firstNames(s_rand.Next() Mod s_firstNames.Length)
                }
            End Function
        End Class

        ' Adds new employee records to the database.
        Private Shared Sub InsertEmployees(ByVal employees() As Employee, ByVal connectionString As String)
            Using connection As New SqlConnection(connectionString)
                Try
                    ' Create the SQL command.
                    Dim command As New SqlCommand($"INSERT INTO [{s_employeeTable}] ([Last Name], [First Name]) VALUES (@lastName, @firstName)", connection)

                    connection.Open()
                    For i As Integer = 0 To employees.Length - 1
                        ' Set parameters.
                        command.Parameters.Clear()
                        command.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = employees(i).LastName
                        command.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = employees(i).FirstName

                        ' Execute the command.
                        command.ExecuteNonQuery()
                    Next i
                Finally
                    connection.Close()
                End Try
            End Using
        End Sub

        ' Retrieves the number of entries in the sample employee table.
        Private Shared Function GetEmployeeCount(ByVal connectionString As String) As Integer
            Dim result As Integer = 0
            Using sqlConnection As New SqlConnection(connectionString)
                Dim sqlCommand As New SqlCommand($"SELECT COUNT(*) FROM [{s_employeeTable}]", sqlConnection)

                sqlConnection.Open()
                Try
                    result = CInt(Fix(sqlCommand.ExecuteScalar()))
                Finally
                    sqlConnection.Close()
                End Try
            End Using
            Return result
        End Function

        ' Retrieves the ID of the first employee that has the provided name.
        Private Shared Function GetEmployeeID(ByVal lastName As String, ByVal firstName As String, ByVal connectionString As String) As Integer
            Using connection As New SqlConnection(connectionString)
                Dim query As String = $"SELECT TOP (1) [Employee ID] FROM [{s_employeeTable}] WHERE [Last Name] = @lastName AND [First Name] = @firstName ORDER BY [Employee ID]"
                Dim command As New SqlCommand(query, connection)
                command.Parameters.Add("@lastName", SqlDbType.NVarChar, 50).Value = lastName
                command.Parameters.Add("@firstName", SqlDbType.NVarChar, 50).Value = firstName

                connection.Open()
                Try
                    Dim result = command.ExecuteScalar()
                    If result Is Nothing Then
                        Throw New KeyNotFoundException("No matching employee.")
                    End If
                    Return CInt(result)
                Finally
                    connection.Close()
                End Try
            End Using
        End Function

        ' Posts random Employee data to the provided target block.
        Private Shared Sub PostRandomEmployees(ByVal target As ITargetBlock(Of Employee), ByVal count As Integer)
            Console.WriteLine("Adding {0} entries to Employee table...", count)

            For i As Integer = 0 To count - 1
                target.Post(Employee.Random())
            Next i
        End Sub

        ' Adds random employee data to the database by using dataflow.
        Private Shared Sub AddEmployees(ByVal connectionString As String, ByVal count As Integer)
            ' Create an ActionBlock<Employee> object that adds a single
            ' employee entry to the database.
            Dim insertEmployee = New ActionBlock(Of Employee)(Sub(e) InsertEmployees(New Employee() {e}, connectionString))

            ' Post several random Employee objects to the dataflow block.
            PostRandomEmployees(insertEmployee, count)

            ' Set the dataflow block to the completed state and wait for 
            ' all insert operations to complete.
            insertEmployee.Complete()
            insertEmployee.Completion.Wait()
        End Sub

        ' Adds random employee data to the database by using dataflow.
        ' This method is similar to AddEmployees except that it uses batching
        ' to add multiple employees to the database at a time.
        Private Shared Sub AddEmployeesBatched(ByVal connectionString As String, ByVal batchSize As Integer, ByVal count As Integer)
            ' Create a BatchBlock<Employee> that holds several Employee objects and
            ' then propagates them out as an array.
            Dim batchEmployees = New BatchBlock(Of Employee)(batchSize)

            ' Create an ActionBlock<Employee[]> object that adds multiple
            ' employee entries to the database.
            Dim insertEmployees = New ActionBlock(Of Employee())(Sub(a) Program.InsertEmployees(a, connectionString))

            ' Link the batch block to the action block.
            batchEmployees.LinkTo(insertEmployees)

            ' When the batch block completes, set the action block also to complete.
            batchEmployees.Completion.ContinueWith(Sub() insertEmployees.Complete())

            ' Post several random Employee objects to the batch block.
            PostRandomEmployees(batchEmployees, count)

            ' Set the batch block to the completed state and wait for 
            ' all insert operations to complete.
            batchEmployees.Complete()
            insertEmployees.Completion.Wait()
        End Sub

        ' Displays information about several random employees to the console.
        Private Shared Sub GetRandomEmployees(ByVal connectionString As String, ByVal batchSize As Integer, ByVal count As Integer)
            ' Create a BatchedJoinBlock<Employee, Exception> object that holds
            ' both employee and exception data.
            Dim selectEmployees = New BatchedJoinBlock(Of Employee, Exception)(batchSize)

            ' Holds the total number of exceptions that occurred.
            Dim totalErrors As Integer = 0

            ' Create an action block that prints employee and error information
            ' to the console.
            Dim printEmployees = New ActionBlock(Of Tuple(Of IList(Of Employee), IList(Of Exception)))(
                Sub(data)
                    ' Print information about the employees in this batch.
                    ' Print the error count for this batch.
                    ' Update total error count.
                    Console.WriteLine("Received a batch...")
                    For Each e As Employee In data.Item1
                        Console.WriteLine("Last={0} First={1} ID={2}", e.LastName, e.FirstName, e.EmployeeID)
                    Next e
                    Console.WriteLine("There were {0} errors in this batch...", data.Item2.Count)
                    totalErrors += data.Item2.Count
                End Sub)

            ' Link the batched join block to the action block.
            selectEmployees.LinkTo(printEmployees)

            ' When the batched join block completes, set the action block also to complete.
            selectEmployees.Completion.ContinueWith(Sub() printEmployees.Complete())

            ' Try to retrieve the ID for several random employees.
            Console.WriteLine("Selecting random entries from Employees table...")
            For i As Integer = 0 To count - 1
                Try
                    ' Create a random employee.
                    Dim e As Employee = Employee.Random()

                    ' Try to retrieve the ID for the employee from the database.
                    e.EmployeeID = GetEmployeeID(e.LastName, e.FirstName, connectionString)

                    ' Post the Employee object to the Employee target of 
                    ' the batched join block.
                    selectEmployees.Target1.Post(e)
                Catch e As KeyNotFoundException
                    ' GetEmployeeID throws KeyNotFoundException when there is
                    ' no such employee with the given name. When this happens,
                    ' post the Exception object to the Exception target of
                    ' the batched join block.
                    selectEmployees.Target2.Post(e)
                End Try
            Next i

            ' Set the batched join block to the completed state and wait for 
            ' all retrieval operations to complete.
            selectEmployees.Complete()
            printEmployees.Completion.Wait()

            ' Print the total error count.
            Console.WriteLine("Finished. There were {0} total errors.", totalErrors)
        End Sub

        Shared Sub Main()
            Dim connectionString = Environment.GetEnvironmentVariable("SQL_CONNECTION_STRING")
            If String.IsNullOrWhiteSpace(connectionString) Then
                Throw New InvalidOperationException("Set the SQL_CONNECTION_STRING environment variable.")
            End If

            ' Keep the global temporary table alive while worker connections use it.
            Using keeper As New SqlConnection(connectionString)
                keeper.Open()
                Using setup As New SqlCommand(
                    $"CREATE TABLE [{s_employeeTable}] " &
                    "([Employee ID] int IDENTITY PRIMARY KEY, " &
                    "[Last Name] nvarchar(50) NOT NULL, [First Name] nvarchar(50) NOT NULL)", keeper)
                    setup.ExecuteNonQuery()
                End Using

                Try
                    Dim stopwatch As New Stopwatch()
                    Console.WriteLine("Demonstrating non-batched database insert operations...")
                    Console.WriteLine("Original size of Employee table: {0}.", GetEmployeeCount(connectionString))
                    stopwatch.Start()
                    AddEmployees(connectionString, s_insertCount)
                    stopwatch.Stop()
                    Console.WriteLine("New size of Employee table: {0}; elapsed insert time: {1} ms.", GetEmployeeCount(connectionString), stopwatch.ElapsedMilliseconds)
                    Console.WriteLine()

                    Using reset As New SqlCommand($"TRUNCATE TABLE [{s_employeeTable}]", keeper)
                        reset.ExecuteNonQuery()
                    End Using

                    Console.WriteLine("Demonstrating batched database insert operations...")
                    Console.WriteLine("Original size of Employee table: {0}.", GetEmployeeCount(connectionString))
                    stopwatch.Restart()
                    AddEmployeesBatched(connectionString, s_insertBatchSize, s_insertCount)
                    stopwatch.Stop()
                    Console.WriteLine("New size of Employee table: {0}; elapsed insert time: {1} ms.", GetEmployeeCount(connectionString), stopwatch.ElapsedMilliseconds)
                    Console.WriteLine()

                    Using reset As New SqlCommand($"TRUNCATE TABLE [{s_employeeTable}]", keeper)
                        reset.ExecuteNonQuery()
                    End Using

                    Console.WriteLine("Demonstrating batched join database select operations...")
                    AddEmployeesBatched(connectionString, s_insertBatchSize, 16)
                    GetRandomEmployees(connectionString, s_insertBatchSize, 10)
                Finally
                    Using cleanup As New SqlCommand($"DROP TABLE [{s_employeeTable}]", keeper)
                        cleanup.ExecuteNonQuery()
                    End Using
                End Try
            End Using
        End Sub
    End Class
End Namespace

另請參閱