Hi,
I have an application that has a "Data Access Class" and "User Interface
Class".

It is for receiving Purchase Order data from one system and pushing
processed transactions to another system.

The system generally works quite well.
Currently the User interface calls for a refresh of data every 15 minutes
(selectable) and the Data Access Class connects to the DB and retrieves the
data structured to suit the application. During the retrieval it displays
messages informing the User of the various downloads. Each application has
its own Data Access Class. It takes between 30 seconds and 5 minutes to
refresh the data dependant on where the User is located

I have been asked to make the refresh transparent so that the User does not
have to wait for the Data Access Layer to get its data as some site have a
very slow connections to the network.

I am not sure how to achieve this new requirement.
I thought that I could make the Data Access Class a separate application
that loads with the User interface and then periodically retrieves the data
and saves it into a local database file (mdb ?) rather than into the current
Data Set and then have the User Interface asynchronously connect to the
local database and update its records.

I would appreciate any oppinions.

Regards

Doug

Hi Doug,

You can execute the loading of your data on a separate thread so that the
form is still responsive to the user, however, you need to be careful that
none of the UI elements work with that data before the data is retrieved.
Once the data is loaded, your data access class should raise an event that
you can handle so you can call the invoke method to marshal the call to the
UI thread. Here's the basic idea (note- this example uses the SQL northwind
database):

Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
  MyBase.New()

  'This call is required by the Windows Form Designer.
  InitializeComponent()

  'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
  If disposing Then
   If Not (components Is Nothing) Then
    components.Dispose()
   End If
  End If
  MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents Button3 As System.Windows.Forms.Button
Friend WithEvents Button4 As System.Windows.Forms.Button
Friend WithEvents Button5 As System.Windows.Forms.Button
Friend WithEvents CheckBox1 As System.Windows.Forms.CheckBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
  Me.DataGrid1 = New System.Windows.Forms.DataGrid
  Me.TextBox1 = New System.Windows.Forms.TextBox
  Me.Button1 = New System.Windows.Forms.Button
  Me.TextBox2 = New System.Windows.Forms.TextBox
  Me.Button2 = New System.Windows.Forms.Button
  Me.Button3 = New System.Windows.Forms.Button
  Me.Button4 = New System.Windows.Forms.Button
  Me.Button5 = New System.Windows.Forms.Button
  Me.CheckBox1 = New System.Windows.Forms.CheckBox
  CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
  Me.SuspendLayout()
  '
  'DataGrid1
  '
  Me.DataGrid1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or
System.Windows.Forms.AnchorStyles.Bottom) _
     Or System.Windows.Forms.AnchorStyles.Left) _
     Or System.Windows.Forms.AnchorStyles.Right),
System.Windows.Forms.AnchorStyles)
  Me.DataGrid1.DataMember = ""
  Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
  Me.DataGrid1.Location = New System.Drawing.Point(16, 120)
  Me.DataGrid1.Name = "DataGrid1"
  Me.DataGrid1.Size = New System.Drawing.Size(472, 208)
  Me.DataGrid1.TabIndex = 0
  '
  'TextBox1
  '
  Me.TextBox1.Location = New System.Drawing.Point(24, 56)
  Me.TextBox1.Name = "TextBox1"
  Me.TextBox1.Size = New System.Drawing.Size(48, 20)
  Me.TextBox1.TabIndex = 1
  Me.TextBox1.Text = "TextBox1"
  '
  'Button1
  '
  Me.Button1.Location = New System.Drawing.Point(24, 88)
  Me.Button1.Name = "Button1"
  Me.Button1.TabIndex = 2
  Me.Button1.Text = "Next"
  '
  'TextBox2
  '
  Me.TextBox2.Location = New System.Drawing.Point(80, 56)
  Me.TextBox2.Name = "TextBox2"
  Me.TextBox2.Size = New System.Drawing.Size(112, 20)
  Me.TextBox2.TabIndex = 3
  Me.TextBox2.Text = "TextBox2"
  '
  'Button2
  '
  Me.Button2.Location = New System.Drawing.Point(136, 88)
  Me.Button2.Name = "Button2"
  Me.Button2.TabIndex = 4
  Me.Button2.Text = "Add Child"
  '
  'Button3
  '
  Me.Button3.Location = New System.Drawing.Point(240, 88)
  Me.Button3.Name = "Button3"
  Me.Button3.Size = New System.Drawing.Size(96, 23)
  Me.Button3.TabIndex = 5
  Me.Button3.Text = "Delete Parent"
  '
  'Button4
  '
  Me.Button4.Location = New System.Drawing.Point(376, 88)
  Me.Button4.Name = "Button4"
  Me.Button4.Size = New System.Drawing.Size(96, 23)
  Me.Button4.TabIndex = 6
  Me.Button4.Text = "Show Changes"
  '
  'Button5
  '
  Me.Button5.Location = New System.Drawing.Point(24, 16)
  Me.Button5.Name = "Button5"
  Me.Button5.TabIndex = 7
  Me.Button5.Text = "Load Data"
  '
  'CheckBox1
  '
  Me.CheckBox1.Checked = True
  Me.CheckBox1.CheckState = System.Windows.Forms.CheckState.Checked
  Me.CheckBox1.Location = New System.Drawing.Point(104, 16)
  Me.CheckBox1.Name = "CheckBox1"
  Me.CheckBox1.Size = New System.Drawing.Size(64, 24)
  Me.CheckBox1.TabIndex = 8
  Me.CheckBox1.Text = "Async"
  '
  'Form1
  '
  Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
  Me.ClientSize = New System.Drawing.Size(496, 334)
  Me.Controls.Add(Me.CheckBox1)
  Me.Controls.Add(Me.Button5)
  Me.Controls.Add(Me.Button4)
  Me.Controls.Add(Me.Button3)
  Me.Controls.Add(Me.Button2)
  Me.Controls.Add(Me.TextBox2)
  Me.Controls.Add(Me.Button1)
  Me.Controls.Add(Me.TextBox1)
  Me.Controls.Add(Me.DataGrid1)
  Me.Name = "Form1"
  Me.Text = "Form1"
  CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
  Me.ResumeLayout(False)

