CODEWITHSUNDEEP
c#sqlsqldependency
Dr Schizo
Dr Schizo

Reputation: 4362

SqlDependency constant database hits

I have just copied the example from an official Microsoft source http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx and I am baffled by it. Having run the application, it makes constant database hits even though nothing is using that table? I thought when that table actually changes the event will fire? I don't want it to make constant DB calls every second, that is awful.

Have I done something wrong? I imagine I have not sure what though. Anybody have a link to a good example preferably not MSDN.

Thanks in advance, Onam.

This is the SQL:

return "SELECT [ID],[FromMachine],[FromStore],[FromUser] FROM dbo.Store_Message";

As requested, all code:

    public partial class Form1 : Form
{
    string connectionString = "server=localhost;database=usicoal;uid=admin;password=";

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        GetNames();
    }

    private bool DoesUserHavePermission()
    {
        try
        {
            SqlClientPermission clientPermission = new SqlClientPermission(PermissionState.Unrestricted);
            clientPermission.Demand();
            return true;
        }
        catch
        {
            return false;
        }
    }

    void dep_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (this.InvokeRequired)
        {
            this.BeginInvoke(new MethodInvoker(GetNames));
        }
        else
        {
            GetNames();
        }
        SqlDependency dep = sender as SqlDependency;
        dep.OnChange -= new OnChangeEventHandler(dep_OnChange);
    }

    private void GetNames()
    {
        if (!DoesUserHavePermission())
            return;

        SqlDependency.Stop(connectionString);
        SqlDependency.Start(connectionString);

        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT ID FROM dbo.[BTE_SIMPLE_STORE_MESSAGE]";
                cmd.Notification = null;

                SqlDependency dep = new SqlDependency(cmd);
                dep.OnChange += new OnChangeEventHandler(dep_OnChange);

                cn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                    }
                }
            }
        }
    }
}

Upvotes: 4

Views: 6196

Answers (2)

Dr Schizo
Dr Schizo

Reputation: 4362

Found that the above code was fine when I:

  • Right click database select properties
  • Select options
  • Set compatibility level to SQL Server 2008(100)
  • OK

Bang the code works correctly.

Upvotes: 1

Andreas
Andreas

Reputation: 2242

SqlDependency usually works as you expected it. It fires events if something was changed in your db. Constant calls occure if something is wrong with your query. It's important to use the full two part table name (e.g. dbo.TableName).

The MSDN documentation is not as bad - have a look at this more up-to-date article or this one version.

Upvotes: 1

Related Questions