Hizkuntza

Modificar datos de valores grandes (max) en ADO.NET

Los tipos de datos de objeto grande (LOB) son aquellos que superan el tamaño máximo de fila de 8 kilobytes (KB). SQL Server proporciona un especificador max para los tipos de datos varchar, nvarchar y varbinary que permite el almacenamiento de valores tan grandes como 2^32 bytes. Las columnas de tabla y las variables de Transact-SQL pueden especificar tipos de datos varchar(max), nvarchar(max) o varbinary(max). En ADO.NET, los max tipos de datos se pueden obtener mediante DataReader y también se pueden especificar como valores de parámetros de entrada y salida sin ningún manejo especial. En el caso de los tipos de datos varchar grandes, los datos se pueden recuperar y actualizar incrementalmente.

Los tipos de datos max se pueden usar para comparaciones, como variables de Transact-SQL y para la concatenación. También se pueden usar en las cláusulas DISTINCT, ORDER BY, GROUP BY de una SELECT sentencia, así como en agregados, combinaciones y subconsultas.

Para obtener más información, consulte Uso de tipos de datos Large-Value.

Restricciones de los tipos de valor grande

Las restricciones siguientes se aplican a los tipos de datos max, que no existen para tipos de datos más pequeños:

  • Un sql_variant no puede contener un tipo de datos varchar grande.

  • No se pueden especificar columnas de varchar grandes como columna de clave en un índice. Se permiten en una columna incluida en un índice no agrupado.

  • Las columnas de varchar grandes no se pueden usar como columnas de clave de partición.

Trabajar con tipos de valor grande en Transact-SQL

La función OPENROWSET de Transact-SQL es un método único de conexión y acceso a los datos remotos. Incluye toda la información de conexión necesaria para acceder a datos remotos desde un origen de datos OLE DB. se puede hacer referencia a OPENROWSET en la cláusula FROM de una consulta como si fuera un nombre de tabla. También se puede hacer referencia a ella como la tabla de destino de una instrucción INSERT, UPDATE o DELETE, en función de las capacidades del proveedor OLE DB.

La función OPENROWSET incluye el proveedor de conjuntos de filas BULK, que permite leer los datos directamente de un archivo sin tener que cargarlos en una tabla de destino. Esto le permite usar OPENROWSET en una simple instrucción INSERT SELECT.

Los argumentos de la opción OPENROWSET BULK ofrecen un control significativo sobre dónde comienza y termina la lectura de datos, cómo tratar los errores y cómo interpretar los datos. Por ejemplo, puede especificar que el archivo de datos se lea como un conjunto de filas de una sola fila y una sola columna de tipo varbinary, varchar o nvarchar.

En el ejemplo siguiente se inserta una foto en la tabla ProductPhoto de la base de datos de ejemplo AdventureWorks. Si usa el proveedor BULK OPENROWSET, debe proporcionar la lista con nombre de columnas, incluso aunque no inserte valores en todas las columnas. En este caso, la clave principal se define como una columna de identidad y se puede omitir en la lista de columnas. Tenga en cuenta que también debe proporcionar un nombre de correlación al final de la instrucción OPENROWSET, que en este caso es ThumbnailPhoto. Esto se correlaciona con la columna de la tabla ProductPhoto en la que se carga el archivo.

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

Actualización de datos mediante UPDATE .WRITE

La instrucción Transact-SQL UPDATE tiene una nueva sintaxis WRITE para modificar el contenido de las columnas varchar(max), nvarchar(max) o varbinary(max). Esto le permite realizar actualizaciones parciales de los datos. La UPDATE .WRITE sintaxis se muestra aquí en forma abreviada:

UPDATE

{ <objeto> }

SET

