Avatar of vcharles
vcharles
 asked on

Help with displaying partial fields in DataGridView

Hi,

I'm using the code below to display data from 1 table to multiple DataGrid, how do I mofiy the code in part A to choose which fields I want to display in each table, the code in part B to display the fields take too long.

Part A:

Dim FilteredDT As DataTable
        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
        FilteredDT = DV.ToTable

        If FilteredDT.Rows.Count > 0 Then

            C1Screen1.DataSource = FilteredDT
            C1Screen2.DataSource = FilteredDT
            C1Screen3.DataSource = FilteredDT
            C1Screen4.DataSource = FilteredDT

Part B:

  Dim i As Integer
        i = 0
        For i = 14 To 86
            Me.C1Screen1.Columns(i).Visible = False
        Next
       
        For i = 1 To 13
            Me.C1Screen2.Columns(i).Visible = False
        Next
        For i = 23 To 86
            Me.C1Screen2.Columns(i).Visible = False
        Next

        For i = 1 To 22
            Me.C1Screen3.Columns(i).Visible = False
        Next
        For i = 41 To 86
            Me.C1Screen3.Columns(i).Visible = False
        Next

        For i = 1 To 40
            Me.C1Screen4.Columns(i).Visible = False
        Next
Visual Basic.NET

Avatar of undefined
Last Comment
vcharles

8/22/2022 - Mon
vcharles

ASKER
Hi,

The code below is an approach but I need to list the fields to be displayed, is there a way to include fields 1 to field 9 without listing them in the code?

  C1Screen1.DataSource = FilteredDT.DefaultView.ToTable(True, New String() {"Field1", "Field2", "Field3"..........})
Jacques Bourgeois (James Burger)

Since none of the columns is displayed in 2 tables, why don't you create 4 datatables, one for each column?

And by the way, you seem to be making a common error, by putting a single table in a DataSet (When to use a DataSet / DataTable / DataReader / Command)
vcharles

ASKER
Hi,

When working with xml, i find it easy to use a dataset, but will look at your lonk to try to improve the code.

If pssible would prefer to use one table to display different fields in multiple  Grids, but current  spproach is too slow, was hoping for a better approach  to acheive the same.

Thanks,

V.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jacques Bourgeois (James Burger)

The problem with using one big DataTable in 4 grids is probably that it takes up too much memory.

When you use DataBinding with a Control, the values contained in the DataTable need to be converted to strings in order to be displayed. You thus end up with a copy of most of the data, in text form.

When you fill 4 grids from the same DataTable, you are doing 4 of these copies. That is a lot of redundancy. And this is done even if you hide the columns as you are doing. So you are taking up a lot of space in memory for nothing.
vcharles

ASKER
The data is from an xml file, how would yoou create multiple tables with different  fields from an xml file? Since I am loading the xml to a dataset  and putting my results in a datatable, will still need to run my query fron one table.
Thanks.
vcharles

ASKER
Hi,

Below is the code I am using with a query string (Searchcriteria),  not sure how to create multiple table following this approach.

 Dim FilteredDT As DataTable
        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
        FilteredDT = DV.ToTable

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jacques Bourgeois (James Burger)

For the XML, I am sorry, but I do not have an answer. In the environments where I work, we deal directly with the databases and use XML only for small stuff. So, my knowledge of XML manipulations is limited to reading and writing a file as a whole.

There a probably ways to query a XML file, but this is not something I a familiar with. That might be another question to post to XML experts.

I do not know how much power you have over the generation of that XML, but if I had to do something like what you are trying to do, I would generate 4 XML files out of my database instead of getting out a big one that needs to be manipulated later. I would then simply create one DataTable from each file.

-----

As for the DataView, it let's you filter records and sort them, but it returns all the fields. There is no way to select the fields. As a way to prevent columns generation so that you do not need to hide them and end up with a lot of useless data in memory, it's not a solution.

But your reposting of the code let me see something that did not catch my attention the first time. You are creating a second DataTable from your DataView. This is overkill, because you end up with the original table + the table generated from the DataView. Another good way of filling up the memory with data that you do not use.

There are 2 ways you could help your code, depending on your needs. I do not know if it would completely solve your problem, but it could surely help.

If you do not need to keep the original table (dtsetlinkBEL.Tables(0)) after creating FilteredDT through the DataView, get rid of the first table and its dataset before hiding your columns. That might free a lot of memory if you have many records in the datatable.

            dtsetlinkBEL.Tables(0).Dispose()
            dtsetlinkBEL.Dispose()

If you need to keep the table in memory to enable the user to make a new selection form the same data, then drop FilteredDT, you do not need it.

A DataView is a limited window on a full DataTable. You can use it almost anywhere you use a DataTable. But it is still the original table that you are working with, not a copy. Make changes in the DataView, and they go in the DataTable. You could then simply use your DataView as the DataSource.

           C1Screen1.DataSource = DV
            C1Screen2.DataSource = DV
            C1Screen3.DataSource = DV
            C1Screen4.DataSource = DV

You would still end up with useless hidden fields in your columns, so the first part of this comment still holds. But it is a quick fix that might help you to get a better performance.
vcharles

ASKER
Hi,

Is it possible to add the results of my query to multiple tables by choosing which columns I want to add in each table? Noticed a columns property but unable to add the columns.


 Dim FilteredDT As DataTable
 Dim FilteredDT1 As DataTable

        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
          FilteredDT = DV.ToTable.Columns.?
          FilteredDT1 = DV.ToTable.Columns.?
vcharles

ASKER
Hi,

Thank you for the suggestions, will try them and get back to you, I had another suggestion as posted above but it may not be possible.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
vcharles

ASKER
Hi,

I'm using the code below to create and close the dataset.

Unfortunately the performance didn't seem to  improve using DV as datasource. Part B of the code n my initial post still causes the delay.

Dim curfile As String = (Application.StartupPath + "\linkSearch.xml")
            fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\linkSearch.xml", IO.FileMode.Open)
            dtsetlinkBEL.Clear()
            dtsetlinkBEL.ReadXml(fslinkBEL)
            fslinkBEL.Close()
vcharles

ASKER
Hi,

Is there a way to save the data in DV to an xml file? would than create 4 xml files save them to 4 data tables and display them in 4 grids.

V.
ASKER CERTIFIED SOLUTION
Jacques Bourgeois (James Burger)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
vcharles

ASKER
Thank you for all the information. Will try suspendlayout to see if it works a nit faster, the the system runs slowly only for the first query, sebsequent searches displays much faster, if going through the same process each time why is it much faster after the first search?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jacques Bourgeois (James Burger)

I could not answer that last one. I do not have all the code and all the properties you have set on the different components that you use. And the DataGridView is a complex control. Who knows how Microsoft implemented things in the background to optimize it.

One would need to use the debugger and analysis tools in order to have the whole picture. If you are in Visual Studio 2015, the Debug menu now has options for Diagnostic Tools that can be used to help you see the roadblocks in your application. It can help to understand stuff like that.
vcharles

ASKER
Thank You.