Modifikasi Data Large-Value (max) di ADO.NET

Jenis data objek besar (LOB) adalah yang melebihi ukuran baris maksimum 8 kilobyte (KB). SQL Server menyediakan penentu max untuk varchar, nvarchar, dan varbinary tipe data yang memungkinkan penyimpanan nilai sebesar 2^32 byte. Kolom tabel dan variabel Transact-SQL dapat menentukan varchar(max)jenis data , nvarchar(max), atau varbinary(max) . Dalam ADO.NET, max jenis data dapat diambil oleh DataReader, dan juga dapat ditentukan sebagai nilai parameter input dan output tanpa penanganan khusus. Untuk jenis data besar varchar , data dapat diambil dan diperbarui secara bertahap.

Jenis max data 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 Large-Value.

Large-Value Pembatasan Jenis

Pembatasan berikut berlaku untuk max jenis data, yang tidak ada untuk jenis data yang lebih kecil:

• sql_variant tidak dapat berisi tipe data yang besar varchar.

• Kolom besar varchar tidak dapat ditentukan sebagai kolom kunci dalam indeks. Mereka diizinkan dalam kolom yang termasuk dalam indeks non-terkelompok.

• Kolom besar varchar tidak dapat digunakan sebagai kolom kunci partisi.

Bekerja dengan jenis-jenis Large-Value di Transact-SQL

Fungsi Transact-SQL OPENROWSET adalah metode satu kali untuk menghubungkan dan mengakses data jarak jauh. Ini termasuk semua informasi koneksi yang diperlukan untuk mengakses data jarak jauh dari sumber data OLE DB. OPENROWSET dapat dirujuk dalam klausa FROM kueri seolah-olah itu adalah nama tabel. Ini juga dapat direferensikan sebagai tabel target pernyataan INSERT, UPDATE, atau DELETE, yang tunduk pada kemampuan penyedia OLE DB.

Fungsi ini OPENROWSET mencakup BULK penyedia himpunan baris, yang memungkinkan Anda membaca data langsung dari file tanpa mengunggah data ke tabel tujuan. Ini memungkinkan Anda untuk menggunakan OPENROWSET dalam pernyataan INSERT SELECT sederhana.

Argumen OPENROWSET BULK opsi 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, , varcharatau 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 kasus 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 sedang 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 untuk melakukan pembaruan parsial data. Sintaks UPDATE.WRITE ditunjukkan di sini dalam bentuk yang lebih singkat:

Pembaruan

{ <object> }

MENGESET

{ 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 di mana ekspresi akan ditulis, dan @Length argumen adalah panjang bagian dalam kolom.

Kalau	Kemudian
Ekspresi diatur ke NULL	@Length diabaikan dan nilai dalam column_name dipotong pada nilai yang ditentukan @Offset.
@Offset adalah NULL	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 NULL	Operasi pembaruan menghapus semua data dari @Offset ke akhir column_name nilai.

Nota

Baik @Offset maupun @Length tidak boleh berupa angka negatif.

Contoh

Contoh Transact-SQL 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 Tipe Large-Value di ADO.NET

Anda dapat bekerja dengan jenis nilai besar di ADO.NET dengan menentukan jenis nilai besar sebagai SqlParameter objek dalam SqlDataReader untuk mengembalikan tataan 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 dari SqlDataReader dapat digunakan untuk mengambil isi 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 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 dari SqlDataReader dapat 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 GetSqlBinarySqlDataReader dapat digunakan untuk mengambil isi kolom varbinary(max). Fragmen kode berikut mengasumsikan adanya SqlCommand objek bernama cmd yang memilih data varbinary(max) dari tabel dan SqlDataReader objek bernama reader yang mengambil data sebagai aliran 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);  
    }  

Menggunakan GetBytes untuk Mengambil Data

Metode GetBytes dari SqlDataReader membaca aliran byte dari offset kolom yang ditentukan ke dalam array byte, dimulai pada offset array yang telah ditentukan. Fragmen kode berikut mengasumsikan sebuah objek yang bernama SqlDataReader yang mengambil byte ke 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 dari SqlDataReader membaca nilai dari offset kolom yang ditentukan ke dalam sebuah array. Fragmen kode berikut mengasumsikan SqlDataReader objek bernama reader yang mengambil data biner dari offset kolom pertama, dan data string 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 Tipe Nilai Besar ke Tipe CLR

Anda dapat mengonversi konten varchar(max) kolom atau nvarchar(max) menggunakan salah satu metode konversi string, seperti ToString. Fragmen kode berikut mengasumsikan SqlDataReader objek bernama reader yang mengambil data.

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 LargePhoto objek dari ProductPhoto tabel dalam AdventureWorks database dan menyimpannya ke file. Rakitan perlu dikompilasi dengan referensi ke namespace System.Drawing. Metode GetSqlBytes dari SqlDataReader mengembalikan objek SqlBytes 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($"{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

Menggunakan Parameter Tipe Nilai Besar

Jenis nilai besar dapat digunakan dalam SqlParameter objek dengan cara yang sama seperti Anda menggunakan jenis nilai yang lebih kecil dalam SqlParameter objek. Anda dapat mengambil tipe nilai besar dalam bentuk SqlParameter seperti yang ditunjukkan dalam contoh berikut. Kode mengasumsikan bahwa prosedur tersimpan GetDocumentSummary berikut ada di database sampel AdventureWorks. Prosedur tersimpan mengambil parameter input bernama @DocumentID dan mengembalikan konten kolom DocumentSummary dalam @DocumentSummary parameter output.

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 SqlConnection dan SqlCommand objek untuk menjalankan prosedur tersimpan GetDocumentSummary dan mengambil ringkasan dokumen, yang disimpan sebagai jenis nilai besar. Kode meneruskan nilai untuk @DocumentID parameter input, dan menampilkan hasil yang diteruskan kembali dalam @DocumentSummary parameter output 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

Lihat juga

• Data Biner dan Large-Value SQL Server
• Pemetaan Tipe Data SQL Server
• Operasi Data SQL Server di ADO.NET
• Gambaran Umum ADO.NET