none
Store Data Table into Application RRS feed

  • Question

  • Hi all,

    I have 4 set of tabled data. I need to access those data when my application.

    Is it any way to save Tabled data in the application itself. I dont want to use SQL data base.

    Please suggest the options.

    Thanks in Advance

    Monday, November 4, 2019 3:32 AM

Answers

  • Hi

    It just occurred to me that perhaps you were asking for a way to store your data in the My.Settings. This can be done.

    Here is an example using just one datatable but others could be included as well.

    Be aware that the size of the user.config file (C:\Users\<user>\AppData\Local\<App Name>) will reflect the amount of data being stored. In this example I set up a setting in My.Settings called dtStore, of type System.Data.DataTable, User Scoped, with an empty value (code will produce  the data)

    The DataGridView1 and Button1 are there to illustrate the code either creating some test data, or, using the stored data. This example is not very far removed from using my earlier example of storing data in an .xml file.

    ' This is an example to illustrate the use of a
    ' DataTable (or several) entirely contained
    ' within the application executable.
    
    ' Be aware of executable size when data is
    ' used like this. Here the user.config file 
    ' went from 781 bytes (no data) to
    ' 3.08 MB for the data as generated below.
    ' C:\Users\<user>\AppData\Local\<App Name>
    
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Dim BS As New BindingSource
    	Dim rand As New Random
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		' example for ONE datatable. Other 
    		' datatables could be added in the 
    		' same manner.
    
    		' if My.Settings.DTstore is empty, create
    		' some test data
    		If My.Settings.DTstore Is Nothing Then
    			With dt
    				.Columns.Add("One")
    				.Columns.Add("Two")
    				.Columns.Add("Three")
    
    				For i As Integer = 1 To 9999
    					.Rows.Add(i, rand.Next(99, 999), (rand.NextDouble * rand.Next(99, 999)).ToString("0.00"))
    				Next
    			End With
    			My.Settings.DTstore = dt
    			Label1.Text = "My.Settings.DTstore was empty, test data added"
    		Else
    			Label1.Text = "My.Settings.DTstore was NOT empty, internal test data used"
    		End If
    
    		BS.DataSource = My.Settings.DTstore
    		DataGridView1.DataSource = BS
    	End Sub
    	Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    		' empty and close My.Settings.DTstore 
    		'	for testing. Run again to see the
    		' test data being re-created.
    		My.Settings.DTstore = Nothing
    		Close()
    	End Sub
    End Class


    Regards Les, Livingston, Scotland



    Monday, November 4, 2019 10:12 PM

