to cache a dataset in vb.net windows application

Hi nchandu,
----------

you need no cache, the ado.net dataset is a disconnected datastore so the moment you download the data to the client its there and the connection to the db can be closed and your client can work with the data

this is a bit of indepth article on the dataset
http://msdn.microsoft.com/msdnmag/issues/02/01/data/

which you can preserve to xml on disk and update the database later if you want

this snippet stores your dataset into an xml file
http://www.a1vbcode.com/snippet-2390.asp

and this reads it back
http://www.a1vbcode.com/snippet-2391.asp



----------
bruintje
share what you know, learn what you don't

Thanks,

but actually the problem is when the client wants to search every time he should go to database instead of that the database should be dumped into the client's machine and this should be updated for a certain period of time.Is it possible kinldy help me in this way

the dataset is a database

in a dataset are datatables and they work like normal db tables, that's why i put in the article it has a nice diagram how a dataset is build up

A datatable is, effectively, already a "cache" of the data on a client machine.  When you use a dataadapter's .Fill method the dataadapter opens the required connection, brings over all the data specified, stores it in the datatable specified, and then closes the connection.  The client machine can then work with that datatable as though it were the the database table.  

If the client wants to search the data it can do so in the datatable - it doesn't have to go back to the database to do so.  If the client wants to add a record, or alter a record, or delete a record, it can do so in the datatable - it doesn't have to go back to the database to do so.

Obviously, because the datatable and the database are now disconnected it will be necessary at some stage to write any changes that have been made in the datatable back to the database.  This can be done with the dataadapter's .Update method.  That opens the required connection, checks the datatable for changes, writes any that it finds to the database, and then closes the connection.  Precisely when, and how often, this is done is a matter of choice.  If it is important that the "cached" data - that in the datatable - and that in the database are kept as closely in synch as possible, then an update after every change to the datatable may be desirable.  At the other extreme it may only be necessary to update when the application that has been using the data is closed.  Between those extremes it may be decided to update after a specific time lapse.

Similarly, if it is likely that the database is changing significantly (e.g. as a result of other users' input) while the datatable is disconnected, it may be necessary for an app to refresh its datatable from it from time to time.  But when, and how often, this is done is again a matter of choice.

I've put all that in general terms, so you can see if it properly describes what you are trying to do.  There are other ways of handling things but the above looks to me like it meets your needs.  If it does, and you need specific assistance with - e.g. how to search in the datatable, or how to update the database or refresh the datatable after a specific period of time - then let us know.

Roger

thank you for the update,is it a right choice to update the database for a certain perios of time(10 min) by using the timer control or is there any other way to do that?

Timer control, I think

Roger

i am developing a windows application using VB.NET which access a remote MYSQL database through internet!The local network has a server machine which is only the system connect to the remote MYSQL database server and all the local client machines connects this local server for data!now the problem is how to cache the entire database in the local server machine to server that data to the local client machines?please anybody help me!

Just as, as I said earlier, "A datatable is, effectively, already a 'cache' of the data on a client machine" so "A dataset is, effectively, a 'cache' of a database on a client machine".  A database consists of one or more tables and, possibly, relationships between those tables.  A DataSet consists of one or more DataTables and, possibly, one or more DataRelations between those DataTables.  So I don't see how the information in your latest post alters the principles that were discussed earlier.

In what way do you think it does?

What your question was about was how to cache data on a local machine.  DataTables - or, if you have a collection of such DataTables, a DataSet - is just such a cache.

If that doesn't answer your question, I don't think I can help any more.

Roger

i am updating the database with da.update() method but the actual database is affecting with  newly inserted rows and the modified rowa only!but not affecting the deleted rows?i am using datagrid for deletion!

Ds.Tables(0).Rows.RemoveAt(DataGrid1.CurrentCell.RowNumber)
       Dim ds1 As New DataSet
        ds1 = Ds.GetChanges()
        If (ds1 Is Nothing) Then
            MessageBox.Show("Not Affected")
      else
         da.update()
       End If

This line

         da.update()

is insufficient.  You need to specify the dataset.  So it needs to be either

         da.update(Ds)

or

         da.update(ds1)

for the code above to work.

Roger
 

:)thanks for your update!but when i update the database by using da.update(ds1) editing and adding operations are performing correctly but the deletion operation is not performing correctly(that's deleting the another record in the database!The id is PK in the DB and also in the Dataset!

Public Sub SyncDB()
        Dim temp As New DataSet
        Dim a As Integer
        temp = attendeeDS.GetChanges()
        If Not (temp Is Nothing) Then
            If (Con.State = ConnectionState.Closed) Then Con.Open()
            attendeeDA.DeleteCommand = Cb.GetDeleteCommand
            a = attendeeDA.Update(temp.Tables(0).Select(Nothing, Nothing, DataViewRowState.Deleted))
            MessageBox.Show(attendeeDA.DeleteCommand.CommandText)
            attendeeDA.UpdateCommand = Cb.GetUpdateCommand
            attendeeDA.Update(temp.Tables(0).Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
            attendeeDA.InsertCommand = Cb.GetInsertCommand
            attendeeDA.Update(temp.Tables(0).Select(Nothing, Nothing, DataViewRowState.Added))
            attendeeDS.Tables(0).AcceptChanges()
        End If
End Sub

Although I can see there might be some reason for doing things the way you are for testing purposes, I cannot see why it needs to be so complicated for operational purposes.

First, if you have a commandbuilder - which I assume is what cb is - declared on the following lines

  Dim cb As New SQLCommandBuilder(attendeeDA)

then you can just use the attendeeDA.Update method wihout having specifically to transfer its commands to the attendeeDA like this

            attendeeDA.DeleteCommand = Cb.GetDeleteCommand

and so on.

Second, you don't need to extract changed records from the main dataset/datatable into a different dataset/datatable before you update them.  Nor do you have to do the selections for the particular commands by, for example

            attendeeDA.Update(temp.Tables(0).Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

Dataadapters decide themselves which records need updating and in what way by looking at their rowstate flags.

Third, dataadapters open and close their own connections when necessary, so this line

            If (Con.State = ConnectionState.Closed) Then Con.Open()

is superfluous.  Indeed, it could even be dangerous because, so far as I can see, the connection that is opened by it is never closed.  If a dataadapter finds the connection that it needs to use is open, it leaves it open when it finishes its operation/s.

Putting those points together all you should need in this sub is

Public Sub SyncDB()
        If attendeeDS.HasChanges() Then 'don't do anything if nothing to do
            attendeeDA.Update(attendeeDS.Tables(0)) 'run Update, Delete and Insert commands as necessary
            attendeeDS.Tables(0).AcceptChanges()
        End If
End Sub

Can I suggest you try that and see if the problem about deleting the wrong record still persists?  If it does, then we may need to go back to some more complicated coding to find out why.  Byt let's cross that bridge when we come to it.

Roger

i have tried with your suggestions but still the deleting operation is not performing correctly!that's deleting another record in the detabase!