修改 ADO.NET 中的大數值 (max) 資料
更新： November 2007
在 SQL Server 2005 之前的 SQL Server 版本中，使用大型物件 (LOB) 資料型別時需要進行特殊處理。LOB 資料型別是指資料列大小上限超過 8 KB 的資料型別。SQL Server 2005 導入了 varchar、nvarchar 和 varbinary 資料型別的 max 規範，允許儲存最大達 2^32 位元組的值。資料表資料行及 Transact-SQL 變數可指定 varchar(max)、nvarchar(max) 或 varbinary(max) 資料型別。在 ADO.NET 中，新的 max 資料型別可透過 DataReader 來擷取，也可指定為輸入及輸出參數值，並且不需要任何特殊處理。對於大型 varchar 資料型別，可透過遞增方式擷取及更新資料。
max 資料型別可用於比較 (做為 Transact-SQL 變數) 及串連。它們也可用於 SELECT 陳述式的 DISTINCT、ORDER BY、GROUP BY 子句，以及彙總、聯結 (Join) 及子查詢中。
下表將提供《SQL Server 線上叢書》中相關文件的連結。
SQL Server 2000 |
SQL Server 2005 |
SQL Server 2008 |
|---|---|---|
大數值型別限制
下列限制適用於 max 資料型別，而小型資料型別則沒有這些限制：
sql_variant 不可包含大型 varchar 資料型別。
大型 varchar 資料行不可指定為索引中的索引鍵資料行。而在非叢集索引的內含資料行中，則允許有大型資料行。
大型 varchar 資料行不可用做分割索引鍵資料行。
在 Transact-SQL 中使用大數值型別
Transact-SQL OPENROWSET 函式是連接及存取遠端資料的一次性方法。其包括從 OLE DB 資料來源存取遠端資料時所需的所有連接資訊。您可以在查詢的 FROM 子句中，將 OPENROWSET 當做資料表名稱般地加以參考。此外，它也可參考為 INSERT、UPDATE 或 DELETE 陳述式的目標資料表，但會受到 OLE DB 提供者的功能影響。
OPENROWSET 函式已在 SQL Server 2005 中透過加入 BULK 資料列集提供者而增強，如此可讓您直接從檔案讀取資料，而不需要將資料載入目標資料表中。這可讓您在簡單的 INSERT SELECT 陳述式中使用 OPENROWSET。
OPENROWSETBULK 選項引數可有效地控制何處開始及結束讀取資料、如何處理錯誤，以及如何解譯資料。例如，您可以指定將資料檔案讀取為具有型別 varbinary、varchar 或 nvarchar 的單一資料列及單一資料行資料列集。如需完整的語法及選項，請參閱《SQL Server 線上叢書》。
下列範例會將相片插入 AdventureWorks 範例資料庫中的 ProductPhoto 資料表。使用 BULKOPENROWSET 提供者時，即使未將值插入每個資料行，也必須提供資料行的具名清單。在此情況下，將主索引鍵定義為識別欄位，也可從資料行清單省略。請注意，您還必須提供關聯名稱，將其置於 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
{ <object> }
SET
{ column_name = { .WRITE ( expression , @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 值結尾的所有資料。 |
.gif "注意事項") 注意事項： |
|---|
@Offset 及 @Length 都不可為負數。 |
範例
此 Transact-SQL 範例會更新 DocumentSummary 中的部分值，其為 AdventureWorks 資料庫中 Document 資料表內的 nvarchar(max) 資料行。藉由指定取代單字、現有資料中要取代之單字的開始位置 (位移)，以及要取代的字元數 (長度)，將 components 這個字取代為 features 這個字。該範例會將 SELECT 陳述式併入到 UPDATE 陳述式之前與之後來比較結果。
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 中使用大數值型別
您可以在 ADO.NET 中使用大數值型別，方法是將大數值型別指定為 SqlDataReader 中的 SqlParameter 物件以傳回結果集，或使用 SqlDataAdapter 來填滿 DataSet/DataTable。大型值型別與其相關的小型值資料型別在使用方式上並無差異。
使用 GetSqlBytes 擷取資料
SqlDataReader 的 GetSqlBytes 方法可用於擷取 varbinary(max) 資料行的內容。下列程式碼片段假設名為 cmd 的 SqlCommand 物件會從資料表選取 varbinary(max) 資料；名為 reader 的 SqlDataReader 物件會以 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 擷取資料
SqlDataReader 的 GetSqlChars 方法可用於擷取 varchar(max) 或 nvarchar(max) 資料行的內容。下列程式碼片段假設名為 cmd 的 SqlCommand 物件會從資料表選取 nvarchar(max) 資料；名為 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 擷取資料
SqlDataReader 的 GetSqlBinary 方法可用於擷取 varbinary(max) 資料行的內容。下列程式碼片段假設名為 cmd 的 SqlCommand 物件會從資料表選取 varbinary(max) 資料；名為 reader 的 SqlDataReader 物件會以 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 擷取資料
SqlDataReader 的 GetBytes 方法可將位元組資料流，從指定的資料行位移讀取到在指定陣列位移處開始的位元組陣列。下列程式碼片段假設名為 reader 的 SqlDataReader 物件會將位元組擷取到位元組陣列。請注意，GetBytes 與 GetSqlBytes 不同，其需要一定大小的陣列緩衝區。
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 擷取資料
SqlDataReader 的 GetValue 方法可將值從指定的資料行位移讀取到陣列中。下列程式碼片段假設名為 reader 的 SqlDataReader 物件會從第一個資料行位移擷取二進位資料，然後從第二個資料行位移擷取字串資料。
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 型別
您可以使用任何字串轉換方法 (如 ToString)，來轉換 varchar(max) 或 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);
}
範例
下列程式碼會從 AdventureWorks 資料庫中的 ProductPhoto 資料表擷取名稱及 LargePhoto 物件，並將其儲存至檔案。組件 (Assembly) 需要參考 System.Drawing 命名空間 (Namespace) 才能進行編譯。SqlDataReader 的 GetSqlBytes 方法會傳回 SqlBytes 物件，其會公開 Stream 屬性。該程式碼會使用此物件來建立新的 Bitmap 物件，然後將它儲存為 Gif ImageFormat 格式。
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 Bitmap = _
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
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();
}
}
}
使用大型值型別參數
大型值型別可用於 SqlParameter 物件，其使用方式與在 SqlParameter 物件中使用小型值型別相同。您可以將大數值型別擷取為 SqlParameter 值，如下列範例所示。該程式碼假設下列 GetDocumentSummary 預存程序存在於 AdventureWorks 範例資料庫中。預存程序會接受名為 @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 輸出參數傳回的結果。
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
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;
}
}
}