Modificación de datos de valores grandes (max) en ADO.NET

En las versiones de SQL Server anteriores a SQL Server 2005 era necesario un control especial para trabajar con tipos de datos de objetos grandes (LOB). Los tipos de datos LOB son aquellos que superan el tamaño máximo de fila de 8 kilobytes (KB). SQL Server 2005 incorporó 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 nuevos tipos de datos max se pueden obtener mediante DataReader, y también se pueden especificar como valores de parámetros de entrada y salida sin ningún control especial. En el caso de tipos de datos varchar grandes, los datos se pueden recuperar y actualizar de manera incremental.

Los tipos de datos max se pueden utilizar para las 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 instrucción SELECT, así como en agregados, combinaciones y subconsultas.

La tabla siguiente proporciona vínculos a la documentación de los Libros en pantalla de SQL Server.

Restricciones de los tipos de valor grande

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

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

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

• Las columnas varchar grandes no se pueden utilizar como columnas de claves de partición.

Trabajar con tipos de valor grande en Transact-SQL

La función OPENROWSET de Transact-SQL es un método de un solo uso para la conexión y el acceso a datos remotos. Incluye toda la información de conexión necesaria para tener acceso 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 fuese un nombre de tabla. y como si fuera la tabla de destino de una instrucción INSERT, UPDATE o DELETE, sujeta a las capacidades del proveedor OLE DB.

La función OPENROWSET se ha mejorado en SQL Server 2005 con la incorporación del 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 permite usar OPENROWSET en una instrucción INSERT SELECT simple.

Los argumentos de la opción OPENROWSETBULK ofrecen un control significativo sobre dónde comienza y termina la lectura de datos, así como sobre la forma en que se tratan los errores y la interpretación de los datos. Por ejemplo, puede especificar que el archivo de datos se lea como un conjunto de filas con una única fila y una única columna del tipo varbinary, varchar o nvarchar. Para obtener la sintaxis y las opciones completas, vea los Libros en pantalla de SQL Server.

En el siguiente ejemplo se inserta una foto en la tabla ProductPhoto de la base de datos de ejemplo AdventureWorks. Si usa el proveedor BULKOPENROWSET, debe suministrar 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 podría omitirse de la lista de columnas. Tenga en cuenta que también debe suministrar un nombre de correlación al final de la instrucción OPENROWSET, que en este caso es ThumbnailPhoto. Éste 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

Actualizar datos mediante UPDATE .WRITE

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

UPDATE

{ <object> }

SET

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

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

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

Ejemplo

En este ejemplo de Transact-SQL se actualiza un valor parcial de DocumentSummary, una columna nvarchar(max) de la tabla Document de la base de datos AdventureWorks. La palabra 'components' se sustituye por la palabra 'features' mediante la especificación de la palabra de sustitución, la ubicación de comienzo (desplazamiento) de la palabra que se va a sustituir en los datos existentes y el número de caracteres que se van a sustituir (longitud). El ejemplo incluye instrucciones SELECT antes y después de la instrucción UPDATE 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 valor grande en ADO.NET

Puede trabajar con tipos de valor grande en ADO.NET si los especifica como objetos SqlParameter en SqlDataReader para que devuelvan un conjunto de resultados o mediante el uso de SqlDataAdapter para llenar DataSet/DataTable. No existe ninguna diferencia entre la forma de trabajar con un tipo de valor grande y su tipo de datos de valor más pequeño relacionado.

Uso de GetSqlBytes para recuperar datos

El método GetSqlBytes del SqlDataReader se puede utilizar para recuperar el contenido de una columna varbinary(max). El siguiente fragmento de código asume un objeto SqlCommand llamado cmd que selecciona datos varbinary(max) de una tabla y un objeto SqlDataReader llamado reader que recupera los datos como 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);
    }

Uso de GetSqlChars para recuperar datos

El método GetSqlChars del SqlDataReader se puede utilizar para recuperar el contenido de una columna varchar(max) o nvarchar(max). El siguiente fragmento de código asume un objeto SqlCommand llamado cmd que selecciona datos nvarchar(max) de una tabla y un objeto SqlDataReader llamado reader que recupera los datos.

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

El método GetSqlBinary de un SqlDataReader se puede utilizar para recuperar el contenido de una columna varbinary(max). El siguiente fragmento de código asume un objeto SqlCommand llamado cmd que selecciona datos varbinary(max) de una tabla y un objeto SqlDataReader llamado reader que recupera los datos como una secuencia 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);
    }

Uso de GetBytes para recuperar datos

El método GetBytes de un SqlDataReader lee una secuencia de bytes desde el desplazamiento de columna especificado a una matriz de bytes a partir del desplazamiento de matriz especificado. El siguiente fragmento de código asume un objeto SqlDataReader llamado reader que recupera bytes en una matriz de bytes. Tenga en cuenta que, a diferencia de GetSqlBytes, GetBytes necesita un tamaño para el búfer de la matriz.

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 método GetValue de un SqlDataReader lee el valor desde el desplazamiento de columna especificado a una matriz. El siguiente fragmento de código asume un objeto SqlDataReader llamado reader que recupera datos binarios del primer desplazamiento de columna y, a continuación, datos de cadena del segundo desplazamiento de columna.

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

Conversión de tipos de valor grande a tipos de CLR

Puede convertir el contenido de una columna varchar(max) o nvarchar(max) mediante cualquiera de los métodos de conversión de cadenas, como ToString. El siguiente fragmento de código asume un objeto SqlDataReader llamado reader 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 objeto LargePhoto de la tabla ProductPhoto de la base de datos AdventureWorks y lo guarda en un archivo. Es necesario compilar el ensamblado con una referencia al espacio de nombres System.Drawing. El método GetSqlBytes de SqlDataReader devuelve un objeto SqlBytes que expone una propiedad Stream. El código usa este método para crear un nuevo objeto Bitmap y guardarlo a continuación en el formato Gif ImageFormat.

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 Bitmap = _
                          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

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

Usar parámetros de tipos de valor grande

Los tipos de valor grande se pueden emplear en objetos SqlParameter de la misma manera que se utilizan los tipos de valor más pequeño en objetos SqlParameter. Puede recuperar tipos de valor grande como valores SqlParameter , tal como se muestra en el ejemplo siguiente. El código asume que el siguiente procedimiento almacenado GetDocumentSummary existe en la base de datos de ejemplo AdventureWorks. El procedimiento almacenado toma un parámetro de entrada llamado @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 de ADO.NET crea objetos SqlConnection y SqlCommand para ejecutar el procedimiento almacenado GetDocumentSummary y recuperar el resumen de documento, que se encuentra almacenado como un tipo de valor grande. 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 de la consola.

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

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

Vea también

Conceptos

Asignar tipos de datos de SQL Server (ADO.NET)

Otros recursos

Datos binarios y de valores grandes de SQL Server (ADO.NET)

Operaciones de datos de SQL Server en ADO.NET