C# SqlDependency 활용 SQL Server 실시간 모니터링하기

과거에 개인적으로 정리했던 내 나름대로는 일명 노하우라고 하는 문서로 정리했던 것들을 다 다시 살펴보면서 블로그에 포스팅할 자료들을 물색하고 있는 도중 레이더망에 딱 걸린 이 녀석.

실제로 이 클래스를 활용할 사람이 누가 있겠냐마는... 혹시 모르니까 새로운 프로젝트로 테스트를 하면서 다시 한번 블로그에 정리를 해보려고 한다.

샘플 프로젝트 만들기 (SQL Server 작업)

이전에 SqlConnection, DataAdapter 관련 샘플을 작업하면서 만들었던 DB를 그대로 활용하려고 한다. DB 구조(ERD)와 샘플 데이터를 만들었던 sql을 모두 첨부한다!

Create Database SampleDB;
go

Use SampleDB;
go

Create Table [User] (
	idx int identity primary key,
    name nvarchar(20) not null,
    age int not null,
    uid varchar(20) not null unique,
    pwd varchar(255) not null
);
go

Create Table Payment (
	idx int identity primary key,
    uidx int not null foreign key references [User](idx),
    date datetime not null,
    amount money not null
);
go

Insert Into [User](name, age, uid, pwd) values('Hun', 23, 'admin', 1234);
Insert Into [User](name, age, uid, pwd) values('Gildong', 33, 'gildong2', 3322);
Insert Into [User](name, age, uid, pwd) values('AhMuGae', 50, 'user1', 1234);
go

Insert Into [Payment](uidx, date, amount) values (1, '2024-10-21', 68325779.00);
Insert Into [Payment](uidx, date, amount) values (2, '2024-10-21', 28807092.00);
Insert Into [Payment](uidx, date, amount) values (3, '2024-10-21', 27030986.00);
Insert Into [Payment](uidx, date, amount) values (1, '2024-11-21', 88613495.00);
Insert Into [Payment](uidx, date, amount) values (1, '2024-12-21', 38895914.00);
Insert Into [Payment](uidx, date, amount) values (2, '2024-11-21', 79188298.00);
go

위 sql을 작성해서 바로 DB를 만들었고, 이제 Visual Studio를 사용해서 샘플 프로젝트를 만들면 된다.

샘플 프로젝트 만들기 (Visual Studio Winform Proejct - .Net Framework)

Visual Studio 2022에서 winform 프로젝트를 만든다. 그리고 아래와 같은 결과를 표시하기 위해 폼을 디자인하고 코드를 작성한다.

위 화면을 구성하기 위해 먼저 MainForm.cs 파일에는 아래의 코드를 작성했다.

public partial class MainForm : TemplateForm
{
    private SampleDataManager sdm;

    public MainForm()
    {
        InitializeComponent();
    }

    private void MainForm_Load(object sender, EventArgs e)
    {
        this.sdm = new SampleDataManager();

        this.dgvUsers.DataSource = sdm.userTable;
    }
}

Sql Server로부터 데이터를 불러와서 관리를 하고 있는 SampleDataManager라는 클래스를 만들어서 활용을 했고, 그 코드는 아래와 같다.

internal class SampleDataManager
{
    private SqlConnection con;
    private SqlDataAdapter userAdapter;
    private DataTable _userTable;

    public DataTable userTable { get { return _userTable; } }

    public SampleDataManager()
    {
        Init();    
    }

    private void Init()
    {
        this.con = new SqlConnection("Server=(local);database=SampleDB;Trusted_Connection=True");

        userAdapter =  new SqlDataAdapter("Select * From [User]", con);
        _userTable = new DataTable("user");

        userAdapter.Fill(_userTable);
    }
}

간단하다. 샘플 프로젝트이기 때문에 단순하게 Sql Server와 연결해서 데이터를 불러오고 이를 활용하는 예제다.

SqlDependency 활용 모니터링하는 방법

