VB What is the best way to populate a Datagridview from a text file?

Question

I haven't coded for many many years and have had little experience with .Net. VB6 is mostly what I used and I am trying to adapt, so I thank you for your assistance and patients.

I need to populate a DataGridView from a text file. This file is delimited by tabs and may and may not exist when this little program is run for the first time. If it does not already exist a base version of the text file is created from code the very first time the program runs, after that it is up to the user to update the data.

When the form that uses this data is created it populates the DataGridView with the data directly from the text file, but the problem is that I can't edit or add new data to the DataGridView.

I presume that it is because the DataGridView is populated directly from the text file and therefore isn't bound to a datasource.

Would it be better to create a Table populated by the text file, set the Table as the DataGridView's datasource and then when the data needs to be saved the table is written back to the text file?

Or is there a better way?

Answer 1

As I mentioned in the comments, you can use a DataTable, or a list of SomeModel as data source of your DataGridView. So you can load data to the data table or the list of model, and bind it to data grid view. Then later when it comes to saving, just save in tab-delimited format.

If it's a new application and tab-delimited is not forced by an external requirement, then tab-delimited file doesn't make much sense, while you can save and load the whole DataTable in XML format or the List<SomeModel> in json format.

Example 1 - Load and Save data in XML format using DataTable

Here is a very simple example using DataTable where we save and load data in XML format. All the trick of reading or writing XML is handled by DataTable.

Assuming you have a DataGridView1 on your form:

Imports System.IO
Public Class Form1
    Private FileName As String
    Private Data As DataTable
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FileName = Path.Combine(Application.StartupPath, "Data.xml")
        Data = New DataTable("People")
        Data.Columns.Add("Id", GetType(Integer))
        Data.Columns.Add("FirstName", GetType(String))
        Data.Columns.Add("LastName", GetType(String))
        If (File.Exists(FileName)) Then
            Try
                Data.ReadXml(FileName)
            Catch ex As Exception
                MessageBox.Show("Could not load data")
            End Try
        End If
        DataGridView1.DataSource = Data
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        Me.Validate()
        DataGridView1.EndEdit()
        Data.WriteXml(FileName)
    End Sub
End Class

Example 2 - Load and Save data in JSON format using JsonConvert

In this example, you first need install the "Newtonsoft.Json" NuGet package to your project to take care of reading and writing json data.

To do so, right click on Project and choose Manage NuGet packages, and then in the Browse tab, search for Newtonsoft.Json and choose it and click on Install on the right side of the panel.

Then assuming you have a DataGridView1 on your form:

Imports System.ComponentModel
Imports System.IO
Imports Newtonsoft.Json
Public Class Form1
    Private FileName As String
    Private Data As BindingList(Of Person)
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FileName = Path.Combine(Application.StartupPath, "Data.json")
        Data = New BindingList(Of Person)
        If (File.Exists(FileName)) Then
            Try
                Dim json = File.ReadAllText(FileName)
                Dim array = JsonConvert.DeserializeObject(Of Person())(json)
                Data = New BindingList(Of Person)(array)
            Catch ex As Exception
                MessageBox.Show("Could not load data")
            End Try
        End If
        DataGridView1.DataSource = Data
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        Me.Validate()
        DataGridView1.EndEdit()
        Dim json = JsonConvert.SerializeObject(Data.ToArray())
        File.WriteAllText(FileName, json)
    End Sub
End Class
Public Class Person
    Public Property Id As Integer
    Public Property FirstName As String
    Public Property LastName As String
End Class

Example 3 - Load and Save data in tab-delimited file (.tsv)

This method relies on manually parsing or formatting data from or to a .tsv file (tab delimitted). I assume you have a DataGridView on Form:

Imports System.ComponentModel
Imports System.IO
Public Class Form1
    Private FileName As String
    Private Data As BindingList(Of Person)
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FileName = Path.Combine(Application.StartupPath, "Data.tsv")
        Data = New BindingList(Of Person)
        If (File.Exists(FileName)) Then
            Dim lines = File.ReadAllLines(FileName)
            For Each line As String In lines
                Try
                    Dim segments = line.Split("\t")
                    Dim P = New Person
                    P.Id = Integer.Parse(segments(0))
                    P.FirstName = segments(1)
                    P.LastName = segments(2)
                    Data.Add(P)
                Catch ex As Exception
                    MessageBox.Show("Error loading record")
                End Try
            Next
        End If
        DataGridView1.DataSource = Data
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        Me.Validate()
        DataGridView1.EndEdit()
        Dim txt = String.Join(Environment.NewLine,
            Data.Select(Function(x)
                            Return String.Join("\t", x.Id, x.FirstName, x.LastName)
                        End Function).ToArray()
            )

        File.WriteAllText(FileName, txt)
    End Sub
End Class
Public Class Person
    Public Property Id As Integer?
    Public Property FirstName As String
    Public Property LastName As String
End Class