All replies

  • Hi,

    You can save it in a simple file with low stability and security requirements, because any program can copy and modify files at will. Even if you can't identify the content inside, you can also delete it directly. This method can be implemented by serialization.

    In addition, it is recommended that you use LocalDataBase or SQLite. LocalDB is almost identical to the full version of sql server, and you don't need to modify the code and database. At the same time, its runtime installation package is very small.

    As for SQLite, it is a lightweight database and it is very simple. You can refer to the following link:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/72c4ab3f-e0ef-4a04-9953-1e70d343e771/sqlite-installation-for-c?forum=csharpgeneral

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 4, 2019 6:06 AM
    Moderator
  • Hi

    Here is one way. This just saves/loads data to/from an .xml file.

    	Dim myTable As New DataTable("Freddy")
    	Dim Path As String = Application.StartupPath & "\Data\Data.xml"
    	Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
    		Dim parent As String = My.Computer.FileSystem.GetParentPath(Path)
    		' if the Folder doesn't exist, create it
    		If Not IO.Directory.Exists(parent) Then
    			IO.Directory.CreateDirectory(parent)
    		End If
    		' save data file when exit the application
    		myTable.WriteXml(Path, XmlWriteMode.WriteSchema)
    	End Sub
    
    ' ELSEWHERE IN CODE
    	' if file exists then load data from it
    	If IO.File.Exists(Path) Then
    			' data file found so load it
    			myTable.ReadXml(Path)


    Regards Les, Livingston, Scotland

    Monday, November 4, 2019 1:35 PM
  • Hello,

    By not using a real database many features will not be available which means you will need to roll your own e.g. locks, proper incrementing keys etc.

    If you really want to go without a database here is one solution or look at Les's recommendation for XML.

    Imports System.IO
    Imports System.Runtime.Serialization.Formatters.Binary
    
    Public Module DataTableSerializer
        Public  Function SaveToFile( dataTables() As DataTable,  path As String) As Boolean
    
            Try
                Using stream = New MemoryStream()
                    Dim formatter As Runtime.Serialization.IFormatter = New BinaryFormatter()
                    formatter.Serialize(stream, dataTables)
                    stream.Close()
                    File.WriteAllBytes(path, stream.ToArray())
                End Using
    
                Return True
    
            Catch ex As Exception
    
                Return False
    
            End Try
        End Function
    
        Public  Function LoadFromFile( path As String) As DataTable()
            Try
                Dim buffer() As Byte = File.ReadAllBytes(path)
                Dim stream = New MemoryStream(buffer)
                Dim formatter As Runtime.Serialization.IFormatter = New BinaryFormatter()
    
                Return CType(formatter.Deserialize(stream), DataTable())
    
            Catch ex As Exception
    
                Return Nothing
    
            End Try
        End Function
    
    End Module


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, November 4, 2019 2:36 PM
    Moderator
  • Hi all,

    I have 4 set of tabled data. I need to access those data when my application.

    Is it any way to save Tabled data in the application itself. I dont want to use SQL data base.

    Please suggest the options.

    Thanks in Advance

    This is a horrible decision making process. 

    However, you can create and use a XML database in VB.NET with dataset and tables in a dataset.

    https://www.youtube.com/watch?v=qFdzq66AOJw

    Monday, November 4, 2019 8:15 PM
  • Hi

    It just occurred to me that perhaps you were asking for a way to store your data in the My.Settings. This can be done.

    Here is an example using just one datatable but others could be included as well.

    Be aware that the size of the user.config file (C:\Users\<user>\AppData\Local\<App Name>) will reflect the amount of data being stored. In this example I set up a setting in My.Settings called dtStore, of type System.Data.DataTable, User Scoped, with an empty value (code will produce  the data)

    The DataGridView1 and Button1 are there to illustrate the code either creating some test data, or, using the stored data. This example is not very far removed from using my earlier example of storing data in an .xml file.

    ' This is an example to illustrate the use of a
    ' DataTable (or several) entirely contained
    ' within the application executable.
    
    ' Be aware of executable size when data is
    ' used like this. Here the user.config file 
    ' went from 781 bytes (no data) to
    ' 3.08 MB for the data as generated below.
    ' C:\Users\<user>\AppData\Local\<App Name>
    
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Dim BS As New BindingSource
    	Dim rand As New Random
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		' example for ONE datatable. Other 
    		' datatables could be added in the 
    		' same manner.
    
    		' if My.Settings.DTstore is empty, create
    		' some test data
    		If My.Settings.DTstore Is Nothing Then
    			With dt
    				.Columns.Add("One")
    				.Columns.Add("Two")
    				.Columns.Add("Three")
    
    				For i As Integer = 1 To 9999
    					.Rows.Add(i, rand.Next(99, 999), (rand.NextDouble * rand.Next(99, 999)).ToString("0.00"))
    				Next
    			End With
    			My.Settings.DTstore = dt
    			Label1.Text = "My.Settings.DTstore was empty, test data added"
    		Else
    			Label1.Text = "My.Settings.DTstore was NOT empty, internal test data used"
    		End If
    
    		BS.DataSource = My.Settings.DTstore
    		DataGridView1.DataSource = BS
    	End Sub
    	Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    		' empty and close My.Settings.DTstore 
    		'	for testing. Run again to see the
    		' test data being re-created.
    		My.Settings.DTstore = Nothing
    		Close()
    	End Sub
    End Class


    Regards Les, Livingston, Scotland



    Monday, November 4, 2019 10:12 PM