Bagikan melalui

Memodifikasi data bernilai besar (maks) dalam ADO.NET

Mengunduh 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). Di .NET, max jenis data 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.

Lihat Menggunakan Jenis Data Bernilai Besar dari SQL Server Books Online detail selengkapnya tentang jenis data bernilai besar.

Pembatasan jenis nilai besar

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

  • sql_variant tidak boleh berisi jenis data besar varchar.

  • Kolom besar varchar tidak dapat ditentukan sebagai kolom kunci dalam indeks. Kolom ini diizinkan dalam kolom yang disertakan dalam indeks nonkluster.

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

Bekerja dengan jenis nilai besar di Transact-SQL

Fungsi Transact-SQL OPENROWSET adalah metode satu kali untuk menyambungkan dan mengakses data jarak jauh. OPENROWSET dapat direferensi dalam klausul DARI kueri seolah-olah merupakan nama tabel. Ini juga dapat dirujuk sebagai tabel target pernyataan INSERT, UPDATE, atau DELETE.

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. Untuk sintaks dan opsi lengkap, lihat SQL Server Books Online.

Contoh berikut menyisipkan foto ke dalam tabel ProductPhoto dalam AdventureWorks2025 database sampel. 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 . MENULIS

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 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.

Bekerja dengan jenis nilai besar di 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())  
    {  
        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())  
{  
    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())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

Menggunakan GetBytes 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())  
{  
    byte[] buffer = new byte[4000];  
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);  
}

Menggunakan GetValue untuk mengambil data

Metode GetValueSqlDataReader 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  
    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())  
{  
     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.

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";
    }
}

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 mengasumsikan bahwa prosedur tersimpan GetDocumentSummary berikut ada dalam AdventureWorks2025 database sampel. 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.

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";
    }
}

Langkah berikutnya

  • Last updated on