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.
SQL Server 2000 |
SQL Server 2005 |
SQL Server 2008 |
---|---|---|
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.
Si |
Entonces |
---|---|
La expresión se establece en NULL |
@Length se omite y el valor de column_name se trunca en el valor de @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 de 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 |
---|
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)