SQL Dependency 와 SignalR 로 Database 변경시 웹페이지 자동갱신하기

SignalR Database update notifications in ASP.NET MVC using SQL Dependency

SQL Dependency 와 SignalR 로 Database 변경시 웹페이지 자동갱신하기

여기 강좌는 SQL SERVER 에서 테이블에 INSERT, UPDATE, DELETE 가 발생할 경우, SignalR 와 SQL Dependency 를 이용하여 실시간적으로 웹브라우저에 변경 내용을 보여주는 기술입니다. 웹개발은 ASP.NET MVC 입니다.

먼저 SQL SERVER 의 기능 중  활성화 해야 하는 게 있습니다. 

Step 1: 데이터베이스에서 Broker 서비스를 활성화 합니다.

해당 데이터베이스에 BROKER 의 기능을 활성화 하는 부분의 쿼리 입니다. 이를 실행합니다.

ALTER DATABASE BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

Step 2: WEB.CONFIG 파일에 DB 연결접속 문자열을 추가합니다.

<add name=”DefaultConnection” connectionString=”Server=servername;Database=databasename;User Id=userid;Password=password;” providerName=”System.Data.SqlClient” />

 

Step 3: SQL Dependency 를 활성화 합니다.

asp.net mvc 소스에서 Global.asax 파일을 열어  Application_Start() 의 이벤트에 SQL Dependency 을 작동시키기 위해 Start() 메소드에 database 연결문자열을 입력합니다. 그리고 Application_End() 함수에는 이를 종료하기 위해 Stop() 메소드를 실행합니다. 
이는 처음에 접속자가 있으면 이를 실행하여 작동해 주고, 마지막으로 웹사이트를 사용하는 사용자가 최종적으로 종료 시 이를 중지 시킵니다.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    {
        string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            GlobalConfiguration.Configure(WebApiConfig.Register);
            //Start SqlDependency with application initialization
            SqlDependency.Start(connString);
        }
        protected void Application_End()
        {
            //Stop SQL dependency
            SqlDependency.Stop(connString);
        }
    }

Step 4:  SignalR 를 nuget 를 통해 설치 합니다.

Package Manager Console 를 통해 아래 구문을 입력 후 실행합니다.

Install-Package Microsoft.AspNet.SignalR

Step 5: SignalR Hub Class 를 생성합니다.

Hubs 폴더에 MessagesHub 클래스를 생성하여 아래와 같이 기술합니다.

    {
        private static string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        public void Hello()
        {
            Clients.All.hello();
        }
        [HubMethodName("sendMessages")]
        public static void SendMessages()
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MessagesHub>();
            context.Clients.All.updateMessages();
        }
        
    }

 Step 6: Repository 에서 데이터를 가져오도록 기술합니다.

Create MessagesRepository to get the messages from the database when data is updated. 

데이터베이스에서 데이터를 갱신할 때마다 메세지를 가지고 오기 위해 MessagesRepository 를 생성합니다.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
    {
        readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        public IEnumerable<Messages> GetAllMessages()
        {
            var messages = new List<Messages>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages]", connection))
                {
                    command.Notification = null;
                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        messages.Add(item: new Messages { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage =  reader["EmptyMessage"] != DBNull.Value ? (string) reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
                    }
                }
              
            }
            return messages;
           
            
        }
        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                MessagesHub.SendMessages();
            }
        }
    }

Step 7:  startup class 에 SignalR 를 등록합니다.

app.MapSignalR();

Step 8:  Views > Home > Index.cshtml 의 View Page 를 수정합니다.

div 의 messagesTable 에 데이터베이스에서 가져온 데이터를 table 형태로 그려 넣습니다.

1
2
3
4
5
6
<div class="row">
    <div class="col-md-12">
       <div id="messagesTable"></div>
    </div>
</div>

페이지에 SignalR 과 관련된 스크립트를 추가 합니다.

GetAllMessage 는 PartialView 를 호출하여 실행된 html 을 반환받아 messageTable 의 Div 에 바인딩 합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<script src="/Scripts/jquery.signalR-2.1.1.js"></script>
 <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
<script type="text/javascript">
    $(function () {
        // Declare a proxy to reference the hub.
        var notifications = $.connection.messagesHub;
       
        //debugger;
        // Create a function that the hub can call to broadcast messages.
        notifications.client.updateMessages = function () {
            getAllMessages()
           
        };
        // Start the connection.
        $.connection.hub.start().done(function () {
            alert("connection started")
            getAllMessages();
        }).fail(function (e) {
            alert(e);
        });
    });
    function getAllMessages()
    {
        var tbl = $('#messagesTable');
        $.ajax({
            url: '/home/GetMessages',
            contentType: 'application/html ; charset:utf-8',
            type: 'GET',
            dataType: 'html'
        }).success(function (result) {
            tbl.empty().append(result);
        }).error(function () {
            
        });
    }
</script>

Step 9 : Create Partial View Page 를 생성합니다.

모든 메시지 들을 받을 partial view 인 _MessageList.cshtml 을 shared 폴더에서 생성합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@model IEnumerable<SignalRDbUpdates.Models.Messages>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.MessageID)</th>
        <th>
            @Html.DisplayNameFor(model => model.Message)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EmptyMessage)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.MessageDate)
        </th>
        
    </tr>
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.MessageID)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Message)
        </td>
        <th>
            @Html.DisplayFor(modelItem => item.EmptyMessage)
        </th>
        <td>
            @Html.DisplayFor(modelItem => item.MessageDate)
        </td>
        
    </tr>
}
</table>

Step 10: Database 에 테이블을 생성합니다.

생성한 데이터베이스에 아래의 스크립트를 실행하여 테이블을 생성합니다.

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
GO
/****** Object:  Table [dbo].[Messages]    Script Date: 10/16/2014 12:43:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Messages](
    [MessageID] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](50) NULL,
    [EmptyMessage] [nvarchar](50) NULL,
    [Date] [datetime] NULL,
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD  CONSTRAINT [DF_Messages_Date]  DEFAULT (getdate()) FOR [Date]
GO

Step 11: project 를 실행해 보죠.

When eve data is inserted into the table the dependency_OnChange method will fire.

f5 를 눌러서 웹어플리케이션을 실행하면 브라우저가 실행되어 결과 물이 보여집니다. 만약에 위에 생성한 테이블에 insert, update, delete 쿼리를 실행하게 되면 해당 웹페이지가 SignalR 엔진을 통해 실시간 자동 바인딩 됩니다. 참고로 dependency_OnChange 이벤트가 발생되면 SignalR 이 실행되어 작동됩니다.

 http://www.venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/