지금까지 기초 작업을 모두 완료했으니, 지금부터는 SqlDependency 클래스를 사용해서 DB의 변동사항을 모니터링하고 자동으로 데이터를 업데이트하는 것을 해보려고 한다. 공식 홈페이지에 있는 설명문은 여기에 있다.

SqlDependency로 변경 내용 감지 - ADO.NET

쿼리 결과가 ADO.NET에서 원래 검색된 결과와 다른 경우를 쿼리하려면 SqlDependency 개체를 SqlCommand와 연결합니다.

learn.microsoft.com

워낙 쉽고 간단하게 설명을 잘해놨기에 위 링크의 글을 보고 따라만 해도 된다.

해보려고 하는 기능은 간단하다. SqlServer에 있는 SampleDB에 [User] 테이블에 데이터가 추가가 되거나, 삭제가 되었을 때 바로바로 DataGridView에 반영하는 예제를 해보려고 한다.

SqlDependency를 사용하기 위해서는 아래의 네임스페이스를 사용하고 있어야 한다. (SqlConnection 클래스 등을 이미 사용한 적이 있다면 자동으로 참조가 되어 있을 것이기에 별도로 추가할 필요 없다.)

using System.Data.SqlClient;

SqlDependency를 사용하는 방법은 공식 문서에서 다음의 순서로 정리하고 있다.

SqlServer에 대한 SqlDependency 연결을 시작
SqlConnection 및 SqlCommand 객체를 생성하여 서버에 연결하고 T-SQL문을 정의한다.
새 SqlDepedency 객체를 생성 혹은 기존 객체를 사용하여 미리 생성한 SqlCommand 개체에 바인딩한다.
SqlDepedency의 OnChange 이벤트에 원하는 이벤트 처리 메서드를 등록한다.
SqlDependency 객체가 DB에서의 변경을 읽어올 수 있게 SqlCommand 객체의 Execute 명령을 실행한다.
SqlDependency 연결을 중지

위 순서대로 하나씩 코드를 정리해 가면서 설명을 해보겠다.

1. SqlServer에 대한 SqlDependency 연결을 시작

private static string CON_STR = "Server=(local);database=SampleDB;Trusted_Connection=True";

SqlDependency.Start(CON_STR);

SqlDependency 클래스에는 정적 메서드로 Start 메서드와 Stop 메서드를 제공하고 있다. 각각 DB에 변경사항을 체크하기 위한 Dependency 작업을 시작/중지한다는 내용이다. Start 메서드는 boolean 값을 리턴하는데, 만약 Sql Server에 서비스를 정상적으로 등록이 불가능하여 depedency 작업이 불가능한 경우에는 false를 리턴하고 그 외 정상 작동이 가능하면 true를 반환한다. Server에 SqlDependency에 대한 작업이 가능한지 유무를 확인하고 Service를 활성화시키기 위한 사전 작업이라고 생각하면 된다.

2. SqlConnection 및 SqlCommand 객체를 생성하여 서버에 연결하고 T-SQL문을 정의한다.

SqlConnection con = new SqlConnection(CON_STR);
con.Open();

SqlCommand cmd = new SqlCommand("Select [idx], [name] From dbo.[User];", con);

2번 항목은 말 그대로 SqlConnection 객체와 SqlCommand 객체를 각각 생성시켜 주면 된다.

여기서 하나 주의해야 할 점이 있다. SqlDependency 클래스에 매핑하기 위해 생성하는 SqlCommand 객체는 T-SQL을 정의할 때 무거운 SQL, 정확하지 않은 SQL문 등에 대한 사용을 금하고 있다. 어찌 보면 Server Instance에 과부하를 주는 작업을 하는 것이기에 성능 저하를 일으킬 수 있는 작업들에 대해 사전에 차단하는 것으로 보면 된다. 아래는 내가 직접 찾아봤던 주의사항이다.

Top, Group by, Union 등 복잡한 쿼리 사용 불가
T-SQL에 필드명과 테이블명은 정확하게 기입할 것.
-> "Select * From [user]" 구문에서 *기호로 전체 필드를 대체하는 경우가 많은데, 무책임하게 전체 필드에 대한 모니터링을 지원하는 것으로 판단하여 정상적으로 동작하지 않는다

3. 새 SqlDepedency 객체를 생성 혹은 기존 객체를 사용하여 미리 생성한 SqlCommand 개체에 바인딩

SqlDependency dep = new SqlDependency(cmd);

1번 작업으로는 SqlDependency 서비스가 사용가능한지 등을 체크하고 시작하겠다고 알림을 보내는 것이라면, 지금 작업은 실제 Server Instance에서 어떤 데이터를 모니터링할 것인지 구독하는 객체를 생성하고 등록하는 것이라고 보면 된다.

현재 [User] 테이블에 대한 Select 문을 정의한 SqlCommand 객체를 인자로 넘겼다. 이 경우 [User] 테이블 조회 결과에 변동 사항이 생겼을 때 알림을 받기 위한 SqlDependency 객체를 정의한 것이다. DB에서 추가/수정/삭제 작업이 발생하게 되면 Server Instance는 구독된 SqlDependency에 알림을 보내고 알림을 받은 SqlDependency는 OnChange() 이벤트를 발생시키는 것이다.

4. SqlDepedency의 OnChange 이벤트에 원하는 이벤트 처리 메서드를 등록한다.

dep.OnChange += this.OnChange;
    
private void OnChange(object sender, SqlNotificationEventArgs e)
{
    /* Somethind Method ...*/
    /* Somethind Method ...*/
    
    switch (e.Info)
    { 
        case SqlNotificationInfo.Invalid:
            break;
        case SqlNotificationInfo.Insert:
            break;
        case SqlNotificationInfo.Update:
            break;
        case SqlNotificationInfo.Delete:
            break;
        default:
            break;
    }
}

SqlDependency 객체는 DB Instance로부터 알림을 받게 되면 OnChange 이벤트가 발생되게 된다. 이벤트의 인자 中 SqlNotificationEventArgs 인자를 사용해서 DB에서 어떤 변경이 발생했는지를 체크할 수 있다.

5. SqlDependency 객체가 DB에서의 변경을 읽어올 수 있게 SqlCommand 객체의 Execute 명령 실행

SqlConnection con = new SqlConnection(CON_STR);
con.Open();

SqlCommand cmd = new SqlCommand("Select [idx], [name] From dbo.[User];", con);

SqlDependency dep = new SqlDependency(cmd);

dep.OnChange += this.OnChange;

cmd.ExecuteReader();

1~4번 작업으로 모든 사전 작업을 완료하고 나면 SqlCommand 객체의 Execute 명령을 실행한다. 명령을 수행하게 되면 이제 SqlDependency 객체는 정상적으로 구독을 하게 되고 변경을 모니터링하게 된다.

Execute 명령을 수행하는 이유는 공식 문서에서도 별다른 언급이 없긴 하지만 로직 추측상 최초 명령을 수행함으로써 Server로부터 해당 결과를 받아서 캐싱하고 있다가 DB로부터 변경이 있다고 알림을 받았을 때 어떤 변화가 있는지 체크하기 위한 용도로 명령을 실행해야 하는 것이 아닐까 생각하고 있다.

6. SqlDependency 연결을 중지

SqlDependency.Stop(CON_STR);

SqlDependency 작업을 모두 완료한 이후에는 연결을 끊음으로써 Server Instance에서 더 이상 알림을 보낼 필요가 없게끔 한다.

SqlDependency 활용 모니터링하기 최종 코드

MainForm.cs 코드

public partial class MainForm : TemplateForm
{
    private SampleDataManager sdm;

    public MainForm()
    {
        InitializeComponent();
    }

    private void MainForm_Load(object sender, EventArgs e)
    {
        this.sdm = new SampleDataManager();

        this.sdm.OnListen += this.OnListen;

        this.dgvUsers.DataSource = sdm.userTable;
    }

    private void OnListen(DataTable dt)
    {
        this.Invoke(new Action(() =>
            {
                this.dgvUsers.DataSource = dt;
            }
        ));
    }

    private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
    {
        this.sdm.Dispose();
    }
}

SampleDataManager.cs 코드

