SqlClient ストリーミング サポート
適用対象： 
.NET Framework .NET .NET Standard
SQL Server とアプリケーション間のストリーミング サポートでは、サーバー上の非構造化データ (ドキュメント、画像、およびメディア ファイル) をサポートします。 SQL Server データベースはバイナリ ラージ オブジェクト (BLOB) を格納できますが、BLOB の取得には大量のメモリが使用される可能性があります。
SQL Server との間のストリーミングのサポートにより、データをストリーミングするアプリケーションの作成が簡単になり、データをメモリに完全に読み込む必要がないため、メモリのオーバーフロー例外が減少します。
また、ストリーミング サポートにより、特に大規模な BLOB を送信、取得、操作するためにビジネス オブジェクトが Azure SQL に接続されるシナリオでは、中間層アプリケーションのスケールが向上します。
警告
ストリーミングをサポートするメンバーは、クエリからデータを取得し、クエリやストアド プロシージャにパラメーターを渡すために使用されます。 ストリーミング機能は、基本的な OLTP およびデータ移行のシナリオに対処し、オンプレミスおよびオフプレミスのデータ移行環境に適用できます。
SQL Server からのストリーミング サポート
SQL Server からのストリーミングのサポートでは、Stream、XmlReader、TextReader の各オブジェクトを取得して対応するために、DbDataReader クラスと SqlDataReader クラスに新機能が導入されています。 これらのクラスはクエリからデータを取得するために使用されます。 その結果、SQL Server からのストリーミングのサポートは OLTP シナリオに対応しており、オンプレミスおよびオフプレミス環境に適用されます。
SQL Server からのストリーミングのサポートを有効にするために、次のメンバーが SqlDataReader に追加されました。
SQL Server からのストリーミングのサポートを有効にするために、次のメンバーが DbDataReader に追加されました。
SQL Server へのストリーミング サポート
SqlParameter クラスには SQL Server へのストリーミング サポートがあるため、XmlReader、Stream、および TextReader の各オブジェクトを受け取って対応できます。 SqlParameter はクエリおよびストアド プロシージャにパラメーターを渡すために使用されます。
Note
SqlCommand オブジェクトの破棄または Cancel の呼び出しでは、ストリーミング操作を取り消す必要があります。 アプリケーションが CancellationToken を送信すると、取り消しは保証されません。
次の SqlDbType 型は、Value の Stream を受け取ります。
Binary
VarBinary
次の SqlDbType 型は、Value の TextReader を受け取ります。
Char
NChar
NVarChar
Xml
XmlSqlDbType 型は、XmlReader の Value を受け取ります。
SqlValue は、XmlReader、TextReader、および Stream 型の値を受け取ることができます。
XmlReader、TextReader、および Stream の各オブジェクトは、Size によって定義された値まで転送されます。
サンプル -- SQL Server からのストリーミング
次の Transact-SQL を使用して、サンプル データベースを作成します。
CREATE DATABASE [Demo]
GO
USE [Demo]
GO
CREATE TABLE [Streams] (
[id] INT PRIMARY KEY IDENTITY(1, 1),
[textdata] NVARCHAR(MAX),
[bindata] VARBINARY(MAX),
[xmldata] XML)
GO
INSERT INTO [Streams] (textdata, bindata, xmldata) VALUES (N'This is a test', 0x48656C6C6F, N'<test>value</test>')
INSERT INTO [Streams] (textdata, bindata, xmldata) VALUES (N'Hello, World!', 0x54657374696E67, N'<test>value2</test>')
INSERT INTO [Streams] (textdata, bindata, xmldata) VALUES (N'Another row', 0x666F6F626172, N'<fff>bbb</fff><fff>bbc</fff>')
GO
このサンプルでは、次の処理の実行方法を示します。
大きなファイルを非同期に取得できるようにして、ユーザー インターフェイス スレッドのブロックを回避する。
.NET で SQL Server から大きなテキスト ファイルを転送する。
.NET で SQL Server から大きな XML ファイルを転送する。
SQL Server からデータを取得する。
メモリ不足になることなく、ある SQL Server データベースから別のデータベースに大きなファイル (BLOB) を転送する。
using System;
using System.Data;
using Microsoft.Data.SqlClient;
using System.IO;
using System.Threading.Tasks;
using System.Xml;
namespace StreamingFromServer
{
class Program
{
private const string connectionString = @"Server=localhost;Database=Demo;Integrated Security=true";
static void Main(string[] args)
{
CopyBinaryValueToFile().Wait();
PrintTextValues().Wait();
PrintXmlValues().Wait();
PrintXmlValuesViaNVarChar().Wait();
Console.WriteLine("Done");
}
// Application retrieving a large BLOB from SQL Server in .NET 4.5 using the new asynchronous capability
private static async Task CopyBinaryValueToFile()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (SqlCommand command = new SqlCommand("SELECT [bindata] FROM [Streams] WHERE [id]=@id", connection))
{
command.Parameters.AddWithValue("id", 1);
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
if (await reader.ReadAsync())
{
if (!(await reader.IsDBNullAsync(0)))
{
using (FileStream file = new FileStream("binarydata.bin", FileMode.Create, FileAccess.Write))
{
using (Stream data = reader.GetStream(0))
{
// Asynchronously copy the stream from the server to the file we just created
await data.CopyToAsync(file);
}
}
}
}
}
}
}
}
// Application transferring a large Text File from SQL Server
private static async Task PrintTextValues()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (SqlCommand command = new SqlCommand("SELECT [id], [textdata] FROM [Streams]", connection))
{
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire text document into memory which can cause scalability issues or even OutOfMemoryExceptions
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
while (await reader.ReadAsync())
{
Console.Write("{0}: ", reader.GetInt32(0));
if (await reader.IsDBNullAsync(1))
{
Console.Write("(NULL)");
}
else
{
char[] buffer = new char[4096];
int charsRead = 0;
using (TextReader data = reader.GetTextReader(1))
{
do
{
// Grab each chunk of text and write it to the console
// If you are writing to a TextWriter you should use WriteAsync or WriteLineAsync
charsRead = await data.ReadAsync(buffer, 0, buffer.Length);
Console.Write(buffer, 0, charsRead);
} while (charsRead > 0);
}
}
Console.WriteLine();
}
}
}
}
}
// Application transferring a large Xml Document from SQL Server
private static async Task PrintXmlValues()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
using (SqlCommand command = new SqlCommand("SELECT [id], [xmldata] FROM [Streams]", connection))
{
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire Xml Document into memory which can cause scalability issues or even OutOfMemoryExceptions
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
while (await reader.ReadAsync())
{
Console.WriteLine("{0}: ", reader.GetInt32(0));
if (await reader.IsDBNullAsync(1))
{
Console.WriteLine("\t(NULL)");
}
else
{
using (XmlReader xmlReader = reader.GetXmlReader(1))
{
int depth = 1;
// NOTE: The XmlReader returned by GetXmlReader does NOT support async operations
// See the example below (PrintXmlValuesViaNVarChar) for how to get an XmlReader with asynchronous capabilities
while (xmlReader.Read())
{
switch (xmlReader.NodeType)
{
case XmlNodeType.Element:
Console.WriteLine("{0}<{1}>", new string('\t', depth), xmlReader.Name);
depth++;
break;
case XmlNodeType.Text:
Console.WriteLine("{0}{1}", new string('\t', depth), xmlReader.Value);
break;
case XmlNodeType.EndElement:
depth--;
Console.WriteLine("{0}</{1}>", new string('\t', depth), xmlReader.Name);
break;
}
}
}
}
}
}
}
}
}
// Application transferring a large Xml Document from SQL Server
// This goes via NVarChar and TextReader to enable asynchronous reading
private static async Task PrintXmlValuesViaNVarChar()
{
XmlReaderSettings xmlSettings = new XmlReaderSettings()
{
// Async must be explicitly enabled in the XmlReaderSettings otherwise the XmlReader will throw exceptions when async methods are called
Async = true,
// Since we will immediately wrap the TextReader we are creating in an XmlReader, we will permit the XmlReader to take care of closing\disposing it
CloseInput = true,
// If the Xml you are reading is not a valid document (as per <https://docs.microsoft.com/previous-versions/dotnet/netframework-4.0/6bts1x50(v=vs.100)>) you will need to set the conformance level to Fragment
ConformanceLevel = ConformanceLevel.Fragment
};
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
// Cast the XML into NVarChar to enable GetTextReader - trying to use GetTextReader on an XML type will throw an exception
using (SqlCommand command = new SqlCommand("SELECT [id], CAST([xmldata] AS NVARCHAR(MAX)) FROM [Streams]", connection))
{
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire Xml Document into memory which can cause scalability issues or even OutOfMemoryExceptions
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
while (await reader.ReadAsync())
{
Console.WriteLine("{0}:", reader.GetInt32(0));
if (await reader.IsDBNullAsync(1))
{
Console.WriteLine("\t(NULL)");
}
else
{
// Grab the row as a TextReader, then create an XmlReader on top of it
// We are not keeping a reference to the TextReader since the XmlReader is created with the "CloseInput" setting (so it will close the TextReader when needed)
using (XmlReader xmlReader = XmlReader.Create(reader.GetTextReader(1), xmlSettings))
{
int depth = 1;
// The XmlReader above now supports asynchronous operations, so we can use ReadAsync here
while (await xmlReader.ReadAsync())
{
switch (xmlReader.NodeType)
{
case XmlNodeType.Element:
Console.WriteLine("{0}<{1}>", new string('\t', depth), xmlReader.Name);
depth++;
break;
case XmlNodeType.Text:
// Depending on what your data looks like, you should either use Value or GetValueAsync
// Value has less overhead (since it doesn't create a Task), but it may also block if additional data is required
Console.WriteLine("{0}{1}", new string('\t', depth), await xmlReader.GetValueAsync());
break;
case XmlNodeType.EndElement:
depth--;
Console.WriteLine("{0}</{1}>", new string('\t', depth), xmlReader.Name);
break;
}
}
}
}
}
}
}
}
}
}
}
サンプル -- SQL Server へのストリーミング
次の Transact-SQL を使用して、サンプル データベースを作成します。
CREATE DATABASE [Demo2]
GO
USE [Demo2]
GO
CREATE TABLE [BinaryStreams] (
[id] INT PRIMARY KEY IDENTITY(1, 1),
[bindata] VARBINARY(MAX))
GO
CREATE TABLE [TextStreams] (
[id] INT PRIMARY KEY IDENTITY(1, 1),
[textdata] NVARCHAR(MAX))
GO
CREATE TABLE [BinaryStreamsCopy] (
[id] INT PRIMARY KEY IDENTITY(1, 1),
[bindata] VARBINARY(MAX))
GO
このサンプルでは、次の処理の実行方法を示します。
.NET で SQL Server に大きな BLOB を転送する。
.NET で SQL Server に大きなテキスト ファイルを転送する。
新しい非同期機能を使用して大きな BLOB を転送する。
新しい非同期機能と Await キーワードを使用して大きな BLOB を転送する。
大きな BLOB の転送を取り消す。
新しい非同期機能を使用して 1 つの SQL Server から別の SQL Server にストリーミングする。
using System;
using System.Data;
using Microsoft.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
namespace StreamingToServer
{
class Program
{
private const string connectionString = @"Server=localhost;Database=Demo2;Integrated Security=true";
static void Main(string[] args)
{
CreateDemoFiles();
StreamBLOBToServer().Wait();
StreamTextToServer().Wait();
// Create a CancellationTokenSource that will be cancelled after 100ms
// Typically this token source will be cancelled by a user request (e.g. a Cancel button)
CancellationTokenSource tokenSource = new CancellationTokenSource();
tokenSource.CancelAfter(100);
try
{
CancelBLOBStream(tokenSource.Token).Wait();
}
catch (AggregateException ex)
{
// Cancelling an async operation will throw an exception
// Since we are using the Task's Wait method, this exception will be wrapped in an AggregateException
// If you were using the 'await' keyword, the compiler would take care of unwrapping the AggregateException
// Depending on when the cancellation occurs, you can either get an error from SQL Server or from .Net
if ((ex.InnerException is SqlException) || (ex.InnerException is TaskCanceledException))
{
// This is an expected exception
Console.WriteLine("Got expected exception: {0}", ex.InnerException.Message);
}
else
{
// Did not expect this exception - re-throw it
throw;
}
}
Console.WriteLine("Done");
}
// This is used to generate the files which are used by the other sample methods
private static void CreateDemoFiles()
{
Random rand = new Random();
byte[] data = new byte[1024];
rand.NextBytes(data);
using (FileStream file = File.Open("binarydata.bin", FileMode.Create))
{
file.Write(data, 0, data.Length);
}
using (StreamWriter writer = new StreamWriter(File.Open("textdata.txt", FileMode.Create)))
{
writer.Write(Convert.ToBase64String(data));
}
}
// Application transferring a large BLOB to SQL Server
private static async Task StreamBLOBToServer()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (SqlCommand cmd = new SqlCommand("INSERT INTO [BinaryStreams] (bindata) VALUES (@bindata)", conn))
{
using (FileStream file = File.Open("binarydata.bin", FileMode.Open))
{
// Add a parameter which uses the FileStream we just opened
// Size is set to -1 to indicate "MAX"
cmd.Parameters.Add("@bindata", SqlDbType.Binary, -1).Value = file;
// Send the data to the server asynchronously
await cmd.ExecuteNonQueryAsync();
}
}
}
}
// Application transferring a large Text File to SQL Server
private static async Task StreamTextToServer()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (SqlCommand cmd = new SqlCommand("INSERT INTO [TextStreams] (textdata) VALUES (@textdata)", conn))
{
using (StreamReader file = File.OpenText("textdata.txt"))
{
// Add a parameter which uses the StreamReader we just opened
// Size is set to -1 to indicate "MAX"
cmd.Parameters.Add("@textdata", SqlDbType.NVarChar, -1).Value = file;
// Send the data to the server asynchronously
await cmd.ExecuteNonQueryAsync();
}
}
}
}
// Cancelling the transfer of a large BLOB
private static async Task CancelBLOBStream(CancellationToken cancellationToken)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
// We can cancel not only sending the data to the server, but also opening the connection
await conn.OpenAsync(cancellationToken);
// Artificially delay the command by 100ms
using (SqlCommand cmd = new SqlCommand("WAITFOR DELAY '00:00:00:100';INSERT INTO [BinaryStreams] (bindata) VALUES (@bindata)", conn))
{
using (FileStream file = File.Open("binarydata.bin", FileMode.Open))
{
// Add a parameter which uses the FileStream we just opened
// Size is set to -1 to indicate "MAX"
cmd.Parameters.Add("@bindata", SqlDbType.Binary, -1).Value = file;
// Send the data to the server asynchronously
// Pass the cancellation token such that the command will be cancelled if needed
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
}
}
}
}
}
サンプル -- 1 つの SQL Server から別の SQL Server へのストリーミング
このサンプルでは、大きな BLOB を SQL Server 間で非同期にストリーミングする方法を示します。取り消し処理がサポートされています。
Note
次のサンプルを実行する前に、Demo および Demo2 データベースが作成されていることを確認してください。
using System;
using System.Data;
using Microsoft.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
namespace StreamingFromServerToAnother
{
class Program
{
private const string connectionString = @"Server=localhost;Database=Demo2;Integrated Security=true";
static void Main(string[] args)
{
// For this example, we don't want to cancel
// So we can pass in a "blank" cancellation token
E2EStream(CancellationToken.None).Wait();
Console.WriteLine("Done");
}
// Streaming from one SQL Server to Another One
private static async Task E2EStream(CancellationToken cancellationToken)
{
using (SqlConnection readConn = new SqlConnection(connectionString))
{
using (SqlConnection writeConn = new SqlConnection(connectionString))
{
// Note that we are using the same cancellation token for calls to both connections\commands
// Also we can start both the connection opening asynchronously, and then wait for both to complete
Task openReadConn = readConn.OpenAsync(cancellationToken);
Task openWriteConn = writeConn.OpenAsync(cancellationToken);
await Task.WhenAll(openReadConn, openWriteConn);
using (SqlCommand readCmd = new SqlCommand("SELECT [bindata] FROM [BinaryStreams]", readConn))
{
using (SqlCommand writeCmd = new SqlCommand("INSERT INTO [BinaryStreamsCopy] (bindata) VALUES (@bindata)", writeConn))
{
// Add an empty parameter to the write command which will be used for the streams we are copying
// Size is set to -1 to indicate "MAX"
SqlParameter streamParameter = writeCmd.Parameters.Add("@bindata", SqlDbType.Binary, -1);
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
using (SqlDataReader reader = await readCmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
// Grab a stream to the binary data in the source database
using (Stream dataStream = reader.GetStream(0))
{
// Set the parameter value to the stream source that was opened
streamParameter.Value = dataStream;
// Asynchronously send data from one database to another
await writeCmd.ExecuteNonQueryAsync(cancellationToken);
}
}
}
}
}
}
}
}
}
}