{ column_name = { ( .WRITEexpresión , @Offset , @Length ) }

El WRITE método especifica que se modificará una sección del valor del column_name . La expresión es el valor que se va a copiar en column_name, mientras que @Offset es el punto inicial en el que se va a escribir la expresión y, por su parte, el argumento @Length es la longitud de la sección en la columna.

Si Entonces
La expresión está establecida en NULL @Length se omite y el valor de column_name se trunca en el elemento @Offset especificado.
@Offset es NULL. La operación de actualización anexa la expresión al final del valor de column_name existente y se omite @Length.
@Offset es mayor que la longitud del valor column_name. SQL Server devuelve un error.
@Length es NULL. La operación de actualización quita todos los datos de @Offset hasta el final del valor de column_name.

Nota:

@Offset ni @Length pueden ser un número negativo.

Ejemplo

En este ejemplo de Transact-SQL se actualiza un valor parcial en DocumentSummary, una columna nvarchar(max) de la tabla Document de la base de datos AdventureWorks. La palabra "components" se sustituye por la palabra "features" al especificar la palabra sustituta, la ubicación inicial (desplazamiento) de la palabra que se va a sustituir en los datos existentes y el número de caracteres que se va a sustituir (longitud). En el ejemplo se incluyen SELECT instrucciones antes y después de la UPDATE instrucción para comparar los resultados.

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.

Trabajar con tipos de Large-Value en ADO.NET

Use objetos SqlParameter con SqlCommand para enviar valores grandes. Recupere los resultados con SqlDataReader, o rellene los objetos DataSet o DataTable con SqlDataAdapter. Puede controlar los tipos de valor grandes igual que sus tipos de datos de valor relacionados de menor tamaño.

Uso de GetSqlBytes para recuperar datos

Para recuperar el contenido de una varbinary(max) columna como SqlBytes, llame a GetSqlBytes. En el fragmento siguiente se supone que cmd es el SqlCommand que selecciona los datos binarios y reader es el SqlDataReader que lo recupera.

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

Uso de GetSqlChars para recuperar datos

Para recuperar el contenido de una varchar(max) columna o nvarchar(max) , llame a GetSqlChars. En el fragmento siguiente se da por supuesto que cmd es el SqlCommand que selecciona los datos de nvarchar(max) y reader es el SqlDataReader que los recupera.

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

Uso de GetSqlBinary para recuperar datos

Para recuperar el contenido de una varbinary(max) columna como valor SqlBinary , llame a GetSqlBinary. En el fragmento siguiente se supone que cmd es el SqlCommand que selecciona los datos binarios y reader es el SqlDataReader que los recupera.

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

Uso de GetBytes para recuperar datos

El método GetBytes lee bytes desde el desplazamiento especificado de la columna en una matriz de bytes en el desplazamiento especificado de la matriz. En el fragmento siguiente se supone que reader es el SqlDataReader que recupera los bytes. A diferencia de GetSqlBytes, GetBytes requiere un tamaño para el ArrayBuffer.

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

Uso de GetValue para recuperar datos

El GetValue método devuelve el valor de columna como un objeto . En el fragmento siguiente se supone que reader es el SqlDataReader. En C#, la columna binaria devuelve byte[] y la columna de caracteres devuelve string. En Visual Basic, los tipos correspondientes son Byte() y String.

While reader.Read()
    ' Read the data from varbinary(max) column
    Dim binaryData() As Byte = CType(reader.GetValue(0), Byte())

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

Conversión de tipos de datos grandes a tipos CLR

Puede convertir el contenido de una columna varchar(max) o nvarchar(max) con métodos de conversión de cadenas, como ToString. En el fragmento siguiente se da por supuesto que reader es el SqlDataReader que recupera los datos.

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

Ejemplo

El código siguiente recupera el nombre y el valor LargePhoto de la tabla ProductPhoto de la base de datos AdventureWorks y lo guarda en un archivo. Este ejemplo de conversión de imágenes requiere Windows y el System.Drawing.Common paquete NuGet. Esa restricción se aplica a la conversión de imágenes, no a la recuperación de datos binarios del proveedor. El GetSqlBytes método devuelve SqlBytes, que expone la Stream propiedad . El código usa el flujo para crear el objeto Bitmap y lo guarda en formato GIF.

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

Usar parámetros de tipos de valor grande

Los tipos de valores grandes se pueden usar en objetos SqlParameter de la misma manera en que se usan los tipos de valores más pequeños en objetos SqlParameter. Puede recuperar tipos de valor grande como valores SqlParameter, como se muestra en el ejemplo siguiente. El código supone que el siguiente procedimiento almacenado GetDocumentSummary existe en la base de datos de ejemplo AdventureWorks. El procedimiento almacenado toma un parámetro de entrada denominado @DocumentID y devuelve el contenido de la columna DocumentSummary en el parámetro de salida @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

Ejemplo

El código ADO.NET crea objetos SqlConnection y SqlCommand para ejecutar el procedimiento almacenado GetDocumentSummary y recuperar el resumen del documento, que se almacena como un tipo de valores grandes. El código pasa un valor para el parámetro de entrada @DocumentID y muestra los resultados que se han vuelto a pasar en el parámetro de salida @DocumentSummary de la ventana Consola.

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

Consulte también

  • Azken eguneratzearen ordua: