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

23 Aug

In this we will learn how to display real time updates from the  SQL Server  by using SignalR  and SQL Dependency in ASP.NET MVC.

You can download the source from the Github

The following are the steps that we need to enable in the SQL Server first.

Step 1: Enable Service Broker on the database

The following is the query that need to enable the service broker

ALTER DATABASE BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;

Step 2: Add Connection string to the Web.Config file

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

 

Step 3: Enable SQL Dependency

In Global.asax start the SQL Dependency in App_Start() event and Stop SQL dependency in the Application_End() event

public class MvcApplication : System.Web.HttpApplication
    {
        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: Install SignalR from the nuget

Run the following command in the Package Manager Console

Install-Package Microsoft.AspNet.SignalR

Step 5: Create SignalR Hub Class

Create MessagesHub class in the Hubs folder

public class MessagesHub : Hub
    {
        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: Get the  Data from the Repository

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

public class MessagesRepository
    {
        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:  Register SignalR at startup class

Add the following code

app.MapSignalR();

Step 8:  View Page

Create div messagesTable that will append the table data from the database

<div class="row">
    <div class="col-md-12">
       <div id="messagesTable"></div>
    </div>
</div>

Now Add the SignalR related scripts in the page.

getAllMessages is a function that return the partialview data and bind it into the messagesTable div.

<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

Create a partial view _MessagesList.cshtml that returns all the messages.

@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: Set Up the Database

Create the database called blogdemos and run the following script

USE [BlogDemos]
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: Run the project

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

SIGNALR

 

Source Code

You can download the source from the Github

71 thoughts on “SignalR Database update notifications in ASP.NET MVC using SQL Dependency

  1. Hi,
    The project is running fine however when i update the data in the table dbo.message, it is not being updated on the browser.

    Check list :
    – I am running sql server 2008 and service broker is enabled.

    Help ?

      • Did u started the SQL Dependency at globax.asax

        Please look at step 3

        public class MvcApplication : System.Web.HttpApplication
        {
        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);
        }
        }

      • No error as such, the page loads correctly without any error but as i have stated when i add data in the table, it does not update on the browser in real time.

  2. Do we need to configure something in our IIS to run this, because we are not getting live updated data.

    your thoughts please .

    Thank you,
    Ram.

  3. Hi,

    the cache of the GetMessages() controller action prevented me from seeing the updated data.
    So I placed an OutputCache-Attribute on the method:


    [(Duration = 0)]
    public ActionResult GetMessages()

    BTW: Thanks for sharing your code.

    Floyd

  4. Hello,

    I found a work around to my problem and now the realtime update works.
    But it works only if all columns are filled. How to get a record with some null value?

    • You need to check whether Column value is NULL or not before assigning to a variable using DBNull.Value. I also updated the post, which will display empty message for that i added extra column named EmptyMessage in the database which contains NULL Values in the database.

      While assigning DataReader value to a variable you need to write a if condition like this

      string EmptyMessage = reader[“EmptyMessage”] != DBNull.Value ? (string) reader[“EmptyMessage”] : “”;

      while (reader.Read())
      {
      messages.Add(new Messages {
      MessageID = (int)reader[“MessageID”],
      Message = (string)reader[“Message”],
      EmptyMessage = reader[“EmptyMessage”] != DBNull.Value ? (string) reader[“EmptyMessage”] : “”,
      MessageDate = Convert.ToDateTime(reader[“Date”]) });

      }

  5. Hi,

    I tried your suggestion but I can see the table only on the first load and it doesn’t update when a new row arrives.
    Anyway how can I manage int or DateTime null values?

    • The following is the code to handle empty integers and datetime values

      var emptyInteger = reader[“ID1”] as int? ?? default(int);
      var emptyDateTime = reader[“DATE1”] as DateTime? ?? default(DateTime);

      • Thank you for your reply.
        In my query I’ve a DateTime value so I used your suggestion but it doesn’t work anyway. I tried also to remove that column but nothing.

        Could be some problem in the partial view? Null and empty values have to be handled in some different way?

        Thank you

          • I don’t get any error.
            I run the project with Visual Studio and I don’t get any error. On first run I see my table but when I update the database table nothing happens. It seems that dependency_OnChange event doesn’t fire.
            SqlDependency is already started indeed I tried another page that haven’t null values and it works correctly.

          • OK, maybe I made one little step to the solution: I replaced the INNER JOIN with LEFT JOIN in a page that hasn’t null values and the real time update doesn’t work. I think that the problem are in the join type and null values.

            Let’s make a summary:
            INNER JOIN + No nulls = Works!
            INNET JOIN + Nulls = Doesn’t work…
            LEFT JOIN + No nulls = Doesn’t work…
            LEFT JOIN + Nulls = Doesn’t work…

            Then the only way to make the pages update in real time is INNER JOIN + No nulls.

            Have you ever had a problem like this?

  6. Hello,

    Thanks for this code.
    I have a problem when clicking “Create New” :
    (sorry, it’s in french)
    La ressource est introuvable.

    Description : HTTP 404. La ressource recherchée (ou l’une de ses dépendances) a peut-être été supprimée ou renommée ou bien elle n’est plus disponible temporairement. Vérifiez l’URL ci-après et assurez-vous qu’elle est correcte.

    URL demandée: /home/Create

    thanks for help !

    • Hi Franzzz,

      Actually i din’t implemented the create action method under home controller that’s why you are getting the error.

  7. Pingback: SignalR Database update notifications in ASP.NET MVC using SQL Dependency–by Venkat Baggu. | Bhavin Patel

  8. Hi,

    it might be too late to give you a ‘thanks’. it’s a fantastic example.

    One thing that I have noticed after going through the code (I might be wrong…but it’s my observation)

    ‘dependency_OnChange’ is fired every time during inserting a new row in Table. it’s OK behavior But the list is not updated every time. Let me give you an example on how I have test it:

    Step-1: Instead of retrieving all data , I put a date compare condition.

    using (var command = new SqlCommand(@”SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] where [Date]>=@ModifiedDate order by [Date] desc”, connection))

    Step-2: Try to Insert multiple rows at a time.

    here is my updated GetAllMessages() function:

    static DateTime _modifiedDate = new DateTime(1970, 1, 1);
    public IEnumerable GetAllMessages()
    {
    var messages = new List();
    using (var connection = new SqlConnection(_connString))
    {
    connection.Open();
    using (var command = new SqlCommand(@”SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] where [Date]>=@ModifiedDate order by [Date] desc”, connection))
    {
    var param1 = command.CreateParameter();
    param1.ParameterName = “ModifiedDate”;
    param1.SqlDbType = SqlDbType.DateTime;
    param1.Value = _modifiedDate;
    command.Parameters.Add(param1);

    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())
    {
    // write the output in a file to check the ‘dependency_OnChange’ if firing every time or not.
    using (StreamWriter writer = new StreamWriter(“D:\\test.txt”, true))
    {
    writer.WriteLine(reader[“Message”] + ” ” + reader[“Date”]);
    }

    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”]) });
    }

    _modifiedDate = DateTime.Now;
    }

    }
    return messages;

    }

    It seems like that ‘context.Clients.All.updateMessages();’ creates the main problem.
    Could you please manage some time to let me know your feedback OR am I missing something.

    In advance thanks.

    • Hi,
      I checked your code, i didn’t get any error. Since you are using the Date comparison you will get newly inserted rows only.

      public IEnumerable GetAllMessagesTest()
      {
      var messages = new List
      ();
      using (var connection = new SqlConnection(_connString))
      {
      connection.Open();
      using (var command = new SqlCommand(@”SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] where [Date] >= @ModifiedDate order by [Date] desc”, connection))
      {
      var param1 = command.CreateParameter();
      param1.ParameterName = “ModifiedDate”;
      param1.SqlDbType = SqlDbType.DateTime;
      param1.Value = _modifiedDate;
      command.Parameters.Add(param1);

      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())
      {
      // write the output in a file to check the ‘dependency_OnChange’ if firing every time or not.
      //using (StreamWriter writer = new StreamWriter(“D:\\test.txt”, true))
      //{
      // writer.WriteLine(reader[“Message”] + ” ” + reader[“Date”]);
      //}

      messages.Add(new Messages { MessageID = (int)reader[“MessageID”], Message = (string)reader[“Message”], EmptyMessage = reader[“EmptyMessage”] != DBNull.Value ? (string)reader[“EmptyMessage”] : “”, MessageDate = Convert.ToDateTime(reader[“Date”]) });
      }

      ————————————————–

      View Code:

      public ActionResult GetMessages()
      {
      var messageRepository = new MessagesRepository();
      return PartialView(“_MessagesList”, messageRepository.GetAllMessagesTest());
      }

      _modifiedDate = DateTime.Now;
      }

      }
      return messages;

      }

  9. Hi i followed your document, and able to create project, but i am not able to see any of the records in UI. i think GetAllMessages() is not being called, and partial view is not called. can you help me resolving that. asap.
    thanks in advance

  10. Hello,
    Thank You very much for the tutorials.

    Its working fine in Chrome but not in IE. Can you please help to solve the problem of IE too. Recently, I am using IE11.

    Thanks

    • I have posted the solution further below….

      The “cache: false” setting was missing inside $.ajax request. Add it and it will work in IE.

      Best regards

  11. Good day! Adding ” [OutputCache(Duration = 0)] ” in your GetMessages() action will display updated data. This was my issue before and it is now working fine in IE. But, I tried to run it in using Google Chrome and it gave me the following error.

    “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”

    Any suggestion on this?
    Appreciate your support.

    Thank you.

    • “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”

      The problem is with your database. Please check your connection string once, because the server didn’t found or unable to connect to the server.

  12. venkat,

    I have a new problem, I am using sql server 2012 and updates is not working. I follow the same procedure but still any updates in the database are not reflecting in the browser.

    Please help me on this.

    Regards

  13. The example didn’t work in IE 10. Because the attribute “cache: false” was missing in your $.ajax request. The first “get” worked, but all further requests were not done. IE reported HTTP error 304 (= Not Modified).

    Best regards

  14. At first I thought the multiple unwanted get requests were caused by my code modification. But they were not! I’ve observed that additional new clients are causing that.

    Please do me a favour and retest your sample with multiple connected browser clients and take a look at the number of GET request e.g. with F12 in IE or Firebug in Mozilla.

    Best regards

      • Put cache: false in your ajax request, it will work

        $.ajax({
        url: ‘/home/GetMessages’,
        cache: false,
        contentType: ‘application/html ; charset:utf-8’,
        type: ‘GET’,
        dataType: ‘html’
        }).success(function (result) {
        tbl.empty().append(result);
        }).error(function () {

        });

  15. I’m using asp.net mvc 5.but in the Global.sax file it says that “WebApiConfig does not exist in current context”.why is that?

  16. Thanks for the article Venkat, it’s really helped me get started with SignalR. I know it’s a while since you posted this, but hopefully you are still monitoring this as I have a question. With your code you have the dependency monitoring all messages. So whenever there’s a change everything gets reloaded. I want to make things more efficient, so if there’s an insert then I’d only return that record and insert it. Or with an update just get the record that’s updated and have my JS client side do the update.

    I looked at doing something on the dependency_OnChange so that it e.Info for SqlNotificationInfo.Insert or SqlNotificationInfo.Update (for instance) and added new routines to my Hub to get the most recent item that was inserted or updated.

    This fires, but it then kills any future events. Here’s the code for an insert, as you see I’ve commented out the dependency code, hoping it’d keep running the code from your GetAllMessages().

    public IEnumerable GetInsertedMessage()
    {
    var messages = new List();
    using (var connection = new SqlConnection(_connString))
    {
    connection.Open();
    using (var command = new SqlCommand(@”SELECT TOP 1 [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages] ORDER BY MessageID DESC”, 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;
    }

    Any thoughts on how to get this working? Thanks.

  17. when i follw these steps and add all the stuff to my new project, its getting error
    “/signelr/hubs” not found
    help please???

    • Make sure your Hub is registered in the Global.asax

      RouteTable.Routes.MapHubs();

      protected void Application_Start()
      {
      AreaRegistration.RegisterAllAreas();

      // The order of this is important
      RouteTable.Routes.MapHubs();

      WebApiConfig.Register(GlobalConfiguration.Configuration);
      FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
      RouteConfig.RegisterRoutes(RouteTable.Routes);
      BundleConfig.RegisterBundles(BundleTable.Bundles);
      AuthConfig.RegisterAuth();
      }
      If they above solution, didn’t work try reinstall SignalR the Nuget using following command
      Install-Package Microsoft.AspNet.SignalR -pre

      • “RouteTable.Routes.MapHubs();” is throwing error “Obsolute”
        reinstall signalR doesn’t affect the application,
        i am using MVC 5

        • Can you verify Startup.cs is firing?

          If not try install following package
          Install-Package Microsoft.Owin.Host.SystemWeb

          Also add the app setting in the web.config file

          Clear your temp folder

  18. Hello,

    I dowload the same project from Github and the same project works when i ran in sql server 2008 R2 and VS 2012 but when I use same project in SQL Server 2014 and VS 2013 the update on client (browser) not working ?

    Help or suggesstion would be really really helpful ?

    • Did you Enable Service Broker on the database

      Currently, i didn’t had SQL Server 2014 installed in my machine. I will install it and check is there any problem.

      • Thank You!! will wait on your response and Yes, i have enabled service broker on my database and still i see issue that not updating the client when db changes.

        • Do you have cache: false in ajax request?
          Also enable $.connection.hub.logging = true; before connection starts.
          Check the browser console for any client side errors?

  19. Everything was working fine using your tutorial. I have posted a question on asp .net forum http://forums.asp.net/p/2057277/5933615.aspx?p=True&t=635714625013713336

    By mistake, I then executed ALTER DATABASE DatabaseName SET ENABLE_BROKER ; for some reason, it kept executing and never completed. It ran for more than an hour and then I stopped this command from Query Analyzer.

    As a result of running the ALTER DATABASE, the asp .net app stopped getting notified. Is there a way to debug the broker service? I tried creating a new database and enabled it with broker service, but it never sends out notifications. It looks like there is something at the service level that is stuck and does not let notifications out. Is there a way to completely start fresh by deleting the existing service broker on SQL Server for this database?

    When I execute sp_who2, I noticed a command that is always present and is in a SUSPENDED state, is this normal?

    Status Command ProgramName

    SUSPENDED DELETE SqlQueryNotificationService-612b3033-ec5e-41e9-9b60-253b0c79c2c8

    • Hi,
      First check in which tables the broker service are enabled, by using the following command
      SELECT name, is_broker_enabled FROM sys.databases

      Firstly the following command works fine if the database is not in use, Regarding problem consider an example that if you have opened two new query window editor for that database, In one of the query window you are executing the following command it will keeps executing the command until all the database related connection closed for that database

      ALTER DATABASE test SET ENABLE_BROKER

      So to avoid the above problem we need to add WITH ROLLBACK IMMEDIATE to the above query, the query will be as follows

      ALTER DATABASE BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

      Another Fix Execute the following query
      ALTER DATABASE BlogDemos SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      ALTER DATABASE BlogDemos SET ENABLE_BROKER
      ALTER DATABASE BlogDemos SET MULTI_USER

      The first command will set the SINGLE_USER permission on the database with ROLLBACK IMMEDIATE
      The Second command will enable the Broker Service
      The Third command will set the MULTI_USER permission on the database

      • Thanks that worked. Really appreciate the help.

        BTW, the above works for SELECT statements, if I change it to use a stored procedure with a parameter, then it stops sending notifications.

        For example, I change the SELECT statement to
        stored proc “procGetAllMessages” as shown below
        using (var command = new SqlCommand(cmdText: “procGetAllMessages”, connection: connection))
        {
        command.CommandType = CommandType.StoredProcedure;
        SqlParameter tvpParam = command.Parameters.AddWithValue(“@paramId”, “abc”);
        tvpParam.SqlDbType = SqlDbType.VarChar;
        command.Notification = null;
        var dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

  20. hey!
    i downloaded your source code from GitHub…but when i run it on VS i am getting an error
    “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”…
    can u help please?

      • that issue is resolved, now the project runs fine but i don’t get notification n also data is not updated in real time…. i tried all the solutions mentioned in above comments but they didn’t worked. I am using firefox and apparently dependency_OnChange does not fire. i am using my database with your code.
        My table:
        public partial class Emergency
        {
        public long EmergencyID { get; set; }
        public Nullable UserID { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }
        public Nullable EmergencyDate { get; set; }
        public Nullable EmergencyTime { get; set; }
        public string EmergencyType { get; set; }
        public Nullable EmergencyAttended { get; set; }

        public virtual User User { get; set; }
        }

        ———————————————————————————————————–
        public IEnumerable GetAllMessages()
        {
        var messages = new List();
        var connection = new SqlConnection(_connString);

        connection.Open();
        var command = new SqlCommand(@”SELECT [Latitude], [Longitude], [EmergencyType] FROM [dbo].[Emergency]”, 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 Emergency { Latitude = (string)reader[“Latitude”], Longitude = (string)reader[“Longitude”] });
        }
        return messages;
        }
        ————————————————————————————————————-

        please can u help on this?

  21. Thanks for your Topic it is really very useful.
    I’ve downloaded your project and I ran it , it show only the table on loading but the changes on the database records don’t show up on the page. Please help.
    is there any thing else need to be applied on sql 2014.
    I ran this command “SELECT name, is_broker_enabled FROM sys.databases” and it shows :
    BlogDemos=True.

  22. Great article Venkat. I’ve been struggling all evening with an issue with passing a username to my sqldependency onchange event and your examples got me going in the right direction and I solved my issue. I was not wiring up my SqlDependency call and SignalR hub like you are and that is so much more intuitive from how I was trying to do it.

    Thanks,
    Kevin

Leave a Reply

Your email address will not be published. Required fields are marked *