C# MSSQL 데이터 변경 시 바로 확인하는 방법

이번에는 C#에서 MSSQL과 연동해서 사용 시에 활용하면 좋은 기능 중 하나인

데이터 변경 시 바로 확인할 수 있는 기능에 대하여 알아보겠습니다.

 

일반적으로 해당 기능을 사용하지 않고 모니터링하려면 프로그램에서 타이머를 돌려서 주기적으로

Select문을 날려서 확인하는 방법 밖에 마땅한 것이 없지만 해당 기능을 사용하면

MSSQL에서 지정된 곳의 데이터가 변경되면 이벤트를 날려서 프로그램에서 받아 처리하기 때문에

불필요한 동작을 획기적으로 줄일 수 있게 됩니다.

 

이 기능을 사용하기 위해서는 DB에서 Service Broker라는 것을 활성화해야 합니다.

먼저 SSMS(SQL Server Management Studio)를 통하여 활성화 할 DB의 속성에 접근합니다.

그리고 표시된 부분을 True로 변경해주어야 합니다.

 

DB 속성 창

 

Service Broker를 활성화 해주는 부분은 쿼리문으로도 가능하며, 아래와 같이 입력 후 실행하면 됩니다.

WITH ROLLBACK IMMEDIATE GO 부분을 추가하였기 때문에 DB가 사용중일 때도 활성화 가능합니다.

비활성화 할 경우 ENABLE을 DISABLE로 변경하고 실행하면 됩니다.

ALTER DATABASE TestDB
SET ENABLE_BROKER 
WITH ROLLBACK IMMEDIATE 
GO

 

Service Broker를 활성화하지 않고 사용하려 할 경우 다음과 같은 예외가 발생합니다.

 

SqlDependency Start 예외

 

Service Broker 설정이 끝났다면 이제 C#에서 연동을 위한 구현을 진행해야 합니다.

해당 글에서는 WPF로 진행하였습니다만 Winform에도 적용 가능한 점 참고해주세요.

해당 기능을 C#에서 구현하기 위해서는 SqlDependency 라는 클래스를 사용해야 합니다.

사용하기 위해서는 아래와 같이 using문에 등록이 필요합니다.

using System.Data.SqlClient;

 

다음은 SqlDependency 클래스를 사용 시 참고해야 할 내용입니다.

 

* SqlDependency

- 설명에서 나오는 구독의 의미는 SqlDependency 클래스를 다시 생성 및 이벤트 등록 후

  조회 쿼리문을 ExecuteReader() 하는 것 입니다.

 

1. 이벤트를 받기 위해서는 이벤트를 받기 전 구독을 해야 합니다.

2. 이벤트를 받으면 구독이 해제되므로 다시 구독을 해야 합니다.

3. 다시 구독을 진행할 때 SqlDependency 및 Command 객체를 그대로 사용하면

    '쿼리 알림 구독 메시지가 잘못되었습니다' 예외가 발생하므로 새로 생성한 후 설정을 해야합니다.

    만약 그대로 사용하지 않았는데 해당 예외가 발생한다면 현재 발생된 OnChange 이벤트에서

    변경을 감지하여 발생한 것이므로 무시해도 된다는 내용을 해외 포럼에서 발견하였으나,

    무시하는 부분에 대해서는 실제로 테스트를 해보지 않았으므로 참고만 해주세요.

4. Start와 Stop은 시작, 정지 시에 1번만 진행합니다.

5. SqlDependency에서 구독할 때 사용하는 조회 쿼리문은 모든 Column 및 테이블의 Schema 이름을 명시해야 합니다.

 

* SqlDependency 시작

SqlDependency를 시작시킬 DB에 먼저 접속한 후,

Start 메서드를 이용해서 프로그램에서 SqlDependency를 시작하여 MSSQL에서 변경 이벤트를 받을 준비를 합니다.

혹시 몰라서 테스트한 결과 DB에 먼저 접속하지 않아도 Start는 실패하지 않았으니 참고해주세요.

ConnectionString 구성 시에 들어가는 값은 순서대로 확인해보면

- IP

- Port

- DB Name

- 사용자 계정 ID

- 사용자 계정 비밀번호

- Windows 인증으로 접속 여부

입니다.

// 필드
SqlConnection conn;

