Yestreday, when I was learning about “whats new in .NET framewok 2.0”, I found new nice class. SQLDepenedncy gives you the ability to monitor if there were any changes in your data. I don’t have any useful example for now, but becouse trying to run SQLDep was a little bit hard, I present a (not so) complete tutorial how to do it.

First of all you must ensure that your data source support notifications.
In case of databases you can do that by executing an ALTER command.

ALTER DATABASE [database_name] SET ENABLE_BROKER;

And from now you can use notifications. But using it is not so simple as you may think.
Hear is some code for it. Firstly we create sql connection to our sever.

//create a connection string with SQlExpress
SqlConnectionStringBuilder conStrBuilder = new 
SqlConnectionStringBuilder();
//must be .SQLExpress; no local or (your_server_name)
conStrBuilder.DataSource = @".\SQLExpress";
conStrBuilder.IntegratedSecurity = true;
//select database
conStrBuilder["database"] = "tempdb";

Remember that if you are using SQLexpress you must use “.\SQLExpress” as DataSource. If you use “(local)” or “localhost” you won’t be able to connect. I think this is some sort of bug or sth.
Ok when we have our connection string we can start to monitor our data:

//turn on listener for reciving dependency notifications 
//   in this particular sql sever
System.Data.SqlClient.SqlDependency.Start(conStrBuilder.ToString());

Next, create SQLCommand to specify what data will be monitored:

System.Data.SqlClient.SqlCommand cmd = new 
SqlCommand("SELECT nazwa FROM dbo.temp", conn);

(conn is a connection to SQLServer)
Few words about command. The command cannot be an INSERT,UPDATE or DELETE and it must scpecify columns(cannot use *).
Now when we have command, we can create SQLDependency object and add notification handler.



//add command to dependency list
dep.AddCommandDependency(cmd);
//add notification handler
dep.OnChange += new OnChangeEventHandler(dep_OnChange);

A OnChange handler can be like this:
void  dep_OnChange(object sender, SqlNotificationEventArgs e)
{
//write some information about the event
Console.WriteLine("Change was made" + e.Info + " " + e.Source 
          + " " + e.Type);  
}

And that’s all folks.
Now you can put it together to a right program – execute it. Then start a sql console and try to INSERT,DELETE or UPDATE data in temp table in tempdb database. You should get a notification to your program! Nice :]

Ok that is all about programming – now something about blog. As you can see I start writing in English. I am aware that this isn’t my strong side but that’s why I made my decision. To improve my poor English language :-). So if you notice any error please give me constructive criticism. And second reason was to bring my blog to all people around the world (hehehe 🙂 j/k).