在 ADO.NET 中修改 Large-Value（最大值）数据

大型对象 (LOB) 数据类型是指那些超过 8 KB 最大行大小的数据类型。 SQL Server 为 max、varchar 和 nvarchar 数据类型引入了 varbinary 说明符，以允许存储最长可达 2^32 个字节的值。表列和 Transact-SQL 变量可以指定 varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型。在 ADO.NET 中， max 数据类型可由 a DataReader提取，也可以指定为输入和输出参数值，而无需进行任何特殊处理。对于大型 varchar 数据类型，可以增量地检索和更新数据。

max 数据类型可用于进行比较（作为 Transact-SQL 变量）和串联。它们还可以用于 SELECT 语句的 DISTINCT、ORDER BY、GROUP BY 子句，以及聚合、连接和子查询。

有关详细信息，请参阅使用 Large-Value 数据类型。

大值类型限制

以下限制适用于 max 数据类型，这些数据类型对于较小的数据类型是不存在的。

sql_variant 不能包含大型 varchar 数据类型。
不能将大型 varchar 列指定为索引中的键列。允许在非聚集索引的包含列中使用它们。
大型 varchar 列不能用作分区键列。

在 Transact-SQL 中使用大值类型

Transact-SQL OPENROWSET 函数是连接和访问远程数据的一次性方法。它包括从 OLE DB 数据源访问远程数据所需的所有连接信息。可在查询的 FROM 子句中像引用表名那样引用 OPENROWSET。还可以将其引用为 INSERT、UPDATE 或 DELETE 语句的目标表，但受 OLE DB 访问接口的功能的约束。

OPENROWSET 函数包含 BULK 行集提供程序，它允许你直接从文件读取数据而不必将数据加载到目标表。这样你可以在简单的 INSERT SELECT 语句中使用 OPENROWSET。

OPENROWSET BULK 选项参数可以有效地控制在何处开始和结束读取数据、如何处理错误以及如何解释数据。例如，可以指定以类型为 varbinary、varchar 或 nvarchar 的单行单列行集的形式读取数据文件。

以下示例将照片插入 AdventureWorks 示例数据库中的 ProductPhoto 表中。在使用 BULK OPENROWSET 提供程序时，即使不将值插入每个列中，也必须提供列的命名列表。在此示例中，主键定义为标识列，可以从列列表中省略。请注意，还必须在 OPENROWSET 语句的末尾提供相关名称，在本例中为 ThumbnailPhoto。这与文件被加载到 ProductPhoto 表的列相对应。

INSERT Production.ProductPhoto (
    ThumbnailPhoto,
    ThumbnailPhotoFilePath,
    LargePhoto,
    LargePhotoFilePath)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

使用 UPDATE .WRITE 更新数据

Transact-SQL UPDATE 语句具有新的 WRITE 语法，用于修改 varchar(max)、nvarchar(max) 或 varbinary(max) 列的内容。这允许你对数据进行部分更新。 UPDATE .WRITE 语法在此以缩写形式显示：

更新

{ <object> }

设置

