ADO.NET에서 큰 값(최대) 데이터 수정
LOB(Large Object) 데이터 형식은 최대 행 크기 8KB를 초과하는 형식입니다. SQL Server에서는 max, varchar 및 nvarchar 데이터 형식에 사용할 수 있는 varbinary 지정자를 제공하여 2^32바이트에 이르는 큰 값도 스토리지할 수 있습니다. 테이블 열과 Transact-SQL 변수는 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식을 지정할 수 있습니다. .NET에서는 max를 사용하여 DataReader 데이터 형식을 가져올 수 있을 뿐 아니라 특별한 처리 없이도 입력 및 출력 매개 변수 값을 모두 지정할 수 있습니다. 큰 varchar 데이터 형식의 경우 데이터를 증분 방식으로 검색하고 업데이트할 수 있습니다.
max 데이터 형식은 비교 및 연결 작업에 사용할 수 있으며, 비교를 수행할 경우에는 Transact-SQL 변수로 사용합니다. SELECT 문의 DISTINCT, ORDER BY, GROUP BY 절에서뿐 아니라 집계, 조인 및 하위 쿼리에도 사용할 수 있습니다.
큰 값 데이터 형식에 대한 자세한 내용은 SQL Server 온라인 설명서에서 큰 값 데이터 형식 사용을 참조하세요.
큰 값 형식 제한 사항
max 데이터 형식에는 더 작은 데이터 형식에서는 존재하지 않는 다음 제한 사항이 적용됩니다.
sql_variant에는 큰varchar데이터 형식이 포함될 수 없습니다.큰
varchar열은 인덱스에 키 열로 지정할 수 없으며 클러스터링되지 않은 인덱스에 포함된 열에는 사용할 수 있습니다.큰
varchar열은 키 열을 분할하는 데 사용할 수 없습니다.
Transact-SQL에서 큰 값 형식 사용
Transact-SQL OPENROWSET 함수는 원격 데이터 연결 및 액세스를 한 번에 실행합니다. OPENROWSET은 쿼리의 FROM 절에서 테이블 이름인 것처럼 참조할 수 있습니다. 또한 INSERT, UPDATE 또는 DELETE 문의 대상 테이블로 참조될 수도 있습니다.
OPENROWSET 함수에서는 BULK 행 집합 공급자를 추가하여 대상 테이블로 데이터를 로드하지 않고 파일에서 직접 데이터를 읽어올 수 있습니다. 따라서 간단한 INSERT SELECT 문에서 OPENROWSET을 사용할 수 있습니다.
OPENROWSET BULK 옵션 인수를 사용하면 데이터 읽기의 시작 및 종료 지점, 오류 처리 방법 및 데이터 해석 방법을 효과적으로 제어할 수 있습니다. 예를 들어, 데이터 파일을 varbinary, varchar 또는 nvarchar 형식의 단일 행 및 단일 열 행 집합으로 읽도록 지정할 수 있습니다. 전체 구문 및 옵션에 대한 자세한 내용은 SQL Server 온라인 설명서를 참조하세요.
다음 예제에서는 AdventureWorks2022 샘플 데이터베이스의 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 AdventureWorks2022;
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())
{
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())
{
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())
{
SqlBinary binaryStream = reader.GetSqlBinary(0);
}
GetBytes를 사용하여 데이터 검색
GetBytes의 SqlDataReader 메서드에서는 지정된 열 오프셋의 바이트 스트림을 지정된 배열 오프셋에서 시작하는 바이트 배열로 읽어들입니다. 다음 코드 조각에서는 바이트를 바이트 배열로 검색하는 reader라는 SqlDataReader 개체를 가정합니다. GetSqlBytes와 달리 GetBytes에는 배열 버퍼의 크기가 필요합니다.
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
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())
{
string str = reader[0].ToString();
Console.WriteLine(str);
}
예시
다음 코드에서는 LargePhoto 데이터베이스의 ProductPhoto 테이블에서 이름과 AdventureWorks 개체를 검색한 다음 이를 파일에 저장합니다. 어셈블리는 System.Drawing 네임스페이스에 대한 참조로 컴파일해야 합니다. GetSqlBytes의 SqlDataReader 메서드는 SqlBytes 속성을 노출하는 Stream 개체를 반환합니다. 코드에서는 이 개체를 사용하여 새 Bitmap 개체를 만든 ImageFormat Gif으로 저장합니다.
using Microsoft.Data.SqlClient;
using System.Data.SqlTypes;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
class Program
{
static void Main()
{
// Supply any valid DocumentID value and file path.
// The value 3 is supplied for DocumentID, and a literal
// string for the file path where the image will be saved. 1, 60
TestGetSqlBytes(7, @"c:\temp\");
Console.ReadLine();
}
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();
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connectionection string in your code,
// you can retrieve it from a configuration file, using the
// System.Configuration.ConfigurationSettings.AppSettings property
return "Data Source=(local);Initial Catalog=AdventureWorks;" +
"Integrated Security=SSPI";
}
}
큰 값 형식 매개 변수 사용
SqlParameter 개체에서 더 작은 값 형식을 사용하는 것과 같은 방법으로 SqlParameter 개체에서 큰 값 형식을 사용할 수 있습니다. 다음 예제에서처럼 큰 값 형식은 SqlParameter 값으로 검색할 수 있습니다. 이 코드에서는 AdventureWorks2022 샘플 데이터베이스에 다음 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 출력 매개 변수에 다시 전달된 결과를 표시합니다.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
// Supply any valid Document ID value.
// The value 7 is supplied for demonstration purposes.
string summaryString = GetDocumentSummary(7);
Console.ReadLine();
}
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;
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connectionection string in your code,
// you can retrieve it from a configuration file, using the
// System.Configuration.ConfigurationSettings.AppSettings property
return "Data Source=(local);Initial Catalog=AdventureWorks;" +
"Integrated Security=SSPI";
}
}
다음 단계
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기