[RESOLVED] How To Make DataTable Static

Hi All,
Please, have a look at the code snippet below:

Code:

---

Private Function GetRecords() As DataTable
        Static Dim staticTable As DataTable
        Dim temporaryTable As New DataTable

        If Me.Tag.Equals("Static table has not yet been loaded with daily records") Then
            staticTable = New DataTable
            'temporaryTable gets its contents from a database. The code for that is not included here.
            staticTable = temporaryTable.Copy()
            Me.Tag = "Static table has been loaded with daily records"
        End If
        Return staticTable
    End Function

---

The temporaryTable gets its contents from a database. It takes several minutes to populate this table, so I decided to fetch the records from the database and keep them in a static table. Subsequent fetch should simply use the static table instead of going to the database. The problem is that once code execution goes from this form to another form and back to this form, the static table would no longer have rows (i.e. staticTable.Rows.Count = 0). The reason for the IF ... End If block is to prevent having to connect to the database when the staticTable has already been populated.

Good people of VBForums, please, help!

You can store and retrieve the schema as well as the contents of the DataTable into a My.Settings value by using the following code:

Code:

---

Private Function ConvertDataToString(ByVal dt As DataTable) As String
    Dim conversion As String = String.Empty

    Using writer As IO.TextWriter = New IO.StringWriter()
        dt.WriteXml(writer, XmlWriteMode.WriteSchema)
        conversion = writer.ToString()
    End Using

    Return conversion
End Function

Private Function ConvertStringToData(ByVal value As String) As DataTable
    Dim dt As DataTable

    Using reader As IO.TextReader = New IO.StringReader(value)
        dt = New DataTable()
        dt.ReadXml(reader)
    End Using

    Return dt
End Function

---

Assuming that you create a My.Setting named table and set its type to String, then you could use the code like this to save/load it:

Code:

---

'Save the setting
My.Settings.Table = ConvertDataToString(MyDataTable) : My.Settings.Save()

'Get the table back from the setting
MyDataTable = ConvertStringToData(My.Settings.Table)

---

Why does it need to be static? Why not create a private DataTable, and initialize it to Nothing.

Code:

---

Private MyData as DataTable = Nothing

---

Then in your function, see if it is nothing, and if so, create it and fill it.

Code:

---

Private Function GetRecords() As DataTable
        If MyData is Nothing Then
            MyData = New DataTable
            'Load data into MyData (you didn't show that)
        End If
        Return MyData
    End Function

---

-tg

Thank you very much Sir, could you kindly expatiate on this "create a My.Setting named table and set its type to String".

Quote:

---

The problem is that once code execution goes from this form to another form and back to this form, the static table would no longer have rows

---

If that means that the original form is closed and reopened, then it is understandable that it does not work as you expect. the reason is that the form is deleted and nothing within it will survive. no static vars, no form global vars, nothing contained in the form.
you could put your table in a module though

Thanks, the original form is not closed. It remains open even as code execution goes to the second form. When the second form closes, control returns to the original form and the requirement is to load the records again and perform some more processing.

Quote:

---

Originally Posted by 1369Times

Thanks, the original form is not closed. It remains open even as code execution goes to the second form. When the second form closes, control returns to the original form and the requirement is to load the records again and perform some more processing.

---

then my guess would be that because your GetRecords function does return a reference to staticTable some further processing of this return value executes a .clear on the table. that would explain why

Quote:

---

staticTable.Rows.Count = 0

---

and not staticTable=nothing.
could that be?

Quote:

---

Originally Posted by 1369Times

Thank you very much Sir, could you kindly expatiate on this "create a My.Setting named table and set its type to String".

---

To create a setting, open the project's properties by clicking on Menu > [project name] Properties. Once you have the properties window pulled up, click on the Settings tab. Now that the settings tab is pulled up, you can create a new setting and name it whatever you want.

Thanks dday9.
This line of code dt.WriteXml(writer, XmlWriteMode.WriteSchema)
Throws the following exception:
Cannot serialize the DataTable. DataTable name is not set.

My confession is that I am not conversant with this aspect of VB.

If your DataTable doesn't have a name, then give it one. This is generally done whenever you create the DataTable:

Code:

---

Dim dt As New DataTable("Name Goes Here")

---

Apology, here's the function that's throwing the exception. The dt is already defined as a parameter.

Code:

---

Private Function ConvertDataToString(ByVal dt As DataTable) As String
    Dim conversion As String = String.Empty

    Using writer As IO.TextWriter = New IO.StringWriter()
        dt.WriteXml(writer, XmlWriteMode.WriteSchema)
        conversion = writer.ToString()
    End Using

    Return conversion
End Function

---

Quote:

---

Originally Posted by 1369Times

Apology, here's the function that's throwing the exception. The dt is already defined as a parameter.

Code:

---

Private Function ConvertDataToString(ByVal dt As DataTable) As String
    Dim conversion As String = String.Empty

    Using writer As IO.TextWriter = New IO.StringWriter()
        dt.WriteXml(writer, XmlWriteMode.WriteSchema)
        conversion = writer.ToString()
    End Using

    Return conversion
End Function

---

---

You are passing a DataTable into this function, you have even named the parameter dt - simply make sure whatever DataTable you pass into this function has a name and it should work just fine.

Thanks. I just added dt.TableName = "table" and the error message gone. I need to modify the rest of my codes and run the complete program. I will give feedback shortly.

Much respect to all of you! The fix work fine. Thank you very much dday9.