none
For Experts: Propagate changes from Database to GridView RRS feed

  • Question

  • Hi,

    I have a question to an expert: Is there a way to get en event from the database in case the underlaying data in the database table has been changed? Normally an application would poll the database with a Refresh() call. But I want to get informed about a change in (any row) of one particular table from the database itself to avoid polling.

    I just need an idea.
    Thursday, March 11, 2010 1:38 PM

Answers

  • Hello,

     

    Welcome to MSDN Forums!

     

    I think what you are looking for is the SqlDependency class if you are using SQL Server database.   This class represents a query notification dependency between an application and an instance of SQL Server 2005/2008.  

     

    Here is a detailed sample about how to use SqlDependency in a Windows Application:

    http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx

     

    Other great references about the SQL Server Query Notifications:

    http://msdn.microsoft.com/en-us/library/ms130764.aspx

    http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/

     

    If you have any questions, please feel free to let me know.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, March 12, 2010 2:53 AM
    Moderator
  • Hi Lingzhi Sun and all interested people,

    I tried the sample given in the first url (http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx ). I just needed the 'Watcher Application', because I used the SQL 2008 Server Management Studio to change the data of the 'Customers' table of database 'NORTHWND' when testing.

    The sample is straight forward and easy to build. I used the C# code in Visual C# Studio 2008 starting from a Windows Forms Application and followed all steps 1..15.

    The only point, which took a bit longer was how to enable the Broker for my database 'NORTHWND'. Here I needed the SQL 2008 Server Management Studio to execute the following SQL command:
          ALTER DATABASE NORTHWND SET ENABLE_BROKER;

    First, I got endless (invalid) notifications, bacause of a missing "dbo." in the FROM table specifier. I had to change the code to:
            private string GetSQL()
            {
                return "SELECT CustomerID, CompanyName, ContactName, Phone FROM dbo.Customers;";
            }

    For those, who perhaps do not know, where to find the connection string (and what to insert), here my setting in file "app.config" Hint: On my PC the wizzard was not able to create a proper connection string for SQLSERVER, it was always needing a database .mdf file, which is not needed here in my case:
        <connectionStrings>
            <add name="GUI_Update.Properties.Settings.northwndConnectionString"
                connectionString="Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\SQLEXPRESS;database=NORTHWND;"
                providerName="System.Data.SqlClient" />
        </connectionStrings>


    After this, the sample was working fine.


    Again many thanks to your help Lingzhi Sun!

    Best Regards
    Marcel

    Wednesday, March 17, 2010 9:16 AM

All replies

  • Hello,

     

    Welcome to MSDN Forums!

     

    I think what you are looking for is the SqlDependency class if you are using SQL Server database.   This class represents a query notification dependency between an application and an instance of SQL Server 2005/2008.  

     

    Here is a detailed sample about how to use SqlDependency in a Windows Application:

    http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx

     

    Other great references about the SQL Server Query Notifications:

    http://msdn.microsoft.com/en-us/library/ms130764.aspx

    http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/

     

    If you have any questions, please feel free to let me know.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, March 12, 2010 2:53 AM
    Moderator
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 15, 2010 1:15 AM
    Moderator
  • Hi Lingzhi Sun,

    many thanks to your excellent response. This is
    exactly , what I was looking for. I'm upset to test the sample.

    I think, you could mark this entry as solved.

    Best Regards
    Marcel
    Monday, March 15, 2010 9:33 AM
  • Hi Marcel,

     

    It's my pleasure!  :)

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, March 16, 2010 1:23 AM
    Moderator
  • Hi Lingzhi Sun and all interested people,

    I tried the sample given in the first url (http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx ). I just needed the 'Watcher Application', because I used the SQL 2008 Server Management Studio to change the data of the 'Customers' table of database 'NORTHWND' when testing.

    The sample is straight forward and easy to build. I used the C# code in Visual C# Studio 2008 starting from a Windows Forms Application and followed all steps 1..15.

    The only point, which took a bit longer was how to enable the Broker for my database 'NORTHWND'. Here I needed the SQL 2008 Server Management Studio to execute the following SQL command:
          ALTER DATABASE NORTHWND SET ENABLE_BROKER;

    First, I got endless (invalid) notifications, bacause of a missing "dbo." in the FROM table specifier. I had to change the code to:
            private string GetSQL()
            {
                return "SELECT CustomerID, CompanyName, ContactName, Phone FROM dbo.Customers;";
            }

    For those, who perhaps do not know, where to find the connection string (and what to insert), here my setting in file "app.config" Hint: On my PC the wizzard was not able to create a proper connection string for SQLSERVER, it was always needing a database .mdf file, which is not needed here in my case:
        <connectionStrings>
            <add name="GUI_Update.Properties.Settings.northwndConnectionString"
                connectionString="Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\SQLEXPRESS;database=NORTHWND;"
                providerName="System.Data.SqlClient" />
        </connectionStrings>


    After this, the sample was working fine.


    Again many thanks to your help Lingzhi Sun!

    Best Regards
    Marcel

    Wednesday, March 17, 2010 9:16 AM
  • Hi Marcel,

     

    Great!   Thank you so much for sharing the solution here.  It will be definitely beneficial to other community members. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 22, 2010 1:42 AM
    Moderator