{ column_name = { .WRITE （ 表达式 ， @Offset ， @Length ） }

WRITE 方法指定将修改 column_name 值的一个部分。表达式是将复制到 column_name 的值，是将写入表达式的开始点，@Offset 自变量是列中该部分的长度@Length。

如果	则
表达式设置为 NULL	`@Length` 被忽略，并在指定的 `@Offset` 处截断 column_name 中的值。
`@Offset` 为 NULL	更新操作将表达式追加到现有 column_name 值的末尾并忽略 `@Length`。
`@Offset` 大于“column_name”的值的长度	SQL Server 将返回错误。
`@Length` 为 NULL	更新操作将删除从 `@Offset` 到 `column_name` 值的结尾的所有数据。

注释

@Offset 和 @Length 都不能为负数。

示例：

此 Transact-SQL 示例更新 DocumentSummary 中的一个部分值，这是 AdventureWorks 数据库中 Document 表的 nvarchar(max) 列。通过指定替换单词、现有数据中要替换的单词的开始位置（偏移量）以及要替换的字符数（长度），将单词“components”替换为单词“features”。该示例在 UPDATE 语句之前和之后都包含 SELECT 语句来比较结果。

USE AdventureWorks;
GO
--View the existing value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety components of your bicycle.

--Modify a single word in the DocumentSummary column
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
WHERE DocumentID = 3 ;
GO
--View the modified value.
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 3;
GO
-- The first sentence of the results will be:
-- Reflectors are vital safety features of your bicycle.

在 ADO.NET 中使用大值类型

通过将大值类型指定为 SqlParameter 中的 SqlDataReader 对象以返回结果集，或者通过 SqlDataAdapter 来填充 DataSet/DataTable，即可在 ADO.NET 中使用大值类型。处理大值类型与其相关的较小值数据类型的方式没有区别。

使用 GetSqlBytes 检索数据

GetSqlBytes 的 SqlDataReader 方法可用于检索 varbinary(max) 列的内容。下面的代码段假定一个名为 SqlCommand 的 cmd 对象，该对象从表中选择 varbinary(max) 数据，并假定一个名为 SqlDataReader 的 reader 对象以 SqlBytes 的形式检索数据。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim bytes As SqlBytes = reader.GetSqlBytes(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
    {
        SqlBytes bytes = reader.GetSqlBytes(0);
    }

使用 GetSqlChars 检索数据

GetSqlChars 的 SqlDataReader 方法可用于检索 varchar(max) 或 nvarchar(max) 列的内容。下面的代码段假定一个名为 SqlCommand 的 cmd 对象，该对象从表中选择 nvarchar(max) 数据，并假定一个名为 SqlDataReader 的 reader 对象来检索数据。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim buffer As SqlChars = reader.GetSqlChars(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
    SqlChars buffer = reader.GetSqlChars(0);
}

使用 GetSqlBinary 检索数据

GetSqlBinary 的 SqlDataReader 方法可用于检索 varbinary(max) 列的内容。下面的代码段假定一个名为 SqlCommand 的 cmd 对象，该对象从表中选择 varbinary(max) 数据，并假定一个名为 SqlDataReader 的 reader 对象以 SqlBinary 流的形式检索数据。

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    Dim binaryStream As SqlBinary = reader.GetSqlBinary(0)
End While

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
    {
        SqlBinary binaryStream = reader.GetSqlBinary(0);
    }

使用 GetBytes 检索数据

GetBytes 的 SqlDataReader 方法将指定列偏移量的字节流，从指定的数组偏移量开始，读入一个字节数组。下面的代码段假定一个名为 SqlDataReader 的 reader 对象，该对象将字节检索到字节数组中。请注意，与 GetSqlBytes 不同，GetBytes 需要数组缓冲区的大小。

While reader.Read()
    Dim buffer(4000) As Byte
    Dim byteCount As Integer = _
    CInt(reader.GetBytes(1, 0, buffer, 0, 4000))
End While

while (reader.Read())
{
    byte[] buffer = new byte[4000];
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);
}

使用 GetValue 检索数据

GetValue 的 SqlDataReader 方法将指定列偏移量的值读入数组。下面的代码段假定一个名为 SqlDataReader 的 reader 对象，该对象从第一个列偏移量检索二进制数据，然后从第二列偏移量检索字符串数据。

While reader.Read()
    ' Read the data from varbinary(max) column
    Dim binaryData() As Byte = CByte(reader.GetValue(0))

    ' Read the data from varchar(max) or nvarchar(max) column
    Dim stringData() As String = Cstr((reader.GetValue(1))
End While

while (reader.Read())
{
    // Read the data from varbinary(max) column
    byte[] binaryData = (byte[])reader.GetValue(0);

    // Read the data from varchar(max) or nvarchar(max) column
    String stringData = (String)reader.GetValue(1);
}

从大值类型转换为 CLR 类型

可以使用任何字符串转换方法（如 varchar(max)）来转换 nvarchar(max) 或 ToString 列的内容。下面的代码段假定一个名为 SqlDataReader 的 reader 对象，该对象检索数据。

While reader.Read()
    Dim str as String = reader(0).ToString()
    Console.WriteLine(str)
End While

while (reader.Read())
{
     string str = reader[0].ToString();
     Console.WriteLine(str);
}

示例：

下面的代码从 LargePhoto 数据库的 ProductPhoto 表中检索名称和 AdventureWorks 对象，并将其保存到文件中。需要使用对 System.Drawing 命名空间的引用来编译该程序集。 SqlDataReader 的 GetSqlBytes 方法返回一个包含 Stream 属性的 SqlBytes 对象。代码使用此对象创建新的 Bitmap 对象，然后以 Gif ImageFormat 格式保存该对象。

static void TestGetSqlBytes(int documentID, string filePath)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        SqlCommand command = connection.CreateCommand();
        SqlDataReader reader = default!;
        try
        {
            // Setup the command
            command.CommandText =
                "SELECT LargePhotoFileName, LargePhoto "
                + "FROM Production.ProductPhoto "
                + "WHERE ProductPhotoID=@ProductPhotoID";
            command.CommandType = CommandType.Text;

            // Declare the parameter
            SqlParameter paramID =
                new("@ProductPhotoID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);
            connection.Open();

            string photoName = default!;

            reader = command.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    // Get the name of the file.
                    photoName = reader.GetString(0);

                    // Ensure that the column isn't null
                    if (reader.IsDBNull(1))
                    {
                        Console.WriteLine($"{photoName} is unavailable.");
                    }
                    else
                    {
                        SqlBytes bytes = reader.GetSqlBytes(1);
                        using (Bitmap productImage = new(bytes.Stream))
                        {
                            var fileName = filePath + photoName;

                            // Save in gif format.
                            productImage.Save(fileName, ImageFormat.Gif);
                            Console.WriteLine($"Successfully created {fileName}.");
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine("No records returned.");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            reader?.Dispose();
        }
    }
}

Private Sub GetPhoto(
  ByVal documentID As Integer, ByVal filePath As String)
    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        Dim command As SqlCommand = connection.CreateCommand()
        Dim reader As SqlDataReader
        Try
            ' Setup the command
            command.CommandText =
              "SELECT LargePhotoFileName, LargePhoto FROM" _
                & " Production.ProductPhoto" _
                & " WHERE ProductPhotoID=@ProductPhotoID"
            command.CommandType = CommandType.Text

            ' Declare the parameter
            Dim paramID As SqlParameter =
                New SqlParameter("@ProductPhotoID", SqlDbType.Int)
            paramID.Value = documentID
            command.Parameters.Add(paramID)
            connection.Open()

            Dim photoName As String

            reader =
             command.ExecuteReader(CommandBehavior.CloseConnection)

            If reader.HasRows Then
                While reader.Read()
                    ' Get the name of the file
                    photoName = reader.GetString(0)

                    ' Ensure that the column isn't null
                    If (reader.IsDBNull(1)) Then
                        Console.WriteLine("{0} is unavailable.", photoName)
                    Else
                        Dim bytes As SqlBytes = reader.GetSqlBytes(1)
                        Using productImage As New Bitmap(bytes.Stream)
                            Dim fileName As String = filePath & photoName

                            ' Save in gif format.
                            productImage.Save(
                              fileName, ImageFormat.Gif)
                            Console.WriteLine("Successfully created {0}.", fileName)
                        End Using
                    End If
                End While
            Else
                Console.WriteLine("No records returned.")
            End If
        Catch ex As Exception
            Console.WriteLine("Exception: {0}", ex.Message)
        End Try
    End Using
End Sub

使用大值类型参数

在 SqlParameter 对象中使用的大值类型的方式与在 SqlParameter 对象中使用较小值类型的方式相同。可以将大值类型作为 SqlParameter 值进行检索，如下面的示例所示。该代码假定 AdventureWorks 示例数据库中存在以下 GetDocumentSummary 存储过程。该存储过程采用名为 @DocumentID 的输入参数，并在 @DocumentSummary 输出参数中返回 DocumentSummary 列的内容。

CREATE PROCEDURE GetDocumentSummary
(
    @DocumentID int,
    @DocumentSummary nvarchar(MAX) OUTPUT
)
AS
SET NOCOUNT ON
SELECT  @DocumentSummary=Convert(nvarchar(MAX), DocumentSummary)
FROM    Production.Document
WHERE   DocumentID=@DocumentID

示例：

ADO.NET 代码将创建 SqlConnection 和 SqlCommand 对象以执行 GetDocumentSummary 存储过程，并检索文档摘要，该摘要存储为大值类型。代码为 @DocumentID 输入参数传递一个值，并在控制台窗口显示 @DocumentSummary 输出参数中传回的结果。

static string? GetDocumentSummary(int documentID)
{
    // Assumes GetConnectionString returns a valid connection string.
    using (SqlConnection connection =
               new(GetConnectionString()))
    {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        try
        {
            // Set up the command to execute the stored procedure.
            command.CommandText = "GetDocumentSummary";
            command.CommandType = CommandType.StoredProcedure;

            // Set up the input parameter for the DocumentID.
            SqlParameter paramID =
                new("@DocumentID", SqlDbType.Int)
                {
                    Value = documentID
                };
            command.Parameters.Add(paramID);

            // Set up the output parameter to retrieve the summary.
            SqlParameter paramSummary =
                new("@DocumentSummary",
                SqlDbType.NVarChar, -1)
                {
                    Direction = ParameterDirection.Output
                };
            command.Parameters.Add(paramSummary);

            // Execute the stored procedure.
            command.ExecuteNonQuery();
            Console.WriteLine((string)paramSummary.Value);
            return (string)paramSummary.Value;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return null;
        }
    }
}

Private Function GetDocumentSummary( _
  ByVal documentID As Integer) As String

    ' Assumes GetConnectionString returns a valid connection string.
    Using connection As New SqlConnection(GetConnectionString())
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()

        ' Setup the command to execute the stored procedure.
        command.CommandText = "GetDocumentSummary"
        command.CommandType = CommandType.StoredProcedure

        ' Set up the input parameter for the DocumentID.
        Dim paramID As SqlParameter = _
            New SqlParameter("@DocumentID", SqlDbType.Int)
        paramID.Value = documentID
        command.Parameters.Add(paramID)

        ' Set up the output parameter to retrieve the summary.
        Dim paramSummary As SqlParameter = _
            New SqlParameter("@DocumentSummary", _
               SqlDbType.NVarChar, -1)
        paramSummary.Direction = ParameterDirection.Output
        command.Parameters.Add(paramSummary)

        ' Execute the stored procedure.
        command.ExecuteNonQuery()
        Console.WriteLine(paramSummary.Value)
        Return paramSummary.Value.ToString
    End Using
End Function

另请参阅

Last updated on 2026-02-24