ADO.NET에서 큰 값(최대값) 데이터 수정
LOB(Large Object) 데이터 형식은 최대 행 크기 8KB를 초과하는 형식입니다. SQL Server에서는 max, varchar 및 nvarchar 데이터 형식에 사용할 수 있는 varbinary 지정자를 제공하여 2^32바이트에 이르는 큰 값도 스토리지할 수 있습니다. 테이블 열과 Transact-SQL 변수는 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식을 지정할 수 있습니다. ADO.NET에서는 max를 사용하여 DataReader 데이터 형식을 가져올 수 있을 뿐 아니라 특별한 처리 없이도 입력 및 출력 매개 변수 값을 모두 지정할 수 있습니다. 큰 varchar 데이터 형식의 경우 데이터를 증분 방식으로 검색하고 업데이트할 수 있습니다.
max 데이터 형식은 비교 및 연결 작업에 사용할 수 있으며, 비교를 수행할 경우에는 Transact-SQL 변수로 사용합니다. SELECT 문의 DISTINCT, ORDER BY, GROUP BY 절에서뿐 아니라 집계, 조인 및 하위 쿼리에도 사용할 수 있습니다.
자세한 내용은 큰 값 데이터 형식 사용을 참조하세요.
큰 값 형식 제한 사항
max 데이터 형식에는 더 작은 데이터 형식에서는 존재하지 않는 다음 제한 사항이 적용됩니다.
sql_variant에는 큰varchar데이터 형식이 포함될 수 없습니다.큰
varchar열은 인덱스에 키 열로 지정할 수 없으며 클러스터링되지 않은 인덱스에 포함된 열에는 사용할 수 있습니다.큰
varchar열은 키 열을 분할하는 데 사용할 수 없습니다.
Transact-SQL에서 큰 값 형식 사용
Transact-SQL OPENROWSET 함수는 원격 데이터 연결 및 액세스를 한 번에 실행합니다. 이 함수에는 OLE DB 데이터 소스에서 원격 데이터에 액세스하는 데 필요한 모든 연결 정보가 들어 있습니다. OPENROWSET은 쿼리의 FROM 절에서 테이블 이름인 것처럼 참조할 수 있습니다. 또한 OLE DB 공급자의 기능에 따라 INSERT, UPDATE 또는 DELETE 문의 대상 테이블로 참조할 수도 있습니다.
OPENROWSET 함수에서는 BULK 행 집합 공급자를 추가하여 대상 테이블로 데이터를 로드하지 않고 파일에서 직접 데이터를 읽어올 수 있습니다. 따라서 간단한 INSERT SELECT 문에서 OPENROWSET을 사용할 수 있습니다.
OPENROWSET BULK 옵션 인수를 사용하면 데이터 읽기의 시작 및 종료 지점, 오류 처리 방법 및 데이터 해석 방법을 효과적으로 제어할 수 있습니다. 예를 들어, 데이터 파일을 varbinary, varchar 또는 nvarchar 형식의 단일 행 및 단일 열 행 집합으로 읽도록 지정할 수 있습니다.
다음 예제에서는 AdventureWorks 샘플 데이터베이스의 ProductPhoto 테이블에 사진을 삽입합니다. BULK OPENROWSET 공급자를 사용하는 경우 모든 열에 값을 삽입하지 않더라도 명명된 열 목록을 제공해야 합니다. 이 경우 기본 키는 ID 열로 정의되며 열 목록에서 생략할 수 있습니다. 또한 OPENROWSET 문의 끝에 상관 관계 이름을 제공해야 하며, 이 경우 ThumbnailPhoto입니다. 이렇게 하면 파일이 로드되는 ProductPhoto 테이블의 열과 연결됩니다.
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
UPDATE .WRITE를 사용하여 데이터 업데이트
Transact-SQL UPDATE 문에는 varchar(max), nvarchar(max) 또는 varbinary(max) 열의 내용을 수정하기 위한 새로운 WRITE 구문이 들어 있습니다. 이 구문을 사용하면 데이터를 부분적으로 업데이트할 수 있습니다. 다음은 약식으로 나타낸 UPDATE .WRITE 구문입니다.
UPDATE
{ <object> }
SET
{ column_name = { .WRITE ( expression , @Offset , @Length ) }
WRITE 메서드에서는 column_name 값의 일정 부분이 수정되도록 지정합니다. 식은 column_name에 복사되는 값이고, @Offset은 식이 작성되는 시작점이며, @Length 인수는 열에 있는 일정 부분의 길이입니다.
| If | 결과 |
|---|---|
| 식이 NULL로 설정된 경우 | @Length가 무시되고 column_name의 값은 지정된 @Offset에서 잘립니다. |
@Offset이 NULL인 경우 |
업데이트 작업을 통해 기존 column_name 값의 끝에 식이 추가되고 @Length는 무시됩니다. |
@Offset이 column_name 값의 길이보다 큰 경우 |
SQL Server에서 오류를 반환합니다. |
@Length이 NULL인 경우 |
업데이트 작업을 통해 @Offset부터 column_name 값 끝 사이에 있는 모든 데이터가 제거됩니다. |
참고 항목
@Offset과 @Length는 모두 음수일 수 없습니다.
예시
이 Transact-SQL 예제에서는 AdventureWorks 데이터베이스에 있는 Document 테이블의 nvarchar(max) 열인 DocumentSummary에서 값의 일부를 업데이트합니다. 'components'라는 단어는 대체 단어, 기존 데이터에서 대체할 단어의 시작 위치(오프셋) 및 대체할 문자 수(길이)를 지정하여 'features'라는 단어로 대체됩니다. 이 예제에는 결과를 비교하기 위해 UPDATE 문 앞뒤에 SELECT 문이 포함되어 있습니다.
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.
ADO.NET에서 큰 값 형식 사용
ADO.NET에서는 SqlDataReader에서 SqlParameter 개체로 지정하여 결과 집합을 반환하거나 SqlDataAdapter를 사용하여 DataSet/DataTable을 채우는 방식으로 큰 값 형식을 사용할 수 있습니다. 큰 값 형식과 관련된 작은 값의 데이터 형식을 작업하는 방식에는 차이가 없습니다.
GetSqlBytes를 사용하여 데이터 검색
GetSqlBytes의 SqlDataReader 메서드를 사용하면 varbinary(max) 열의 내용을 검색할 수 있습니다. 다음 코드 조각에서는 테이블에서 varbinary(max) 데이터를 선택하는 cmd라는 SqlCommand 개체와 SqlBytes로 데이터를 검색하는 reader라는 SqlDataReader 개체를 가정합니다.
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);
}
GetSqlChars를 사용하여 데이터 검색
GetSqlChars의 SqlDataReader 메서드를 사용하면 varchar(max) 또는 nvarchar(max) 열의 내용을 검색할 수 있습니다. 다음 코드 조각에서는 SqlCommand라는 cmd 개체는 테이블에서 nvarchar(max) 데이터를 선택하고 SqlDataReader라는 reader 개체는 데이터를 검색하는 것으로 가정합니다.
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);
}
GetSqlBinary를 사용하여 데이터 검색
GetSqlBinary의 SqlDataReader 메서드를 사용하면 varbinary(max) 열의 내용을 검색할 수 있습니다. 다음 코드 조각에서는 SqlCommand라는 cmd 개체는 테이블에서 varbinary(max) 데이터를 선택하고 SqlDataReader라는 reader 개체는 데이터를 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);
}
GetBytes를 사용하여 데이터 검색
GetBytes의 SqlDataReader 메서드에서는 지정된 열 오프셋의 바이트 스트림을 지정된 배열 오프셋에서 시작하는 바이트 배열로 읽어들입니다. 다음 코드 조각에서는 바이트를 바이트 배열로 검색하는 reader라는 SqlDataReader 개체를 가정합니다. GetSqlBytes와 달리 GetBytes에는 배열 버퍼의 크기가 필요합니다.
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);
}
GetValue를 사용하여 데이터 검색
SqlDataReader의 GetValue 메서드는 지정된 열 오프셋에서 배열로 값을 읽습니다. 다음 코드 조각에서는 SqlDataReader라는 reader 개체가 첫 번째 열 오프셋에서 이진 데이터를 검색한 다음 두 번째 열 오프셋에서 문자열 데이터를 검색하는 것으로 가정합니다.
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);
}
큰 값 형식을 CLR 형식으로 변환
varchar(max) 같은 문자열 변환 메서드를 사용하면 nvarchar(max) 또는 ToString 열의 내용을 변환할 수 있습니다. 다음 코드 조각에서는 SqlDataReader라는 reader 개체가 데이터를 검색하는 것으로 가정합니다.
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);
}
예시
다음 코드에서는 LargePhoto 데이터베이스의 ProductPhoto 테이블에서 이름과 AdventureWorks 개체를 검색한 다음 이를 파일에 저장합니다. 어셈블리는 System.Drawing 네임스페이스에 대한 참조로 컴파일해야 합니다. GetSqlBytes의 SqlDataReader 메서드는 SqlBytes 속성을 노출하는 Stream 개체를 반환합니다. 코드에서는 이 개체를 사용하여 새 Bitmap 개체를 만든 ImageFormat 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("{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
큰 값 형식 매개 변수 사용
SqlParameter 개체에서 더 작은 값 형식을 사용하는 것과 같은 방법으로 SqlParameter 개체에서 큰 값 형식을 사용할 수 있습니다. 다음 예제에서처럼 큰 값 형식은 SqlParameter 값으로 검색할 수 있습니다. 이 코드에서는 AdventureWorks 샘플 데이터베이스에 다음 GetDocumentSummary 저장 프로시저가 있는 것으로 가정합니다. 저장 프로시저는 @DocumentID라는 입력 매개 변수를 사용하고 @DocumentSummary 출력 매개 변수에 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
예시
ADO.NET 코드에서는 SqlConnection 및 SqlCommand 개체를 만들어 GetDocumentSummary 저장 프로시저를 실행하고 큰 값 형식으로 저장되는 문서 요약을 검색할 수 있습니다. 이 코드는 @DocumentID 입력 매개 변수의 값을 전달하고 콘솔 창의 @DocumentSummary 출력 매개 변수에 다시 전달된 결과를 표시합니다.
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