Click here to Skip to main content
15,916,457 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This question has been asked by many times but i've read all these on different forums and this as well. But can't find my solution. I am using SQL server 2008 R2, Visual Studio 2010 , C# in Windows Application and Try to use the SQL dependency.Before the actual application , i've developed a test application to check the SQL Dependency Working. I've just copy and paste the code from http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/ But do all steps properly such as in Database for Service Broker, Queue, Grant permissions Etc Everything sound like fine but not working as that article shows Here is my code Please Let me if i did something wrong... Obviously There is something Wrong But i want to figure out. Already i've spent two days on it..
C#
private bool DoesUserHavePermission()
    {
        try
        {
            SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);

            // will throw an error if user does not have permissions
            clientPermission.Demand();

            return true;
        }
        catch
        {
            return false;
        }
    }
    private void GetNames()
    {
        if (!DoesUserHavePermission())
            return;

        lbNames.Items.Clear();

        //  You must stop the dependency before starting a new one.
        //  You must start the dependency when creating a new one.
        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT FirstName, LastName FROM dbo.[Users]";

                cmd.Notification = null;

                //  creates a new dependency for the SqlCommand
                SqlDependency dep = new SqlDependency(cmd);

                //  creates an event handler for the notification of data
                //      changes in the database.
                //  NOTE: the following code uses the normal .Net capitalization methods, though
                //      the forum software seems to change it to lowercase letters
                dep.OnChange += new OnChangeEventHandler(dep_onchange);
                cn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        lbNames.Items.Add(dr.GetString(0) + " " + dr.GetString(1));
                    }
                }
            }
        }
    }


    void dep_onchange(object sender, SqlNotificationEventArgs e)
    {
        // this event is run asynchronously so you will need to invoke to run on UI thread(if required).
        ISynchronizeInvoke i = (ISynchronizeInvoke)this;
        if (i.InvokeRequired)
            lbNames.BeginInvoke(new MethodInvoker(GetNames));
        else
            GetNames();

        // this will remove the event handler since the dependency is only for a single notification
        SqlDependency dep = sender as SqlDependency;

        //  NOTE: the following code uses the normal .Net capitalization methods, though
        //      the forum software seems to change it to lowercase letters
        dep.OnChange -= new OnChangeEventHandler(dep_onchange);
    }

    private void Form1_FormClosing(object sender, FormClosingEventArgs e)
    {
        SqlDependency.Stop(connectionString);
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        GetNames();
        button1.Enabled = DoesUserHavePermission();
    }

    // button to show other form with textboxes to insert data into database
    private void btnShowForm_Click(object sender, EventArgs e)
    {
        Form2 f = new Form2();
        f.Show();
    }

Please Help me out what i need to do exactly

Thanx in Advance
Posted
Comments
Sunasara Imdadhusen 21-May-13 3:45am    
What is your error?
[no name] 21-May-13 5:21am    
it shows no error... but not display data when changes occur in database

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900