Share via

Gegevens met een grote waarde (max) wijzigen in ADO.NET

ADO.NET downloaden

Grote objectgegevenstypen (LOB) zijn gegevenstypen die groter zijn dan de maximale rijgrootte van 8 kB (kB). SQL Server biedt een max aanduiding voor varchar, nvarcharen varbinary gegevenstypen om de opslag van waarden zo groot als 2^32 bytes mogelijk te maken. Tabelkolommen en Transact-SQL variabelen kunnen varchar(max), nvarchar(max), of varbinary(max) gegevenstypen opgeven. In .NET kunnen de max gegevenstypen worden opgehaald door een DataReader, en kunnen ook worden opgegeven als invoer- en uitvoerparameterwaarden zonder speciale verwerking. Voor grote varchar gegevenstypen kunnen gegevens incrementeel worden opgehaald en bijgewerkt.

De max gegevenstypen kunnen worden gebruikt voor vergelijkingen, zoals Transact-SQL variabelen en voor samenvoeging. Ze kunnen ook worden gebruikt in de DISTINCT-, ORDER BY-, GROUP BY-componenten van een SELECT-instructie en in aggregaties, joins en subquery's.

Zie Het gebruik van Large-Value gegevenstypen van SQL Server Books Online voor meer informatie over gegevenstypen met een grote waarde.

Beperkingen voor typen met grote waarde

De volgende beperkingen gelden voor de max gegevenstypen, die niet bestaan voor kleinere gegevenstypen:

  • Een sql_variant kan geen groot varchar gegevenstype bevatten.

  • Grote varchar kolommen kunnen niet worden opgegeven als een sleutelkolom in een index. Ze zijn toegestaan in een opgenomen kolom in een niet-geclusterde index.

  • Grote varchar kolommen kunnen niet worden gebruikt als partitioneringssleutelkolommen.

Werken met grootwaardetypen in Transact-SQL

De functie Transact-SQL OPENROWSET is een eenmalige methode voor het verbinden en openen van externe gegevens. OPENROWSET kan worden verwezen in de FROM-component van een query alsof het een tabelnaam is. Er kan ook naar worden verwezen als de doeltabel van de instructie INSERT, UPDATE of DELETE.

De OPENROWSET functie bevat de BULK rijensetprovider, waarmee u gegevens rechtstreeks vanuit een bestand kunt lezen zonder de gegevens in een doeltabel te laden. Hiermee kunt u OPENROWSET gebruiken in een eenvoudige INSERT SELECT-instructie.

De OPENROWSET BULK optieargumenten bieden aanzienlijke controle over waar het lezen van gegevens moet beginnen en eindigen, hoe fouten moeten worden behandeld en hoe gegevens worden geïnterpreteerd. U kunt bijvoorbeeld opgeven dat het gegevensbestand wordt gelezen als een rij met één rij, een rijset met één kolom van het type varbinary, varcharof nvarchar. Zie SQL Server Books Online voor de volledige syntaxis en opties.

In het volgende voorbeeld wordt een foto ingevoegd in de tabel ProductPhoto in de AdventureWorks2025 voorbeelddatabase. Wanneer u de BULK OPENROWSET provider gebruikt, moet u de benoemde lijst met kolommen opgeven, zelfs als u geen waarden in elke kolom invoegt. De primaire sleutel in dit geval wordt gedefinieerd als een identiteitskolom en kan worden weggelaten uit de kolomlijst. Houd er rekening mee dat u ook een correlatienaam moet opgeven aan het einde van de OPENROWSET instructie, in dit geval ThumbnailPhoto. Dit correleert met de kolom in de ProductPhoto tabel waarin het bestand wordt geladen.

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

Gegevens bijwerken met UPDATE. SCHRIJVEN

De instructie Transact-SQL UPDATE heeft een nieuwe WRITE-syntaxis voor het wijzigen van de inhoud van varchar(max), nvarchar(max)of varbinary(max) kolommen. Hiermee kunt u gedeeltelijke updates van de gegevens uitvoeren. De UPDATE .WRITE-syntaxis wordt hier in verkorte vorm weergegeven:

UPDATE

{ <object> }

SET

