Results 1 to 13 of 13

Thread: DataTable & Grid Guidance

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Post DataTable & Grid Guidance

    Hello Everyone,

    My application in .net usually stores data in List of & normal arrays. Arrays are declared from structures. No DB is used.

    Code:
    E.g.
    
    Structure Man
    Dim Name as string
    Dim Age as integer
    Dim Gender as string
    End Structure
    
    Dim Person as new list of Man, Men() as Man  ' arrays
    When I populate a DataGridView then I loop through each item in structure array and populate it. This method I think is not efficient.

    I know how to use data tables but I have to manually create columns through code. However I have a few questions and will be grateful if anyone can answer them:


    1) If I use data tables then do I need to loop through my structure array in order to populate values in it
    2) Do I need to add column through code in data table
    3) If a value changes in datatable then how do I know as there are no events
    4) Is this the most efficient way or in my case is there a better way to populate grid control since data is stored in list of structure array.

    Thank you,

    Regards,
    GR

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: DataTable & Grid Guidance

    well you could use a XML File

    here a sample with Textboxes for input, and display in a Datagridview
    I made it as simple as possible

    Code:
    Public Class Form2
    
        Private dt As New DataTable("myUsers")
        Private bs As New BindingSource
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
            'save the Data when you close the Form
            Me.Validate()
            bs.EndEdit()
            dt.WriteXml("E:\Test.xml")
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' your columns
            dt.Columns.Add("Firstname", GetType(System.String))
            dt.Columns.Add("LastName", GetType(System.String))
    
            dt.Rows.Add("John", "Doe")
    
            ' XML load
            If IO.File.Exists("E:\Test.xml") Then
                dt.ReadXml("E:\Test.xml")
            End If
    
            ' bind  Datatable-Bindingsource
            bs.DataSource = dt
    
            ' bind Textboxes 
            Me.DataGridView1.DataSource = bs
            Me.TextBox1.DataBindings.Add("Text", bs, "Firstname")
            Me.TextBox2.DataBindings.Add("Text", bs, "Lastname")
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            ' add new line in Datagridview for Input
            bs.AddNew()
    
        End Sub
    End Class
    EDIT:
    but why not use a Database?
    if you use the .mdb Format, Access does not have to be installed

    here a sample how to create a Database with some Tables
    http://www.vbforums.com/showthread.p...base-with-Code

    hth
    Last edited by ChrisE; Feb 26th, 2020 at 05:22 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: DataTable & Grid Guidance

    Quote Originally Posted by ChrisE View Post
    well you could use a XML File

    here a sample with Textboxes for input, and display in a Datagridview
    I made it as simple as possible

    Code:
    Public Class Form2
    
        Private dt As New DataTable("myUsers")
        Private bs As New BindingSource
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    
            'save the Data when you close the Form
            Me.Validate()
            bs.EndEdit()
            dt.WriteXml("E:\Test.xml")
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ' your columns
            dt.Columns.Add("Firstname", GetType(System.String))
            dt.Columns.Add("LastName", GetType(System.String))
    
            dt.Rows.Add("John", "Doe")
    
            ' XML load
            If IO.File.Exists("E:\Test.xml") Then
                dt.ReadXml("E:\Test.xml")
            End If
    
            ' bind  Datatable-Bindingsource
            bs.DataSource = dt
    
            ' bind Textboxes 
            Me.DataGridView1.DataSource = bs
            Me.TextBox1.DataBindings.Add("Text", bs, "Firstname")
            Me.TextBox2.DataBindings.Add("Text", bs, "Lastname")
        End Sub
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            ' add new line in Datagridview for Input
            bs.AddNew()
    
        End Sub
    End Class
    EDIT:
    but why not use a Database?
    if you use the .mdb Format, Access does not have to be installed

    here a sample how to create a Database with some Tables
    http://www.vbforums.com/showthread.p...base-with-Code

    hth
    I will be happy if you can answer my questions. As of now I cannot use DB or XML.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: DataTable & Grid Guidance

    1) If I use data tables then do I need to loop through my structure array in order to populate values in it
    2) Do I need to add column through code in data table
    3) If a value changes in datatable then how do I know as there are no events
    4) Is this the most efficient way or in my case is there a better way to populate grid control since data is stored in list of structure array.
    to 1 + 2
    see the sample with XML

    to 3
    if you were to use a DataSet with a Database then you could use somthing like
    Code:
    Private Sub Form4_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Try
                objcmdBuilder = New OleDbCommandBuilder(objDataAdapter)
                '####################################
                objchanges = objDataSet.GetChanges() '  < was something changed ?
                If objchanges Is Nothing Then ' < no 
                    Exit Sub               ' < if no then exit
                End If
                '####################################
                'Data changed
                'ask the user
                If MsgBox("save changes ?", MsgBoxStyle.Critical + MsgBoxStyle.YesNo, "save Data !") = MsgBoxResult.Yes Then
                    objDataAdapter.Update(objchanges)
                    MsgBox("saved !")
                Else
                    MsgBox("no changes !")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub
    to 4
    use a Database
    you can use SQL and return only the Data you need
    Code:
    Select * From Customers Where Lastname Like 'A*'


    why can't you use XML or a Database ?
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: DataTable & Grid Guidance

    Quote Originally Posted by ChrisE View Post
    why can't you use XML or a Database ?
    Let's assume for a moment that this is a class assignment and it's a restriction for the time being....

    1) If I use data tables then do I need to loop through my structure array in order to populate values in it
    2) Do I need to add column through code in data table
    3) If a value changes in datatable then how do I know as there are no events
    4) Is this the most efficient way or in my case is there a better way to populate grid control since data is stored in list of structure array.
    1) If you use datatables, then you wouldn't use the structures... you would use one or the other... I'm not sure what looping you are currently doing to populate the array structure, but you with a datatable what you would do is create a new datarow, set each column in the dr, then add the dr to the dt.

    2) Yes... unless you create the datatable at design time using the designer. That creates a typed datatable which is more geared towards dealing with connected to databases. Never worked with one that was 100% disconnected though. Still, it only takes a line to create a column in the datatable.

    3) Depends on why you need to know something has changed and when. there are ways of binding the datatable to get that information.

    4) When it comes to a grid, the easiest way to populated and manage it is with a datatable and a BindingSource. Once you have your datatable, you create a bindingsource... set the dt as the DataSource of the bs.... then set the bs as the DataSource of the DataGridView.... now when you make changes to the dt, it is automatically reflected in the grid. This includes filtering, sorting, data changes, etc.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: DataTable & Grid Guidance

    Hello,

    The following is a conceptual example, not using a structure but a class implementing INotifyPropertyChanged interface and a custom interface coupled to a BindingList, only thing missing is automatic sorting but that can be easily remedied too.

    Note that the syntax may be different for your version of .NET Framework, this was done in a newer version of the .NET Framework.

    Code:
    Public Interface IPerson
        Property Id() As Integer
        Property FirstName() As String
        Property LastName() As String
        Property Age() As Integer
    End Interface
    Person class
    Code:
    Imports System.ComponentModel
    Imports System.Runtime.CompilerServices
    
    Public Class Person
        Implements INotifyPropertyChanged, IPerson
    
        Private _firstName As String
        Private _lastName As String
        Private _age As Integer
        Public Property Id As Integer Implements IPerson.Id
    
        Public Property FirstName() As String Implements IPerson.FirstName
            Get
                Return _firstName
            End Get
            Set
                _firstName = Value
                OnPropertyChanged()
            End Set
        End Property
        Public Property LastName() As String Implements IPerson.LastName
            Get
                Return _lastName
            End Get
            Set
                _lastName = Value
                OnPropertyChanged()
            End Set
        End Property
        Public Property Age() As Integer Implements IPerson.Age
            Get
                Return _age
            End Get
            Set
                _age = Value
                OnPropertyChanged()
            End Set
        End Property
        Public Overrides Function ToString() As String
            Return FirstName & " " & LastName
        End Function
    
        Public Event PropertyChanged As PropertyChangedEventHandler _
            Implements INotifyPropertyChanged.PropertyChanged
    
        Protected Overridable Sub OnPropertyChanged(<CallerMemberName> Optional ByVal propertyName As String = Nothing)
            PropertyChangedEvent?.Invoke(Me, New PropertyChangedEventArgs(propertyName))
        End Sub
    End Class
    Form code
    Code:
    Imports System.ComponentModel
    
    Public Class Form1
        Private _customersBindingList As New BindingList(Of Person)()
        '
        ' This is only to mock up data quickly and syntax may be different in your 
        ' version of the .NET Framework
        '
        Private ReadOnly _people As New List(Of Person) From {
            New Person() With {.Id = 1, .FirstName = "Karen", .LastName = "Payne", .Age = 45},
            New Person() With {.Id = 2, .FirstName = "Jean", .LastName = "Payne", .Age = 40}}
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            _customersBindingList = New BindingList(Of Person)(_people)
            DataGridView1.DataSource = _customersBindingList
        End Sub
    
        Private Sub CurrentButton_Click(sender As Object, e As EventArgs) Handles CurrentButton.Click
            If DataGridView1.CurrentRow IsNot Nothing OrElse DataGridView1.CurrentRow.IsNewRow = False Then
                Dim person = _customersBindingList(DataGridView1.CurrentRow.Index)
                MessageBox.Show(person.ToString())
            End If
    
        End Sub
    End Class
    If using a DataTable there are events to subscribe too.
    https://docs.microsoft.com/en-us/dot...tatable-events

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Question Re: DataTable & Grid Guidance

    Thank you very much Techgnome, kareninstructor & ChrisE for your replies.

    I will describe my case here so that you can figure out why I am using structures and not database.

    I have a web server which has a MYSQL database. In that database information of customers, products etc are stored.

    My .NET Windows application reads all that data and stored it in structure arrays. That structure array is then edited or it's data is populated in the Grid. Thus looping though the structure array and adding rows in grid is a method but its in-efficient and time consuming.

    Thus I feel I should to transfer data from structure array to datatable and then bind it and pass it to data grid.

    Let me know if you have any thoughts or suggestions.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: DataTable & Grid Guidance

    So you DO have a database... so you CAN select from the DATABASE and get a DATATABLE directly... I haven't seen anything in your statement that says why you can't use a database because by your own statement, you already are... so my guess is, you're already getting a datatable, or at least a datareader from it at the very least... so from there, it's a very simple leap to get a datatable that you can use to bind to a bindinsource to bind to a grid.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: DataTable & Grid Guidance

    Yeah, whatever you are doing to get the data from the database can almost certainly be easily changed to populate a datatable without going through the arrays of structures. If you need the arrays of structures for some other reason, you'd likely be better off going from DB to datatable to structure, as opposed to DB to structures to datatable.

    Going from the DB to a datatable would remove the need to construct the datatable in code, as it would be all set.

    One thing about datatables is that each row has a RowState property. This starts out as Unchanged. If you alter any field in the datatable, then the RowState for that row changes to Modified. If you delete a row from the datatable, the RowState changes to Deleted. If you add a row to the datatable, the rowstate changes to Added. So, you can check the RowState property to see if anything has changed for any row in the datatable.

    Another point is that you can call GetChanges to get a datatable with all the datarows that have a RowState that is not Unchanged. You can also call AcceptChanges or RejectChanges methods, both of which will result in the RowState going back to Unchanged, but, of course, RejectChanges has another effect, as well. Normally, you wouldn't have to call either of those, since they will be called automatically if you push the changes from the datatable back to the database using an Update call on a dataadapter. However, if you are populating the datatable from structures, I would expect that every new row you add will start out with a RowState of Added, so once you have added all the rows, you'll probably need to call AcceptChanges to reset the RowState, or else you won't really be able to track changes.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Thumbs up Re: DataTable & Grid Guidance

    Thank you Shaggy Hiker & techgnome.

    I just saw my code, its funny that I am reading from MySql DB into a datatable and then looping that table and populating data in structure array.

    When I can assign a datatable to a grid control and it shows values etc then what is the point of DataSet & Binding Control. Plus a datatable may have 50 columns but if I only wish to show 10 and edit my own column names then what to do?

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: DataTable & Grid Guidance

    There is no need for a dataset if you are only using one datatable, datasets are for organizing groups of datatables.

    You don't have to use a bindingsource but they do provide many helpful methods for working with the data.

    If you only want to show 10 columns then don't display the unwanted columns. Every column has a "visible" property. Also every column has a "Name" property and a "HeaderText" property.

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: DataTable & Grid Guidance

    I often don't use a bindingsource. It kind of depends on just what you want to do. If all you are doing is displaying the data, then a bindingsource, while simple, is likely overkill.
    My usual boring signature: Nothing

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Feb 2004
    Location
    India
    Posts
    517

    Re: DataTable & Grid Guidance

    Thanks. I just want to know one last thing. LINQ in datatable is not easy.

    For e.g. in case of structure array I did

    Code:
    Dim a = from b in ArrayX where b.age > 10 order by b.age desc
    
    OR 
    
    Dim a = from b in ArrayX where b.name <> "Jack" order by b.age
    how to do such a thing in datatable ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width