Refresh Database

[RusdyRIP]

i have a problem and it's hard to explain but don't know the solution for many years,pls help me...

A is viewing database from datagrid.
after that
B inserting a new data to database from different computer.
A still looking datagrid from database before updated.

if he don't click refresh or using timer how can the datagrid automatically refresh the data from database without using button or timer.
i don't like using timer cause it eat resources a lot.

thx
sorry bad english

[dglienna]

User B cannot EDIT a record in that table, because it cannot get a LOCK without refreshing first.

[DataMiser]

That depends on the connection methods, it is totally possible for user A to be viewing data in a grid and user B edit the data or insert a record on a different machine. User A will not see the edit or insert until a refresh is done at user A's location.

A timer is the best solution I can think of, if used right a timer takes very little resources

[RusdyRIP]

i am afraid timer can make problem too.
example :
a grid display 100 row with vertical scroll. the user is looking at row 90
because i am using timer the grid is refreshing every interval 1 second ( i make 1 second timer refresh) so the scroll is always at top position.it make user hard to see the grid
is there any solution?



User B cannot EDIT a record in that table, because it cannot get a LOCK without refreshing first.

sorry i don't really understand it,do u mean i must lock the database if anyone see the grid

thx

[DataMiser]

I would not recommend actually refreshing the data every second but rather create a routine to see if the data has changed. I am not sure if there is a method in dot net for this or not but I am sure there is a way especially if the other user is using your program as well.

I have a program that does refresh the data in a timer routine written in vb6. The way I got around the display issues was by using a second grid. When I refresh I refresh the second [invisible] grid, move the record pointer and then display the second grid and hide the first. This prevents screen flicker and speeds up the process of populating the grid as well. Works good in this case but the data being used is not usually more than a couple of hundred records.
.

[RusdyRIP]

I would not recommend actually refreshing the data every second but rather create a routine to see if the data has changed. I am not sure if there is a method in dot net for this or not but I am sure there is a way especially if the other user is using your program as well.

I have a program that does refresh the data in a timer routine written in vb6. The way I got around the display issues was by using a second grid. When I refresh I refresh the second [invisible] grid, move the record pointer and then display the second grid and hide the first. This prevents screen flicker and speeds up the process of populating the grid as well. Works good in this case but the data being used is not usually more than a couple of hundred records.
.

i think that's not really effective ways cause my data is more than hundred records.
i heard about using threading but i don't really understand how to use it

[dglienna]

If you let the DB LOCK the record, then user B can't edit it. Please wait...

[DataMiser]

i think that's not really effective ways cause my data is more than hundred records.
i heard about using threading but i don't really understand how to use it

I understand, and as I said it is probably not a good idea to refresh in a timer every n miliseconds but rather place some code in a timer to see if the data has changed and only when this is true do a refresh. This should nto be an issue other than the fact you will need to determine if the data has changed.

Of course you could lock the database and not allow anyone to edit it but in most cases that is not much of an option.

[DataMiser]

If you let the DB LOCK the record, then user B can't edit it. Please wait...Note in the OP he was refering to another user inserting a record so locking the record you are viewing would have no effect.

[RusdyRIP]

Note in the OP he was refering to another user inserting a record so locking the record you are viewing would have no effect.

yes,i think same too.if 2 user editing same record then it will have effect,but the problem is on grid report,thx

if i make a routine that check any changes on database,
i am afraid the server CPU Usage will raise,
if i make the routine then it will gonna check every second,if it have changes then it will refresh.
but if it don't have changes then it will gonna check to server.
if just one user i think it don't really will raise the cpu usage on server,but how about
if there is 5 user connecting and see the grid on 5 cpu,or every cpu open 5 report grid.
i think it will raise the cpu usage on server and can make the server crash.

thx

sorry bad english

[dglienna]

Just LOCK the table when it EDIT mode, and you won't have a problem.

[Alsvha]

I didn't see a mention of which database you're using, but if you are using for MS SQL Server 2005 or later, you have access to the SQLDependency which can notify clients when changes occur in the database.
I'd suspect other databases might have similar technologies.

But this way you can set up a dependency to the data being viewed, and when they change the notification will fire an event and you can then refresh the data for the other viewers based on the edit.

[DataMiser]

Just LOCK the table when it EDIT mode, and you won't have a problem. I do not understand how this could relate to the issue of wanting to refresh the view on the pc which is viewing the data that may have been added to or edited at another location? It the other pc locks the data for edit that is fine but that does not refresh the views on other pcs.

[DataMiser]

yes,i think same too.if 2 user editing same record then it will have effect,but the problem is on grid report,thx

if i make a routine that check any changes on database,
i am afraid the server CPU Usage will raise,
if i make the routine then it will gonna check every second,if it have changes then it will refresh.
but if it don't have changes then it will gonna check to server.
if just one user i think it don't really will raise the cpu usage on server,but how about
if there is 5 user connecting and see the grid on 5 cpu,or every cpu open 5 report grid.
i think it will raise the cpu usage on server and can make the server crash.

thx

sorry bad english

Not sure what you are thinking here, no need for 5 grids.

[RusdyRIP]

Not sure what you are thinking here, no need for 5 grids.

sory, i mean like this
i have application which using tab pages.
so user can show like 5 report/grid report.
if i use the check database method then i think the server will overload cause
the timer always send check routine and the 5 tab pages opened by user also make 5 timer and 5 check routine.
it will make server cpu usage rise.
thx

@Alsvha

i am using firebird database.may i know what the "name" of that dependancy.
so i can search the feature of the database.cause i think it will work
thx

@dglienna
i think i have same opinion like DataMiser,i don't really think it related
thx

[sotoasty]

If you don't have a way to notify User a when updates are applied, you will need to devise a way yourself. 1 way would be to create another table with a single field and single row. Whenever user B/C/D updates a record in the table User A needs refreshed, update the new table with a value (1 for refresh). You can then use a timer to check the value of this table. If a refresh is required, you can pop up a message box asking User A if he wants to update right now or wait. That way, User A gets to decide if he is in the middle of something important and delay the refresh. If he says no, activate a refresh button on the screen. When a refresh happens, update the new table with a value (say 0 for just refreshed).

[DataMiser]

Yep, that is what I was thinking as well,

[DataMiser]

sory, i mean like this
i have application which using tab pages.
so user can show like 5 report/grid report.
if i use the check database method then i think the server will overload cause
the timer always send check routine and the 5 tab pages opened by user also make 5 timer and 5 check routine.
it will make server cpu usage rise.
thx



You only need 1 timer and 1 check routine. Look at the post by sotoasty. This table could have one record per table with different columns for different users.

Of course any code you add will increase processing but if this is done properly it should be minimal.

[RusdyRIP]

If you don't have a way to notify User a when updates are applied, you will need to devise a way yourself. 1 way would be to create another table with a single field and single row. Whenever user B/C/D updates a record in the table User A needs refreshed, update the new table with a value (1 for refresh). You can then use a timer to check the value of this table. If a refresh is required, you can pop up a message box asking User A if he wants to update right now or wait. That way, User A gets to decide if he is in the middle of something important and delay the refresh. If he says no, activate a refresh button on the screen. When a refresh happens, update the new table with a value (say 0 for just refreshed).

i see,but i have one opinion again..soryy..

let's say timer interval is 5 second to prevent cpu usage rise to high
first A is looking Grid with row 10,
2 second after that B is looking Grid with row 10 ( different computer same application)

then timer check if the table value is 1 or 0,and the value is 0 so there is no pop up asking.

the C is inserting record on database on different computer again.then update the new table value to 1

the grid at A computer will trigger first to pop up cause the value is 1, and asking if he wants to update and it says yes,so the grid is updated and the new table value become 0

the grid at B computer will not trigger because it check the new table value is 0,
i think it will become bugs

thx

sorry bad english


@datamiser

yes i just have 1 timer and 1 check routine,but i have more than 5 grid at different forms,
and i must implement each timer at each form that have grid

thx

[DataMiser]

I think you are seeing more complexity than what is here.

Let's say you have a 10 user setup with 5 tables that may be open and may need to be refreshed. You would want one record per table to monitor with one field per connected pc in each record as well as a field indicating which table you are referring to.

When a user changes data in a table the flag would be set to 1 in that record for the other 9 users but not the user who made the change.

The timer would execute a simple query select TableName from UpdatedDefs where thispc=1. The query would return a list of tables that may need to be refreshed, assuming they were being viewed at the time. If any records are returned then a prompt asking the user if they want to refresh now if so execute a routine that refreshes the proper views that are active and have changed otherwise you could enable a button like suggested which would call the same code.

Basically you are looking at one simple query that at most will return 5 records consisiting of only one field each and if you are doing it every 5 seconds then surely yoru server has the power to handle such a trival transaction load. If not then I feel sorry for the users when they actually need to refresh a large data table.

[DataMiser]

yes i just have 1 timer and 1 check routine,but i have more than 5 grid at different forms,
and i must implement each timer at each form that have grid

thxYou do not need a timer on every form, this can be done with one timer on the main form if done correctly.

[RusdyRIP]

I think you are seeing more complexity than what is here.

Let's say you have a 10 user setup with 5 tables that may be open and may need to be refreshed. You would want one record per table to monitor with one field per connected pc in each record as well as a field indicating which table you are referring to.

When a user changes data in a table the flag would be set to 1 in that record for the other 9 users but not the user who made the change.

The timer would execute a simple query select TableName from UpdatedDefs where thispc=1. The query would return a list of tables that may need to be refreshed, assuming they were being viewed at the time. If any records are returned then a prompt asking the user if they want to refresh now if so execute a routine that refreshes the proper views that are active and have changed otherwise you could enable a button like suggested which would call the same code.

Basically you are looking at one simple query that at most will return 5 records consisiting of only one field each and if you are doing it every 5 seconds then surely yoru server has the power to handle such a trival transaction load. If not then I feel sorry for the users when they actually need to refresh a large data table.