{ column_name = { .WRITE ( expressie , @Offset , @Length ) }

De WRITE-methode geeft aan dat een sectie van de waarde van de column_name wordt gewijzigd. De expressie is de waarde die wordt gekopieerd naar de column_name, het is het @Offset beginpunt waarop de expressie wordt geschreven en het @Length argument is de lengte van de sectie in de kolom.

Als Dan
De expressie is ingesteld op NULL @Length wordt genegeerd en de waarde in column_name wordt afgekapt op de opgegeven @Offset.
@Offset is NULL De updatebewerking voegt de expressie toe aan het einde van de bestaande column_name-waarde en @Length wordt genegeerd.
@Offset groter is dan de lengte van de column_name waarde SQL Server retourneert een fout.
@Length is NULL De updatebewerking verwijdert alle gegevens van @Offset tot het einde van de column_name waarde.

Opmerking

@Offset noch @Length kunnen een negatief getal zijn.

Example

In dit Transact-SQL voorbeeld wordt een gedeeltelijke waarde in DocumentSummary bijgewerkt, een nvarchar(max) kolom in de tabel Document in de AdventureWorks-database. Het woord 'onderdelen' wordt vervangen door het woord 'functies' door het vervangende woord aan te geven, de beginpositie (offset) van het te vervangen woord in de bestaande gegevens en het aantal tekens dat moet worden vervangen (lengte). Het voorbeeld bevat SELECT-instructies voor en na de UPDATE-instructie om resultaten te vergelijken.

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.

Werken met grootschalige waardetypen in ADO.NET

U kunt met grote waardetypen in ADO.NET werken door grote waardetypen op te geven als SqlParameter-objecten in een SqlDataReader om een resultatenset terug te geven, of door een SqlDataAdapter te gebruiken om een DataSet/DataTable-waarden op te vullen. Er is geen verschil tussen de manier waarop u met een groot waardetype en het bijbehorende, kleinere waardegegevenstype werkt.

GetSqlBytes gebruiken om gegevens op te halen

De GetSqlBytes methode van de SqlDataReader methode kan worden gebruikt om de inhoud van een varbinary(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat er een object met de naam SqlCommand is dat gegevens uit een tabel selecteert, en een ander object met de naam cmd dat de gegevens ophaalt als SqlBytes.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBytes bytes = reader.GetSqlBytes(0);  
    }

GetSqlChars gebruiken om gegevens op te halen

De GetSqlChars methode van de SqlDataReader methode kan worden gebruikt om de inhoud van een varchar(max) of nvarchar(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlCommand naam cmd gegevens uit een tabel selecteert nvarchar(max) en een SqlDataReader object met de naam reader waarmee de gegevens worden opgehaald.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
{  
    SqlChars buffer = reader.GetSqlChars(0);  
}

GetSqlBinary gebruiken om gegevens op te halen

De GetSqlBinary methode van een SqlDataReader kan worden gebruikt om de inhoud van een varbinary(max) kolom op te halen. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlCommand naam cmd gegevens uit een tabel selecteert varbinary(max) en een SqlDataReader object met de naam reader waarmee de gegevens als een SqlBinary stroom worden opgehaald.

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
while (reader.Read())  
    {  
        SqlBinary binaryStream = reader.GetSqlBinary(0);  
    }

GetBytes gebruiken om gegevens op te halen

De GetBytes methode van een SqlDataReader leest een stroom van bytes vanaf de opgegeven kolom-offset in een byte-array beginnend bij de opgegeven array-offset. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlDataReader naam reader bytes in een bytematrix ophaalt. Houd er rekening mee dat, in tegenstelling tot GetSqlBytes, GetBytes een grootte voor de array-buffer vereist.

while (reader.Read())  
{  
    byte[] buffer = new byte[4000];  
    long byteCount = reader.GetBytes(1, 0, buffer, 0, 4000);  
}

GetValue gebruiken om gegevens op te halen

De GetValue methode van een SqlDataReader leest de waarde van de opgegeven kolomverschuiving in een array. In het volgende codefragment wordt ervan uitgegaan dat een object met de SqlDataReader naam reader binaire gegevens ophaalt uit de eerste kolom offset en vervolgens tekenreeksgegevens uit de tweede kolom offset.

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

Converteren van grote waardetypen naar CLR-typen

U kunt de inhoud van een varchar(max)- of nvarchar(max)-kolom converteren met behulp van een van de methoden voor tekenreeksconversie, zoals ToString. In het volgende codefragment wordt ervan uitgegaan dat er een SqlDataReader-object is waarmee de gegevens worden opgehaald.

while (reader.Read())  
{  
     string str = reader[0].ToString();  
     Console.WriteLine(str);  
}

Example

Met de volgende code worden de naam en het LargePhoto object opgehaald uit de ProductPhoto tabel in de AdventureWorks database en opgeslagen in een bestand. De assembly moet worden gecompileerd met een verwijzing naar de System.Drawing naamruimte. De GetSqlBytes methode van de SqlDataReader functie retourneert een SqlBytes object dat een Stream eigenschap beschikbaar maakt. De code gebruikt dit om een nieuw Bitmap object te maken en slaat het vervolgens op in 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";
    }
}

Parameters van een type met grote waarde gebruiken

Grote waardetypen kunnen op dezelfde manier worden gebruikt in SqlParameter objecten als u kleinere waardetypen in SqlParameter objecten gebruikt. U kunt grote waardetypen ophalen als SqlParameter waarden, zoals wordt weergegeven in het volgende voorbeeld. In de code wordt ervan uitgegaan dat de volgende opgeslagen procedure GetDocumentSummary bestaat in de AdventureWorks2025 voorbeelddatabase. De opgeslagen procedure gebruikt een invoerparameter met de naam @DocumentID en retourneert de inhoud van de kolom DocumentSummary in de @DocumentSummary uitvoerparameter.

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

Example

De ADO.NET code maakt SqlConnection en SqlCommand objecten om de opgeslagen procedure GetDocumentSummary uit te voeren en haalt de documentsamenvatting op, die is opgeslagen als een groot waardetype. De code geeft een waarde door voor de @DocumentID invoerparameter en geeft de resultaten weer die zijn doorgestuurd in de @DocumentSummary uitvoerparameter in het consolevenster.

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

Volgende stappen

  • Last updated on