Dynamically refresh datagridview

[swethajain]

Hi
Let's say that I have a windows application in which I have a form with some controls that are bound to a dataset. A DataAdapter or TableAdapter fills the dataset on form load with a large number of records and user can navigate and change the details for each record. This works fine ... but this app runs on a network. And let's say meanwhile you've loaded your form and you're navigating through it (for half an hour let's say whithout saving changes or reloading the dataset) another user modifes the data and updates the database but as long as you have your application running you can't see those changes !!! what is the method to know whether the database has been updated or how to refresh the datagrid view with the new changes updated by other users while the application is running?

code is

Imports System.Data.OleDb
Public Class Main_Page

Private Sub Main_Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Lusail_Test_DBDataSet.Status' table. You can move, or remove it, as needed.
Me.StatusTableAdapter.Fill(Me.Lusail_Test_DBDataSet.Status)
'TODO: This line of code loads data into the 'Lusail_Test_DBDataSet.Priority' table. You can move, or remove it, as needed.
Me.PriorityTableAdapter.Fill(Me.Lusail_Test_DBDataSet.Priority)
'TODO: This line of code loads data into the 'Lusail_Test_DBDataSet.Owner' table. You can move, or remove it, as needed.
Me.OwnerTableAdapter.Fill(Me.Lusail_Test_DBDataSet.Owner)
'TODO: This line of code loads data into the 'Lusail_Test_DBDataSet.Project_Details' table. You can move, or remove it, as needed.
Me.Project_DetailsTableAdapter.Fill(Me.Lusail_Test_DBDataSet.Project_Details)
datagrid_width()
End Sub
Public Function datagrid_width()
Project_DetailsDataGridView.Columns(0).Width = 220
Project_DetailsDataGridView.Columns(1).Width = 160
Project_DetailsDataGridView.Columns(2).Width = 110
Project_DetailsDataGridView.Columns(3).Width = 110
Project_DetailsDataGridView.Columns(4).Width = 110
Project_DetailsDataGridView.Columns(5).Width = 110

End Function

Private Sub bt_Add_New_Project_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Add_New_Project.Click
Add_New_Project.Show()
End Sub

Private Sub bt_Edit_Project_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_Edit_Project.Click
Edit_Project.Show()
End Sub

Private Sub Project_DetailsDataGridView_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles Project_DetailsDataGridView.CellEndEdit
Dim row_index As Integer = e.RowIndex
Dim project_name As String = Project_DetailsDataGridView.Item(0, row_index).Value.ToString
Dim owner As String = Project_DetailsDataGridView.Item(1, row_index).Value.ToString
Dim priority As String = Project_DetailsDataGridView.Item(2, row_index).Value.ToString
Dim status As String = Project_DetailsDataGridView.Item(3, row_index).Value.ToString
Dim start_date As Date = Project_DetailsDataGridView.Item(4, row_index).Value
Dim end_date As Date = Project_DetailsDataGridView.Item(5, row_index).Value

Try
myconnection.Open()
Dim str_updt As String = "update [Project_Details] set status='" & status & "',priority='" & priority & "',[Start_Date]=#" & start_date & "#,[End_Date]=#" & end_date & "#,[Owner]='" & owner & "' where [project_name]='" & project_name & "' "
Dim cmd As New OleDbCommand(str_updt, myconnection)
cmd.ExecuteNonQuery()
myconnection.Close()
Me.Project_DetailsTableAdapter.Fill(Me.Lusail_Test_DBDataSet.Project_Details)
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End Sub
End Class


Thanks

 

[jmcilhinney]

There generally isn't one. You can build your app to provide automatic data refreshes at intervals or manual data refreshes on a button click but there's generally no mechanism for the database to notify the app that data has changed.

If you're using SQL Server then you can make use of the SqlDependency class, although I toyed with it out of interest at one stage and couldn't get it to work. I did intend to revisit it but haven't got around to it yet. It's not appropriate where the number of clients is large either.

Some other large-scale databases may provide something similar but most won't even do that, so there's basically no mechanism for notifications. It would just be done by polling.

 

[swethajain]

Hi
Thanks for the immediate reply. The only method to refresh the datagrid is to do it for some interval of time. Is there anything like when we press F5 the data will be refreshed?

Thanks

 

[jmcilhinney]

Is there anything like when we press F5 the data will be refreshed?

Sure there is, if you code it. If you want to retrieve data then you have to write code to retrieve data. Any time you want to retrieve data you need to execute that code. If you want to trap the F5 key and execute your code then you can certainly do that, but it's up to you to write the code.

If you have a reasonable amount of data then you probably don't want to be getting it all every time. You might like to added a modified time column to your tables. That will allow you to only retrieve records that have been saved since the last time you retrieved data, which might be none in some cases.

 

[swethajain]

What you said is,
First create a modified time column which stores the time when the record is updated in the database
Second, i should get the time when the datagridview is loaded( ie, it will the time when the application is runned) .

My questions are,
1) How will we get the time when the application is runned?
2) How to refresh depending on the modified time?

I have used the timer and the datagrid view is getting refreshed but the problem is our database is large and there will be a performance issue. so how to use the modified time method?

 

[jmcilhinney]

You don't really have to care about the actual time you retrieved the data. All you need to care about is that you retrieve only records with a modified time that is greater than the latest modified time in your current data. You can get that value using the Compute method of your DataTable, then execute a query that uses it in a WHERE clause to filter the data.

 

[swethajain]

I understood the method but in which event i should write the code to refresh the datgrid view without closing the application
where should the code be written so that it will be executed when F5 is presssed?