sorry i don't really understand it.
10 user (1 to 10)
5 Table(A,B,C,D,E,F)

table i must have :
Name:RefreshTable
GridTableName|Flag
A |0
B |0
C |0
D |0
E |0
F |0

if 1 is inserting table A then he changed RefreshTable,record A will update the flag to 1.
after the user say yes to update the record A will update flag to 0

is that right?looks like this will gonna work,but still think that different time will make bugs like i said first,cause timer at user 2 different from user 1 and when user 1 is updated,user 2 check that flag is 0

thx




You do not need a timer on every form, this can be done with one timer on the main form if done correctly.

you are right,i think i can set the timer on mainform.

[dglienna]

You really have to learn about table locking and concurrency. You can lock for Update, or Exclusive access. If you have two users, SQL Server takes care of the locks and the uses do their thing.

TransA: get U lock on row
TransB: wants U lock on row, has to wait
TransA: convert U lock to X, does the work and (at some time) move on
TransB: convert U to X lock (A has now released its lock), does the work an move on

[DataMiser]

It seems that something is being missed here with the talk of record locking. User A is viewing the data. User B changes the data. User A will not see the changes until User A does a refresh. The lock for edit does nothing in the way of notifing User A that the data in the table has been changed by another user.

The locking will stop 2 people from making changes to the same record at the same time or table but when several users are viewing and one makes a change the lock does nothing for the visibility unless the data is refreshed on the other machines afterwards.

[DataMiser]

As for the table layout. Lets say we have 5 Tables A,B,C,D,E and 10 Pcs Named PC1 - PC10.

Lets say the table name is RefreshMe and the table columns are tblName | PC1 | PC2 | PC3 ..........

If the user on PC3 needs to check the changed tables the query would be select tblName from RefreshMe where PC3=1

Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed. There may be a better way to do this but I do not see a problem here from a logical standpoint other than you must account for each pc that will be connected by having the proper field in the table.

On the pc that does the edit/insert it would update all the columns on the record for the table in question except the one for the PC doing the edit.

Does that make sense to you?

[RusdyRIP]

As for the table layout. Lets say we have 5 Tables A,B,C,D,E and 10 Pcs Named PC1 - PC10.

Lets say the table name is RefreshMe and the table columns are tblName | PC1 | PC2 | PC3 ..........

If the user on PC3 needs to check the changed tables the query would be select tblName from RefreshMe where PC3=1

Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed. There may be a better way to do this but I do not see a problem here from a logical standpoint other than you must account for each pc that will be connected by having the proper field in the table.

On the pc that does the edit/insert it would update all the columns on the record for the table in question except the one for the PC doing the edit.

Does that make sense to you?

i understand
i think like this :

"Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed"

if the table B or D have been inserted/updated ,ie on PC4 then the flag on other pc will set to 1 (just table B or D).
if the other pc is viewing before updated/inserted ,it's okay

but if the other pc is viewing after updated(i mean like pc2 first don't open,then he open after updated)
don't it will be strange,because when the pc2 open, the timer also trigger to check the flag and found that the flag is 1 then pop up asking him to refresh.

sorry bad english

[DataMiser]

I would of course set it to 0 when I open the table

[RusdyRIP]

I would of course set it to 0 when I open the table

nice,i think that complete all my question

really thank you

[dglienna]

So, you finish everything up (about 300 lines of code) that works perfectly.'

What happens when they add a field or another user?

Look up locking. What would you do with 500 concurrent users? (besides lock up the server)

[DataMiser]

It was a simple suggestion that would work on a small scale multiuser application. Large scale would require different methods but that is beside the point.

Could you explain how locking a record for edit will help anyone using the system see the modified data if they were already viewing the data in a grid display? If there is a way that this would work it is one that I am unaware of. At first I thought you simply missunderstood the question but now it is hard to believe that you do not understand, either you know something I do not or ...

[dglienna]

If the grid is active, you'd use RowVersion

http://www.mssqltips.com/tip.asp?tip=1501

[DataMiser]

I looked at the link but I still do not see where this is of any use to the original question. If user a is viewing a grid that is currently displaying 100 records and User B inserts a record User A will not see the record that was added until User A refreshs the data and there is no indication if any record has been added prior to this action.

I'll ask one more time how does a row lock for edit on a different machine magically refresh records on yet another machine or 499 machines?

[dglienna]

User B checks the ROWVERSION table to see WHICH records have been modified. It refreshes the table if changes are pending.

Look at this site: http://www.devexpress.com/Products/NET/Controls/WinForms/Grid/

[RusdyRIP]

User B checks the ROWVERSION table to see WHICH records have been modified. It refreshes the table if changes are pending.

Look at this site: http://www.devexpress.com/Products/NET/Controls/WinForms/Grid/

do u mean it refreshed without making any code?

thx

[dglienna]

Yes. They right the code to only get values for the results that are VISIBLE on the screen (Server Mode)

If your query returned 1000 records, and your grid showed 10 lines, it'd load in the time to query 10 records. Pretty cool controls.