Posted by: Cirilo Meggiolaro | 10/26/2008

Tip of the day #12 – When the data has changed let me know

Imagine you have an application with a form that lists new orders to be approved. You have some options to get up to date information. You can use an automatic refresh based on time, a button that reloads the list and also you can let SQL Server notify your application when data has changed. Yes, you can and your application can as well.

The System.Data.SqlClient namespace provides an object named SqlDependency (since .NET framework 2.0) that allows your application handle an event raised when data changes in database.

The first question: Do I need to keep the connection opened? The answer is: No.

The second question: How easy is it to develop? The answer is: Easy but some very specific details.

 
What do you need?

 
1. Start a SqlDependency listener when your application starts;

SqlDependency.Start(connectionString);

 

2. A SqlCommand instance;

/// Select * cannot be used.
/// Schema + table name to define table.

SqlCommand com = new SqlCommand(@“select id, name, url from dbo.application”, conn);

 
3. A SqlDependency instance and a delegate to handle the OnChange event;

SqlDependency dependency = new SqlDependency(com);
dependency.OnChange += new OnChangeEventHandler(DataHasChanged);

 

4. A delegate method to handle the data when it has changed;

private void DataHasChanged(object sender, SqlNotificationEventArgs e) { }

 
5. Execute the command object (The example uses a DataTable as data source);

dt.Load(com.ExecuteReader(CommandBehavior.CloseConnection));

 
6. Stop the SqlDependency listener when your application ends.

SqlDependency.Stop(connectionString);

 
Important

1. You must enable the service broker in your database;

ALTER DATABASE [DatabaseName] SET ENABLE_BROKER

 
2. There are a lot of details related to the queries:

– Table name must be two part (schema + table name) like dbo.myTable for example;

– Stored procedures can be used;

– Select * cannot be used. Actually you should never use select * for any of your queries.

 
3. When working with UI to display the results of the change, you will face thread errors due to the fact your UI controls are created in a different thread than the delegate that receive the notification. One of the options is to invoke a delegate instead of direct call. ISynchronizeInvoke interface can help you to achieve that.

 
To get full details about the SqlDependency object, click here!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: