Get data from WebMethod using SignalR SqlDependency in ASP.Net

Last Reply 2 months ago By Shahzad786

Posted 2 months ago

hi

how to get data from webmethod using signalr SqlDependency

i am using singalr with SqlDependency its working fine when i change any thing in sql its call webmethod but in ajax on scuess not showing data, please help me how to get data from 

pubblic static IEnumerable<Products> GetData()  on ajax sucess 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace SHAZZ
{
    public partial class Index : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }       
        [WebMethod]
        public static IEnumerable<Products> GetData()
        {
            string conString = ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString;
            var messages = new List<Products>();
            using (var connection = new SqlConnection(conString))
            {
                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 Products { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage = reader["EmptyMessage"] != DBNull.Value ? (string)reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
                    }
                }

            }
            return messages;
        }
        private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
               // MessagesHub.SendMessages();

                MyHub.Show();
            }
        }
    }
}

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="SHAZZ.Index" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.6.4.min.js"></script>
    <script src="Scripts/jquery.signalR-2.2.1.min.js"></script>
    <script src="/signalR/hubs"></script>

    <script type="text/javascript">
        $(function () {
            var notifications = $.connection.myHub;
            // Create a function that the hub can call to broadcast messages.
            notifications.client.displayStatus = function () {
                getData();
            };
            // Start the connection.
            $.connection.hub.start().done(function () {
                alert("connection started")
                getData();
            }).fail(function (e) {
                alert(e);
            });
        });
        function getData() {
            $.ajax({
                type: "POST",
                url: "/Index.aspx/GetData",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (data) {
                    //alert(JSON.stringify(data));                  
                    $("#DIV").html('');
                    var DIV = '';
                    $.each(data, function (i, item) {
                        var rows = "<tr>" +
                            "<td id='MessageID'>" + item.MessageID + "</td>" +
                            "<td id='Message'>" + item.Message + "</td>" +
                            "<td id='EmptyMessage'>" + item.EmptyMessage + "</td>" +
                            "<td id='MessageDate'>" + item.MessageDate + "</td>" +
                            "</tr>";
                        $('#Table').append(rows);
                    }); //End of foreach Loop   
                    console.log(data);
                }, //End of AJAX Success function  
                failure: function (data) {
                    alert(data.responseText);
                }, //End of AJAX failure function  
                error: function (data) {
                    alert(data.responseText);
                } //End of AJAX error function  
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>  
      <div class="row">
    <div class="col-md-12">
       <div id="messagesTable"></div>
    </div>
</div>
<div class="panel panel-primary">  
    <div class="panel-heading">  
        Test Data from API  
    </div>   <!--en dof panel-heading -->
    <div class="panel-body">  
        <table class="table table-bordered" id="Table">  
            <tr>  
                <th>MessageID</th>  
                <th>Message</th>  
                <th>EmptyMessage</th>  
                <th>MessageDate</th>               
            </tr>   <!--end of table-row -->
        </table>   <!--end of table -->
    </div> <!--end of Panel-body -->      
</div> <!--end of Panel --> 
    </div>
    </form>
</body>
</html>

 

Posted 2 months ago Modified on 2 months ago

its working fine with this code

$.ajax({
    url: 'index.aspx/GetData',
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    type: "POST",
    success: function (data) {
        if (data.d.length > 0) {
            var newdata = data.d; 
            $tbl.empty();
            $tbl.append('<tr><th>ID</th><th>Name></th> <th> Last Executed</th> <th>Status</th></tr>');
            var rows = [];
            for (var i = 0; i < newdata.length; i++)
            {
                rows.push(' <tr><td>' + newdata[i].MessageID