End Sub

#End Region

Private Class DataAccess
  Private Const SQL_CONNECTION_STRING As String = _
   "Data Source=localhost;" & _
   "Initial Catalog=Northwind;" & _
   "Integrated Security=SSPI"

  Public Event DataLoaded(ByVal sender As Object, ByVal e As EventArgs)
  Protected Overridable Sub OnDataLoaded(ByVal e As EventArgs)
   RaiseEvent DataLoaded(Me, e)
  End Sub

  Public Sub GetData(ByRef ds As DataSet)
   ds = New DataSet
   Dim da, da2 As SqlDataAdapter
   Dim cnn As SqlConnection
   Try
    cnn = New SqlConnection(SQL_CONNECTION_STRING)

    da = New SqlDataAdapter("SELECT * FROM Region", cnn)
    da.Fill(ds, "Region")
    da = New SqlDataAdapter("SELECT * FROM Territories", cnn)
    da.Fill(ds, "Territories")

    ds.Relations.Add("Region_Territories", _
     ds.Tables("Region").Columns("RegionID"), _
     ds.Tables("Territories").Columns("RegionID"))

    ds.DataSetName = "RegionTerritories"

    OnDataLoaded(EventArgs.Empty)

   Catch Exp As Exception
    MessageBox.Show(Exp.Message)
   End Try
  End Sub

End Class

Private Delegate Sub GetDataArgsDelegate(ByVal e As EventArgs)

Private WithEvents Access As New DataAccess
Private myDataSet As DataSet
Private cmParent, cmChild As CurrencyManager

Private m_async As Boolean = True
Public Property LoadAsync() As Boolean
  Get
   Return m_async
  End Get
  Set(ByVal Value As Boolean)
   m_async = Value
  End Set
End Property

Private m_isDataLoaded As Boolean = False
Public Property IsDataLoaded() As Boolean
  Get
   Return m_isDataLoaded
  End Get
  Set(ByVal Value As Boolean)
   m_isDataLoaded = Value
  End Set
End Property

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
  Me.Button5.Enabled = False
  Me.BeginGetData()
End Sub

Private Sub BeginGetData()
  m_isDataLoaded = False
  Me.DataGrid1.DataSource = Nothing
  Me.TextBox1.DataBindings.Clear()
  Me.TextBox2.DataBindings.Clear()
  Me.TextBox1.Text = ""
  Me.TextBox2.Text = "Loading Data..."

  If Me.LoadAsync Then
   Dim t As New Threading.Thread(AddressOf Me.GetData)
   t.Name = Me.Name + "DataThread"
   t.IsBackground = True
   t.Start()
  Else
   Me.GetData()
  End If
End Sub

Private Sub EndGetData(ByVal e As EventArgs)
  m_isDataLoaded = True

  Me.DataGrid1.DataSource = myDataSet
  Me.DataGrid1.DataMember = "Region.Region_Territories"

  Me.TextBox1.DataBindings.Add("Text", myDataSet, "Region.RegionID")
  Me.TextBox2.DataBindings.Add("Text", myDataSet,
"Region.RegionDescription")

  cmParent = Me.BindingContext(myDataSet, "Region")
  cmChild = Me.BindingContext(myDataSet, "Region.Region_Territories")
  Me.Button5.Enabled = True
End Sub

Public Sub GetData()
  'Simulates a longer time.....
  System.Threading.Thread.Sleep(5000)
  Access.GetData(myDataSet)
End Sub

