Memodifikasi Data Bernilai Besar (maks) dalam ADO.NET
Jenis data objek besar (large object atau LOB) adalah yang melebihi ukuran baris maksimum 8 kilobyte (KB). SQL Server menyediakan max penentu untuk jenis data varchar, nvarchar, dan varbinary untuk memungkinkan penyimpanan nilai sebesar 2^32 byte. Kolom tabel dan variabel Transact-SQL dapat menentukan jenis data varchar(max), nvarchar(max), atau varbinary(max). Dalam ADO.NET, jenis data max dapat diambil oleh DataReader, dan juga dapat ditentukan sebagai nilai parameter input dan output tanpa penanganan khusus. Untuk jenis data varcharbesar, data dapat diambil dan diperbarui secara bertahap.
Jenis data max dapat digunakan untuk perbandingan, sebagai variabel Transact-SQL, dan untuk perangkaian. Mereka juga dapat digunakan dalam klausa DISTINCT, ORDER BY, GROUP BY dari pernyataan SELECT serta dalam agregat, gabungan, dan subkueri.
Untuk informasi selengkapnya, lihat Menggunakan Jenis Data Bernilai-Besar.
Pembatasan Jenis Bernilai-Besar
Pembatasan berikut berlaku untuk jenis data max, yang tidak ada untuk jenis data yang lebih kecil:
sql_varianttidak boleh berisi jenis data besarvarchar.Kolom besar
varchartidak dapat ditentukan sebagai kolom kunci dalam indeks. Kolom ini diizinkan dalam kolom yang disertakan dalam indeks nonkluster.Kolom besar
varchartidak dapat digunakan sebagai kolom kunci pemartisian.
Bekerja dengan Jenis Bernilai-Besar di SQL Transact
Fungsi Transact-SQL OPENROWSET adalah metode satu kali untuk menyambungkan dan mengakses data jarak jauh. Ini termasuk semua informasi koneksi yang diperlukan untuk mengakses data jarak jauh dari sumber data OLE DB. OPENROWSET dapat direferensi dalam klausul DARI kueri seolah-olah merupakan nama tabel. Ini juga dapat direferensikan sebagai tabel target pernyataan INSERT, UPDATE, atau DELETE, tunduk pada kemampuan penyedia OLE DB.
Fungsi ini OPENROWSET mencakup BULK penyedia set baris, yang memungkinkan Anda membaca data langsung dari file tanpa memuat data ke dalam tabel target. Ini memungkinkan Anda menggunakan OPENROWSET dalam pernyataan INSERT SELECT sederhana.
Argumen opsi OPENROWSET BULK memberikan kontrol signifikan atas tempat memulai dan mengakhiri membaca data, cara menangani kesalahan, dan bagaimana data ditafsirkan. Misalnya, Anda dapat menentukan bahwa file data dibaca sebagai baris tunggal, himpunan baris kolom tunggal jenis varbinary, varchar, atau nvarchar.
Contoh berikut menyisipkan foto ke dalam tabel ProductPhoto di database sampel AdventureWorks. Saat menggunakan penyedia BULK OPENROWSET, Anda harus menyediakan daftar kolom bernama meskipun Anda tidak menyisipkan nilai ke setiap kolom. Kunci utama dalam hal ini didefinisikan sebagai kolom identitas, dan dapat dihilangkan dari daftar kolom. Perhatikan bahwa Anda juga harus memberikan nama korelasi di akhir OPENROWSET pernyataan, yang dalam hal ini adalah ThumbnailPhoto. Ini berkorelasi dengan kolom dalam ProductPhoto tabel tempat file dimuat.
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
Memperbarui Data Menggunakan UPDATE. WRITE
Pernyataan TRANSACT-SQL UPDATE memiliki sintaks WRITE baru untuk memodifikasi konten kolom varchar(max), nvarchar(max), atau varbinary(max). Ini memungkinkan Anda melakukan pembaruan parsial data. Sintaks UPDATE. WRITE ditunjukkan di sini dalam bentuk singkatan:
UPDATE
{ <objek> }
SET
{ column_name = { . WRITE ( ekspresi , @Offset, @Length ) }
Metode WRITE menentukan bahwa bagian dari nilai column_name akan dimodifikasi. Ekspresi adalah nilai yang akan disalin ke column_name, @Offset adalah titik awal ketika ekspresi akan ditulis, dan argumen @Length adalah panjang bagian dalam kolom.
| Jika | Kemudian |
|---|---|
| Ekspresi diatur ke NOL | @Length diabaikan dan nilai dalam column_name dipotong pada yang ditentukan @Offset. |
@Offset adalah nol |
Operasi pembaruan menambahkan ekspresi di akhir nilai column_name yang ada dan @Length diabaikan. |
@Offset lebih besar dari panjang nilai column_name |
SQL Server mengembalikan kesalahan. |
@Length adalah nol |
Operasi pembaruan menghapus semua data dari @Offset hingga akhir nilai column_name. |
Catatan
Baik @Offset juga @Length tidak dapat berupa angka negatif.
Contoh
Contoh SQL Transact ini memperbarui nilai parsial di DocumentSummary, nvarchar(max) kolom dalam tabel Dokumen di database AdventureWorks. Kata 'komponen' digantikan oleh kata 'fitur' dengan menentukan kata pengganti, lokasi awal (offset) kata yang akan diganti dalam data yang ada, dan jumlah karakter yang akan diganti (panjang). Contohnya mencakup pernyataan SELECT sebelum dan sesudah pernyataan UPDATE untuk membandingkan hasil.
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.
Bekerja dengan Jenis Bernilai-Besar dalam ADO.NET
Anda dapat bekerja dengan jenis nilai besar dalam ADO.NET dengan menentukan jenis nilai besar sebagai SqlParameter objek dalam SqlDataReader untuk mengembalikan set hasil, atau dengan menggunakan SqlDataAdapter untuk mengisi DataSet/DataTable. Tidak ada perbedaan antara cara Anda bekerja dengan jenis nilai besar dan jenis data nilai terkait yang lebih kecil.
Menggunakan GetSqlBytes untuk Mengambil Data
Metode GetSqlBytes dapat SqlDataReader digunakan untuk mengambil konten dari varbinary(max) kolom. Fragmen kode berikut mengasumsikan SqlCommand objek bernama cmd yang memilih varbinary(max) data dari tabel dan SqlDataReader objek bernama reader yang mengambil data sebagai 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);
}
Menggunakan GetSqlChars untuk Mengambil Data
Metode GetSqlChars dapat SqlDataReader digunakan untuk mengambil konten dari kolom varchar(max) atau nvarchar(max). Fragmen kode berikut mengasumsikan SqlCommand objek bernama cmd yang memilih nvarchar(max) data dari tabel dan SqlDataReader objek bernama reader yang mengambil data.
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);
}
Menggunakan GetSqlBinary untuk Mengambil Data
Metode GetSqlBinary dapat SqlDataReader digunakan untuk mengambil konten dari kolom varbinary(max). Fragmen kode berikut mengasumsikan SqlCommand objek bernama cmd yang memilih varbinary(max) data dari tabel dan SqlDataReader objek bernama reader yang mengambil data sebagai aliranSqlBinary.
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);
}
Menggunakan GetSqlBytes untuk Mengambil Data
Metode GetBytesSqlDataReader membaca aliran byte dari offset kolom yang ditentukan ke dalam array byte yang dimulai pada offset array yang ditentukan. Fragmen kode berikut mengasumsikan SqlDataReader objek bernama reader yang mengambil byte ke dalam array byte. Perhatikan bahwa, tidak seperti GetSqlBytes, GetBytes memerlukan ukuran untuk buffer array.
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);
}
Menggunakan GetValue untuk Mengambil Data
Metode GetValue SqlDataReader membaca nilai dari offset kolom yang ditentukan ke dalam array. Fragmen kode berikut mengasumsikan SqlDataReader objek bernama reader yang mengambil data biner dari offset kolom pertama, lalu string data dari offset kolom kedua.
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);
}
Mengonversi dari Jenis Nilai Besar ke Jenis CLR
Anda dapat mengonversi konten kolom varchar(max) atau nvarchar(max) menggunakan salah satu metode konversi string, seperti ToString. Fragmen kode berikut mengasumsikan objek SqlDataReader bernama reader yang mengambil byte ke dalam array byte.
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);
}
Contoh
Kode berikut mengambil nama dan objek LargePhoto dari tabel ProductPhoto dalam AdventureWorks database dan menyimpannya ke file. Assembly perlu dikompilasi dengan referensi ke namespace System.Drawing. Metode GetSqlBytesSqlDataReader mengembalikan SqlBytes objek yang mengekspos properti Stream. Kode menggunakan ini untuk membuat objek baru Bitmap, lalu menyimpannya di 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("{0} is unavailable.", photoName);
}
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 {0}.", 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
Menggunakan Parameter Jenis Nilai Besar
Jenis nilai besar dapat digunakan dalam SqlParameter objek dengan cara yang sama seperti Anda menggunakan jenis nilai yang lebih kecil dalam objekSqlParameter. Anda dapat mengambil jenis nilai besar sebagai nilai SqlParameter, seperti yang ditunjukkan dalam contoh berikut. Kode ini mengasumsikan bahwa prosedur tersimpan GetDocumentSummary berikut ada di database sampel AdventureWorks. Prosedur tersimpan mengambil parameter input bernama @DocumentID dan mengembalikan konten kolom DocumentSummary dalam parameter output @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
Contoh
Kode ADO.NET membuat objek SqlConnection dan SqlCommand untuk menjalankan prosedur tersimpan GetDocumentSummary dan mengambil ringkasan dokumen, yang disimpan sebagai jenis nilai besar. Kode meneruskan nilai untuk parameter input @DocumentID, dan menampilkan hasil yang diteruskan kembali dalam parameter output @DocumentSummary di jendela Konsol.
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