locked
no response about SqlDependency used in SQLServer 2005 RRS feed

  • Question

  •  

    i have followed the sample to build a app, but the app have no response when database have updating process.

     

    here is the sample:http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

     

    open the database notification function already

    ALTER DATABASE <DatabaseName> SET ENABLE_BROKER

    i have changed the connection with database and SQL command.

    much appreciate for some advice on this problem.

    here is my code:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Security.Permissions;
    namespace Icon
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private int changeCount = 0;


            private const string tableName = "Inventory";

            private const string statusMessage = "{0} changes have occurred.";


            // The following objects are reused

            // for the lifetime of the application.

            private DataSet dataToWatch = null;

            private SqlConnection connection = null;

            private SqlCommand command = null;


            private void timer1_Tick(object sender, EventArgs e)
            {

                /here to update the data every 4 sec
                           SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "UPDATE [sup_Log_tab] SET [EventTime] = getdate() where msrepl_tran_version='E48EBA91-F937-459F-B782-470ACBD5E629'";
                SqlConnection con = new SqlConnection(GetConnectionString ());
                cmd.Connection = con;
                con.Open();
                int c=cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                this.label2.Text = DateTime.Now.ToString()+" "+c.ToString ()+" rows";
            }

            string conString = @"server=**;database=**;user id=**;password=***";
            private void Form1_Load(object sender, EventArgs e)
            {
                button1.Enabled = CanRequestNotifications();


              
            }

            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {

                // This event will occur on a thread pool thread.

                // Updating the UI from a worker thread is not permitted.

                // The following code checks to see if it is safe to

                // update the UI.

                ISynchronizeInvoke i = (ISynchronizeInvoke)this;


                // If InvokeRequired returns True, the code

                // is executing on a worker thread.

                if (i.InvokeRequired)
                {

                    // Create a delegate to perform the thread switch.

                    OnChangeEventHandler tempDelegate =

                    new OnChangeEventHandler(dependency_OnChange);


                    object[] args = { sender, e };


                    // Marshal the data from the worker thread

                    // to the UI thread.

                    i.BeginInvoke(tempDelegate, args);


                    return;

                }


                // Remove the handler, since it is only good

                // for a single notification.

                SqlDependency dependency =

                (SqlDependency)sender;


                dependency.OnChange -= dependency_OnChange;

     

                // At this point, the code is executing on the

                // UI thread, so it is safe to update the UI.

                ++changeCount;

                label1.Text = String.Format(statusMessage, changeCount);


                // Add information from the event arguments to the list box

                // for debugging purposes only.

                listBox1.Items.Clear();

                listBox1.Items.Add("Info: " + e.Info.ToString());

                listBox1.Items.Add("Source: " + e.Source.ToString());

                listBox1.Items.Add("Type: " + e.Type.ToString());

     

                // Reload the dataset that is bound to the grid.

                GetData();

            }


            private void GetData()
            {

                // Empty the dataset so that there is only

                // one batch of data displayed.

                dataToWatch.Clear();


                // Make sure the command object does not already have

                // a notification object associated with it.

                command.Notification = null;


                // Create and bind the SqlDependency object

                // to the command object.

                SqlDependency dependency =new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);


                using (SqlDataAdapter adapter =

                new SqlDataAdapter(command))
                {

                    adapter.Fill(dataToWatch, tableName);


                    dataGridView1.DataSource = dataToWatch;

                    dataGridView1.DataMember = tableName;


                }

            }


            private string GetSQL()
            {

                return "SELECT  [OmniComResourceName],[InstanceNo],[ComputerName],[EventTime],[ServiceEventId]  FROM [sup_Log_tab] ";

            }


            private string GetConnectionString()
            {

                // To avoid storing the connection string in your code,

                // you can retrive it from a configuration file using the

                // System.Configuration.ConfigurationSettings.AppSettings property.


                // In general client applications don't need to incur

                // overhead of connection pooling.

                return conString ;


            }


            private bool CanRequestNotifications()
            {

                // In order to use the callback feature of the

                // SqlDependency, the application must have

                // the SqlClientPermission permission.

                try
                {

                    SqlClientPermission perm =

                    new SqlClientPermission(

                    PermissionState.Unrestricted);


                    perm.Demand();


                    return true;

                }

                catch
                {

                    return false;

                }

            }

     

            private void Form1_FormClosed(object sender, FormClosedEventArgs e)
            {
                SqlDependency.Stop(GetConnectionString());

                if (connection != null)
                {

                    connection.Close();

                }


            }

            private void button1_Click(object sender, EventArgs e)
            {
                changeCount = 0;

                label1.Text = String.Format(statusMessage, changeCount);


                // Remove any existing dependency connection, then create a new one.

                SqlDependency.Stop(GetConnectionString());

                SqlDependency.Start(GetConnectionString());


                if (connection == null)
                {

                    connection = new SqlConnection(GetConnectionString());

                }


                if (command == null)
                {

                    // GetSQL is a local procedure that returns

                    // a paramaterized SQL string. You might want

                    // to use a stored procedure in your application.

                    command = new SqlCommand(GetSQL(), connection);


                }

                if (dataToWatch == null)
                {

                    dataToWatch = new DataSet();

                }


                GetData();


            }
        }
    }

    Wednesday, August 8, 2007 4:27 AM

All replies

  • I don't have an answer to your question in the post, but I do have an answer to the question in your "signature" ["How can I become a real MS employee?"]:

    I guess, one of the minimum requirements (in addition to any others) is to be able to tell the difference between SQL Server Query Notifications and SQL Server Notification Services.

    This particular forum is for NS-related discussions.

    Most likely, your question about QN will be answered here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91&SiteID=1

    Wednesday, August 8, 2007 12:55 PM
  •  

    yeah i know the distance to be professional.

     

    this code is worked in my laptop, but not on work server. i will try again tomorrow.

     

    thanks

    Wednesday, August 8, 2007 2:21 PM