Private Sub Access_DataLoaded(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Access.DataLoaded
  If Me.InvokeRequired Then
   '-- Marshall to the UI thread...
   Dim dlg As New GetDataArgsDelegate(AddressOf EndGetData)
   Dim args() As Object = {e}
   Me.BeginInvoke(dlg, args)
  Else
   Me.EndGetData(EventArgs.Empty)
  End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
  If Me.IsDataLoaded Then
   cmParent.Position += 1
  End If
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
  If Me.IsDataLoaded Then
   cmChild.AddNew()
  End If
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
  If Me.IsDataLoaded Then
   cmParent.RemoveAt(cmParent.Position)
  End If
End Sub

Public Overridable Sub ShowDiffData()
  '-- View the diffgram in the web browser
  Try
   If Not (myDataSet Is Nothing) Then

    Dim cFileName As String =
Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) +
"\Diff"
    Dim dsDiffgram As DataSet

    If myDataSet.HasChanges Then
     dsDiffgram = myDataSet.GetChanges()
     dsDiffgram.WriteXml(cFileName + myDataSet.DataSetName + ".xml",
XmlWriteMode.DiffGram)
     System.Diagnostics.Process.Start("file://" + cFileName +
myDataSet.DataSetName + ".xml")
    Else
     MessageBox.Show("Please make changes first.", "Show Changes",
MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
   End If
  Catch exp As Exception
  End Try
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
  If Me.IsDataLoaded Then
   Me.ShowDiffData()
  End If
End Sub

Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles CheckBox1.CheckedChanged
  Me.LoadAsync = Me.CheckBox1.Checked
End Sub
End Class



Show quoteHide quote

Thanks Beth, I will have a look at that. I haven't done anything in
different threads before.
I wonder if I could get around locking access to the data by buffering it
into a secon data set, updating ds1 and then cloning ds1 to ds2 so that it
is only unavailable momentarily?

Thanjs

Show quoteHide quoteSystem.ComponentModel.ISupportInitialize).BeginInit()
Show quoteHide quote

Doug,

I absolute don't see from your message what you are doing. I think that an
newsgroup message is not able to show that. You can try it however than it
becomes probably so long that nobody reads it, so it has no sense to explain
it more in my opinion.

However you asked an opinion. I surely would not go the direction in
duplicating your data in an access database. I have seen a lot of people
trying that in these newsgroups. It is difficult managable and cost as well
probably a lot of time.

To give you my first idea, that was creating XML files dataset on the server
and than distribute those on that 15 minute base by instance with a
webservice.  (Where you than would have to decide if you create on your
server the xmlfiles on a time base or on demand).

However that last is just an idea that I got reading your message and never
did.

I hope this helps somehow

Cor

Thanks Cor,
I understand it is difficult to see what I am trying to achieve.

I would rather keep it as a dataset in memory. And possibly I can do
something like using a different thread as Beth has suggested.

I did not mention but the data has to be available even when the AS400
Server is busy doing End Of Day and End Of Month stuff.
That is currently handled by writing the local Data Set to an XML when ever
it is refreshed but I think generating and distributing XML from the AS400
is out of the question.

Doug

Show quoteHide quote

Doug,

This does not change when you use the XML file as Intermidiate, only there
is not needed any action from the server, and probably will that save time.
(You can even think on zipping and dezipping it). (See my part about the
AS400 later).

What do you than achieve with that. Or don't you have updates? That makes it
even more to try the solution I told.

Than I would probalby try directly to integrate an extra server in my
domain, that only gets that data, set it on a XMLfile dataset and is the
server for the clients to get that dataset.

However as I said I never tried.

Cor

Dough,

I have looked better at the solution from Beth, there is nothing that
conflicts with my idea of a solution. You can integrate both.

Cor

doh,

Assuming again that there are no updates needed.
Show quoteHide quote

Cor,
There are no updates.
Processed transactions go through a different system.

But I don't see how I can get AS400 to generate (and optionally zip) an XML
file?

I can see benefits if I could.

Doug

Show quoteHide quote

Doug,

Only with that extra server as I wrote.

That server collects the data from the AS400 in the same way as now the
clients do.

When ready write a zipped serialized dataset as a blob (bytearray).

With a Webservice you make it the client possible to get that blob.

The client do on seperated times (what can be even in a extra thread) the
collections of that blob using that webservice, unzip it and read (or
deserialize it) it in the proper place when it is arrived.

However again just an idea with what I see directly not much problems to
realise when you have somewhere a server which you can use for that. (It
needs IIS and ASPNET).

Cor

Yes, If I had a server with IIS that I could use, I would do a lot of things
here more simply and probably more effectively.

Doug

Show quoteHide quote

Question on impersonation
Opening a dbf file with .NET code
Previnstance function
VS 2003 DataGrid, Bug or Feature???
win ap with arguments
.Net Framework Installer Class
Connect student version of vb.net to a sql database?
Why not color?
Xml appending child nodes
Date function