연결 클래스

연결 클래스는 고정된 사용 패턴을 가지며, 가장 일반적인 낙관적 패턴은 다음과 같습니다:

using (var dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();

    // 데이터베이스 작업 수행

}

비관적이고 방어적인 프로그래밍 방식에서는 using 문 끝에 연결을 명시적으로 닫고, 전체 using 문에 대한 데이터베이스 예외를 처리합니다:

try
{
    using (var dbConn = new SqlConnection(connectionString))
    {
        dbConn.Open();

        // 데이터베이스 작업 수행

        dbConn.Close();
    }
}
catch (SqlException ex)
{
    throw;
}

트랜잭션

여러 문을 사용하여 데이터베이스를 수정할 때, 이러한 모든 문을 하나의 단위로 처리해야 합니다. 모든 수정 사항이 성공하면 커밋되고, 하나라도 실패하면 어떤 수정 사항도 적용되지 않고 롤백됩니다. 트랜잭션을 사용하려면 System.Transactions 어셈블리를 참조해야 합니다:

using (var txScope = new TransactionScope())
{
    using (var dbConn = new SqlConnection(connectionString))
    {
        dbConn.Open();
        // 데이터베이스 작업 수행
    }

    txScope.Complete();
}

여기서 TransactionScope는 기본 생성자를 사용하며, 이는 TransactionScopeOption.Required, IsolationLevel.Serializable, TimeOut=1분을 의미합니다.

트랜잭션은 scope.Complete()를 호출하여 명시적으로 완료로 표시됩니다. 이 호출이 없으면 트랜잭션은 롤백됩니다.

트랜잭션 범위 내에 여러 연결이 있을 수 있으며, 이들은 모두 하나의 단위로 처리됩니다.

트랜잭션 격리 수준:

격리 수준	설명
ReadCommitted	이미 커밋된 데이터 읽기
ReadUncommitted	아직 커밋되지 않은 데이터 읽기
RepeatableRead	반복 가능한 읽기
Serializable	직렬화 가능한 읽기

명령 실행

데이터베이스 연결을opened 후, 실행할 명령을 정의해야 합니다. SQL Server에서 사용 가능한 명령 유형은 텍스트와 저장 프로시저 두 가지입니다.

텍스트 유형 명령을 정의하는 방법은 다음과 같습니다. 텍스트 유형이 기본값이므로 명령 유형을 명시적으로 지정할 필요가 없습니다:

var query = "SELECT CustomerName FROM Orders";
var command = new SqlCommand(query, dbConn);

저장 프로시저 유형 명령을 정의합니다:

var command = new SqlCommand("GetOrderHistory", dbConn) 
{ 
    CommandType = CommandType.StoredProcedure 
};
command.Parameters.AddWithValue("@CustomerID", "ALFKI");

명령 실행 메서드

명령을 정의한 후에는 실행해야 합니다. 반환 결과에 따라 세 가지 실행 방법이 있습니다.

ExecuteNonQuery: 일반적으로 UPDATE, INSERT, 또는 DELETE 문에 사용됩니다. 명령을 실행하고 영향을 받은 행 수를 반환합니다:

var command = new SqlCommand("GetOrderHistory", dbConn)
{
    CommandType = CommandType.StoredProcedure
};
command.Parameters.AddWithValue("@CustomerID", "ALFKI");
var affectedRows = command.ExecuteNonQuery();

ExecuteReader: 명령을 실행하고 데이터 리더 객체를 반환합니다:

var query = "SELECT CustomerName, CompanyName FROM Orders";
var command = new SqlCommand(query, dbConn);
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
    Console.WriteLine("Customer: {0,-20} Company: {1}",
                        dataReader[0], dataReader[1]);
}

ExecuteScalar: 단일 값을 반환하는 명령을 실행합니다:

var query = "SELECT COUNT(*) FROM Orders";
var command = new SqlCommand(query, dbConn);
var orderCount = (int)command.ExecuteScalar();
Console.WriteLine(orderCount);

ExecuteScalar 메서드는 object를 반환하므로 실제 유형으로 변환해야 합니다.

저장 프로시저 호출

값을 반환하지 않는 저장 프로시저 호출

두 가지 예를 보여드리겠습니다. Northwind 데이터베이스의 Region 테이블을 사용합니다.

레코드 업데이트: 저장 프로시저 정의:

