Update User Interface when there is change in database

[NitinSawant]

I'm developing app. using C# and SQL Server 2005,

What i'm trying to do is "Update User Interface when there is change in database",

suppose, there is datagrid on a form..

two different users on different computers -open the form simultaneously.

one user edits some value..
then the same change displayed on the second user's form.

how to do this??

I'm stuck.. help

[Bassem]

Well, I did it before as a web application not a desktop application. I used JavaScript to do that, even AJAX didn't help me much in my specific case, and a multi-threading implementation.
I think it is difficult what you are seeking, I heard about network stream-socket in c# but didn't try to do, also multi-threading, your application should be implemented according to your case.

Regards,
Bassem

[NitinSawant]

How to automatically tell the connected clients that there is change in database/tables?

[Frinavale]

I've never done this myself but I'm sure an expert here has....I think you can configure MS Sql Server 2005 to send a notification whenever changes are made.

If it's possible you may be able receive that notification in a .NET application.

If you can do that then the solution you're looking for is probably going to involve Sockets....but I'm not sure what the SQL Server can do so I might be wrong here.

My initial thoughts on this are:

• Implement a Socket Server that listens for changes made to the MS Sql
• Server also listens for Socket connections (from clients)
  When the Socket Server detects a change made to the MS SQL Server, it sends a notifications to all Socket Connections connected to it.
• Implement .NET client applications that use Sockets to connect to the Socket Server....they receive the notification and do whatever you need to do when stuff happens

[Curtis Rutland]

There is no way that I am aware of to get the SqlServer to send clients notification of changes. (myself being no great expert, so maybe someone else knows)

There is, however, a way for the client to determine if the table has changed: use checksums.

Consider this code sample:

string connstr = @"Server=FAKENAME\SQLEXPRESS;Database=Sample;Trusted_Connection=True;";
string sql = "SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHKSUMVALUE FROM Test";
SqlConnection conn = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand(sql, conn);
long oldsum = 0;
conn.Open();
do
{
    long newsum = Convert.ToInt64(cmd.ExecuteScalar());
    Console.WriteLine("Checksum:  {0}", newsum);
    Console.WriteLine("Old Checksum: {0} \nTable{1}changed.", oldsum, (oldsum == newsum ? " has not " : " "));
    Console.WriteLine("Press 'Y' to checksum again, any key to exit.{0}", Environment.NewLine);
    oldsum = newsum;
}while (Console.ReadKey(true).Key == ConsoleKey.Y);
conn.Close();

So, if you dropped that into a console application, and ran it, it would get you an aggregated checksum of your table. Then, while it waits for you to press 'y', you can go make a change in your DB, and then come back and press 'y.' You will notice that the checksum changes.

So, what you can do, is periodically do a checksum from your client. If the most recent checksum differs from the previous one, the table has been updated and must be reloaded.

[NitinSawant]

Thanks a lot @insertAlias, Frinavale and Bassem

I searched in MSDN "Using an Asynchronous Server Socket"
( http://msdn.microsoft.com/en-us/library/5w7b7x5f.aspx )

Will the Socket server send notification to all the thousands of clients?

There will be disaster if one of the client does not receive data change notification..

[Bassem]

So, it is not what Yahoo does, right?
I see my browser in yahoo mail box reload every time, something like timer.

[JamieHowarth0]

Hi guys,

You've all missed the slightly obvious one - SqlCacheDepende ncy class.
It's been a while since I did this but basically you can set up your data to be bound to a SqlDataReader which is linked to a SqlCacheDepeden cy object. When the data in the underlying database changes, the object will receive a notification that the data has been updated (note: only SQL 2005 support cache dependencies out-of-the-box, for SQL 2000 you have to create a couple of tables - if you Google "SqlCacheDepend ency" there's plenty of great tutorials on how to set it up), and then you can re-query your SqlDataReader or SqlDataSet as a result.

Hope this helps.

codegecko

[PRR]

SqlCacheDepende ncy is more or less like cache, except it monitors a Sql table and update as soon as there is a change in that table. I guess using XMLHttpRequest and updating will be a good option...
I do remember reading something about making your own control: implementing INamingContaine r interface overriding the render method ... something like that ...
I dont clearly remember the details... but i guess you could implement a timer ...

[Frinavale]

:) I love it when I learn something new!
Thanks guys :)

[NitinSawant]

Will the Socket server send notification to all the thousands of clients?

I'm still confused..

[Bassem]

SqlCacheDepende ncy is more or less like cache, except it monitors a Sql table and update as soon as there is a change in that table. I guess using XMLHttpRequest and updating will be a good option...
I do remember reading something about making your own control: implementing INamingContaine r interface overriding the render method ... something like that ...
I dont clearly remember the details... but i guess you could implement a timer ...

I used XMLHttpRequest and instead of poll the server after a specific duration I used Thread-Event to control the Response on the server, it was part of my graduation project and it worked fine, minimizing the number of requests to cut off redundant for efficiently using of the available bandwidth. My implementation depended on JavaScript, but it was my case then.

[Bassem]

--In the memory of SqlCacheDepende ncy
What if my application doesn't implement a database?

[Curtis Rutland]

I'm still confused..

Have you attempted to implement what CodeGecko has suggested?

Test that out, and get back to us.

[PRR]

--In the memory of SqlCacheDepende ncy
What if my application doesn't implement a database?

Then i guess you need not worry about caching :) For SqlCacheDepende nc you need DB and that too sql server 2000/2005. How to use
some rules to follow