在 ADO.NET 中修改 Large-Value （max）數據

大型物件（LOB）資料類型是超過最大資料列大小 8 KB 的數據類型。 SQL Server 提供max、varchar、nvarchar和varbinary資料類型的指定符號，以允許儲存大小為2^32字節的值。數據表數據行和 Transact-SQL 變數可以指定 varchar(max)、 nvarchar(max)或 varbinary(max) 資料類型。在 ADO.NET 中， max 數據類型可由擷取 DataReader，也可以指定為輸入和輸出參數值，而不需要任何特殊處理。針對大型 varchar 數據類型，可以以累加方式擷取和更新數據。

max數據類型可用於比較、作為 Transact-SQL 變數，以及串連。它們也可以用於 SELECT 陳述式中的 DISTINCT、ORDER BY、GROUP BY 子句，以及彙總、連接和子查詢。

如需詳細資訊，請參閱使用 Large-Value 數據類型。

Large-Value 類型限制

下列限制適用於 max 資料類型，而這些限制在較小的資料類型中並不存在：

sql_variant不能包含大型varchar數據類型。
大型 varchar 欄無法指定為索引中的索引鍵欄。在非叢集索引的內含欄位中允許它們。
大型 varchar 欄位不能用作分區鍵欄位。

在 Transact-SQL 中處理 Large-Value 類型

OPENROWSET Transact-SQL 函式是連線和存取遠端數據的一次性方法。其中包含從 OLE DB 資料來源存取遠端資料所需的所有連接資訊。 OPENROWSET 可以在查詢的 FROM 子句中被引用，就像它是資料表名稱一樣。它也可以作為 UPDATE、DELETE 或 INSERT 陳述句的目標表來參考，這取決於 OLE DB 提供者的能力。

函 OPENROWSET 式包含 BULK 數據列集提供者，可讓您直接從檔案讀取數據，而不需要將數據載入目標數據表。這讓你能在簡單的OPENROWSET陳述句中使用INSERT SELECT。

選項 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語法，以修改 nvarchar(max)、varbinary(max) 或 varchar(max) 欄位的內容。這可讓您執行數據的部分更新。 UPDATE .WRITE語法以縮寫形式展示如下：

UPDATE

{ <物件> }

SET

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

該 WRITE 方法指定 column_name 值的某一段會被修改。表達式是將複製到 column_name的值， @Offset 是將寫入表達式的起點，而 @Length 自變數是數據行中區段的長度。

如果	然後
表達式已設為 `NULL`	`@Length` 被忽略，而 column_name 中的值會在指定的 `@Offset` 處截斷。
`@Offset` 是 `NULL`	更新作業會在現有 column_name 值結尾附加表達式，並且 `@Length` 會被忽略。
`@Offset` 大於column_name值的長度	SQL Server 會傳回錯誤。
`@Length` 是 `NULL`	更新作業會將所有數據從 `@Offset` 移除至值結尾 `column_name` 。

備註

既不是 @Offset 也不是 @Length 可以是負數。

範例

本 Transact-SQL 範例會更新 DocumentSummary 中的部分值，這是 nvarchar(max) AdventureWorks 資料庫中 Document 數據表中的數據行。 '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 中使用 Large-Value 類型

使用 SqlParameter 物件搭配 SqlCommand 傳送較大的值。用 SqlDataReader 來檢索結果，或用 SqlDataAdapter 填入 DataSet 或 DataTable 物件。你要像處理其相關的較小值資料型態一樣處理大型值型態。

使用 GetSqlBytes 擷取數據

若要取得 varbinary(max) 欄位以 SqlBytes 形式的內容，呼叫 GetSqlBytes。以下片段假設 cmd 是選擇二進位資料的 SqlCommand，而 reader 是擷取它的 SqlDataReader。

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 擷取數據

要取得 varchar(max) 或 GetSqlChars 欄位的內容，請呼叫 nvarchar(max)。以下片段假設 cmd 是選擇 nvarchar(max) 資料的 SqlCommand，而 reader 是擷取這些資料的 SqlDataReader。

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 擷取數據

若要將 varbinary(max) 欄位的內容擷取為 SqlBinary 值，請呼叫 GetSqlBinary。以下片段假設 cmd 是用來選擇二進位資料的 SqlCommand，而 reader 是用來擷取該資料的 SqlDataReader。

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 方法會從指定的資料行位移讀取位元組，並讀入位元組陣列中的指定陣列位移。以下片段假設 reader 是擷取該位元組的 SqlDataReader。與 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 方法會以物件形式回傳欄位值。以下片段假設 reader 為 SqlDataReader。在 C# 中，二進位欄位回傳 byte[] ，字元欄位回傳 string。在 Visual Basic 中，對應的類型為 Byte() 和 String。

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

    ' 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) 或 ToString 欄位的內容，例如 nvarchar(max)。以下片段假設 reader 是擷取資料的 SqlDataReader。

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資料庫中的AdventureWorks資料表取得名稱與ProductPhoto值，並將其儲存到檔案中。此影像轉換範例需要 Windows 與 System.Drawing.Common NuGet 套件。此限制不適用於影像轉換，而是適用於提供者的二進位資料擷取。該 GetSqlBytes 方法返回 SqlBytes，這會公開該 Stream 屬性。程式碼利用串流建立 Bitmap 物件並以 GIF 格式儲存。

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

另請參閱

上次更新時間： 2026-10-27

在 ADO.NET 中修改 Large-Value （max） 數據