SQL dependency

[percramer]

Hi,

we are interrested in the this control, but i can't find anywhere how to work with SQLdependency?

Regards,

Per

[Shalex]

Please try this code:

Code: Select all

class Program {
    static string connString = "Data Source=mssqlserver;user id=sa;password=;Initial Catalog=test;";
    static void Main(string[] args) {
        SqlDependency.Start(connString);
        GetData();
        SqlDependency.Stop(connString);
    }

    public static void GetData()
    {
        using (SqlConnection connection = new SqlConnection(connString))
        {
        connection.Open();
        SqlCommand cmd = new SqlCommand("select deptno, dname, loc from dbo.dept", connection);
        SqlDependency depend = new SqlDependency(cmd);
        depend.OnChange += new OnChangeEventHandler(OnDataChange);

        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
            Console.WriteLine(rdr[0] + "\t" + rdr[2] + "\t" + rdr[1]);
        }

        Console.WriteLine("\r\nPress Enter to exit \r\nOR \r\nModify the table in the database to make SqlDependency work");
        Console.ReadLine();
    }

    public static void OnDataChange(object sender, SqlNotificationEventArgs e)
    {
        Console.WriteLine(String.Format("{0}Result has changed{0}Source {1}{0}Type {2}{0}Info {3}{0}", Environment.NewLine, e.Source, e.Type, e.Info));
        if (e.Info != SqlNotificationInfo.Invalid)
            GetData();
        else
            Console.WriteLine("The query is invalid for notification");
    }
}

Note that the select command must be with explicitly specified column names and with the schema prefix for the table name (e.g.: "select deptno, dname, loc from dbo.dept"). Otherwise, you will get the "The query is invalid for notification" message.