-
May 24th, 2017, 11:57 AM
#1
Thread Starter
Lively Member
[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!
-
May 24th, 2017, 01:10 PM
#2
Re: How To Make DataTable Static
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)
-
May 24th, 2017, 02:58 PM
#3
Re: How To Make DataTable Static
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
-
May 24th, 2017, 03:42 PM
#4
Thread Starter
Lively Member
Re: How To Make DataTable Static
Thank you very much Sir, could you kindly expatiate on this "create a My.Setting named table and set its type to String".
-
May 24th, 2017, 03:45 PM
#5
Re: How To Make DataTable Static
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
-
May 24th, 2017, 03:50 PM
#6
Thread Starter
Lively Member
Re: How To Make DataTable Static
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.
-
May 24th, 2017, 03:56 PM
#7
Re: How To Make DataTable Static
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
staticTable.Rows.Count = 0
and not staticTable=nothing.
could that be?
-
May 24th, 2017, 04:33 PM
#8
Re: How To Make DataTable Static
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.
-
May 24th, 2017, 05:57 PM
#9
Thread Starter
Lively Member
Re: How To Make DataTable Static
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.
-
May 24th, 2017, 06:06 PM
#10
Re: How To Make DataTable Static
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")
-
May 24th, 2017, 06:33 PM
#11
Thread Starter
Lively Member
Re: How To Make DataTable Static
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
Last edited by 1369Times; May 24th, 2017 at 06:36 PM.
-
May 24th, 2017, 06:38 PM
#12
Re: How To Make DataTable Static
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.
-
May 24th, 2017, 06:50 PM
#13
Thread Starter
Lively Member
Re: How To Make DataTable Static
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.
-
May 24th, 2017, 07:01 PM
#14
Thread Starter
Lively Member
Re: How To Make DataTable Static
Much respect to all of you! The fix work fine. Thank you very much dday9.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|