// 폼 로드
private void Window_Loaded(object sender, RoutedEventArgs e)
{
    string connectionString = string.Format("Data Source={0},{1};Initial Catalog={2};uid={3};pwd={4};Integrated Security={5};Connection Timeout=1",
                                            "127.0.0.1", "1433", "TestDB", "sa", "1", false);
    conn = new SqlConnection(connectionString);
    conn.Open();

    // SqlDependency 시작
    if (SqlDependency.Start(connectionString))
    {
        MessageBox.Show("Dependency Start 성공", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    else
    {
        MessageBox.Show("Dependency Start 실패", "알림", MessageBoxButton.OK, MessageBoxImage.Error);
    }
    
    // 구독 쿼리 생성

 

시작에 성공하면 다음과 같이 MSSQL의 활성화한 DB에 Service Broker 내부를 보면

큐와 서비스가 생성된 것을 확인할 수 있습니다.

 

생성된 큐, 서비스

 

생성되었다면 이제 구독에 사용 될 쿼리문을 등록해주어야 합니다.

구독 쿼리문 작성 시 조건은 접은글을 펼치면 확인 가능합니다.

해당 내용은 MS 사이트에서 발췌한 내용입니다.

 

Supported SELECT Statements

Query notifications are supported for SELECT statements that meet the following requirements:

 

• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• The statement may not use unnamed columns or duplicate column names.
• The statement must reference a base table.
• The statement must not reference tables with computed columns.
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
• The statement must not include PIVOT or UNPIVOT operators.
• The statement must not include the UNION, INTERSECT, or EXCEPT operators.
• The statement must not reference a view.
• The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
• The statement must not reference server global variables (@@variable_name).
• The statement must not reference derived tables, temporary tables, or table variables.
• The statement must not reference tables or views from other databases or servers.
• The statement must not contain subqueries, outer joins, or self-joins.
• The statement must not reference the large object types: text, ntext, and image.
• The statement must not use the CONTAINS or FREETEXT full-text predicates.
• The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
• The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
• The statement must not use any nondeterministic functions, including ranking and windowing functions.
• The statement must not contain user-defined aggregates.
• The statement must not reference system tables or views, including catalog views and dynamic management views.
• The statement must not include FOR BROWSE information.
• The statement must not reference a queue.
• The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
• The statement can not specify READPAST locking hint.
• The statement must not reference any Service Broker QUEUE.
• The statement must not reference synonyms.
• The statement must not have comparison or expression based on double/real data types.
• The statement must not use the TOP expression.

 

해당 내용을 참고하여 위의 소스 밑에 구독 쿼리문을 추가하고,

Dependency_OnChange 이벤트도 추가합니다.

// SqlDependency 시작

    // 구독 쿼리 생성
    string query = string.Format(@"SELECT [ID], [Password], [Name]
                                   FROM [dbo].[UserInfo]");
    SqlCommand command = new SqlCommand(query, conn);

    // Dependency 생성 및 구독
    SqlDependency dependency = new SqlDependency(command);
    dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);
    SqlDataReader dependencyReader = command.ExecuteReader();
    dependencyReader.Close();
}

private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    // 에러 발생 여부 확인
    if (e.Info == SqlNotificationInfo.Invalid)
    {
        MessageBox.Show("에러 발생", "알림", MessageBoxButton.OK, MessageBoxImage.Error);
        return;
    }
    
    if (e.Info == SqlNotificationInfo.Insert)
    {
        MessageBox.Show("Insert 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    else if (e.Info == SqlNotificationInfo.Update)
    {
        MessageBox.Show("Update 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    else if (e.Info == SqlNotificationInfo.Delete)
    {
        MessageBox.Show("Delete 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
}

 

1. 쿼리문 작성 시 Column 명을 모두 기재하고 테이블명 앞에 스키마명도 같이 붙여줍니다.

2. SqlCommand 클래스로 command 변수를 만들고 SqlDependency 생성 시에 붙여넣어 줍니다.

3. Dependency_OnChange 라는 이름의 OnChange 이벤트를 등록해줍니다.

4. 적용된 쿼리에 ExecuteReader를 사용하여 알림 개체에 명령을 등록합니다.

5. 추후에 해당 Connection으로 Reader 재사용을 위하여 Reader를 닫아줍니다.

 

위와 같이 작성한 후 프로그램을 실행하고 DB에서 UserInfo 테이블의 ID, Password, Name 컬럼에 변경사항이 발생할 경우 Onchange에 등록한 Dependency_OnChange 이벤트로 알림이 발생합니다.

 

 

MSSQL에 접속하여 위 상태에서 다음과 같이 변경해보겠습니다.

 

 

3번째 행의 Name을 TestName3 → TestName5로 변경하였더니 프로그램에서 이벤트가 발생하였습니다.

 

 

이벤트 발생 시점의 속성 값은 다음과 같습니다.

 

- Info : Update (Update 작업이 발생했음)

- Source : Data (Data가 변경되었음)

- Type : Change (모니터링중인 데이터가 변경되었음)

 

info, Source, Type의 종류는 이것 말고도 여러 가지가 있기 때문에 소스에서 확인하신 후 입맛대로 사용하시면 됩니다.

그리고 이벤트를 받았으니 다시 이벤트를 받기 위하여 재구독을 진행해야 합니다.

다음과 같이 추가하면 됩니다.

 

private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    // 에러 발생 여부 확인
    if (e.Info == SqlNotificationInfo.Invalid)
    {
        MessageBox.Show("에러 발생", "알림", MessageBoxButton.OK, MessageBoxImage.Error);
        return;
    }
    
    if (e.Info == SqlNotificationInfo.Insert)
    {
        MessageBox.Show("Insert 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    else if (e.Info == SqlNotificationInfo.Update)
    {
        MessageBox.Show("Update 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }
    else if (e.Info == SqlNotificationInfo.Delete)
    {
        MessageBox.Show("Delete 작업 감지됨", "알림", MessageBoxButton.OK, MessageBoxImage.Information);
    }

    // 추가 알림을 받기 위하여 다시 구독
    try
    {
        string query = string.Format(@"SELECT [ID], [Password], [Name]
                                       FROM [dbo].[UserInfo]");
        SqlCommand command = new SqlCommand(query, conn);
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);
        SqlDataReader reader = command.ExecuteReader();
        reader.Close();
    }
    catch (SqlException ex)
    {
        // '쿼리 알림 구독 메시지가 잘못되었습니다' 예외는 무시해도 됨
        if (ex.Errors[0].Number != 9202)
        {
            MessageBox.Show("재구독에 실패하여 이후의 변경 사항은 감지되지 않습니다.", "재구독 실패", MessageBoxButton.OK, MessageBoxImage.Error);
            return;
        }
    }
}

 

재구독 코드는 기존과 별로 차이가 없으나 command.Notification = null; 부분이 추가되었습니다.

해당 코드가 들어가있는 이유는 이전에 사용했던 Notification을 제거하기 위해서입니다.

하지만 필수 사항은 아니므로 빼도 무방하나, 저는 만약을 위해서 넣는 편입니다.

 

먼저 SqlException의 오류 코드에 대한 설명이 있는 MS 사이트 링크입니다.

항목이 너무 많기 때문에 원하시는 분들은 해당 링크로 들어가서 원하는 코드를 검색해보시기 바랍니다.

 

데이터베이스 엔진 이벤트 및 오류 - SQL Server

 

그리고 catch 구문에 SqlException 예외의 Number가 9202일 경우 왜 무시해도 되는지에 대한 내용입니다.

 

일단 SqlException의 9202 예외가 발생하는 이유는 SqlDependency가 호출하기 전에 OnChange 처리기를 실행한 경우에 의해 발생합니다. 따라서 하나의 SqlDependency를 사용하여 구독하는 경우에 발생하는 충돌로 보시면 됩니다.

하나의 SqlCommand를 SqlDependency에 추가하고 구독하면 다시 구독할 때 오류 9202가 발생할 수 있습니다.

 

이 경우 이는 SQL Server가 이전 명령으로 변경 사항을 감지했으며 이미 OnChange를 실행했거나 곧 실행할 것임을 의미합니다. 만약 해당 예외를 발생시키고 싶지 않을 경우에는 각 SqlDependency 개체를 한 번만 사용하고 각 SqlCommand를 완전히 생성 및 삭제해야 합니다. 예외가 발생해도 상관 없다면 위의 소스처럼 해당 예외 코드는 무시하도록 하는 방법을 사용해야 합니다.

 

그러면 이제 위와 같이 구성된 코드로 다시 실행해봅니다.

그리고 MSSQL에 접속하여 모니터링중인 테이블의 컬럼 데이터를 변경해주거나 새로운 행을 추가해줍니다.

저는 이 상태에서

 

 

다음과 같이 행을 1개 추가하였습니다.

 

 

그리고 프로그램에 발생한 이벤트를 보니 다음과 같았습니다.

 

 

- Info : Insert (Insert 작업이 발생했음)

- Source : Data (Data가 변경되었음)

- Type : Change (모니터링중인 데이터가 변경되었음)

 

그리고 재구독을 진행한 후 다시 MSSQL에 접속해서 데이터를 다음과 같이 1개 더 추가했습니다.

 

 

DB가 변경되면 이벤트가 다시 발생하는 것을 확인할 수 있습니다.

이렇게 Insert, Update, Delete 및 다른 경우의 변경 내역을 이벤트로 받아봄으로써

타이머를 이용한 주기적인 Select문 쿼리를 보내는 방법보다 부하를 더 적게 가져갈 수 있게 되었습니다.

물론 현재 설명드린 코드만으로는 주기적인 Select문 쿼리 사용보다 정확한 결과를 받기는 힘들지만,

개량하면 어떤 부분의 데이터가 변경되었는지 좀 더 세밀하게 다룰 수 있으므로,

SqlDependency 클래스의 이용 가치는 상당히 높을 것으로 보입니다.

 

이상으로 C#으로 MSSQL 데이터 변경 시 바로 확인하는 방법에 대하여 알아보았습니다.