Modyfikowanie dużej wartości (wartość maksymalna) danych w ADO.NET
Typy danych dużych obiektów (LOB) to te, które przekraczają maksymalny rozmiar wiersza wynoszący 8 kilobajtów (KB). Program SQL Server udostępnia max specyfikator typów varchardanych , nvarchari varbinary w celu umożliwienia przechowywania wartości o rozmiarze 2^32 bajtów. Kolumny tabeli i zmienne Języka Transact-SQL mogą określać varchar(max)typy danych , nvarchar(max)lub varbinary(max) . W ADO.NET max typy danych można pobierać za pomocą DataReaderelementu , a także można określić jako zarówno wartości parametrów wejściowych, jak i wyjściowych bez specjalnej obsługi. W przypadku dużych varchar typów danych dane można pobierać i aktualizować przyrostowo.
Typy max danych mogą służyć do porównań, jako zmiennych Języka Transact-SQL i łączenia. Mogą być również używane w klauzulach DISTINCT, ORDER BY, GROUP BY instrukcji SELECT, a także w agregacjach, sprzężeniach i podzapytaniach.
Aby uzyskać więcej informacji, zobacz Using Large-Value Data Types (Używanie typów danych o dużej wartości).
Ograniczenia typu dużych wartości
Następujące ograniczenia dotyczą max typów danych, które nie istnieją w przypadku mniejszych typów danych:
Element
sql_variantnie może zawierać dużegovarchartypu danych.Nie można określić dużych
varcharkolumn jako kolumny klucza w indeksie. Są one dozwolone w dołączonej kolumnie w indeksie nieklasowanym.Nie można używać dużych
varcharkolumn jako kolumn kluczy partycjonowania.
Praca z typami dużych wartości w języku Transact-SQL
Funkcja Transact-SQL OPENROWSET jest jednorazową metodą nawiązywania połączenia i uzyskiwania dostępu do danych zdalnych. Zawiera wszystkie informacje o połączeniu niezbędne do uzyskania dostępu do danych zdalnych ze źródła danych OLE DB. OPENROWSET można odwoływać się do klauzuli FROM zapytania, tak jakby była to nazwa tabeli. Można go również przywoływać jako tabelę docelową instrukcji INSERT, UPDATE lub DELETE, z zastrzeżeniem możliwości dostawcy OLE DB.
Funkcja OPENROWSET zawiera dostawcę BULK zestawu wierszy, który umożliwia odczytywanie danych bezpośrednio z pliku bez ładowania danych do tabeli docelowej. Dzięki temu można użyć OPENROWSET prostej instrukcji INSERT SELECT.
OPENROWSET BULK Argumenty opcji zapewniają znaczącą kontrolę nad tym, gdzie rozpocząć i zakończyć odczytywanie danych, jak radzić sobie z błędami i jak dane są interpretowane. Można na przykład określić, że plik danych będzie odczytywany jako zestaw wierszy z jedną kolumną typu varbinary, varcharlub nvarchar.
Poniższy przykład wstawia zdjęcie do tabeli ProductPhoto w przykładowej bazie danych AdventureWorks. W przypadku korzystania z BULK OPENROWSET dostawcy należy podać nazwaną listę kolumn, nawet jeśli nie wstawiasz wartości do każdej kolumny. Klucz podstawowy w tym przypadku jest definiowany jako kolumna tożsamości i może zostać pominięty z listy kolumn. Należy również podać nazwę korelacji na końcu OPENROWSET instrukcji , która w tym przypadku to ThumbnailPhoto. Jest to skorelowane z kolumną w ProductPhoto tabeli, do której jest ładowany plik.
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
Aktualizowanie danych przy użyciu aktualizacji UPDATE. NAPISZ
Instrukcja Transact-SQL UPDATE ma nową składnię WRITE do modyfikowania zawartości varchar(max)kolumn , nvarchar(max)lub varbinary(max) . Umożliwia to wykonywanie częściowych aktualizacji danych. Update . Składnia WRITE jest wyświetlana tutaj w postaci skróconej:
UPDATE
{ <object> }
SET
{ column_name = { . WRITE ( wyrażenie , , @Length @Offset ) }
Metoda WRITE określa, że sekcja wartości column_name zostanie zmodyfikowana. Wyrażenie jest wartością, która zostanie skopiowana do column_name, jest punktem początkowym, @Offset w którym zostanie zapisane wyrażenie, a @Length argument jest długością sekcji w kolumnie.
| If | Następnie |
|---|---|
| Wyrażenie jest ustawione na wartość NULL | @Length jest ignorowany, a wartość w column_name jest obcięta w określonym @Offsetobiekcie . |
@Offset ma wartość NULL |
Operacja aktualizacji dołącza wyrażenie na końcu istniejącej wartości column_name i @Length jest ignorowane. |
@Offset jest większa niż długość wartości column_name |
Program SQL Server zwraca błąd. |
@Length ma wartość NULL |
Operacja aktualizacji usuwa wszystkie dane z @Offset do końca column_name wartości. |
Uwaga
Ani nie @Offset @Length może być liczbą ujemną.
Przykład
Ten przykład języka Transact-SQL aktualizuje wartość częściową w dokumencie DocumentSummary, nvarchar(max) kolumnę w tabeli Document w bazie danych AdventureWorks. Wyraz "components" jest zastępowany słowem "features" przez określenie wyrazu zastępczego, lokalizację początkową (przesunięcie) wyrazu, który ma zostać zastąpiony w istniejących danych, oraz liczbę znaków do zastąpienia (długość). Przykład zawiera instrukcje SELECT przed instrukcją UPDATE i po nim, aby porównać wyniki.
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.
Praca z typami dużych wartości w ADO.NET
W ADO.NET można pracować z dużymi typami wartości, określając duże typy wartości jako SqlParameter obiekty w SqlDataReader obiekcie w celu zwrócenia zestawu wyników lub za pomocą elementu SqlDataAdapter , aby wypełnić DataSet/DataTableelement . Nie ma różnicy między sposobem pracy z dużym typem wartości a powiązanym, mniejszym typem danych wartości.
Pobieranie danych przy użyciu polecenia GetSqlBytes
Metoda GetSqlBytes SqlDataReader metody może służyć do pobierania zawartości varbinary(max) kolumny. Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera varbinary(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane jako 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);
}
Pobieranie danych przy użyciu polecenia GetSqlChars
Metoda GetSqlChars SqlDataReader metody może służyć do pobierania zawartości varchar(max) kolumny lub nvarchar(max) . Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera nvarchar(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane.
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);
}
Pobieranie danych przy użyciu metody GetSqlBinary
Metoda GetSqlBinary elementu SqlDataReader może służyć do pobierania zawartości varbinary(max) kolumny. Poniższy fragment kodu zakłada SqlCommand obiekt o nazwie cmd , który wybiera varbinary(max) dane z tabeli i SqlDataReader obiekt o nazwie reader , który pobiera dane jako SqlBinary strumień.
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);
}
Pobieranie danych przy użyciu funkcji GetBytes
GetBytes Metoda SqlDataReader odczytywania strumienia bajtów z określonej kolumny przesunięcia do tablicy bajtów rozpoczynającej się od określonego przesunięcia tablicy. Poniższy fragment kodu zakłada SqlDataReader obiekt o nazwie reader , który pobiera bajty do tablicy bajtów. Należy pamiętać, że w przeciwieństwie do GetSqlBytesGetBytes parametru , wymaga rozmiaru buforu tablicy.
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);
}
Pobieranie danych przy użyciu polecenia GetValue
GetValue Metoda SqlDataReader elementu odczytuje wartość z określonej kolumny przesunięcia do tablicy. Poniższy fragment kodu zakłada SqlDataReader obiekt o nazwie reader , który pobiera dane binarne z pierwszego przesunięcia kolumny, a następnie dane ciągu z przesunięcia drugiej kolumny.
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);
}
Konwertowanie z dużych typów wartości na typy CLR
Zawartość kolumny lub można przekonwertować varchar(max) przy użyciu dowolnej z metod konwersji ciągów, takich jak ToString.nvarchar(max) Poniższy fragment kodu zakłada SqlDataReader obiekt o nazwie reader , który pobiera dane.
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);
}
Przykład
Poniższy kod pobiera nazwę i LargePhoto obiekt z ProductPhoto tabeli w AdventureWorks bazie danych i zapisuje go w pliku. Zestaw należy skompilować przy użyciu odwołania do System.Drawing przestrzeni nazw. Metoda GetSqlBytes zwraca SqlDataReader SqlBytes obiekt, który uwidacznia Stream właściwość. Kod używa go do utworzenia nowego Bitmap obiektu, a następnie zapisuje go w pliku GIF ImageFormat.
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("{0} is unavailable.", photoName);
}
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 {0}.", 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
Używanie parametrów typu dużej wartości
Duże typy wartości mogą być używane w obiektach w SqlParameter taki sam sposób, jak w przypadku mniejszych typów wartości w SqlParameter obiektach. Możesz pobrać duże typy wartości jako SqlParameter wartości, jak pokazano w poniższym przykładzie. W kodzie przyjęto założenie, że w przykładowej bazie danych AdventureWorks istnieje następująca procedura składowana GetDocumentSummary. Procedura składowana przyjmuje parametr wejściowy o nazwie @DocumentID i zwraca zawartość kolumny DocumentSummary w parametrze wyjściowym @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
Przykład
Kod ADO.NET tworzy SqlConnection obiekty i SqlCommand służące do wykonywania procedury składowanej GetDocumentSummary i pobierania podsumowania dokumentu przechowywanego jako duży typ wartości. Kod przekazuje wartość parametru wejściowego @DocumentID i wyświetla wyniki przekazane z powrotem w parametrze wyjściowym @DocumentSummary w oknie Konsola.
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