CREATE PROCEDURE UpdateRegion (@RegionID INTEGER,
                               @RegionDescription NCHAR(50)) AS
   SET NOCOUNT OFF
   UPDATE Region
      SET RegionDescription = @RegionDescription
      WHERE RegionID = @RegionID
GO

이전과 같이 명령을 정의하고 매개변数和 값을 추가합니다:

var command = new SqlCommand("UpdateRegion", dbConn) 
{ 
    CommandType = CommandType.StoredProcedure 
};

command.Parameters.AddWithValue("@RegionID", 50);
command.Parameters.AddWithValue("@RegionDescription", "Northern Region");

command.ExecuteNonQuery();

레코드 삭제: 저장 프로시저 정의:

CREATE PROCEDURE DeleteRegion (@RegionID INTEGER) AS
   SET NOCOUNT OFF
   DELETE FROM Region
   WHERE RegionID = @RegionID
GO

명령 정의 부분에서 SqlParameter 생성자를 사용하여 명령 매개변수를 구성합니다:

var command = new SqlCommand("DeleteRegion", dbConn) 
{ 
    CommandType = CommandType.StoredProcedure 
};

command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
command.UpdatedRowSource = UpdateRowSource.None;

명령을 구성하고 매개변수 정의를 추가한 후, 매개변수 이름을 사용하여 검색하고 값을 설정한 후 명령을 실행합니다:

command.Parameters["@RegionID"].Value = 999;
command.ExecuteNonQuery();

매개변수 위치로 검색할 수도 있습니다.

출력 매개변수를 사용하는 저장 프로시저 호출

다음 예제는 데이터베이스에 레코드를 삽입하고 해당 레코드의 기본 키를 호출자에게 반환하는 방법을 보여줍니다:

CREATE PROCEDURE InsertRegion(@RegionDescription NCHAR(50),
                              @RegionID INTEGER OUTPUT)AS
   SET NOCOUNT OFF
   SELECT @RegionID = MAX(RegionID) + 1
   FROM Region
   INSERT INTO Region(RegionID, RegionDescription)
   VALUES(@RegionID, @RegionDescription)
GO

삽입 프로시저는 새로운 Region 레코드를 생성합니다. 기본 키가 데이터베이스에서 자동으로 생성되므로, 이 값은 출력 매개변수(@RegionID)를 통해 반환됩니다. RegionInsert 저장 프로시저를 호출하는 방법은 다음과 같습니다:

var command = new SqlCommand("InsertRegion", dbConn) 
{ 
    CommandType = CommandType.StoredProcedure 
};

command.Parameters.Add(new SqlParameter("@RegionDescription",
                                    SqlDbType.NChar,
                                    50,
                                    "RegionDescription"));

command.Parameters.Add(new SqlParameter("@RegionID",
                                    SqlDbType.Int,
                                    0,
                                    ParameterDirection.Output,
                                    false,
                                    0,
                                    0,
                                    "RegionID",
                                    DataRowVersion.Default,
                                    null));

command.UpdatedRowSource = UpdateRowSource.OutputParameters;

두 번째 매개변수 @RegionID는 이 예제에서 Output인 매개변수 방향을 포함합니다. 마지막 줄의 UpdateRowSource 열거형은 데이터가 출력 매개변수를 통해 이 저장 프로시저에서 반환됨을 나타내는 데 사용됩니다.

이 저장 프로시저 호출은 이전 예제와 유사하지만, 출력 매개변수는 프로시저 실행 후 읽어집니다:

command.Parameters["@RegionDescription"].Value = "South West";
command.ExecuteNonQuery();
var newRegionID = (int)command.Parameters["@RegionID"].Value;

데이터 리더

데이터 리더는 직접 인스턴스화할 수 없으며, 해당 명령 객체를 통해 ExecuteReader 메서드로만 반환받을 수 있습니다:

using (var dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();

    var query = "SELECT CustomerName, CompanyName FROM Orders";
    var command = new SqlCommand(query, dbConn);

    var dataReader = command.ExecuteReader();
    while (dataReader.Read())
    {
        var customerName = dataReader.GetString(0);
        var companyName = dataReader.GetString(1);

        Console.WriteLine("'{0}' from {1}", customerName, companyName);
    }

    dataReader.Close();

    dbConn.Close();
}

위치 또는 이름으로 필드 값을 읽을 수 있으며, 반환된 object 값을 적절한 데이터 유형으로 캐스팅합니다:

var customerName = (string)dataReader[0];
var companyName = (string)dataReader["CompanyName"];