语言
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 包。
先决条件
在开始本演练之前,请先阅读 数据流 文档中的“联接块”部分。
安装 .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。
创建控制台应用
在 Visual Studio 中,创建 Visual C# 或 Visual Basic 控制台应用程序 项目。 在本文档中,该项目命名
DataflowBatchDatabase。面向 .NET 10,并安装 Microsoft.Data.SqlClient NuGet 包。 对于 Microsoft.Data.SqlClient 7 及更高版本,若要使用 Microsoft Entra ID 身份验证,还需安装 Microsoft.Data.SqlClient.Extensions.Azure。 数据流 API 包含在 .NET 10 中。
将 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将以下数据成员添加到
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类包含三个属性、EmployeeID和 LastNameFirstName。 这些属性对应于临时员工表中的 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对象,以及对调用失败产生的System.Exception对象。
ActionBlock<TInput>对象在此示例中作用于一个Tuple<T1,T2>对象,该对象包含Employee对象的列表和Exception对象的列表。 如果收到的 Employee 和 Exception 对象数总和等于批大小,BatchedJoinBlock<T1,T2> 对象就会传播出此类数据。
完整示例
以下示例显示了完整的代码。 该方法 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