internal class SampleDataManager:IDisposable
{
    private static string CON_STR = "Server=(local);database=SampleDB;Trusted_Connection=True";

    private SqlConnection con;
    private SqlDataAdapter userAdapter;
    private DataTable _userTable;

    public DataTable userTable { get { return _userTable; } }

    public Action<DataTable> OnListen { get; set; }

    public SampleDataManager()
    {
        Init();    
    }

    private void Init()
    {
        this.con = new SqlConnection(CON_STR);
        this.con.Open();

        ReadData();

        SqlDependency.Start(CON_STR);

        AddDependency();
    }

    private void AddDependency()
    {
        if (con == null || con.State != ConnectionState.Open)
        {
            this.con = new SqlConnection(CON_STR);
            this.con.Open();
        }

        SqlCommand cmd = new SqlCommand("Select [idx], [name] From dbo.[User];", con);
        SqlDependency dep = new SqlDependency(cmd);

        dep.OnChange += this.OnChange;
        
        cmd.ExecuteReader().Close();
    }

    private void ReadData()
    {
        userAdapter = new SqlDataAdapter("Select * From [User]", CON_STR);
        _userTable = new DataTable("user");

        userAdapter.Fill(_userTable);
    }

    private void OnChange(object sender, SqlNotificationEventArgs e)
    {
        AddDependency();

        if (this.OnListen != null)
        {
            ReadData();

            this.OnListen(this._userTable);
        }
    }

    public void Dispose()
    {
        SqlDependency.Stop(CON_STR);

        if (this.con != null && this.con.State == ConnectionState.Open) this.con.Close();
    }
}

최종코드에서 하나 얘기하고 싶은 것은 SqlDependency 클래스의 OnChange 이벤트가 동작하고 나면 해당 구독 서비스는 바로 종료된다고 봐야 한다. 그래서 계속 구독을 이어나가고 싶은 경우에는 OnChange 이벤트 내부에서 바로 또 구독을 할 수 있게 세팅을 해줘야 한다. (AddDependency() 메서드가 해당 역할을 수행한다.)

만약, SqlDependency 사용이 불가능하다면?

Sql Server에서 Database를 생성하고 별도로 설정을 바꾸지 않았다면 동작에는 문제가 없지만, 어떤 특이한 상황에 의해 Service Broker 기능을 끈 상태라면 위와 같이 코드를 작성하고 프로그램 실행을 수행하면 아래와 같은 에러가 발생한다.

System.InvalidOperationException

위 그림과 같이 Service가 꺼진 경우 System.InvalidOperationException가 발생하게 된다. 친절하게 예외 설명글에 SQL Server Service Broker를 설정이 되어 있지 않다고 한다. 즉, Database의 Service Broker 옵션만 키면 되는 것이다.

해결 방법 - 1) GUI로 Service Broker 옵션 켜기

옵션 페이지 - Service Broker - Broker 활성화를 True로 변경

해결 방법 - 2) T-SQL로 Service Broker 옵션 켜기

T-SQL로 서비스를 종료하고 실행하는 방법을 모두 아래에 작성했다.

/* Service Broker 비활성화 */
ALTER DATABASE SampleDB
SET DISABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO

/* Service Broker 활성화 */
ALTER DATABASE SampleDB
SET DISABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO

조금이나마 도움이 되었길 바라며...

728x90

'C# > Winform (.Net Framework)' 카테고리의 다른 글

C# - Winform 바탕화면 File을 Form(or Control)에 Drag하여 추가하기 (2)	2024.12.20
C# - Winform 화면에 보이는 컨트롤(Form or Panel 등)을 Print 하기 (1)	2024.12.20
C# - SQL Server 연동하기 (Chapter.2 - DataAdapter 활용) (1)	2024.12.02
C# - SQL Server 연동하기 (Chapter.1 - DataSet, DataTable 활용) (1)	2024.11.29
C# SQL Server 연동 작업 (SqlCommand) (0)	2024.11.29

훈발개발

C# SqlDependency 활용 SQL Server 실시간 모니터링하기