修改 ADO.NET 中的大型數值 (max) 資料

下載 ADO.NET

大型物件 (LOB) 資料類型是指資料列大小上限超過 8 KB 的資料類型。 SQL Server 提供 max、varchar 和 nvarchar 資料類型的 varbinary 規範，允許儲存最大達 2^32 位元組的值。資料表資料行和 Transact-SQL 變數可能會指定 varchar(max)、nvarchar(max) 或 varbinary(max) 資料類型。在 .NET 中，max 資料類型可透過 DataReader 來擷取，也可指定為輸入及輸出參數值，並且不需要任何特殊處理。對於大型 varchar 資料類型，可以使用累加方式來擷取和更新資料。

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

如需大數值資料類型的詳細資訊，請參閱《SQL Server 線上叢書》的使用大數值資料類型。

大數值類型限制

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

sql_variant 不能包含大型 varchar 資料類型。
大型 varchar 資料行不能指定為索引的鍵資料行。它們可包含於非叢集索引的內含資料行中。
大型 varchar 資料行不能用來作為資料分割索引鍵資料行。

在 Transact-SQL 中使用大數值類型

Transact-SQL OPENROWSET 函數是連線和存取遠端資料的一次性方法。 OPENROWSET 可以在查詢的 FROM 子句中如同資料表名稱一樣加以參照。它也可以作為 INSERT、UPDATE 或 DELETE 陳述式的目標資料表來參照。

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

OPENROWSET BULK 選項引數可有效地控制何處開始及結束讀取資料、如何處理錯誤，以及如何解譯資料。例如，您可以指定讓資料檔案當作 varbinary、varchar 或 nvarchar 類型的單一資料列、單一資料行資料列集加以讀取。如需完整的語法和選項，請參閱《SQL Server 線上叢書》。

下列範例會在 AdventureWorks2025 範例資料庫的 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 語法以縮寫形式展示如下：

UPDATE

{ <物件> }

SET

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

WRITE 方法指定將對 column_name 值的某個區段進行修改。其中，expression 是將複製到 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 中的部分值，這是 AdventureWorks 資料庫內 Document 資料表中的 nvarchar(max) 資料行。將 'components' 一字取代為 'features'，方法是指定取代字詞、現有資料中要取代之字詞的起始位置（位移），以及要取代的字元數（長度）。範例在 UPDATE 陳述式之前和之後都包含 SELECT 陳述式，以便比較結果。

USE AdventureWorks2022;
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 中使用大數值類型

您可以在 ADO.NET 中使用大數值類型，方法是將大數值類型指定為 SqlParameter 中的 SqlDataReader 物件以傳回結果集，或使用 SqlDataAdapter 來填滿 DataSet/DataTable。您使用大數值類型及其相關且較小數值資料類型的方式並無任何差異。

使用 GetSqlBytes 來擷取資料

GetSqlBytes 的 SqlDataReader 方法可以用來擷取 varbinary(max) 資料行的內容。下列程式碼片段會假設一個名為 SqlCommand 的 cmd 物件 (此物件會從資料表中選取 varbinary(max) 資料)，以及一個名為 SqlDataReader 的 reader 物件 (此物件會以 SqlBytes 形式來擷取資料)。

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())  
{  
    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())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

使用 GetBytes 來擷取資料

GetBytes 的 SqlDataReader 方法可將指定資料行位移的位元組資料流，讀取到始於指定陣列位移的位元組陣列。下列程式碼片段會假設一個名為 SqlDataReader 的 reader 物件，此物件可將位元組擷取到位元組陣列。請注意，不同於 GetSqlBytes，GetBytes 需要陣列緩衝區的大小。

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  
    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())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

範例

下列程式碼會從 LargePhoto 資料庫中的 ProductPhoto 資料表，擷取名稱和 AdventureWorks 物件，並將其儲存到檔案。此組件必須以 System.Drawing 命名空間的參考進行編譯。 GetSqlBytes 的 SqlDataReader 方法會傳回 SqlBytes 物件，此物件會公開 Stream 屬性。該程式碼會使用此物件來建立新的 Bitmap 物件，然後將其儲存為 Gif ImageFormat。

using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

class Program
{
    static void Main()
    {
        // Supply any valid DocumentID value and file path.
        // The value 3 is supplied for DocumentID, and a literal
        // string for the file path where the image will be saved. 1, 60
        TestGetSqlBytes(7, @"c:\temp\");
        Console.ReadLine();
    }
    static private void TestGetSqlBytes(int documentID, string filePath)
    {
        // Assumes GetConnectionString returns a valid connection string.
        using (SqlConnection connection =
                   new SqlConnection(GetConnectionString()))
        {
            SqlCommand command = connection.CreateCommand();
            SqlDataReader reader = null;
            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 SqlParameter("@ProductPhotoID", SqlDbType.Int);
                paramID.Value = documentID;
                command.Parameters.Add(paramID);
                connection.Open();

                string photoName = null;

                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("{0} is unavailable.", photoName);
                        }
                        else
                        {
                            SqlBytes bytes = reader.GetSqlBytes(1);
                            using (Bitmap productImage = new Bitmap(bytes.Stream))
                            {
                                String fileName = filePath + photoName;

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

    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

使用大數值類型參數

大數值類型可以用於 SqlParameter 物件中，就像您在 SqlParameter 物件中使用較小數值類型一樣。您可以將大數值類型擷取為 SqlParameter 值，如下列範例所示。此程式碼假設下列 GetDocumentSummary 預存程序存在於 AdventureWorks2025 範例資料庫中。預存程序會接受名為 @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 輸出參數中傳回的結果。

using Microsoft.Data.SqlClient;
class Program
{
    static void Main()
    {
        // Supply any valid Document ID value.
        // The value 7 is supplied for demonstration purposes.
        string summaryString = GetDocumentSummary(7);
        Console.ReadLine();
    }
    static private string GetDocumentSummary(int documentID)
    {
        //Assumes GetConnectionString returns a valid connection string.
        using (SqlConnection connection =
                   new SqlConnection(GetConnectionString()))
        {
            connection.Open();
            SqlCommand command = connection.CreateCommand();
            try
            {
                // Setup 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 SqlParameter("@DocumentID", SqlDbType.Int);
                paramID.Value = documentID;
                command.Parameters.Add(paramID);

                // Set up the output parameter to retrieve the summary.
                SqlParameter paramSummary =
                    new SqlParameter("@DocumentSummary",
                    SqlDbType.NVarChar, -1);
                paramSummary.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;
            }
        }
    }
    static private string GetConnectionString()
    {
        // To avoid storing the connectionection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=(local);Initial Catalog=AdventureWorks;" +
            "Integrated Security=SSPI";
    }
}

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-07-21