title: VB.Net Global DataSets inside a public function
body: <p>I am having some problem with a function that I hope you can help.</p>
  
  <p>My Application is a simple one, it uses an Access database to load employee information and creates letters and financial breakdown sheet from word templates that users can then print and save back to the database.</p>
  
  <p>I started by creating a dataset containing several datatables for each form subroutine but it resulted in literally hundreds of lines of repeated code. But it worked.</p>
  
  <p>What I want to do, is have one dataset containing all the information needed about an employee and be able to reference it over several forms at the same time. So i created a public module that looks like this:</p>
  
  <pre><code>Public Module Datasets
  Public update As String
  Dim pCn As OleDb.OleDbConnection
  
  Public Function CSofwareDataSet() As DataSet
      'open new connection to database
      pCn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\CGI Project\CorrespondenceSoftware\Database1.accdb; Persist Security Info=False;")
      Try
          Call pCn.Open() 'opens the connection
      Catch ex As Exception
          MessageBox.Show("Could not open a database connection! 1")
          MessageBox.Show(ex.ToString)
      End Try
  
      CSofwareDataSet = New DataSet
  Dim daOPG As New OleDb.OleDbDataAdapter("SELECT * FROM Overpayment WHERE PayNumber='" &amp; Main.tbPayNumber.Text &amp; "' AND Gross=1", pCn) 'get all data from Overpayment Details table
  Dim daOPN As New OleDb.OleDbDataAdapter("SELECT * FROM Overpayment WHERE PayNumber='" &amp; Main.tbPayNumber.Text &amp; "' AND Net=1", pCn) 'get all data from Overpayment Details table
  Dim daOPR As New OleDb.OleDbDataAdapter("SELECT * FROM OvpReasons", pCn) 'get overpayment reasons
  Dim daREC As New OleDb.OleDbDataAdapter("SELECT * FROM TaxYear", pCn) 'get recovery date options
  Dim daEMP As New OleDb.OleDbDataAdapter("SELECT * FROM EmployeeDetails WHERE PayNumber='" &amp; Main.tbPayNumber.Text &amp; "' AND Active=1 ", pCn) 'get all data from Employee Details table
  Dim daCON As New OleDb.OleDbDataAdapter("SELECT * FROM Consultant", pCn) 'get all data from Consultant Details table
  Dim daSET As New OleDb.OleDbDataAdapter("SELECT * FROM Settings", pCn) 'get all data from Consultant Details table
  'Find the primary key (if missing)
      daOPG.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daOPN.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daOPR.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daREC.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daEMP.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daCON.MissingSchemaAction = MissingSchemaAction.AddWithKey
      daSET.MissingSchemaAction = MissingSchemaAction.AddWithKey
  'setup prefixes
  Dim cbOPG As New OleDb.OleDbCommandBuilder(daOPG)
      cbOPG.QuotePrefix = "["
      cbOPG.QuoteSuffix = "]"
  Dim cbOPN As New OleDb.OleDbCommandBuilder(daOPN)
      cbOPG.QuotePrefix = "["
      cbOPG.QuoteSuffix = "]"
  Dim cbOPR As New OleDb.OleDbCommandBuilder(daOPR)
      cbOPG.QuotePrefix = "["
      cbOPG.QuoteSuffix = "]"
  Dim cbREC As New OleDb.OleDbCommandBuilder(daREC)
      cbOPG.QuotePrefix = "["
      cbOPG.QuoteSuffix = "]"
  Dim cbEMP As New OleDb.OleDbCommandBuilder(daEMP)
      cbEMP.QuotePrefix = "["
      cbEMP.QuoteSuffix = "]"
  Dim cbCON As New OleDb.OleDbCommandBuilder(daCON)
      cbEMP.QuotePrefix = "["
      cbEMP.QuoteSuffix = "]"
  Dim cbSET As New OleDb.OleDbCommandBuilder(daSET)
      cbEMP.QuotePrefix = "["
      cbEMP.QuoteSuffix = "]"
  
  
  
      If CSofwareDataSet.HasChanges Then
          Try
              daEMP.Update(CSofwareDataSet, "EmployeeDetails")
              daOPG.Update(CSofwareDataSet, "OverPaymentGross")
              daOPN.Update(CSofwareDataSet, "OverPaymentNet")
              daSET.Update(CSofwareDataSet, "Settings")
  
              MessageBox.Show("Success! Records updated.")
              update = "0"
          Catch ex As Exception
              MessageBox.Show("Oops - something went wrong and it didn't update")
              update = "0"
          End Try
      ElseIf CSofwareDataSet.Tables.Count = 0 Then
          daOPG.Fill(CSofwareDataSet, "OverPaymentGross")
          daOPN.Fill(CSofwareDataSet, "OverPaymentNet")
          daOPR.Fill(CSofwareDataSet, "OverPaymentReasons")
          daREC.Fill(CSofwareDataSet, "RecoveryDates")
          daEMP.Fill(CSofwareDataSet, "EmployeeDetails")
          daCON.Fill(CSofwareDataSet, "ConsultantDetails")
          daSET.Fill(CSofwareDataSet, "Settings")
  
      End If
  
  
      'If update = "1" Then
      ' Try
      ' daEMP.Update(CSofwareDataSet, "EmployeeDetails")
      ' daOPG.Update(CSofwareDataSet, "OverPaymentGross")
      ' daOPN.Update(CSofwareDataSet, "OverPaymentNet")
      ' daSET.Update(CSofwareDataSet, "Settings")
      '
      '        MessageBox.Show("Success! Records updated.")
      '        update = "0"
      '        Catch ex As Exception
      ' MessageBox.Show("Oops - something went wrong and it didn't update")
      ' update = "0"
      ' End Try
      ' End If
  
      pCn.Close()
  
  End Function
  End Module
  </code></pre>
  
  <p>On each form, it gets referenced like this (as an example):</p>
  
  <pre><code>Imports WeifenLuo.WinFormsUI.Docking
  Imports Word = Microsoft.Office.Interop.Word
  Imports CorrespondenceSoftware.Datasets
  
  Public Class GrossInput
  
  Dim loading = "1"
  Dim NewEmployee = "0" 'sets the default new employee flag to 0
  Private pCn As OleDb.OleDbConnection
  
  Private Sub GrossInput_Load(ByVal Sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  
      Try
  
          Try
              If CSofwareDataSet.Tables("EmployeeDetails").Rows.Count &gt; 0 Then
                  For i As Integer = 0 To CSofwareDataSet.Tables("EmployeeDetails").Rows.Count - 1
                      cbTitle.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(2)
                      tbFName.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(3)
                      tbLName.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(4)
                      tbAddress1.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(5)
                      tbAddress2.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(6)
                      tbAddress3.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(7)
                      tbAddress4.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(8)
                      tbPostcode.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(9)
                      tbWorkLocation.Text = CSofwareDataSet.Tables("EmployeeDetails").Rows(i)(10)
                      tbWorkLocation.Enabled = False
                      tbPostcode.Enabled = False
                      tbAddress4.Enabled = False
                      tbAddress3.Enabled = False
                      tbAddress2.Enabled = False
                      tbAddress1.Enabled = False
                      tbLName.Enabled = False
                      tbFName.Enabled = False
                      cbTitle.Enabled = False
                      chkMSC.Enabled = False
                      chkOfficer.Enabled = False
                      chkStaff.Enabled = False
                      bnSaveEmp.Enabled = False
                      bnEditEmp.Enabled = True
  
                  Next
              End If
              If CSofwareDataSet.Tables("EmployeeDetails").Rows(0)(11) = "1" Then
                  chkOfficer.Checked = True
              Else
                  chkOfficer.Checked = False
  
              End If
              If CSofwareDataSet.Tables("EmployeeDetails").Rows(0)(12) = "1" Then
                  chkStaff.Checked = True
              Else
                  chkStaff.Checked = False
  
              End If
              If CSofwareDataSet.Tables("EmployeeDetails").Rows(0)(13) = "1" Then
                  chkMSC.Checked = True
              Else
                  chkMSC.Checked = False
  
              End If
  
          Catch ex As Exception
              MessageBox.Show(ex.ToString)
              MessageBox.Show("Employee not found. Ensure pay number is correct and create a new record")
              NewEmployee = "1" ' tells the program to create a new record if saved
              cbReference.Enabled = False
              cbReference.Text = ""
              bnEditEmp.Enabled = False
          End Try
  
          'display the overpayment references to the user
          If CSofwareDataSet.Tables("OverPaymentGross").Rows.Count &gt; 0 Then
              For i As Integer = 0 To CSofwareDataSet.Tables("OverPaymentGross").Rows.Count - 1
                  cbReference.Items.Add(CSofwareDataSet.Tables("OverPaymentGross").Rows(i)(2))
              Next
          End If
          'display the available consultants to the user
          If CSofwareDataSet.Tables("ConsultantDetails").Rows.Count &gt; 0 Then
              For i As Integer = 0 To CSofwareDataSet.Tables("ConsultantDetails").Rows.Count - 1
                  cbConsultant.Items.Add(CSofwareDataSet.Tables("ConsultantDetails").Rows(i)(1) &amp; " " &amp; CSofwareDataSet.Tables("ConsultantDetails").Rows(i)(2))
              Next
          End If
          'display the available Overpayment reasons to the user
          If CSofwareDataSet.Tables("OverPaymentReasons").Rows.Count &gt; 0 Then
              For i As Integer = 0 To CSofwareDataSet.Tables("OverPaymentReasons").Rows.Count - 1
                  cbReason.Items.Add(CSofwareDataSet.Tables("OverPaymentReasons").Rows(i)(1))
              Next
          End If
          'Load other recovery date options 
          If CSofwareDataSet.Tables("RecoveryDates").Rows.Count &gt; 0 Then
              For i As Integer = 0 To CSofwareDataSet.Tables("RecoveryDates").Rows.Count - 1
                  cbStartRecovery.Items.Add(CSofwareDataSet.Tables("RecoveryDates").Rows(i)(1))
              Next
          End If
  
  
      Catch ex As Exception
          MessageBox.Show(ex.ToString) 'Show any errors to the user
      End Try
      loading = "0"
  End Sub
  </code></pre>
  
  <p>Now! the problem that I'm having is that, this does work and run without any errors BUT every time the CSSoftwareDataSet function runs it populates the tables correctly and returns the expected results but it then deletes the datatable data so every time the function is referenced from a winform it needs to haul all the data from the access database from scratch, severely impacting on the performance of the program. The tables wont update properly because its not storing the datatable information and as soon as its inserted its forgotten but again, produces no errors. An example of my update script looks like this:</p>
  
  <pre><code> Else 'create a new record
          'create a new reference
          Dim REFRowCount = CSofwareDataSet.Tables("OverPaymentGross").Rows.Count + 1 'count the number of rows in table and add 1
          Dim NewREF = "OVPG" &amp; Main.tbPayNumber.Text &amp; "-" &amp; REFRowCount
          'Find todays date and reply dates
  
          Dim TodayDatedate = Format(Now.Date(), "dd/MM/yyyy")
          Dim ReplyDatedate = Format(Now.Date.AddDays(21), "dd/MM/yyyy")
  
          'Create a new row
          Dim OPNew As DataRow = CSofwareDataSet.Tables("OverPaymentGross").NewRow() 'create a variable to contain the new row
          OPNew.Item(1) = Main.tbPayNumber.Text
          OPNew.Item(2) = NewREF
          OPNew.Item(3) = tbOverpaymentAmount.Text.ToString
          OPNew.Item(4) = tbMonRec.Text
          OPNew.Item(5) = tbTaxP.Text
          OPNew.Item(6) = TodayDatedate
          OPNew.Item(7) = ReplyDatedate
          OPNew.Item(8) = tbMoRep.Text
          OPNew.Item(9) = cbStartRecovery.Text
          OPNew.Item(10) = "1" 'Set as gross
          OPNew.Item(11) = "0" 'do not set as net
          OPNew.Item(12) = cbReason.Text
          OPNew.Item(13) = tbAI.Text
          OPNew.Item(14) = dtpStart.Value.Date
          OPNew.Item(15) = dtpFinish.Value.Date
          OPNew.Item(16) = cbConsultant.Text
          OPNew.Item(17) = tbPosition.Text
  
          Call CSofwareDataSet.Tables("OverPaymentGross").Rows.Add(OPNew) 'fill the new row and insert the data
  </code></pre>
  
  <p>There must be a solution to this. To create a dataset that holds its data in session while you open other winforms until it is reset. I'm out of ideas because i really don't want to go back to repeating all this code for practically every subroutine in my program. </p>
  
  <p>I hope I've explained it OK .. Any help here will be greatly appreciated.</p>
  
  <p>Many thanks, 
  Shane</p>